Using a regular expression, the SQL Server database supports the location parameter syntax !, Sqlserver

Source: Internet
Author: User

Using a regular expression, the SQL Server database supports the location parameter syntax !, Sqlserver

. In. NET Framework 2.0, Microsoft. data. the Common namespace defines a set of classes for programmers to write data access code for different databases, and provides a standard demonstration in the Enterprise Library. However, a major problem is that when programmers access the database using the SQL method with parameters, everything becomes meaningless. Because SQL Server supports name parameters prefixed with @ and Oracle supports name parameters prefixed with:, when OleDb is used to Access, is it used? As the placeholder location parameter syntax. When writing a program, you must write SQL statements for different ADO. NET providers.

 


This article will introduce a way to enable SQL Server and Oracle to support? Is the placeholder location parameter syntax. To achieve this effect, you need to set? Replace the placeholder with the corresponding name parameter. The following uses the SQL Server database as an example. Assume there is an SQL statement:

select count(0) from sys_user where user_code=? and password=?

If you can change it:

select count(0) from sys_user where user_code=@p1 and password=@p2

So that SQL Server also supports? Is the placeholder location parameter.

 

 

During this transformation, I used a regular expression. Using this regular expression, you can set all? Locate the placeholder and replace it with the location parameter prefixed with @. The Code is as follows:

Private string PreparePlaceHolder (string srcSql) {string _ sqlTokenPattern = "[\ s] + | (? <String> '([^'] | '') * ') | (? <Comment> (/\ * ([^ \ *] | \ * [^/]) * \ */) | (--. *) | (? <Parametermarker> \\?) | (? <Query> select) | (? <Identifier> ([\ p {Lo} \ p {Lu} \ p {Ll} \ p {Lm} \ p {Nd} \ uff3f _# $] +) | (\ "([^ \"] | \ "\") * \ ") | (? <Other> .) "; Regex sqlTokenParser = new Regex (_ sqlTokenPattern, RegexOptions. explicitCapture); List <Group> groups = new List <Group> (); bool flag = false; for (Match match = sqlTokenParser. match (srcSql); Match. empty! = Match; match = match. NextMatch () {if (! Match. groups ["comment"]. success) {if (match. groups ["comment"]. success | match. groups ["string"]. success) | match. groups ["other"]. success) {flag = true;} else if (match. groups ["query"]. success) {if (! Flag) {// here, it indicates that this is a select statement.} Else if (match. Groups ["parametermarker"]. Success) {// here,? Placeholder. Groups. add (match. groups ["parametermarker"]) ;}} StringBuilder desSql = new StringBuilder (srcSql); for (int I = groups. count-1; I> = 0; I --) {Group group = groups [I]; desSql. remove (group. index, group. length); desSql. insert (group. index, ParameterToken + "p" + (I + 1);} return desSql. toString ();}

The above method can be used to compile a more general SQLHelper class. Programmers can use the location parameter syntax to execute SQL statements at any time without considering whether the corresponding ADO. NET Provider supports location parameters.

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.