EF6+MYSQL之初体验

初次使用EF6+MYSQL

这次的项目时间可拉得够长的,定制开发就是这样。客户真正用上了才能基本上不再改了。起先项目是php实现的，改造成桌面程序。用.net winform开发,像这种小项目肯定要用EF了。以前一直用4.0，用DB First，把向导生成的模型扩展一下，另写一个分部类来实现bulkcopy等这种特殊需求。一直都是这样简单的用着，懒得花时间成本去搞高深的AOP/IOC/泛型工厂... 有时候就是越简单越好，当只会捉老鼠的猫就好。这次依然还是使用的DB First。

经验总结：

1、EF6之CRUD

EF向导自动转换成的DbContext类:

public partial class POS_Entities : DbContext
    {
        public POS_Entities()
            : base("name=POS_Entities")
        {
        }
     
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
     
        public virtual DbSet<bm_brandclasses> bm_brandclasses { get; set; }
        public virtual DbSet<bm_brandsubclasses> bm_brandsubclasses { get; set; }
        public virtual DbSet<bm_membermng> bm_membermng { get; set; }
        public virtual DbSet<bm_memcard_privilege> bm_memcard_privilege { get; set; }
        public virtual DbSet<bm_memcardclasses> bm_memcardclasses { get; set; }
        public virtual DbSet<bm_pointmng> bm_pointmng { get; set; }
        public virtual DbSet<ls_busimng> ls_busimng { get; set; }
        public virtual DbSet<ls_cashierlist> ls_cashierlist { get; set; }
        public virtual DbSet<ls_cashierlist_detail> ls_cashierlist_detail { get; set; }
        public virtual DbSet<ls_cashiernum> ls_cashiernum { get; set; }
        public virtual DbSet<ls_commoditymng> ls_commoditymng { get; set; }
        public virtual DbSet<ls_couponmng> ls_couponmng { get; set; }
        public virtual DbSet<ls_couponmng_list> ls_couponmng_list { get; set; }
        public virtual DbSet<ls_handovermng> ls_handovermng { get; set; }
        public virtual DbSet<ls_promotionclasses> ls_promotionclasses { get; set; }
        public virtual DbSet<ls_promotionmng> ls_promotionmng { get; set; }
        public virtual DbSet<ls_promotionmng_subact> ls_promotionmng_subact { get; set; }
        public virtual DbSet<ls_promotionsubclasses> ls_promotionsubclasses { get; set; }
        public virtual DbSet<sa_manager> sa_manager { get; set; }
        public virtual DbSet<sa_num_auto> sa_num_auto { get; set; }
        public virtual DbSet<sa_role> sa_role { get; set; }
        public virtual DbSet<ls_leasemng> ls_leasemng { get; set; }
    }

我的扩展类：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

