"Go" XML and DataTable parameters for the where in and like implementations of SQL Server parameterized queries

Reprint to: http://www.cnblogs.com/lzrabbit/archive/2012/04/29/2475427.html

In the previous SQL Server parameterized query where in and like implementations Jianzhong describes several implementations of the Where in SQL Server using parameterized queries, omitting XML and table-valued parameters, here's a supplement

Article guide

Scenario 5 Using XML parameters

Scenario 6 using the table-valued parameter tvp,datatable to pass the parameter

Summary of 6 Implementation scenarios

Scenario 5 Using XML parameters

Children's shoes unfamiliar with SQL Server XML type parameters need to understand the next XQuery concept, where the XQuery is simply the language used to find and extract elements and attributes from an XML document, which is simply the language used to query the XML speaking of which will lead 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, XPath methods are applicable to XQuery, if you have learned XPath, then learning XQuery is not a problem. See http://www.w3school.com.cn/xquery/xquery_intro.asp

The XQuery concept is understood to require further understanding of SQL Server support functions for XML, mainly query (), nodes (), exist (), value (), modify (), as detailed in http://msdn.microsoft.com/ Zh-cn/library/ms190798.aspx

There are two ways to implement where in with XML, using value and exist, where the exist method is recommended, as MSDN describes:

D. Using the exist () method without using the value () method

For performance reasons, do not use the value () method in the predicate to compare with the relationship value instead of the exist () with Sql:column ().

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

Implementation using the value method of XML (not recommended)

