SQL Server parameterized query-where in and like implementation-passing parameters through XML and datatable

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

ArticleGuide

Solution 5 use XML Parameters

Solution 6 use the table value parameter TVP and datatable to pass Parameters

Summary of six implementation schemes

 

Solution 5 use XML Parameters

If you are not familiar with SQL Server XML type parameters, you must first understand the XQuery concept.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 and support the same functions and operators. The XPath method applies to XQuery. If you have learned XPath, so learning XQuery will not be a problem. See http://www.w3school.com.cn/xquery/xquery_intro.asp

After understanding the concept of XQuery, You need to further understand the SQL server support functions for XML, mainly query (), nodes (), exist (), value (), modify (), see the http://msdn.microsoft.com/zh-cn/library/ms190798.aspx

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.

value () method in a predicate to compare with a relational value, use exist () with SQL: column () . "> for performance reasons, value () is not used in the predicate. Instead, use the SQL: column () exist () .

Http://msdn.microsoft.com/zh-cn/library/ms178030.aspx

Implementation using the XML Value Method (not recommended)

Datatable dt = New  Datatable ();  Using (Sqlconnection conn = New  Sqlconnection (connectionstring )){  String Xml =@"  <Root> <userid> 1 </userid> <userid> 2 </userid> <userid> 5 </userid> </root>  "  ; Sqlcommand comm = Conn. createcommand ();  //  It is not recommended to use the value method. 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)

Datatable dt = New  Datatable ();  Using (Sqlconnection conn = New  Sqlconnection (connectionstring )){  String Xml = @"  <Root> <userid> 1 </userid> <userid> 2 </userid> <userid> 5 </userid> </root>  "  ; 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.

Datatable dt = New  Datatable ();  Using (Sqlconnection conn = New  Sqlconnection (connectionstring )){  String Xml = @"  <Root> <user> <userid> 1 </userid> </user> <userid> 2 </userid> </user> <userid> 5 </userid> </user> </root> "  ; Sqlcommand comm = Conn. createcommand ();  //  It is not recommended to use the value method. 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 );}} 

Datatable dt = New  Datatable ();  Using (Sqlconnection conn = New Sqlconnection (connectionstring )){  String Xml = @"  <Root> <user> <userid> 1 </userid> </user> <userid> 2 </userid> </user> <userid> 5 </userid> </user> </root>  "  ; 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, TVP parameters have excellent performance when the data volume is smaller than 1000. For TVP, refer to http://msdn.microsoft.com/en-us/library/bb510489.aspx.

This section describes how to use TVP to implementParameter passing in a able setImplement where in

1. To use table value parameters, first create a table Value Function in the database

CreateType intcollectiontvpAs Table(IDInt)
 

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.

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 able for passing Parameters 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 we have defined. 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.