轻量级DbHelper之Insert和Update方法

最近刚接触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 });

其实有人看的对吧？初次发贴，不知道内容写的清不清楚，对你有没有用。欢迎拍砖！欢迎吐槽！