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

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.