DataTable dt =NewDataTable ();using (SqlConnection conn =NewSqlConnection (connectionString)) {string XML =@"<root> <UserID>1</UserID> <UserID>2</UserID> <UserID>5</UserID> </ Root>"; SqlCommand Comm =Conn. CreateCommand ();//The use of the value method is not recommended, and the performance relative exist is lower comm.commandtext =@"SELECT * from the Users where exists (select 1 from @xml. Nodes ('/root/userid ') as T (c) where T.c.value (' text () [1] ', ' int ') = Us ERs. UserID)";//Can also be written like this, 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 of the exist method using XML (recommended)

DataTable dt =NewDataTable ();using (SqlConnection conn =NewSqlConnection (connectionString)) {string XML =@"<root> <UserID>1</UserID> <UserID>2</UserID> <UserID>5</UserID> </ Root>" Conn. CreateCommand (); //@ "select * from Users where @xml. exist ('/ Root/userid[text () =sql:column ("UserID" ")] =1 ; Comm. Parameters.Add (new SqlParameter ( " @xml  XML}); using (SqlDataAdapter adapter = new SqlDataAdapter (Comm)) {adapter. SelectCommand = Comm; adapter. Fill (DT); }} 

Examples of query methods for different XML structures are listed, often in practical use because different XML structures often break the brain

DataTable dt =NewDataTable ();using (SqlConnection conn =NewSqlConnection (connectionString)) {string XML =@"<root> <User> <UserID>1</UserID> </User> <User> <UserID>2</UserID> </User> <User> <UserID>5</UserID> </User> </root>"; SqlCommand Comm =Conn. CreateCommand ();//The use of the value method is not recommended, and the performance relative exist is lower comm.commandtext =@"SELECT * from the Users where UserID in (select T.c.value (' userid[1] ', ' int. ') from @xml. Nodes ('/root/user ') as T (c))";//Can also be written like this, the result is the same//Comm.commandtext = @ "SELECT * FROM Users// where exists // (// Select 1 from @xml. Nodes ('/root/user ') as T (c) //< Span style= "color: #008000;" > where T.c.value (' userid[1] ', ' int ') = Users.userid //) "; Comm. Parameters.Add (new SqlParameter ( " @xml  XML}); using (SqlDataAdapter adapter = new SqlDataAdapter (Comm)) {adapter. SelectCommand = Comm; adapter. Fill (DT); }} 

DataTable dt =NewDataTable ();using (SqlConnection conn =NewSqlConnection (connectionString)) {string XML =@"<root> <User> <UserID>1</UserID> </User> <User> <UserID>2</UserID> </User> <User> <UserID>5</UserID> </User> </root>" Conn. CreateCommand (); //@ "select * from Users where @xml. exist ('/ Root/user[userid=sql:column ("UserID" ")] ') =1" new SqlParameter ( " @xml  XML}); using (SqlDataAdapter adapter = new SqlDataAdapter (Comm)) {adapter. SelectCommand = Comm; adapter. Fill (DT); }} 

There are a few points to note when working with XML parameters:

1. Unlike SQL statements, which are not case-sensitive by default, the XQuery expressions of XML are case-sensitive, so be sure to write with case-sensitivity attention

2. Use double quotes for column names in Sql:column () when using exist, such as Sql:column ("userid"), if you want to enter two single quotes Sql:column (' userid ') without using single quotes

3. Either where in or in other cases using XML query can use exist (see clearly not SQL exists) method with the exist method, we do not deliberately pursue performance optimization, but can be handy for it.

Scenario 6 using Table-valued parameters (table-valued Parameters abbreviation TVP SQL Server2008 start support)

The TVP parameter, as described in MSDN, has excellent performance when the amount of data is less than 1000, and the TVP can be referenced http://msdn.microsoft.com/en-us/library/bb510489.aspx

This article mainly describes how to use TVP to implement a DataTable collection parameter implementation where in

1. Using table-valued parameters, first create a table-valued function in the database

int

2. When the table-valued function is created, make a C # call,

Note the point:

1. You need to set SqlDbType in SqlParameter to sqldbtype.structured and then you need to set TypeName to the name of the table-valued function created in the database, in this example INTCOLLECTIONTVP

2. The constructed Datatabel column must be the same as the table-valued function definition, with the exact column name, without having to match the column name defined by the table-valued function, the data type can be arbitrary, but also the recommendation and the consistency of the table-valued type definition, one eliminates the implicit type conversion, Secondly, you can filter out illegal parameters when initializing Datatabel.

3. It is recommended to define TVP when the type in the query condition is consistent with the TVP corresponding field type, which avoids the performance penalty of implicit type conversion

DataTable RESULTDT =NewDataTable ();using (SqlConnection conn =NewSqlConnection (connectionString)) {SqlCommand Comm =Conn. CreateCommand (); Comm.commandtext =@"SELECT * from the Users (NOLOCK) where exists (select 1 from @MyTvp TVP where Tvp.id=users.userid)";//Constructs a TVP DataTable DataTable TVPDT that requires a referenceNewDataTable ();//To add columns to a table, the number of columns needs to be consistent with the table-valued function INTCOLLECTIONTVP, and the column names can be different tvpDt.Columns.Add ("myID",typeofInt));//Add Data TvpDt.Rows.Add (1); TVPDT.ROWS.ADD (2); TVPDT.ROWS.ADD (34< Span style= "color: #000000;" >); //new SqlParameter ( " @MyTvp  "intcollectiontvp" Span style= "color: #000000;" > }); using (SqlDataAdapter adapter = new SqlDataAdapter (Comm)) {adapter. SelectCommand = Comm; adapter. Fill (RESULTDT); }} 

Summarize:

At this point, summed up 66 kinds of where parameterization implementation, respectively, as follows

1. Using charindex or like to implement where in parameterization

2. Implementing the Where in parameterization using exec dynamic execution of SQL

3. Generate a parameter for each parameter implement where in parameterization

4. Using temporal tables to implement where in parameterization

5. Implementing where in parameterization using XML parameters

6. Implementing where in parameterization using table-valued parameters (TVP)

The first 4 examples are listed in the where in and like implementations of SQL Server parameterized queries in a detailed article

6 methods, 6 kinds of ideas,

Where Method 1 equals the full deprecated index, if no special needs are not recommended,

Method 2 is essentially a combination of SQL no difference with its method 2 self-deception is not as good as directly splicing SQL to the benefits

Method 3 is limited by the number of parameters (long 2,100 parameters), and if the parameters too many of the performance to be verified, you can use as appropriate

The temporary table used in the method 4 example can actually be better than the table variable performance, but it's a little cumbersome, and it's better to encapsulate it into a function (recommended)

Method 5 using the XML parameter, since there is this type of description should be good performance, others will be better than splicing SQL, the use is also more convenient, but require developers to have a certain understanding of XML query (recommended)

Method 6 TVP SQL server2008 can be used later, very good and powerful, if only for where in the words can be defined a few TVP where in the problem is easy to solve, and is strongly typed and easier to understand (recommended)

It's best to comment on a particular method, or the old adage is the best.

"Go" XML and DataTable parameters for the where in and like implementations of SQL Server parameterized queries