SQL Server, Access database query Confusing point and C # parameterspecifies the length of the advantages and disadvantages of parameter

 

at school often do some websites, so this internship in the company, the group leader for the first time a company's website or quickly accomplished . Middle and did not encounter any major problems, but still met two very easy to confuse the novice small problems, so come up with to share with you.

 

divided into two main contents: 1) SQL Server, Access database query miscible point; 2) C # parameter specifies the length of the parameter in the performance of the program

 

1) SQL Server, Access database query miscible point

 

learning SQl all know, we have to realize fuzzy query in the database is to use the Like statement, while using some wildcards to complete the query of fuzzy matching.

 

first wildcard is "%", as long as any character is to complete the match (in the Access database using the "*"); second wildcard "_ "is the completion of a single character matching;

 

but what is in the program look like?

 

website because I did use the Access database, when I was in the program using the "*" wildcard query the database, in any event in its own DataTable instance created of the object is always no data, just began to suspect that DataAdapter can not use the "Like" This command statement. After the final test found that if used in the program "%" operator, there will be data.

 

While in Access use the "*" to complete fuzzy matching, but the program is still in C # To use the "%" to complete fuzzy matching.

 

2) C # in parameter specifies the length of the program's performance parameters effects

 

In fact, I myself have a lingering question is about the SqlParameter object is instantiated, which we will go to the specified parameters, parameter types, and parameter length (note that many times we are both omitted this step). Example of a statement such as the following methods:

 

  

1             //显式指定参数长度 
2             SqlParameter parameter1 = new SqlParameter("@home", SqlDbType.NChar, 4); 
3             //不指明参数长度 
4             SqlParameter parameter2 = new SqlParameter("@home", SqlDbType.NChar);

 

 

in the end we specify the length of the program parameters have no effect on the performance of it.

 

on Microsoft's MSDN, explained that "in the parameter declaration, if not explicitly set, then from the actual size of the specified parameter values ​​inferred that size."

 

we write a small program, and then use SQL Server2008 comes with SQL Server Profiler to test look at this process it.

 

code is as follows:

 

  

 1 public DataTable GetTable() 
 2         { 
 3             SqlConnection con = new SqlConnection(strConnection); 
 4             SqlCommand cmd = new SqlCommand("select * from testsql5 where name=@Name", con); 
 5  
 6             //这里两句来测试程序语句获取参数的方法 
 7             //SqlParameter parameter = new SqlParameter("@Name", SqlDbType.NVarChar); 
 8             SqlParameter parameter = new SqlParameter("@Name", SqlDbType.NVarChar,8); 
 9  
10             parameter.Value = "nijiushi"; 
11             cmd.Parameters.Add(parameter); 
12  
13             SqlDataAdapter adapter = new SqlDataAdapter(cmd); 
14             DataTable dt = new DataTable(); 
15             adapter.Fill(dt); 
16  
17             return dt; 
18  
19         }

 

 

in the program, is executed first uncommented code, in the SQL Server Profiler to see the execution results are as follows:

 

< / span>

 

this time explicitly specified as a parameter in length, so tracking results seen in length of the parameter nvarchar (8) .

 

let us try to specify the length of the parameter ratio of the length of the given parameter worth little. Code is as follows:

 

  

1  SqlParameter parameter = new SqlParameter("@Name", SqlDbType.NVarChar,6);

 

 

tracking results are as follows:

 

< / span>

 

we can see is the length of time parameters has become nvarchar (6) , and the parameter values ​​are truncated to "nijius"

 

then no named parameter length, the program is sqlDbType.NChar know the parameters passed to face length or length of the parameter parameter assignment know it ?

 

top notes and then we execute the code, which is the length of the parameter is not specified, the code and see the results as follows:

 

< / span>

 

this time we see the test software to track the result is the same, but you can see is the bottom of the parameter length has been identified as nvarchar (8) . We can try to change the value of the parameter to look at. For example, I changed the value of the parameter "nijiu" , look at the test results are as follows:

 

< / span>

 

Thus, we can know that the procedure is through the back of the parameter values ​​to find the length of the parameter , rather than by the type of parameter SqlDbType.NVarChar

 

So in the end so what impact does the program?

 

statement for the same length of the parameter in SQl server will perform the same execution plan, but for a different length of the parameter, then generates a new execution plan so When the parameters explicitly specified length of time will speed up the sql execution speed.