SQL Server parameterized query-Implementation of wherein and like-passing parameters in xml and DataTable

In the previous article, wherein and like implementation of SQL Server parameterized query describes several Implementation Solutions for SQL Server parametric query of wherein. xml and Table value parameters are omitted. Here is a supplement.

In the previous description of SQL Server parameterized query's where in and like implementation, we introduced several implementation schemes for SQL Server to use parameterized query where in, with xml and Table value parameters missing, here is a supplement

Solution 5 use xml Parameters

If you are not familiar with SQL server xml type parameters, you must first understand the XQuery concept. Here, an XQuery is a language used to search for and extract elements and attributes from XML documents, simply put, the language used to query xml leads to XPath. In fact, XPath is a subset of XQuery. XQuery 1.0 and XPath 2.0 share the same data model, the same functions and operators are supported. The XPath method applies to XQuery. If you have learned XPath, there is no problem in learning XQuery. For details, see

After understanding the XQuery concept, you need to learn more about SQL Server's support functions for xml, mainly including, and. For details, see

There are two ways to implement where in using xml: value and exist. The exist method is recommended here, as described in msdn:

D. Use the exist () method instead of the value () method.
For performance reasons, the value () method is not used in the predicate to compare with the relational value, but the exist () method with SQL: column () is used instead ().


Implementation using the xml value Method (not recommended)
The Code is as follows:
DataTable dt = new DataTable ();
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
String xml = @"

1
2
5
";
SqlCommand comm = conn. CreateCommand ();
// The value method is not recommended, and the performance is lower than that of exist.
Comm. CommandText = @ "select * from Users
Where exists
(
Select 1 from @ xml. nodes ('/root/userid') as T (c)
Where T. c. value ('text () [1] ', 'int') = Users. UserID
)";

// It can also be written in this way, and the result is the same
// Comm. CommandText = @ "select * from Users
// Where UserID in
//(
// Select T. c. value ('text () [1] ', 'int') from @ xml. nodes ('/root/userid') as T (c)
//)
Comm. Parameters. Add (new SqlParameter ("@ xml", SqlDbType. Xml) {Value = xml });
Using (SqlDataAdapter adapter = new SqlDataAdapter (comm ))
{
Adapter. SelectCommand = comm;
Adapter. Fill (dt );
}
}

Implementation using the xml exist method (recommended)
The Code is as follows:
DataTable dt = new DataTable ();
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
String xml = @"

1
2
5
";
SqlCommand comm = conn. CreateCommand ();

// Use the xml exist method to achieve high performance.
Comm. commandText = @ "select * from Users where @ xml. exist ('/root/UserID [text () = SQL: column ("" UserID "")]') = 1 ";
Comm. Parameters. Add (new SqlParameter ("@ xml", SqlDbType. Xml) {Value = xml });
Using (SqlDataAdapter adapter = new SqlDataAdapter (comm ))
{
Adapter. SelectCommand = comm;
Adapter. Fill (dt );
}
}

Lists examples of query methods of different xml structures. In actual use, different xml structures often cause headaches.
The Code is as follows:
DataTable dt = new DataTable ();
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
String xml = @"


1


2


5

";
SqlCommand comm = conn. CreateCommand ();

// The value method is not recommended, and the performance is lower than that of exist.
Comm. CommandText = @ "select * from Users
Where UserID in
(
Select T. c. value ('userid [1] ', 'int') from @ xml. nodes ('/root/user') as T (c)
)";
// It can also be written in this way, and the result is the same
// Comm. CommandText = @ "select * from Users
// Where exists
//(
// Select 1 from @ xml. nodes ('/root/user') as T (c)
// Where T. c. value ('userid [1] ', 'int') = Users. UserID
//)";
Comm. Parameters. Add (new SqlParameter ("@ xml", SqlDbType. Xml) {Value = xml });
Using (SqlDataAdapter adapter = new SqlDataAdapter (comm ))
{
Adapter. SelectCommand = comm;
Adapter. Fill (dt );
}
}

The Code is as follows:
DataTable dt = new DataTable ();
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
String xml = @"


1


2


5

";
SqlCommand comm = conn. CreateCommand ();
// Use the xml exist method to achieve high performance.
Comm. commandText = @ "select * from Users where @ xml. exist ('/root/User [UserID = SQL: column ("" UserID "")]') = 1 ";

