Sqlserver in 实现 参数化查询 XML类型解决方案

1：如果 参数 是int 类型 ： declare @a xml set @a=' rowid1/id/row rowid5/id/row rowid4/id/row rowid3/id/row rowid2/id/row' select * from product where id in ( select d.x.value('./id[1]','int') from @a.nodes('/*') as d(x)) 2：如果 参数 是var

1：如果参数是int类型：

declare @a xml
set @a='
1
5
4
3
2'
select * from product where id in (
select d.x.value('./id[1]','int') from @a.nodes('/*') as d(x))

 

2：如果参数是varchar类型：

declare @a xml
set @a='
a
b5
c4
d3
e2'
select * from product where pname in (
select d.x.value('./name[1]','varchar(100)') from @a.nodes('/*') as d(x))

 

以第一个为例写的C#简单方法

    <span>public</span> DataSet GetData(Listint><span> idList) {
        System.Text.StringBuilder idXML </span>= <span>new</span><span> System.Text.StringBuilder();
        </span><span>//</span><span> 把IdList转换成idxml（后面要用到的xml<strong>参数</strong>的值）</span>
        <span>foreach</span> (<span>var</span> item <span>in</span><span> idList)
        {
            idXML.AppendFormat(</span><span>"</span><span><row><id>{0}</id></row></span><span>"</span><span>,item);
        }

        System.Text.StringBuilder strSql </span>= <span>new</span><span> System.Text.StringBuilder();
        strSql.Append(</span><span>"</span><span>select * from product where id in (</span><span>"</span><span>);
        </span><span>//</span><span> 解析xml<strong>参数</strong>@a 取出ID 这里可以认为@a是一个特殊的表</span>
        strSql.Append(<span>"</span><span>select d.x.value('./id[1]','int') from @a.nodes('/*') as d(x)</span><span>"</span><span>);

        strSql.Append(</span><span>"</span><span>)</span><span>"</span><span>);
        SqlConnection con </span>= <span>new</span> SqlConnection(<span>"</span><span>数据库连接字符串</span><span>"</span><span>);
        SqlCommand cmd </span>= <span>new</span><span> SqlCommand(strSql.ToString(), con);
        </span><span>//</span><span> <strong>参数</strong>赋值</span>
        SqlParameter[] para = <span>new</span><span> SqlParameter[]{
            </span><span>new</span> SqlParameter(<span>"</span><span>@a</span><span>"</span>,SqlDbType.Xml){Value=<span> idXML.ToString()}
        };
        cmd.Parameters </span>=<span> para;

        </span><span>//</span><span> <strong>查询</strong></span>
        SqlDataAdapter sda = <span>new</span><span> SqlDataAdapter(cmd);
        DataSet ds </span>= <span>new</span><span> DataSet();
        sda.Fill(ds);
        </span><span>return</span><span> ds;
    }</span>

 

如果认为是好文的请不要吝啬您的推荐，写个文章不容易。