Sql Server参数化查询之where in和like实现详解（在和像实现详解在SQL Server参数化查询之）.doc

Sql Server参数化查询之where in和like实现详解（在和像实现详解在SQL Server参数化查询之） As a small program ape, can not be avoided to where and in and like in dealing with the daily development, then in most cases the parameters we pass is not easy to do single quotes, sensitive character escape directly into the fight SQL, query, fix. If one day you inevitably need to improve the query performance of SQL, you need a one-time where in, hundreds, thousands, or even tens of thousands of data, parameterized query will be the inevitable choice. However, how to implement the parameterized query of where, in and like is a headache for many people. Implementation of parameterized query of where in First of all, let us talk about the commonly used method, the direct spelling SQL implementation, in general, can meet the needs 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 (); } An attempt to take a parameterized query; it is obvious that following this, SQL will make a false error 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 (); } Obviously this will report error: failed in the varchar value of1,2,3,4into the int data type, because the parameter of type string, where in will use @UserID as a string, equivalent to the actual implementation of the following statement Select *, from, Users (nolock), where, UserID, in (1,2,3,4) If the executed statement is string type, the SQL executes without error and certainly does not query any results Using (SqlConnection, Conn = new, Sql