最近刚接触EntityFramework6,不能说EF不好用吧,就是感觉宠大,还各种配置,要做批量更新先全查出来再一行行执行update,这种执行效率就不用说了,开发代码写起来也感觉不容易(可能刚接触),最终我还是放弃用EF,写了个通用的DbHelper,和网上的差不多,但写到insert或update的时候感觉也挺费时间的,故写了如下两个方法,用于插入和更新。
Insert
public int Insert(string tableName, DbTransaction tr, object obj)
{
if (_type == DbContextType.Oracle)
{
throw new NotImplementedException("该方法暂不支持Oracle!");
}
var str = _type == DbContextType.MySql ? "?" : "@";
var objType = obj.GetType();
var data = objType.GetProperties().ToDictionary(p => p.Name, p => p.GetValue(obj, null));
var sql = string.Format("INSERT INTO {0}({2}) VALUES ({1}{3})",
tableName, str, string.Join(",", data.Keys), string.Join("," + str, data.Keys));
using (var cmd = _conn.CreateCommand())
{
this.Open();
cmd.CommandText = sql;
cmd.Transaction = tr;
foreach (string key in data.Keys)
{
var p = cmd.CreateParameter();
p.ParameterName = key;
p.Value = data[key] ?? DBNull.Value;
cmd.Parameters.Add(p);
}
return cmd.ExecuteNonQuery();
}
}
Update
public int Update(string tableName, DbTransaction tr, object values, object where)
{
if (_type == DbContextType.Oracle)
{
throw new NotImplementedException("该方法暂不支持Oracle!");
}
var str = _type == DbContextType.MySql ? "?" : "@";
var fields = values.GetType().GetProperties()
.ToDictionary(p => p.Name, p => p.GetValue(values, null));
var sb = new StringBuilder();
sb.AppendFormat("UPDATE {0} ", tableName);
sb.AppendFormat("SET {0} ", string.Join(", ",
fields.Keys.ToList().ConvertAll<string>(key => key + "=" + str + key).ToArray()));
using (var cmd = _conn.CreateCommand())
{
foreach (string key in fields.Keys)
{
var p = cmd.CreateParameter();
p.ParameterName = key;
p.Value = fields[key] ?? DBNull.Value;
cmd.Parameters.Add(p);
}
if (where != null)
{
sb.Append("WHERE ");
if (where is String)
{
sb.Append(where.ToString());
}
else
{
var wheres = where.GetType().GetProperties()
.ToDictionary(p => p.Name, p => p.GetValue(where, null));
sb.Append(string.Join(", ", // eg: field1=@_field1;
wheres.Keys.ToList().ConvertAll<string>(key => key + "=" + str + "_" + key).ToArray()));
foreach (string key in wheres.Keys)
{
var p = cmd.CreateParameter();
p.ParameterName = "_" + key;
p.Value = wheres[key] ?? DBNull.Value;
cmd.Parameters.Add(p);
}
}
}
cmd.CommandText = sb.ToString();
cmd.Transaction = tr;
this.Open();
return cmd.ExecuteNonQuery();
}
}
以下是使用前后对比:
使用前:
// insert
db.Execute("insert into TUser (name,age,sex) values(@name,@age,@sex)",
new DbParameter[]{
new SqlParameter("name","小明"),
new SqlParameter("age",18),
new SqlParameter("sex","男"),
}
);
// update
db.Execute("update TUser set name=@name, age=@age, sex=@sex where id=@id",
new DbParameter[]{
new SqlParameter("name","小明"),
new SqlParameter("age",18),
new SqlParameter("sex","男"),
new SqlParameter("id",1001),
}
);
可以看到,每个字段名均出现三次,坑爹。再看使用后:
// insert
db.Insert("TUser", new
{
name = "小明",
age = 18,
sex = "男"
});
// update
db.Update("TUser", new
{
name = "小明",
age = 18,
sex = "男"
}, new{ id = 1001 });
其实有人看的对吧?初次发贴,不知道内容写的清不清楚,对你有没有用。欢迎拍砖!欢迎吐槽!