Access sql语句构建（规避单引号）

简单的一个执行Update的语句，条件确定，当然这样很傻，尚待提高，
代码如下：

public bool ExeHashTableSqlstr(Hashtable ht, string TableName, string where)
        {
            List<OleDbParameter> ParList = new List<OleDbParameter>();
            foreach (DictionaryEntry item in ht)//key 是字段名 value是当前值,对应属性里有各自的数据类型
            {
                ParList.Add(new OleDbParameter("@" + item.Key.ToString(), item.Value));
            }
	    #region 构建sqlStr
            string contents = " ";
            Int16 count = 0;
            foreach (OleDbParameter item in ParList)
            {
                contents += item.ParameterName.Substring(1);
                contents += "=";
                contents += item.ParameterName;
                count++;
                if (count != ht.Count)
                {
                    contents += ", ";
                }
            }
            string sqlString = "UPDATE " + TableName + " SET " + contents;
            if (where != "")
            {
                sqlString += " where " + where + ";";
            }
            else
            {
                sqlString += ";";
            }
	    #endregion
            Open();
            OleDbCommand cmd = new OleDbCommand(sqlString, mConn);
            foreach (OleDbParameter par in ParList)
            {
                cmd.Parameters.Add(par);
            }
            if (cmd.ExecuteNonQuery() >= 1)
            {
                Close();
                return true;
            }
            else
            {
                Close();
                return false;
            }
        }

这里有两个有意思的地方，

用OleDbParameter和OleDbCommand 两个对象来调用sql语句，不需要对任何字符串的sql语句预处理，如包含单引号，换行符等，参考链接：OleDbParameter参数的使用　　
使用用OleDbParameter(String, Object)的技巧

List<OleDbParameter> ParList = new List<OleDbParameter>();
            foreach (DictionaryEntry item in ht)//key 是字段名 value是当前值,对应属性里有各自的数据类型
            {
                ParList.Add(new OleDbParameter("@" + item.Key.ToString(), item.Value));
            }

参考链接：

 1 public bool ExeHashTableSqlstr(Hashtable ht, string TableName, string where)
 2         {
 3             List<OleDbParameter> ParList = new List<OleDbParameter>();
 4             foreach (DictionaryEntry item in ht)//key 是字段名 value是当前值,对应属性里有各自的数据类型
 5              {
 6                 ParList.Add(new OleDbParameter("@" + item.Key.ToString(), item.Value));
 7             }
 8         #region 构建sqlStr
 9             string contents = " ";
10             Int16 count = 0;
11             foreach (OleDbParameter item in ParList)
12             {
13                 contents += item.ParameterName.Substring(1);
14                 contents += "=";
15                 contents += item.ParameterName;
16                 count++;
17                 if (count != ht.Count)
18                 {
19                     contents += ", ";
20                 }
21             }
22             string sqlString = "UPDATE " + TableName + " SET " + contents;
23             if (where != "")
24             {
25                 sqlString += " where " + where + ";";
26             }
27             else
28             {
29                 sqlString += ";";
30             }
31         #endregion
32             Open();
33             OleDbCommand cmd = new OleDbCommand(sqlString, mConn);
34             foreach (OleDbParameter par in ParList)
35             {
36                 cmd.Parameters.Add(par);
37             }
38             if (cmd.ExecuteNonQuery() >= 1)
39             {
40                 Close();
41                 return true;
42             }
43             else
44             {
45                 Close();
46                 return false;
47             }
48         }
49

posted on 2010-05-03 10:12 秋波渡阅读(1443) 评论(0) 编辑收藏举报