Comm. Parameters. Add (new SqlParameter ("@ xml", SqlDbType. Xml) {Value = xml });
Using (SqlDataAdapter adapter = new SqlDataAdapter (comm ))
{
Adapter. SelectCommand = comm;
Adapter. Fill (dt );
}
}

Note the following when using xml parameters:

1. Unlike SQL statements, which are case insensitive by default, xml XQuery expressions are case sensitive.

2. when you use exist, the column names in SQL: column () must use double quotation marks, such as SQL: column ("UserID"). If you do not want to use single quotation marks, you must enter two single quotation marks consecutively. SQL: column (''userid '')

3. whether it is where in or other cases, the exist method is used when using xml queries (it is clear that it is not the exists in SQL). We do not deliberately pursue performance optimization, but if you can do it well, why not.

Solution 6 Use Table value Parameters (Table-Valued Parameters (TVP SQL Server2008)
According to msdn, The TVP parameter has excellent performance when the data volume is smaller than 1000. For more information about TVP, see

This section describes how to use TVP to implement where in for passing parameters in a able set.
1. To use table value parameters, first create a table Value Function in the database
Create type IntCollectionTVP as Table (ID int)
2. Call c # after the table value function is created,
Note:
1. Set SqlDbType in SqlParameter to SqlDbType. Structured, and set TypeName to the name of the Table value function created in the database. In this example, IntCollectionTVP is used.
2. the number of columns of the constructed Abel must be the same as that defined by the table value function. The specific column names are free of choice and do not need to be consistent with the column names defined by the table value function. The data type can be random, however, we recommend that you maintain consistency with the table value type definition to avoid implicit type conversion, and filter out invalid parameters when initializing the Abel.
3. When defining tvp, it is recommended that the type in the query condition be consistent with that of the field corresponding to tvp, so as to avoid performance loss caused by implicit type conversion.
The Code is as follows:
DataTable resultDt = new DataTable ();
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
SqlCommand comm = conn. CreateCommand ();
Comm. CommandText = @ "select * from Users (nolock)
Where exists
(
Select 1 from @ MyTvp tvp
Where tvp. ID = Users. UserID
)";
// Construct the TVP DataTable for parameter passing
DataTable tvpDt = new DataTable ();
// Add columns to a table. The number of columns must be consistent with that of the IntCollectionTVP function. The column names can be different.
TvpDt. Columns. Add ("myid", typeof (int ));
// Add data
TvpDt. Rows. Add (1 );
TvpDt. Rows. Add (2 );
TvpDt. Rows. Add (3 );
TvpDt. Rows. Add (4 );
// The TypeName corresponds to the table value function name defined here
Comm. Parameters. Add (new SqlParameter ("@ MyTvp", SqlDbType. Structured) {Value = tvpDt, TypeName = "IntCollectionTVP "});
Using (SqlDataAdapter adapter = new SqlDataAdapter (comm ))
{
Adapter. SelectCommand = comm;
Adapter. Fill (resultDt );
}
}

Summary:
At this point, six where parameter implementations are summarized, as shown below:
1. Use CHARINDEX or like to implement where in parameterization
2. Use exec to dynamically Execute SQL to implement where in parameterization
3. Generate a parameter for each parameter to implement where in parameterization
4. Use a temporary table to implement where in parameterization
5. Use xml parameters to implement where in parameterization
6. Use the table value parameter (TVP) to implement where in parameterization
The first four of them are listed and used as examples in the where in and like implementations of SQL Server parameterized queries.
6 methods, 6 ideas,
Method 1 means that the index is completely deprecated. It is not recommended if there is no special need,
Method 2 essentially does not differ in the combination of SQL statements. method 2 deceiving itself is not as cost-effective as directly splicing SQL statements.
Method 3 is limited by the number of parameters (more than 2100 parameters). If too many parameters are passed, how can they be verified?
In method 4, the temporary table used in the example may have better performance if it can be changed to a table variable, but it is complicated to write it. It is better to encapsulate it into a function (recommended)
Method 5: use xml to pass parameters. Since this type indicates that the performance should be good, other parameters are much better than concatenated SQL statements, which is also convenient to use, however, developers must have a certain understanding of xml queries (recommended)
Method 6 tvp: SQL server2008 can be used later. It is very powerful. If only the where in method is used, several tvp where in problems can be defined and easily solved, it is strongly typed and easier to understand (recommended)
It is not good to comment on the specific method, or the old saying is the best.

This article is an original Lazy fat rabbit