此方法只要传入表名,字段名,跟值就好了,不用写sql语句,很大程度的节省了开发时间,与代码量
建一个类文件,方便调用,
/// <summary>
/// 执行sql插入语句,返回受影响的行数
/// </summary>
/// <param name="TableName">要插入的表名</param>
/// <param name="ct">操作类型</param>
/// <param name="dic">字段名数组</param>
/// <returns></returns>
public int Insert(string TableName, CommandType ct,Dictionary<string,object> dic)
{
string str1 = "", str2 = "";
if (dic.Count > 0)
{
open();
SqlCommand comm = new SqlCommand();
foreach (KeyValuePair<string, object> kvp in dic)//遍历数组的key,value;
{
comm.Parameters.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
if (str1 == "" && str2 == "")
{
str1 += kvp.Key;
str2 += "@" + kvp.Key;
}
else
{
str1 += "," + kvp.Key;
str2 += ",@" + kvp.Key;
}
}
try
{
comm.CommandText = "insert into " + TableName + "(" + str1 + ") values (" + str2 + ")";
comm.Connection = conn;
comm.CommandType = ct;
int i = comm.ExecuteNonQuery();
return i;
}
catch { return 0; }
finally
{
close();
}
}
else
{
return 0;
}
}
/// <summary>
/// 执行sql修改语句,返回受影响的行
/// </summary>
/// <param name="TableName">要修改的表名</param>
/// <param name="ct">操作类型</param>
/// <param name="dic">字段名数组</param>
/// <param name="where">条件语句</param>
/// <returns></returns>
public int Update(string TableName, CommandType ct, Dictionary<string, object> dic, string where)
{
string str = "";
if (dic.Count > 0)
{
open();
SqlCommand comm = new SqlCommand();
foreach (KeyValuePair<string, object> kvp in dic)
{
comm.Parameters.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
if (str == "")
{
str += kvp.Key + "=@" + kvp.Key;
}
else
{
str += "," + kvp.Key + "=@" + kvp.Key;
}
}
try
{
comm.CommandText = "update " + TableName + " set " + str + " where " + where;
comm.Connection = conn;
comm.CommandType = ct;
int i = comm.ExecuteNonQuery();
return i;
}
catch { return 0; }
finally
{
close();
}
}
else
{
return 0;
}
}
上面是类文件的代码,这里是调用的方法
Dictionary<string, object> dic = new Dictionary<string, object>();
dic.Add("Title", ProductTitle);
dic.Add("addtime", AddTime);
//新建一个Dictionary数组,第一个参数为键值,我们用来传字段名;
//第二个参数为value值,我们用来传对应的字段的值;
db.Insert("SW_Product", CommandType.Text, dic);
//调用插入的方法,第一个为表名,第二个为操作类型,第三个为数组
int id = cm.ChkClng(Request.QueryString["id"].ToString());
db.Update("SW_Product", CommandType.Text, dic, "id=" + id);
//调用修改的方法,第一个为表名,第二个为操作类型,第三个为数组,第四个为条件语句
如果大家还有更好的方法,欢迎留言,一起探讨;