復制代碼 代碼如下:

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();
}

復制代碼 代碼如下: