Scenario 5 Using XML parameters
Children's shoes unfamiliar with SQL Server XML type parameters need to understand the concept of XQuery first, where XQuery is a language used to find and extract elements and attributes from XML documents, simply the language used to query XML. That would lead to XPath, In fact, XPath is a subset of XQuery, and XQuery 1.0 and XPath 2.0 share the same data model and support the same functions and operators, and the XPath method applies to XQuery, and if you've learned XPath, then learning XQuery is fine. Refer to Http://www.jb51.net/w3school/xquery/xquery_intro.htm
The XQuery concept requires a better understanding of the SQL Server support function 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 you recommend using the exist method, 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 to the relationship value and instead to the exist () with Sql:column ().
Http://msdn.microsoft.com/zh-cn/library/ms178030.aspx
Implemented using the XML value method (not recommended)
Copy Code code as follows:
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 ();
Using the value method is not recommended, and performance is relatively exist low
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 be written like this, 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);
}
}
Using the XML exist method implementation (recommended)
Copy Code code as follows:
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 ();
Using the exist method of XML to achieve higher 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);
}
}
Examples of query methods with different XML structures are enumerated, often in practical use because different XML structures often break the brain
Copy Code code as follows:
DataTable dt = new DataTable ();
using (SqlConnection conn = new SqlConnection (connectionString))
{
string xml = @ "
<root>
<User>
<UserID>1</UserID>
</User>
<User>
<UserID>2</UserID>
</User>
<User>
<UserID>5</UserID>
</User>
</root> ";
SqlCommand COMM = conn. CreateCommand ();
Using the value method is not recommended, and performance is relatively exist low
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 be written like this, 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);
}
}
Copy Code code as follows:
DataTable dt = new DataTable ();
using (SqlConnection conn = new SqlConnection (connectionString))
{
string xml = @ "
<root>
<User>
<UserID>1</UserID>
</User>
<User>
<UserID>2</UserID>
</User>
<User>
<UserID>5</UserID>
</User>
</root> ";
SqlCommand COMM = conn. CreateCommand ();
Using the exist method of XML to achieve higher 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);
}
}
You need to be careful when using XML parameters:
1. Unlike SQL statements default case-insensitive, XML XQuery expressions are strictly case-sensitive, so be sure to write with a case
2. The column names in Sql:column () using exist are required to use double quotes, such as Sql:column ("UserID"), which require continuous input of two single quotes Sql:column (' UserID ') without the use of single quotes.
3. Regardless of where in or other situations using the XML query can be used exist (see clearly not in the SQL of the exists) method to use the exist method, we do not deliberately pursue performance optimization, but can conveniently for the words why not.
Scenario 6 uses table-valued parameters (table-valued Parameters short TVP Sql Server2008 start support)
According to the MSDN Description TVP parameters have excellent performance when the amount of data is less than 1000, about TVP can refer to http://msdn.microsoft.com/en-us/library/bb510489.aspx
This article mainly describes how to implement the DataTable collection parameters using TVP to implement where in
1. Using table-valued parameters, first create a table-valued function in the database
Create Type INTCOLLECTIONTVP as Table (ID int)
2. The table-valued function is created to make a C # call,
Note the point:
1. You need to set the SqlDbType in SqlParameter to sqldbtype.structured and then you need to set typename the name of the table-valued function created in the database, in this example, INTCOLLECTIONTVP
2. The number of Datatabel columns to be constructed must be the same as the table-valued function, where the specific column name is arbitrary, without the same column name defined by the table-valued function, the data type can be arbitrary, but the recommendation is consistent with the definition of the table value type, which eliminates implicit type conversions. Secondly, the illegal parameters can be filtered out when the Datatabel is initialized.
3. It is recommended that the definition of TVP be consistent with the type in the query condition and the TVP corresponding field type, which avoids the performance loss of implicit type conversions
Copy Code code 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
)";
Constructs a TVP DataTable that requires a reference
DataTable TVPDT = new DataTable ();
Add a column 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", typeof (int));
Add data
TVPDT.ROWS.ADD (1);
TVPDT.ROWS.ADD (2);
TVPDT.ROWS.ADD (3);
TVPDT.ROWS.ADD (4);
The typename here corresponds to our defined table-valued function name
Comm. Parameters.Add (New SqlParameter ("@MyTvp", sqldbtype.structured) {Value = TVPDT, TypeName = "INTCOLLECTIONTVP"});
Using (SqlDataAdapter adapter = new SqlDataAdapter (comm))
{
Adapter. SelectCommand = comm;
Adapter. Fill (RESULTDT);
}
}
Summarize:
At this point, a total of 66 where parameterized implementations are summarized, respectively, as follows
1. Using charindex or like to implement where in parameterization
2. Use exec dynamic Execute SQL to implement where in parameterization
3. Generate a parameter for each parameter to implement where in parameterization
4. Using temporary tables to implement where in parameterization
5. Implement where in parameterization using XML parameters
6. Using table-valued parameters (TVP) to implement where in parameterization
The top 4 examples are enumerated and illustrated in the where and like implementations of SQL Server parameterized queries
6 ways, 6 kinds of ideas,
Where Method 1 is equivalent to completely discarding the index, if no special needs are not recommended,
Method 2 in essence, it doesn't make any difference how to use the Method 2 self-deception its people are not as good as directly splicing SQL
Method 3 is limited by the number of parameters (more than 2,100), and if too many parameters are passed to be validated, they can be used as appropriate
Method 4 In the example of the temporary table, in fact, can be changed to table variables performance may be better, but the writing is a bit cumbersome, can be specific packaging into a function will be better (recommended)
Method 5 Using XML parameters, since there is this type of performance should be good, others will be better than the concatenation of SQL, but also more convenient to use, but need developers to XML queries have a certain understanding of the line (recommended)
Method 6 TVP Mode SQL server2008 can be used later, very good and powerful, if only for where in the words can define a few TVP where in the problem is easy to solve, but also strong typing is easier to understand (recommended)
It's best to comment on a specific method, or the old adage is the right one.
This article belongs to the Lazy Fat rabbit Original
