Access sql語(yǔ)句構(gòu)建（規(guī)避單引號(hào)）

簡(jiǎn)單的一個(gè)執(zhí)行Update的語(yǔ)句，條件確定，當(dāng)然這樣很傻，尚待提高，代碼如下：

public bool ExeHashTableSqlstr(Hashtable ht, string TableName, string where)
{
List<OleDbParameter> ParList = new List<OleDbParameter>();
foreach (DictionaryEntry item in ht)//key 是字段名 value是當(dāng)前值,對(duì)應(yīng)屬性里有各自的數(shù)據(jù)類(lèi)型
{
ParList.Add(new OleDbParameter("@" + item.Key.ToString(), item.Value));
}
#region 構(gòu)建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;
}
}

這里有兩個(gè)有意思的地方，

用OleDbParameter和OleDbCommand 兩個(gè)對(duì)象來(lái)調(diào)用sql語(yǔ)句，不需要對(duì)任何字符串的sql語(yǔ)句預(yù)處理，如包含單引號(hào)，換行符等。

參考以下：OleDbParameter參數(shù)的使用

運(yùn)用參數(shù)可防止一些sql攻擊

public bool judIDPW(string CustomerName, string CustomerPassword)
{
OleDbParameter par1 = new OleDbParameter();
par1.ParameterName = "@CustomerName";
par1.Value = CustomerName;
OleDbParameter par2 = new OleDbParameter();
par2.ParameterName = "@CustomerPassword";
par2.Value =Security.Encrypt(CustomerPassword);

OleDbCommand cmd = new OleDbCommand("select CustomerID from Customers where CustomerName=@Customer and CustomerPassword=@CustomerPassword", con);
cmd.Parameters.Add(par1);
cmd.Parameters.Add(par2);
con.Open();
OleDbDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
con.Close();
return true;
}
else
{
con.Close();
return false;
}
}

使用用OleDbParameter(String, Object)的技巧：

List<OleDbParameter> ParList = new List<OleDbParameter>();
foreach (DictionaryEntry item in ht)//key 是字段名 value是當(dāng)前值,對(duì)應(yīng)屬性里有各自的數(shù)據(jù)類(lèi)型
{
ParList.Add(new OleDbParameter("@" + item.Key.ToString(), item.Value));
}

public bool ExeHashTableSqlstr(Hashtable ht, string TableName, string where)
{
List<OleDbParameter> ParList = new List<OleDbParameter>();
foreach (DictionaryEntry item in ht)//key 是字段名 value是當(dāng)前值,對(duì)應(yīng)屬性里有各自的數(shù)據(jù)類(lèi)型
{
ParList.Add(new OleDbParameter("@" + item.Key.ToString(), item.Value));
}
#region 構(gòu)建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;
}
}

生活不易，碼農(nóng)辛苦
如果您覺(jué)得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)