Function Overview: Enter the parameter array and the SQL statement containing the parameter, returns a SQL statement that replaces the parameters with the actual value. If the parameters have problems, such as the parameters in the SQL statement cannot find or the value of the parameter is null (NOTHING IN VB) in the parameter array, returns an error message.
The following function is written with VB, suitable for SQLServer
'parameter:
'strsql SQL string to replace the parameter value
'params parameter array
'Return Value: Replace the string
'Note: Behind the parameters in the strsql string must be one of the blank characters, the right brackets, and the comma; and there is no two @@; no case in size
Function ResolveSql (ByVal strSql As String, ByVal params () As System.Data.SqlClient.SqlParameter) Dim mt As System.Text.RegularExpressions.Match Dim gp As System.Text.RegularExpressions.Group Dim cp As System.Text.RegularExpressions. Capture Dim param As SqlClient.SqlParameter Dim paramName As String Dim returnString As String Dim haveParam As Boolean = False Dim expr As String = "@ / w * (/ b |, | /))" Dim re As New System.Text.RegularExpressions .RegEX (expr, system.text.regularExpressions.RegexOptions.ignorecase)
While Re.match (strsql) .Groups (0) .Value.toString (). Trim () <> "" Mt = Re.match (strsql)
Paramname = mt.groups (0) .Value.toString (). Trim (",)") HaveParam = False for Each Param in params
If param.ParameterName = paramName Then haveParam = True strSql = strSql.Remove (mt.Groups (0) .Index, mt.Groups (0) .Length) If param.Value is Nothing ThenReturn "parameter" & param.ParameterName & " 's value is nothing! "end if if param.value.gettype.tostring =" system.string "or param.value.gettype.tostring =" system.date "or param.value.gettype.tostring =" system.datetime "THEN STRSQL = strsql.insert (mt.groups (0) .index," '"& param.value &" ") Else strsql = strsql.insert (mt.groups (0) .index, param.value) End IF
End ifnext if HaveParam = false the return "Parameter" & paramname & "in SQL Does Not Exit in param ()" endiff
End while return strsql end function
C # version of functions: public static string ResolveSql (string strSql, System.Data.SqlClient.SqlParameter [] paramsArr) {System.Text.RegularExpressions.Match mt; string paramName; bool haveParam = false; string expr = @ "@ / w * (/ b |
While (RE.Match (strsql) .Groups [0] .value.toString (). Trim ()! = "") {mt = re.match (strsql); paramname = mt.groups [0] .value.toString () .Trim (',', ')'); HaveParam = false; foreach (system.data.sqlclient.sqlparameter param in paramsarr) {if (param.ParameterName.Equals (paramname) {haveparam = true; strsql = STRSQL.Remove (Mt.Groups [0] .index, mt.groups [0] .length); if (param.value == null) Return "Parameter" param.ParameterName "'s value is nothing!";
IF (param.value.gettype (). Tostring () == "system.string" || param.value.gettype (). TOSTRING () == "system.date" || param.value.gettype (). Tostring () == "system.datetime") {strsql = strsql.insert (mt.groups [0] .index, "'" param.value ");} else {strsql = strsql.insert (MT .Groups [0] .index, convert.tostring (param.value);}}} f (HaveParam == false) {Return "parameter" paramname "in sql does not exsit in paramsarr";}} return strsql } The following function is written with C #, suitable for PL / SQL 'parameters:' strsql to replace parameter value of SQL string 'parameter array' return value: Replace the string 'Note: STRSQL string Behind the parameters must be a blank character, right bracket, comma; and can not have two ::; no case in size
public static string ResolveOracleSql (string strSql, System.Data.OracleClient.OracleParameter [] Params) {System.Text.RegularExpressions.Match mt; string paramName; bool haveParam = false; string expr = @ ": / w * (/ b | , | /)) "; System.Text.RegularExpressions.Regex RE = New System.text.RegularExpressions.Regex (expr, system.text.regularexpressions.RegexOptions.ignoreCase);
While (Re.match (strsql) .Groups [0] .value.toTString (). Trim ()! = "") {mt = Re.match (strsql);
Paramname = mt.groups [0] .Value.Tostring (). Trim (',', ')', ''); HaveParam = false; foreach (OracleParameter Param in params) {ix (param.Parametername.Equals (paramname )) {Haveparam = true; strsql = strsql.remove (mt.groups [0] .index, mt.groups [0] .length); if (param.value == null) {Return ("Parameter param. ParameterName "'s value is null!");} F (param.value.gettype (). Tostring () == "system.string") strsql = strsql.insert (mt.groups [0] .index, " '" param.value "'); else if (param.value.gettype (). Tostring () == "system.date" || param.value.gettype (). Tostring () == "System .Datetime ") strsql = strsql.insert (mt.groups [0] .index," to_date ('" ((" YYYY / MM / DD ") ",' YYYY / mm / dd ') "); else strsql = strsql.insert (mt.groups [0] .index, param.value.toString ());}}
IF ("Parameter" paramname "in sql does NOT EXSIT in params []");} Return strsql;}
