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  

转载于:https://www.cnblogs.com/wuenping/archive/2010/05/03/1726236.html