调试带参数的Sql语句

函数概述： 输入参数数组和含有参数的Sql语句，返回一个用实际值替换了参数的Sql语句。如果参数有问题，如Sql语句中有的参数在参数数组中找不到或者参数的值为null(nothing in VB)，则返回错误信息。

下面的函数是用VB写的，适用于SqlServer

'参数：

'strSql 待替换参数值的sql字符串

'params参数数组

'返回值：替换后的字符串

'注意：strSql字符串中的参数后面必须是空白字符，右括号，逗号中的一种；并且不可以有两个@@；不区分参数的大小写

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 Then
Return "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 If

 Next
 If haveParam = False Then
 Return "parameter " & paramName & " in SQL does not exsit in param()"
 End If

 End While
 Return strSql
 End Function

上个函数的C#版本：
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|,|/))";
   System.Text.RegularExpressions.Regex re = 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;
    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));
      }
     }
    }
    if( haveParam == false )
    {
     return "parameter " + paramName + " in SQL does not exsit in paramsArr";
    }
   }
   return strSql;
  }

下面的函数是用C#写的，适用于PL/SQL

'参数：

'strSql 待替换参数值的sql字符串

'Params 参数数组

'返回值：替换后的字符串

'注意：strSql字符串中的参数后面必须是空白字符，右括号，逗号中的一种；并且不可以有两个::；不区分参数的大小写

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.ToString().Trim() != "")
{
 mt = re.Match(strSql);

 paramName = mt.Groups[0].Value.ToString().Trim(',',')',' ');

 haveParam = false;
 foreach(OracleParameter param in Params)
{
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 null!");
}
 if(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('" + ((DateTime)param.Value).ToString("yyyy/MM/dd") + "','yyyy/mm/dd')");
 else
 strSql = strSql.Insert(mt.Groups[0].Index, param.Value.ToString());
}

}

if(haveParam == false)
return("parameter " + paramName + " in SQL does not exsit in Params[]");
}
return strSql;
 }