SqlServer参数化查询之where in和like实现详解

复制代码 代码如下:

string userIds = "1,2,3,4";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = string.Format("select * from Users(nolock) where UserID in({0})", userIds);
comm.ExecuteNonQuery();
}

复制代码 代码如下:

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from Users(nolock) where UserID in(@UserID)";
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();
}

复制代码 代码如下:

select * from Users(nolock) where UserID in('1,2,3,4')

复制代码 代码如下:

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from Users(nolock) where UserName in(@UserName)";
comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, -1) { Value = "'john','dudu','rabbit'" });
comm.ExecuteNonQuery();
}

复制代码 代码如下:

select * from Users(nolock) where UserName in('''john'',''dudu'',''rabbit''')

复制代码 代码如下:

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//使用CHARINDEX，实现参数化查询，可以复用查询计划，同时会使索引失效
comm.CommandText = "select * from Users(nolock) where CHARINDEX(','+ltrim(str(UserID))+',',','+@UserID+',')>0";
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();
}

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//使用like，实现参数化查询，可以复用查询计划，同时会使索引失效
comm.CommandText = "select * from Users(nolock) where ','+@UserID+',' like '%,'+ltrim(str(UserID))+',%' ";
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();
}

复制代码 代码如下:

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//使用exec动态执行SQL
　　//实际执行的查询计划为(@UserID varchar(max))select * from Users(nolock) where UserID in (1,2,3,4)
　　//不是预期的(@UserID varchar(max))exec('select * from Users(nolock) where UserID in ('+@UserID+')')
comm.CommandText = "exec('select * from Users(nolock) where UserID in ('+@UserID+')')";
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();
}

复制代码 代码如下: