Sql Server参数化查询之where in和like实现详解（国外英文资料）.doc

Sql Server参数化查询之where in和like实现详解（国外英文资料） Apes as a small program, in the day-to-day development and where you cant avoid to dealing in and the like, in most cases we do simple parameters not order after quote, sensitive character escaping spell directly into the SQL, execute queries, done. If one day you will inevitably need to improve the query performance of SQL, need one-time where hundreds, thousands, or even tens of thousands of article in the data, the parameterized queries will be the inevitable choice. However, how to implement the parameterized query of where in and like is a headache for many people. The parameterized query implementation of where in First of all, lets talk about how we can do it in a straightforward way, and in general we can do that String userIds = 1, 2, 3, 4; Using (SqlConnection conn = new SqlConnection (connectionString)) { Conn. The Open (); SqlCommand comm = new SqlCommand (); Comm. Connection = conn; ( select * from Users (nolock) where UserID in ({0}). ) Comm. ExecuteNonQuery (); } The attempt to parameterize queries is obvious, and it is obvious that the following execution of SQL will be a mistake Using (SqlConnection conn = new SqlConnection (connectionString)) { Conn. The Open (); SqlCommand comm = new SqlCommand (); Comm. Connection = conn; Comm.Com mandText = select * from Users (nolock) where UserID in (@userid); Comm. Parameter.add ( @userid , sqldbtype.varchar, -1) {Value = 1, 2, 3, 4}) Comm. ExecuteNonQuery (); } Obviously this will quote error: the varchar value is converted to data type int 1, 2, 3, 4 failed, because of type string, the where in @ UserID as a string to it during the processing, equivalent to actually perform the following statement Select * from Users (nolock) where UserID in ( 1, 2, 3, 4 ) If the executed statement is a string type, the SQL execution will not be incorrect, and of course no results will be queried Using (SqlConnection conn = new SqlConnection (connectionString)) { Conn. The Open ();