public partial class POS_Entities
    {
        private static readonly BindingFlags bf = BindingFlags.Instance | BindingFlags.Public | BindingFlags.Static | BindingFlags.NonPublic;
 
        private static readonly Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
        public string ConnectionString
        {
            get
            {
                var ecb = new EntityConnectionStringBuilder(config.ConnectionStrings.ConnectionStrings["POS_Entities"].ConnectionString);
                return ecb.ProviderConnectionString;
            }
        }
        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteSql(string sql)
        {
            return this.Database.ExecuteSqlCommand(sql, new object[] { });
        }
 
        /// <summary>
        /// 返回查询的第一行第一列的值
        /// </summary>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sqlText)
        {
            var db = this.Database.Connection;
            try
            {
                if (db.State != ConnectionState.Open)
                    db.Open();
                var cmd = db.CreateCommand();
                cmd.CommandText = sqlText;
                return cmd.ExecuteScalar();
            }
            finally
            {
                db.Close();
            }
        }
 
        /// <summary>
        /// 使用同一个事物批量提交SQL
        /// </summary>
        /// <param name="sqls"></param>
        public bool BatchExecuteSqlWithTrans(List<string> sqls)
        {
            var dbConn = this.Database.Connection;
            if (dbConn.State != ConnectionState.Open)
                dbConn.Open();
            var cmd = dbConn.CreateCommand();
            var st = dbConn.BeginTransaction();
            try
            {
                HashSet<string> list = new HashSet<string>();
                cmd.Transaction = st;
                sqls.ForEach(delegate (string sqlTxt)
                {
                    cmd.CommandText = sqlTxt;
                    cmd.ExecuteNonQuery();
                });
                st.Commit();
                return true;
            }
            catch (Exception ex)
            {
                st.Rollback();
                return false;
                throw new Exception(ex.Message);
            }
            finally
            {
                dbConn.Close();
            }
        }
 
        #region MySql 批量插入
        /// <summary>
        /// 批量插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tbName">要插入的目标表名称</param>
        /// <param name="columeArr">要插入的列名数组</param>
        /// <param name="listModels">要插入的实体数组</param>
        /// <param name="strConnection">数据库连接字符串</param>
        /// <returns></returns>
        public int BatchInsert<T>(string tbName, string[] columeArr, IList<T> listModels) where T : class, new()
        {
            if (listModels == null || listModels.Count == 0)
            {
                throw new ArgumentException("没有需要批量插入的数据");
            }
            int columes = columeArr.Length;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("INSERT INTO {0} ", tbName);
            AppendColumes(sb, columeArr);
            sb.Append(" VALUES ");
            var listParamKeys = new List<string>();//参数的键值
            string paramKey = string.Empty;
            for (int i = 0; i < listModels.Count; i++)  //构造参数
            {
                sb.Append("(");
                for (int j = 0; j < columes; j++)
                {
                    paramKey = string.Format("@v_{0}_{1}", columeArr[j], columes * i + j); //参数前必须加入@
                    sb.Append(paramKey);
                    listParamKeys.Add(paramKey);
                    if (j < columes - 1)
                    {
                        sb.Append(",");
                    }
                }
                sb.Append("),");
            }
            var listParamValues = new List<object>();
            for (int i = 0; i < listModels.Count; i++)  //构造参数值数组
            {
                FastPrepareParamValue<T>(listModels[i], columeArr, listParamValues);
            }
            string sqlText = sb.ToString().Trim(',') + ";";
            int affectNum = ExecuteNonQuery(ConnectionString, CommandType.Text, sqlText, PrepareParameters(listParamKeys.ToArray(), listParamValues.ToArray()));
            return affectNum;
        }
        private int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                PrepareCommand(conn, null, cmd, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }
        private MySqlParameter[] PrepareParameters(string[] paramKeys, object[] paramValues)
        {
            MySqlParameter[] clonedParms = new MySqlParameter[paramKeys.Length];
            for (int i = 0; i < paramKeys.Length; i++)
            {
                clonedParms[i] = new MySqlParameter(paramKeys[i], paramValues[i]);
            }
            return clonedParms;
        }
        private void PrepareCommand(MySqlConnection conn, MySqlTransaction trans, MySqlCommand cmd, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
 
        private void AppendColumes(StringBuilder sb, string[] columeArr)
        {
            if (columeArr == null || columeArr.Length == 0)
            {
                throw new ArgumentException("插入列不能为空");
            }
            sb.Append("(");
            for (int i = 0; i < columeArr.Length; i++)
            {
                sb.Append(columeArr[i]);
                if (i < columeArr.Length - 1)
                {
                    sb.Append(",");
                }
            }
            sb.Append(")");
        }
 
        private void FastPrepareParamValue<T>(T model, string[] columeArr, List<object> listPramValues)
        {
            object objValue = null;
            var objType = model.GetType();
            var properties = objType.GetProperties(bf);
            foreach (var columeName in columeArr)
            {
                foreach (var propInfo in properties)
                {
                    if (string.Compare(columeName, propInfo.Name, true) != 0)
                    {
                        continue;
                    }
                    try
                    {
                        objValue = propInfo.GetValue(model);
                    }
                    catch
                    {
                        objValue = null;
                    }
                    finally
                    {
                        listPramValues.Add(objValue);
                    }
                    break;
                }
            }
        }
        #endregion
 
        public DataSet Query(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();
            MySqlConnection conn = new MySqlConnection(ConnectionString);
            try
            {
                PrepareCommand(conn, null, cmd, cmdType, cmdText, commandParameters);
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                cmd.Parameters.Clear();
                conn.Close();
                return ds;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }

扩展类的目的是使用熟悉的原生ado.net的方式来避免趟EF的坑（未必有的）。　

1)增

db.cashierlist.Add(bill);
                    try
                    {
                        db.Entry<cashierlist>(bill).State = System.Data.Entity.EntityState.Added;
                        db.SaveChanges();
                        result.Success = true;
                        result.Message = "操作成功！";
                        result.Result = bill;
                    }
                    catch (Exception ex)
                    {
                        var exx = ex.InnerException == null ? ex : ex.InnerException;
                        result.Message = exx.Message;
                    }