As a small program ape, in the daily development can not avoid to and where in and like to deal with, in most cases we pass the parameters of simple quotes, sensitive word escape directly after the SQL, execute the query, fix. If one day you inevitably need to improve SQL query performance, need a one-time where in hundreds of, 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 problem that many people have a headache.
parameterized query implementation of where in
First of all, we often use the method, directly spell SQL implementation, generally can meet the needs
Copy Code code as follows:
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 ();
}
When a parameterized query is required, it is obvious that executing SQL will error errors
Copy Code code as follows:
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 ();
}
It's obvious that this would be an error: the conversion of the varchar value ' 1,2,3,4 ' to a data type int failed because the parameter type is a string, where in will treat the @userid as a string, which is equivalent to actually executing the following statement
Copy Code code as follows:
SELECT * from Users (NOLOCK) where UserID in (' 1,2,3,4 ')
If the executed statement is of type string, SQL execution does not make an error, and of course no results are queried.
Copy Code code as follows:
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 ();
}
This will not hold any errors, nor can you find the desired results, because this @username is treated as a string, which is actually equivalent to executing the following statement
Copy Code code as follows:
SELECT * from Users (NOLOCK) where UserName in (' John ', ' Dudu ', ' rabbit ')
Therefore, I believe that everyone can not get the correct result for why the simple where in is the argument. Well, let's take a look at how to implement the correct parameterized execution where in, to really implement parameterization where in the argument, a lot of adultery comes up with alternative solutions.
Scenario 1, using charindex or like methods to implement parameterized queries, there is no doubt that this method is successful, and successful reuse of the query plan, but also thoroughly let the query index invalidation (this does not discuss the index topic), the result is a full table scan, if the data volume is large, millions, Tens even more, such a writing would have disastrous consequences; If the data volume is small, just want to rely on parameterized implementation to prevent SQL injection this is understandable, or depends on the specific needs. (Not recommended)
Copy Code code as follows:
using (SqlConnection conn = new SqlConnection (connectionString))
{
conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
//Use CHARINDEX to implement parameterized queries that can reuse the query plan while invalidating the index
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;
//Use like to implement parameterized queries that can reuse the query plan while invalidating the index
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 ();
}
Scenario 2 uses EXEC to execute SQL dynamically, this style of writing is undoubtedly very successful, and the code is also more elegant, also played a role in preventing SQL injection, looks perfect, but this type of writing and direct SQL execution is not a substantial difference between the query plan is not reused, for performance improvement does not help , it's quite a way to get off the pants, but it's a solution. (Not recommended)
Copy Code code as follows:
using (SqlConnection conn = new SqlConnection (connectionString))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
Using exec to execute SQL dynamically
The query plan actually executed is (@UserID varchar (max)) SELECT * from Users (NOLOCK) where UserID in (1,2,3,4)
Not expected (@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 ();
}
Scenario 3 generates a parameter for each parameter in the where in, the writing is more cumbersome, the number of parameters transmitted is limited, up to 2,100, you can use this scheme as needed (recommended)
Copy Code code as follows:
using (SqlConnection conn = new SqlConnection (connectionString))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
Add a parameter to each piece of data
Comm.commandtext = "SELECT * from Users (NOLOCK) where UserID in (@UserID1, @UserId2, @UserID3, @UserID4)";
Comm. Parameters.addrange (
New sqlparameter[]{
New SqlParameter ("@UserID1", SqlDbType.Int) {Value = 1},
New SqlParameter ("@UserID2", SqlDbType.Int) {Value = 2},
New SqlParameter ("@UserID3", SqlDbType.Int) {Value = 3},
New SqlParameter ("@UserID4", SqlDbType.Int) {Value = 4}
});
Comm. ExecuteNonQuery ();
}
Scenario 4 is implemented using temporary tables (which can also be better with table variables), writing implementation is more cumbersome, you can write a generic where in the temporary table Query method, for a rainy, personal more highly praised this style, can make the query plan to be reused and the index can be effectively used , but because you need to create a temporary table, will bring additional IO overhead, if the query frequency is very high, each time the data is recommended to use Scheme 3, if the number of query data, especially thousands or even tens of thousands of times, it is strongly recommended to use this scheme, can bring tremendous performance improvement (highly recommended)
Copy Code code as follows:
using (SqlConnection conn = new SqlConnection (connectionString))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
String sql = @ "
DECLARE @Temp_Variable varchar (max)
CREATE TABLE #Temp_Table (Item varchar (max))
while (LEN (@Temp_Array) > 0)
Begin
if (CHARINDEX (', ', @Temp_Array) = 0)
Begin
Set @Temp_Variable = @Temp_Array
Set @Temp_Array = '
End
Else
Begin
Set @Temp_Variable = Left (@Temp_Array, CHARINDEX (', ', @Temp_Array)-1)
Set @Temp_Array = Right (@Temp_Array, LEN (@Temp_Array)-len (@Temp_Variable)-1)
End
Insert into #Temp_Table (Item) VALUES (@Temp_Variable)
End
SELECT * from the Users (NOLOCK) where exists (select 1 from #Temp_Table (NOLOCK) where #Temp_Table. Item=users.userid)
drop table #Temp_Table ";
Comm.commandtext = SQL;
Comm. Parameters.Add (New SqlParameter ("@Temp_Array", SqlDbType.VarChar,-1) {Value = "1,2,3,4"});
Comm. ExecuteNonQuery ();
}
Like parameterized query
Like query according to personal custom to write wildcard characters in parameter values or in the SQL concatenation can be, the two methods perform the same effect, not in detail here
Copy Code code as follows:
using (SqlConnection conn = new SqlConnection (connectionString))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
Write% to parameter values
Comm.commandtext = "SELECT * from the Users (NOLOCK) where UserName like @UserName";
Comm. Parameters.Add (New SqlParameter ("@UserName", SqlDbType.VarChar) {Value = "rabbit%"});
Comm. ExecuteNonQuery ();
}
using (SqlConnection conn = new SqlConnection (connectionString))
{
Conn. Open ();
SqlCommand comm = new SqlCommand ();
Comm. Connection = conn;
Stitching% in SQL
Comm.commandtext = "SELECT * from Users (NOLOCK) where UserName like @UserName + '% '";
Comm. Parameters.Add (New SqlParameter ("@UserName", SqlDbType.VarChar) {Value = "rabbit%"});
Comm. ExecuteNonQuery ();
}
See Tom Soup and Mosquito Amount comments Add the following XML parameters and TVP, and make a simple summary of 6 scenarios
SQL Server parameterized queries where in and like implementations of XML and DataTable parameters
This article belongs to the Lazy Fat rabbit Original
