C#操作MySQL数据库 简单三层结构设计UI、BLL、DAL、Model实际项目应用例子

C#操作MySQL数据库 简单三层结构设计UI、BLL、DAL、Model实际项目应用例子

在实际项目中,程序设计都有他的层次结构,比如MVC、MVP、普通的三层结构等等,不过现在用三层结构的相比可能少了,但是也有一些小型项目还是挺实用的

1.介绍

普通的三层架构(BLL,DAL,Model)

1)实体(Model),用来创建对象的实体;

2)业务逻辑层(BLL),用来处理复杂的数据间的关系或者是业务间的关系;

3)数据库访问层(DAL),用来用来访问数据库的;

当然还会有,View(视图层),用来展示数据;

2.三层结构例子

2.1创建数据库并创建一张测试表

这里我用的Mysql

/*
Navicat MySQL Data Transfer

Source Server         : JiYFMySQL
Source Server Version : 50553
Source Host           : localhost:3306
Source Database       : testdemo

Target Server Type    : MYSQL
Target Server Version : 50553
File Encoding         : 65001

Date: 2019-01-26 11:06:05
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(8) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '燕子', '25');
INSERT INTO `user` VALUES ('2', 'lisi', '15');
INSERT INTO `user` VALUES ('3', 'wangwu', '15');

数据库表如下

2.2创建实体类

 1     /// <summary>
 2     /// user:实体类(属性说明自动提取数据库字段的描述信息)
 3     /// </summary>
 4     [Serializable]
 5     public partial class user
 6     {
 7         public user()
 8         {}
 9         #region Model
10         private int _id;
11         private string _name;
12         private int? _age;
13         /// <summary>
14         /// 
15         /// </summary>
16         public int id
17         {
18             set{ _id=value;}
19             get{return _id;}
20         }
21         /// <summary>
22         /// 
23         /// </summary>
24         public string name
25         {
26             set{ _name=value;}
27             get{return _name;}
28         }
29         /// <summary>
30         /// 
31         /// </summary>
32         public int? age
33         {
34             set{ _age=value;}
35             get{return _age;}
36         }
37         #endregion Model
38 
39     }

2.3  DAL的User类设计

  1 using System;
  2 using System.Data;
  3 using System.Text;
  4 using MySql.Data.MySqlClient;
  5 using Maticsoft.DBUtility;
  6 using System.Data.SqlClient;//Please add references
  7 namespace BenNH.DAL
  8 {
  9     /// <summary>
 10     /// 数据访问类:user
 11     /// </summary>
 12     public partial class user
 13     {
 14         public user()
 15         {}
 16         #region  BasicMethod
 17 
 18 
 19 
 20         /// <summary>
 21         /// 增加一条数据
 22         /// </summary>
 23         public bool Add(BenNH.Model.user model)
 24         {
 25             StringBuilder strSql=new StringBuilder();
 26             strSql.Append("insert into user(");
 27             strSql.Append("id,name,age)");
 28             strSql.Append(" values (");
 29             strSql.Append("@id,@name,@age)");
 30             MySqlParameter[] parameters = {
 31                     new MySqlParameter("@id", MySqlDbType.Int32,8),
 32                     new MySqlParameter("@name", MySqlDbType.VarChar,255),
 33                     new MySqlParameter("@age", MySqlDbType.Int32,8)};
 34             parameters[0].Value = model.id;
 35             parameters[1].Value = model.name;
 36             parameters[2].Value = model.age;
 37 
 38             int rows=DbHelperMySQL.ExecuteSql(strSql.ToString(),parameters);
 39             if (rows > 0)
 40             {
 41                 return true;
 42             }
 43             else
 44             {
 45                 return false;
 46             }
 47         }
 48         /// <summary>
 49         /// 更新一条数据
 50         /// </summary>
 51         public bool Update(BenNH.Model.user model)
 52         {
 53             StringBuilder strSql = new StringBuilder();
 54             strSql.Append("update user set ");
 55             strSql.Append("name=@name,");
 56             strSql.Append("age=@age");
 57             strSql.Append(" where id=@id ");
 58             MySqlParameter[] parameters = {
 59                     new MySqlParameter("@name", MySqlDbType.VarChar,255),
 60                     new MySqlParameter("@age", MySqlDbType.Int32,8),
 61                     new MySqlParameter("@id", MySqlDbType.Int32,8)};
 62             parameters[0].Value = model.name;
 63             parameters[1].Value = model.age;
 64             parameters[2].Value = model.id;
 65 
 66             int rows=DbHelperMySQL.ExecuteSql(strSql.ToString(),parameters);
 67             if (rows > 0)
 68             {
 69                 return true;
 70             }
 71             else
 72             {
 73                 return false;
 74             }
 75         }
 76 
 77         /// <summary>
 78         /// 删除一条数据
 79         /// </summary>
 80         public bool Delete(long id)
 81         {
 82             //该表无主键信息,请自定义主键/条件字段
 83             StringBuilder strSql=new StringBuilder();
 84             strSql.Append("delete from user ");
 85             strSql.Append(" where id=@userID");
 86             MySqlParameter[] parameters = {
 87                  new MySqlParameter("@@userID", SqlDbType.BigInt)
 88             };
 89 
 90             parameters[0].Value = id;
 91 
 92             int rows=DbHelperMySQL.ExecuteSql(strSql.ToString(),parameters);
 93             if (rows > 0)
 94             {
 95                 return true;
 96             }
 97             else
 98             {
 99                 return false;
100             }
101         }
102 
103 
104         /// <summary>
105         /// 得到一个对象实体
106         /// </summary>
107         public BenNH.Model.user GetModel()
108         {
109             //该表无主键信息,请自定义主键/条件字段
110             StringBuilder strSql=new StringBuilder();
111             strSql.Append("select id,name,age from user ");
112             strSql.Append(" where ");
113             MySqlParameter[] parameters = {
114             };
115 
116             BenNH.Model.user model=new BenNH.Model.user();
117             DataSet ds=DbHelperMySQL.Query(strSql.ToString(),parameters);
118             if(ds.Tables[0].Rows.Count>0)
119             {
120                 return DataRowToModel(ds.Tables[0].Rows[0]);
121             }
122             else
123             {
124                 return null;
125             }
126         }
127 
128 
129         /// <summary>
130         /// 得到一个对象实体
131         /// </summary>
132         public BenNH.Model.user DataRowToModel(DataRow row)
133         {
134             BenNH.Model.user model=new BenNH.Model.user();
135             if (row != null)
136             {
137                 if(row["id"]!=null && row["id"].ToString()!="")
138                 {
139                     model.id=int.Parse(row["id"].ToString());
140                 }
141                 if(row["name"]!=null)
142                 {
143                     model.name=row["name"].ToString();
144                 }
145                 if(row["age"]!=null && row["age"].ToString()!="")
146                 {
147                     model.age=int.Parse(row["age"].ToString());
148                 }
149             }
150             return model;
151         }
152 
153         /// <summary>
154         /// 获得数据列表
155         /// </summary>
156         public DataSet GetList(string strWhere)
157         {
158             StringBuilder strSql=new StringBuilder();
159             strSql.Append("select id,name,age ");
160             strSql.Append(" FROM user ");
161             if(strWhere.Trim()!="")
162             {
163                 strSql.Append(" where "+strWhere);
164             }
165             return DbHelperMySQL.Query(strSql.ToString());
166         }
167 
168         /// <summary>
169         /// 获取记录总数
170         /// </summary>
171         public int GetRecordCount(string strWhere)
172         {
173             StringBuilder strSql=new StringBuilder();
174             strSql.Append("select count(1) FROM user ");
175             if(strWhere.Trim()!="")
176             {
177                 strSql.Append(" where "+strWhere);
178             }
179             object obj = DbHelperSQL.GetSingle(strSql.ToString());
180             if (obj == null)
181             {
182                 return 0;
183             }
184             else
185             {
186                 return Convert.ToInt32(obj);
187             }
188         }
189         /// <summary>
190         /// 分页获取数据列表
191         /// </summary>
192         public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
193         {
194             StringBuilder strSql=new StringBuilder();
195             strSql.Append("SELECT * FROM ( ");
196             strSql.Append(" SELECT ROW_NUMBER() OVER (");
197             if (!string.IsNullOrEmpty(orderby.Trim()))
198             {
199                 strSql.Append("order by T." + orderby );
200             }
201             else
202             {
203                 strSql.Append("order by T. desc");
204             }
205             strSql.Append(")AS Row, T.*  from user T ");
206             if (!string.IsNullOrEmpty(strWhere.Trim()))
207             {
208                 strSql.Append(" WHERE " + strWhere);
209             }
210             strSql.Append(" ) TT");
211             strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
212             return DbHelperMySQL.Query(strSql.ToString());
213         }
214 
215         /*
216         /// <summary>
217         /// 分页获取数据列表
218         /// </summary>
219         public DataSet GetList(int PageSize,int PageIndex,string strWhere)
220         {
221             MySqlParameter[] parameters = {
222                     new MySqlParameter("@tblName", MySqlDbType.VarChar, 255),
223                     new MySqlParameter("@fldName", MySqlDbType.VarChar, 255),
224                     new MySqlParameter("@PageSize", MySqlDbType.Int32),
225                     new MySqlParameter("@PageIndex", MySqlDbType.Int32),
226                     new MySqlParameter("@IsReCount", MySqlDbType.Bit),
227                     new MySqlParameter("@OrderType", MySqlDbType.Bit),
228                     new MySqlParameter("@strWhere", MySqlDbType.VarChar,1000),
229                     };
230             parameters[0].Value = "user";
231             parameters[1].Value = "";
232             parameters[2].Value = PageSize;
233             parameters[3].Value = PageIndex;
234             parameters[4].Value = 0;
235             parameters[5].Value = 0;
236             parameters[6].Value = strWhere;    
237             return DbHelperMySQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
238         }*/
239 
240         #endregion  BasicMethod
241         #region  ExtensionMethod
242 
243         #endregion  ExtensionMethod
244     }
245 }
View Code

2.4 BLL的User类设计

  1 using System;
  2 using System.Data;
  3 using System.Collections.Generic;
  4 using Maticsoft.Common;
  5 using BenNH.Model;
  6 namespace BenNH.BLL
  7 {
  8     /// <summary>
  9     /// user
 10     /// </summary>
 11     public partial class user
 12     {
 13         private readonly BenNH.DAL.user dal=new BenNH.DAL.user();
 14         public user()
 15         {}
 16         #region  BasicMethod
 17 
 18         /// <summary>
 19         /// 增加一条数据
 20         /// </summary>
 21         public bool Add(BenNH.Model.user model)
 22         {
 23             return dal.Add(model);
 24         }
 25 
 26         /// <summary>
 27         /// 更新一条数据
 28         /// </summary>
 29         public bool Update(BenNH.Model.user model)
 30         {
 31             return dal.Update(model);
 32         }
 33 
 34         /// <summary>
 35         /// 删除一条数据
 36         /// </summary>
 37         public bool Delete(long id)
 38         {
 39             //该表无主键信息,请自定义主键/条件字段
 40             return dal.Delete(id);
 41         }
 42 
 43         /// <summary>
 44         /// 得到一个对象实体
 45         /// </summary>
 46         public BenNH.Model.user GetModel()
 47         {
 48             //该表无主键信息,请自定义主键/条件字段
 49             return dal.GetModel();
 50         }
 51 
 52         /// <summary>
 53         /// 得到一个对象实体,从缓存中
 54         /// </summary>
 55         public BenNH.Model.user GetModelByCache()
 56         {
 57             //该表无主键信息,请自定义主键/条件字段
 58             string CacheKey = "userModel-" ;
 59             object objModel = Maticsoft.Common.DataCache.GetCache(CacheKey);
 60             if (objModel == null)
 61             {
 62                 try
 63                 {
 64                     objModel = dal.GetModel();
 65                     if (objModel != null)
 66                     {
 67                         int ModelCache = Maticsoft.Common.ConfigHelper.GetConfigInt("ModelCache");
 68                         Maticsoft.Common.DataCache.SetCache(CacheKey, objModel, DateTime.Now.AddMinutes(ModelCache), TimeSpan.Zero);
 69                     }
 70                 }
 71                 catch{}
 72             }
 73             return (BenNH.Model.user)objModel;
 74         }
 75 
 76         /// <summary>
 77         /// 获得数据列表
 78         /// </summary>
 79         public DataSet GetList(string strWhere)
 80         {
 81             return dal.GetList(strWhere);
 82         }
 83         /// <summary>
 84         /// 获得数据列表
 85         /// </summary>
 86         public List<BenNH.Model.user> GetModelList(string strWhere)
 87         {
 88             DataSet ds = dal.GetList(strWhere);
 89             return DataTableToList(ds.Tables[0]);
 90         }
 91         /// <summary>
 92         /// 获得数据列表
 93         /// </summary>
 94         public List<BenNH.Model.user> DataTableToList(DataTable dt)
 95         {
 96             List<BenNH.Model.user> modelList = new List<BenNH.Model.user>();
 97             int rowsCount = dt.Rows.Count;
 98             if (rowsCount > 0)
 99             {
100                 BenNH.Model.user model;
101                 for (int n = 0; n < rowsCount; n++)
102                 {
103                     model = dal.DataRowToModel(dt.Rows[n]);
104                     if (model != null)
105                     {
106                         modelList.Add(model);
107                     }
108                 }
109             }
110             return modelList;
111         }
112 
113         /// <summary>
114         /// 获得数据列表
115         /// </summary>
116         public DataSet GetAllList()
117         {
118             return GetList("");
119         }
120 
121         /// <summary>
122         /// 分页获取数据列表
123         /// </summary>
124         public int GetRecordCount(string strWhere)
125         {
126             return dal.GetRecordCount(strWhere);
127         }
128         /// <summary>
129         /// 分页获取数据列表
130         /// </summary>
131         public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
132         {
133             return dal.GetListByPage( strWhere,  orderby,  startIndex,  endIndex);
134         }
135         /// <summary>
136         /// 分页获取数据列表
137         /// </summary>
138         //public DataSet GetList(int PageSize,int PageIndex,string strWhere)
139         //{
140             //return dal.GetList(PageSize,PageIndex,strWhere);
141         //}
142 
143         #endregion  BasicMethod
144         #region  ExtensionMethod
145 
146         #endregion  ExtensionMethod
147     }
148 }
View Code

2.5MySQL帮助类

  1 using System;
  2 using System.Collections;
  3 using System.Collections.Specialized;
  4 using System.Data;
  5 using MySql.Data.MySqlClient;
  6 using System.Configuration;
  7 using System.Data.Common;
  8 using System.Collections.Generic;
  9 namespace Maticsoft.DBUtility
 10 {
 11     /// <summary>
 12     /// 数据访问抽象基础类
 13     /// Copyright (C) Maticsoft
 14     /// </summary>
 15     public abstract class DbHelperMySQL
 16     {
 17         //数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.        
 18         public static string connectionString = PubConstant.ConnectionString;
 19         public DbHelperMySQL()
 20         {            
 21         }
 22 
 23         #region 公用方法
 24         /// <summary>
 25         /// 得到最大值
 26         /// </summary>
 27         /// <param name="FieldName"></param>
 28         /// <param name="TableName"></param>
 29         /// <returns></returns>
 30         public static int GetMaxID(string FieldName, string TableName)
 31         {
 32             string strsql = "select max(" + FieldName + ")+1 from " + TableName;
 33             object obj = GetSingle(strsql);
 34             if (obj == null)
 35             {
 36                 return 1;
 37             }
 38             else
 39             {
 40                 return int.Parse(obj.ToString());
 41             }
 42         }
 43         /// <summary>
 44         /// 是否存在
 45         /// </summary>
 46         /// <param name="strSql"></param>
 47         /// <returns></returns>
 48         public static bool Exists(string strSql)
 49         {
 50             object obj = GetSingle(strSql);
 51             int cmdresult;
 52             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 53             {
 54                 cmdresult = 0;
 55             }
 56             else
 57             {
 58                 cmdresult = int.Parse(obj.ToString());
 59             }
 60             if (cmdresult == 0)
 61             {
 62                 return false;
 63             }
 64             else
 65             {
 66                 return true;
 67             }
 68         }    
 69         /// <summary>
 70         /// 是否存在(基于MySqlParameter)
 71         /// </summary>
 72         /// <param name="strSql"></param>
 73         /// <param name="cmdParms"></param>
 74         /// <returns></returns>
 75         public static bool Exists(string strSql, params MySqlParameter[] cmdParms)
 76         {
 77             object obj = GetSingle(strSql, cmdParms);
 78             int cmdresult;
 79             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 80             {
 81                 cmdresult = 0;
 82             }
 83             else
 84             {
 85                 cmdresult = int.Parse(obj.ToString());
 86             }
 87             if (cmdresult == 0)
 88             {
 89                 return false;
 90             }
 91             else
 92             {
 93                 return true;
 94             }
 95         }
 96         #endregion
 97 
 98         #region  执行简单SQL语句
 99 
100         /// <summary>
101         /// 执行SQL语句,返回影响的记录数
102         /// </summary>
103         /// <param name="SQLString">SQL语句</param>
104         /// <returns>影响的记录数</returns>
105         public static int ExecuteSql(string SQLString)
106         {
107             using (MySqlConnection connection = new MySqlConnection(connectionString))
108             {
109                 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
110                 {
111                     try
112                     {
113                         connection.Open();
114                         int rows = cmd.ExecuteNonQuery();
115                         return rows;
116                     }
117                     catch (MySql.Data.MySqlClient.MySqlException e)
118                     {
119                         connection.Close();
120                         throw e;
121                     }
122                 }
123             }
124         }
125 
126         public static int ExecuteSqlByTime(string SQLString, int Times)
127         {
128             using (MySqlConnection connection = new MySqlConnection(connectionString))
129             {
130                 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
131                 {
132                     try
133                     {
134                         connection.Open();
135                         cmd.CommandTimeout = Times;
136                         int rows = cmd.ExecuteNonQuery();
137                         return rows;
138                     }
139                     catch (MySql.Data.MySqlClient.MySqlException e)
140                     {
141                         connection.Close();
142                         throw e;
143                     }
144                 }
145             }
146         }
147       
148         /// <summary>
149         /// 执行MySql和Oracle滴混合事务
150         /// </summary>
151         /// <param name="list">SQL命令行列表</param>
152         /// <param name="oracleCmdSqlList">Oracle命令行列表</param>
153         /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
154         public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)
155         {
156             using (MySqlConnection conn = new MySqlConnection(connectionString))
157             {
158                 conn.Open();
159                 MySqlCommand cmd = new MySqlCommand();
160                 cmd.Connection = conn;
161                 MySqlTransaction tx = conn.BeginTransaction();
162                 cmd.Transaction = tx;
163                 try
164                 {
165                     foreach (CommandInfo myDE in list)
166                     {
167                         string cmdText = myDE.CommandText;
168                         MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
169                         PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
170                         if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
171                         {
172                             if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
173                             {
174                                 tx.Rollback();
175                                 throw new Exception("违背要求"+myDE.CommandText+"必须符合select count(..的格式");
176                                 //return 0;
177                             }
178 
179                             object obj = cmd.ExecuteScalar();
180                             bool isHave = false;
181                             if (obj == null && obj == DBNull.Value)
182                             {
183                                 isHave = false;
184                             }
185                             isHave = Convert.ToInt32(obj) > 0;
186                             if (isHave)
187                             {
188                                 //引发事件
189                                 myDE.OnSolicitationEvent();
190                             }
191                         }
192                         if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
193                         {
194                             if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
195                             {
196                                 tx.Rollback();
197                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
198                                 //return 0;
199                             }
200 
201                             object obj = cmd.ExecuteScalar();
202                             bool isHave = false;
203                             if (obj == null && obj == DBNull.Value)
204                             {
205                                 isHave = false;
206                             }
207                             isHave = Convert.ToInt32(obj) > 0;
208 
209                             if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
210                             {
211                                 tx.Rollback();
212                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
213                                 //return 0;
214                             }
215                             if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
216                             {
217                                 tx.Rollback();
218                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
219                                 //return 0;
220                             }
221                             continue;
222                         }
223                         int val = cmd.ExecuteNonQuery();
224                         if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
225                         {
226                             tx.Rollback();
227                             throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
228                             //return 0;
229                         }
230                         cmd.Parameters.Clear();
231                     }
232                     string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
233                     bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
234                     if (!res)
235                     {
236                         tx.Rollback();
237                         throw new Exception("执行失败");
238                         // return -1;
239                     }
240                     tx.Commit();
241                     return 1;
242                 }
243                 catch (MySql.Data.MySqlClient.MySqlException e)
244                 {
245                     tx.Rollback();
246                     throw e;
247                 }
248                 catch (Exception e)
249                 {
250                     tx.Rollback();
251                     throw e;
252                 }
253             }
254         }        
255         /// <summary>
256         /// 执行多条SQL语句,实现数据库事务。
257         /// </summary>
258         /// <param name="SQLStringList">多条SQL语句</param>        
259         public static int ExecuteSqlTran(List<String> SQLStringList)
260         {
261             using (MySqlConnection conn = new MySqlConnection(connectionString))
262             {
263                 conn.Open();
264                 MySqlCommand cmd = new MySqlCommand();
265                 cmd.Connection = conn;
266                 MySqlTransaction tx = conn.BeginTransaction();
267                 cmd.Transaction = tx;
268                 try
269                 {
270                     int count = 0;
271                     for (int n = 0; n < SQLStringList.Count; n++)
272                     {
273                         string strsql = SQLStringList[n];
274                         if (strsql.Trim().Length > 1)
275                         {
276                             cmd.CommandText = strsql;
277                             count += cmd.ExecuteNonQuery();
278                         }
279                     }
280                     tx.Commit();
281                     return count;
282                 }
283                 catch
284                 {
285                     tx.Rollback();
286                     return 0;
287                 }
288             }
289         }
290         /// <summary>
291         /// 执行带一个存储过程参数的的SQL语句。
292         /// </summary>
293         /// <param name="SQLString">SQL语句</param>
294         /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
295         /// <returns>影响的记录数</returns>
296         public static int ExecuteSql(string SQLString, string content)
297         {
298             using (MySqlConnection connection = new MySqlConnection(connectionString))
299             {
300                 MySqlCommand cmd = new MySqlCommand(SQLString, connection);
301                 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
302                 myParameter.Value = content;
303                 cmd.Parameters.Add(myParameter);
304                 try
305                 {
306                     connection.Open();
307                     int rows = cmd.ExecuteNonQuery();
308                     return rows;
309                 }
310                 catch (MySql.Data.MySqlClient.MySqlException e)
311                 {
312                     throw e;
313                 }
314                 finally
315                 {
316                     cmd.Dispose();
317                     connection.Close();
318                 }
319             }
320         }
321         /// <summary>
322         /// 执行带一个存储过程参数的的SQL语句。
323         /// </summary>
324         /// <param name="SQLString">SQL语句</param>
325         /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
326         /// <returns>影响的记录数</returns>
327         public static object ExecuteSqlGet(string SQLString, string content)
328         {
329             using (MySqlConnection connection = new MySqlConnection(connectionString))
330             {
331                 MySqlCommand cmd = new MySqlCommand(SQLString, connection);
332                 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
333                 myParameter.Value = content;
334                 cmd.Parameters.Add(myParameter);
335                 try
336                 {
337                     connection.Open();
338                     object obj = cmd.ExecuteScalar();
339                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
340                     {
341                         return null;
342                     }
343                     else
344                     {
345                         return obj;
346                     }
347                 }
348                 catch (MySql.Data.MySqlClient.MySqlException e)
349                 {
350                     throw e;
351                 }
352                 finally
353                 {
354                     cmd.Dispose();
355                     connection.Close();
356                 }
357             }
358         }
359         /// <summary>
360         /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
361         /// </summary>
362         /// <param name="strSQL">SQL语句</param>
363         /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
364         /// <returns>影响的记录数</returns>
365         public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
366         {
367             using (MySqlConnection connection = new MySqlConnection(connectionString))
368             {
369                 MySqlCommand cmd = new MySqlCommand(strSQL, connection);
370                 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image);
371                 myParameter.Value = fs;
372                 cmd.Parameters.Add(myParameter);
373                 try
374                 {
375                     connection.Open();
376                     int rows = cmd.ExecuteNonQuery();
377                     return rows;
378                 }
379                 catch (MySql.Data.MySqlClient.MySqlException e)
380                 {
381                     throw e;
382                 }
383                 finally
384                 {
385                     cmd.Dispose();
386                     connection.Close();
387                 }
388             }
389         }
390 
391         /// <summary>
392         /// 执行一条计算查询结果语句,返回查询结果(object)。
393         /// </summary>
394         /// <param name="SQLString">计算查询结果语句</param>
395         /// <returns>查询结果(object)</returns>
396         public static object GetSingle(string SQLString)
397         {
398             using (MySqlConnection connection = new MySqlConnection(connectionString))
399             {
400                 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
401                 {
402                     try
403                     {
404                         connection.Open();
405                         object obj = cmd.ExecuteScalar();
406                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
407                         {
408                             return null;
409                         }
410                         else
411                         {
412                             return obj;
413                         }
414                     }
415                     catch (MySql.Data.MySqlClient.MySqlException e)
416                     {
417                         connection.Close();
418                         throw e;
419                     }
420                 }
421             }
422         }
423         public static object GetSingle(string SQLString, int Times)
424         {
425             using (MySqlConnection connection = new MySqlConnection(connectionString))
426             {
427                 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
428                 {
429                     try
430                     {
431                         connection.Open();
432                         cmd.CommandTimeout = Times;
433                         object obj = cmd.ExecuteScalar();
434                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
435                         {
436                             return null;
437                         }
438                         else
439                         {
440                             return obj;
441                         }
442                     }
443                     catch (MySql.Data.MySqlClient.MySqlException e)
444                     {
445                         connection.Close();
446                         throw e;
447                     }
448                 }
449             }
450         }
451         /// <summary>
452         /// 执行查询语句,返回MySqlDataReader ( 注意:调用该方法后,一定要对MySqlDataReader进行Close )
453         /// </summary>
454         /// <param name="strSQL">查询语句</param>
455         /// <returns>MySqlDataReader</returns>
456         public static MySqlDataReader ExecuteReader(string strSQL)
457         {
458             MySqlConnection connection = new MySqlConnection(connectionString);
459             MySqlCommand cmd = new MySqlCommand(strSQL, connection);
460             try
461             {
462                 connection.Open();
463                 MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
464                 return myReader;
465             }
466             catch (MySql.Data.MySqlClient.MySqlException e)
467             {
468                 throw e;
469             }   
470 
471         }
472         /// <summary>
473         /// 执行查询语句,返回DataSet
474         /// </summary>
475         /// <param name="SQLString">查询语句</param>
476         /// <returns>DataSet</returns>
477         public static DataSet Query(string SQLString)
478         {
479             using (MySqlConnection connection = new MySqlConnection(connectionString))
480             {
481                 DataSet ds = new DataSet();
482                 try
483                 {
484                     connection.Open();
485                     MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
486                     command.Fill(ds, "ds");
487                 }
488                 catch (MySql.Data.MySqlClient.MySqlException ex)
489                 {
490                     throw new Exception(ex.Message);
491                 }
492                 return ds;
493             }
494         }
495         public static DataSet Query(string SQLString, int Times)
496         {
497             using (MySqlConnection connection = new MySqlConnection(connectionString))
498             {
499                 DataSet ds = new DataSet();
500                 try
501                 {
502                     connection.Open();
503                     MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
504                     command.SelectCommand.CommandTimeout = Times;
505                     command.Fill(ds, "ds");
506                 }
507                 catch (MySql.Data.MySqlClient.MySqlException ex)
508                 {
509                     throw new Exception(ex.Message);
510                 }
511                 return ds;
512             }
513         }
514 
515 
516 
517         #endregion
518 
519         #region 执行带参数的SQL语句
520 
521         /// <summary>
522         /// 执行SQL语句,返回影响的记录数
523         /// </summary>
524         /// <param name="SQLString">SQL语句</param>
525         /// <returns>影响的记录数</returns>
526         public static int ExecuteSql(string SQLString, params MySqlParameter[] cmdParms)
527         {
528             using (MySqlConnection connection = new MySqlConnection(connectionString))
529             {
530                 using (MySqlCommand cmd = new MySqlCommand())
531                 {
532                     try
533                     {
534                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
535                         int rows = cmd.ExecuteNonQuery();
536                         cmd.Parameters.Clear();
537                         return rows;
538                     }
539                     catch (MySql.Data.MySqlClient.MySqlException e)
540                     {
541                         throw e;
542                     }
543                 }
544             }
545         }
546 
547 
548         /// <summary>
549         /// 执行多条SQL语句,实现数据库事务。
550         /// </summary>
551         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])</param>
552         public static void ExecuteSqlTran(Hashtable SQLStringList)
553         {
554             using (MySqlConnection conn = new MySqlConnection(connectionString))
555             {
556                 conn.Open();
557                 using (MySqlTransaction trans = conn.BeginTransaction())
558                 {
559                     MySqlCommand cmd = new MySqlCommand();
560                     try
561                     {
562                         //循环
563                         foreach (DictionaryEntry myDE in SQLStringList)
564                         {
565                             string cmdText = myDE.Key.ToString();
566                             MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
567                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
568                             int val = cmd.ExecuteNonQuery();
569                             cmd.Parameters.Clear();
570                         }
571                         trans.Commit();
572                     }
573                     catch
574                     {
575                         trans.Rollback();
576                         throw;
577                     }
578                 }
579             }
580         }
581         /// <summary>
582         /// 执行多条SQL语句,实现数据库事务。
583         /// </summary>
584         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])</param>
585         public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
586         {
587             using (MySqlConnection conn = new MySqlConnection(connectionString))
588             {
589                 conn.Open();
590                 using (MySqlTransaction trans = conn.BeginTransaction())
591                 {
592                     MySqlCommand cmd = new MySqlCommand();
593                     try
594                     { int count = 0;
595                         //循环
596                         foreach (CommandInfo myDE in cmdList)
597                         {
598                             string cmdText = myDE.CommandText;
599                             MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
600                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
601                            
602                             if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
603                             {
604                                 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
605                                 {
606                                     trans.Rollback();
607                                     return 0;
608                                 }
609 
610                                 object obj = cmd.ExecuteScalar();
611                                 bool isHave = false;
612                                 if (obj == null && obj == DBNull.Value)
613                                 {
614                                     isHave = false;
615                                 }
616                                 isHave = Convert.ToInt32(obj) > 0;
617 
618                                 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
619                                 {
620                                     trans.Rollback();
621                                     return 0;
622                                 }
623                                 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
624                                 {
625                                     trans.Rollback();
626                                     return 0;
627                                 }
628                                 continue;
629                             }
630                             int val = cmd.ExecuteNonQuery();
631                             count += val;
632                             if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
633                             {
634                                 trans.Rollback();
635                                 return 0;
636                             }
637                             cmd.Parameters.Clear();
638                         }
639                         trans.Commit();
640                         return count;
641                     }
642                     catch
643                     {
644                         trans.Rollback();
645                         throw;
646                     }
647                 }
648             }
649         }
650         /// <summary>
651         /// 执行多条SQL语句,实现数据库事务。
652         /// </summary>
653         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])</param>
654         public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)
655         {
656             using (MySqlConnection conn = new MySqlConnection(connectionString))
657             {
658                 conn.Open();
659                 using (MySqlTransaction trans = conn.BeginTransaction())
660                 {
661                     MySqlCommand cmd = new MySqlCommand();
662                     try
663                     {
664                         int indentity = 0;
665                         //循环
666                         foreach (CommandInfo myDE in SQLStringList)
667                         {
668                             string cmdText = myDE.CommandText;
669                             MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
670                             foreach (MySqlParameter q in cmdParms)
671                             {
672                                 if (q.Direction == ParameterDirection.InputOutput)
673                                 {
674                                     q.Value = indentity;
675                                 }
676                             }
677                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
678                             int val = cmd.ExecuteNonQuery();
679                             foreach (MySqlParameter q in cmdParms)
680                             {
681                                 if (q.Direction == ParameterDirection.Output)
682                                 {
683                                     indentity = Convert.ToInt32(q.Value);
684                                 }
685                             }
686                             cmd.Parameters.Clear();
687                         }
688                         trans.Commit();
689                     }
690                     catch
691                     {
692                         trans.Rollback();
693                         throw;
694                     }
695                 }
696             }
697         }
698         /// <summary>
699         /// 执行多条SQL语句,实现数据库事务。
700         /// </summary>
701         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])</param>
702         public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
703         {
704             using (MySqlConnection conn = new MySqlConnection(connectionString))
705             {
706                 conn.Open();
707                 using (MySqlTransaction trans = conn.BeginTransaction())
708                 {
709                     MySqlCommand cmd = new MySqlCommand();
710                     try
711                     {
712                         int indentity = 0;
713                         //循环
714                         foreach (DictionaryEntry myDE in SQLStringList)
715                         {
716                             string cmdText = myDE.Key.ToString();
717                             MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
718                             foreach (MySqlParameter q in cmdParms)
719                             {
720                                 if (q.Direction == ParameterDirection.InputOutput)
721                                 {
722                                     q.Value = indentity;
723                                 }
724                             }
725                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
726                             int val = cmd.ExecuteNonQuery();
727                             foreach (MySqlParameter q in cmdParms)
728                             {
729                                 if (q.Direction == ParameterDirection.Output)
730                                 {
731                                     indentity = Convert.ToInt32(q.Value);
732                                 }
733                             }
734                             cmd.Parameters.Clear();
735                         }
736                         trans.Commit();
737                     }
738                     catch
739                     {
740                         trans.Rollback();
741                         throw;
742                     }
743                 }
744             }
745         }
746         /// <summary>
747         /// 执行一条计算查询结果语句,返回查询结果(object)。
748         /// </summary>
749         /// <param name="SQLString">计算查询结果语句</param>
750         /// <returns>查询结果(object)</returns>
751         public static object GetSingle(string SQLString, params MySqlParameter[] cmdParms)
752         {
753             using (MySqlConnection connection = new MySqlConnection(connectionString))
754             {
755                 using (MySqlCommand cmd = new MySqlCommand())
756                 {
757                     try
758                     {
759                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
760                         object obj = cmd.ExecuteScalar();
761                         cmd.Parameters.Clear();
762                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
763                         {
764                             return null;
765                         }
766                         else
767                         {
768                             return obj;
769                         }
770                     }
771                     catch (MySql.Data.MySqlClient.MySqlException e)
772                     {
773                         throw e;
774                     }
775                 }
776             }
777         }
778 
779         /// <summary>
780         /// 执行查询语句,返回MySqlDataReader ( 注意:调用该方法后,一定要对MySqlDataReader进行Close )
781         /// </summary>
782         /// <param name="strSQL">查询语句</param>
783         /// <returns>MySqlDataReader</returns>
784         public static MySqlDataReader ExecuteReader(string SQLString, params MySqlParameter[] cmdParms)
785         {
786             MySqlConnection connection = new MySqlConnection(connectionString);
787             MySqlCommand cmd = new MySqlCommand();
788             try
789             {
790                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
791                 MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
792                 cmd.Parameters.Clear();
793                 return myReader;
794             }
795             catch (MySql.Data.MySqlClient.MySqlException e)
796             {
797                 throw e;
798             }
799             //            finally
800             //            {
801             //                cmd.Dispose();
802             //                connection.Close();
803             //            }    
804 
805         }
806 
807         /// <summary>
808         /// 执行查询语句,返回DataSet
809         /// </summary>
810         /// <param name="SQLString">查询语句</param>
811         /// <returns>DataSet</returns>
812         public static DataSet Query(string SQLString, params MySqlParameter[] cmdParms)
813         {
814             using (MySqlConnection connection = new MySqlConnection(connectionString))
815             {
816                 MySqlCommand cmd = new MySqlCommand();
817                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
818                 using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
819                 {
820                     DataSet ds = new DataSet();
821                     try
822                     {
823                         da.Fill(ds, "ds");
824                         cmd.Parameters.Clear();
825                     }
826                     catch (MySql.Data.MySqlClient.MySqlException ex)
827                     {
828                         throw new Exception(ex.Message);
829                     }
830                     return ds;
831                 }
832             }
833         }
834 
835 
836         private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
837         {
838             if (conn.State != ConnectionState.Open)
839                 conn.Open();
840             cmd.Connection = conn;
841             cmd.CommandText = cmdText;
842             if (trans != null)
843                 cmd.Transaction = trans;
844             cmd.CommandType = CommandType.Text;//cmdType;
845             if (cmdParms != null)
846             {
847 
848 
849                 foreach (MySqlParameter parameter in cmdParms)
850                 {
851                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
852                         (parameter.Value == null))
853                     {
854                         parameter.Value = DBNull.Value;
855                     }
856                     cmd.Parameters.Add(parameter);
857                 }
858             }
859         }
860 
861         #endregion
862 
863         
864 
865     }
866 
867 }
View Code

2.6应用程序创建app.config配置文件(注意链接字符串和自己的数据库对应)

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <!-- 连接字符串是否加密 -->
    <add key="ConStringEncrypt" value="false"/>
    <!-- 数据库连接字符串,(如果采用加密方式,上面一项要设置为true
     如果使用明文这样server=127.0.0.1;database=.....,上面则设置为false。 -->
    <add key="ConnectionString" value="server=127.0.0.1;database=TestDemo;uid=root;pwd=root"/>
    <!--其它模块连接字符串,可以不断增加以便同一个项目支持连接多个数据库。如果没有,可以删除该行-->
    <add key="ConnectionString2" value="server=127.0.0.1;database=TestDemo;uid=root;pwd=root"/>
  </appSettings>
</configuration>

3.0 测试程序

3.1创建简单的UI

3.2首先创建实体对象userModule和业务逻辑BLLUser对象

1         public BenNH.Model.user userModule = new BenNH.Model.user();
2         public BenNH.BLL.user userBLL = new BenNH.BLL.user();

3.3 增、删、改、查 简单编写(这里没有严格判断等等)

查询

 1    /// <summary>
 2         /// 查询
 3         /// </summary>
 4         private void Select()
 5         {
 6             this.dataGridView1.Rows.Clear();
 7             //
 8             List<BenNH.Model.user> userlist = userBLL.GetModelList("");
 9             foreach (BenNH.Model.user item in userlist)
10             {
11                 DataGridViewRow row = new DataGridViewRow();
12 
13                 DataGridViewTextBoxCell cell = new DataGridViewTextBoxCell();
14                 cell.Value = item.id;
15                 DataGridViewTextBoxCell cell2 = new DataGridViewTextBoxCell();
16                 cell2.Value = item.name;
17                 DataGridViewTextBoxCell cell3 = new DataGridViewTextBoxCell();
18                 cell3.Value = item.age;
19                 row.Cells.Add(cell);
20                 row.Cells.Add(cell2);
21                 row.Cells.Add(cell3);
22 
23                 this.dataGridView1.Rows.Add(row);
24             }
25             this.label1.Text = "查询出user表:" + userlist.Count + "条记录";
26 
27             int rowIndex = dataGridView1.CurrentRow.Index;
28             if (rowIndex < 0) return;
29 
30             textBox1.Text = dataGridView1.Rows[rowIndex].Cells[0].Value.ToString();
31             textBox2.Text = dataGridView1.Rows[rowIndex].Cells[1].Value.ToString();
32             textBox3.Text = dataGridView1.Rows[rowIndex].Cells[2].Value.ToString();
33 
34 
35         }

增加

   /// <summary>
        /// 增加
        /// </summary>
        private void Add()
        {

            //增加
            userModule.id = int.Parse(textBox1.Text);
            userModule.name = textBox2.Text;
            userModule.age = int.Parse(textBox3.Text);

            bool isAdd = userBLL.Add(userModule);
            if (isAdd)
                this.label2.Text = "增加数据成功";

            this.Select();
        }

删除

 1   /// <summary>
 2         /// 删除
 3         /// </summary>
 4         private void Delete()
 5         {
 6             //删除
 7             bool isDelete = userBLL.Delete(int.Parse(textBox1.Text));
 8             if (isDelete)
 9                 this.label3.Text = "数据删除成功";
10 
11             this.Select();
12         }

更新

 1 /// <summary>
 2         /// 更新
 3         /// </summary>
 4         private void Update()
 5         {
 6             //修改
 7             userModule.id = int.Parse(textBox1.Text);
 8             userModule.name = textBox2.Text;
 9             userModule.age = int.Parse(textBox3.Text);
10 
11             bool isUpdate = userBLL.Update(userModule);
12             if (isUpdate)
13                 this.label4.Text = "修改数据成功";
14 
15             this.Select();
16         }

4.测试

数据库数据

运行程序

可以进行简单的操作了

 

5.程序下载

目录结构

 

程序源代码工程下载

或者博客留言,给出邮箱,发你!

到这里就结束了,欢迎下载,爱你们呦,么么哒。。。。。❥(^_-)

 

小伙伴们,需要源代码的直接到这里下载: https://files.cnblogs.com/files/JiYF/BLL_Model_UI_MySql.rar 

复制上面的地址到浏览器即可下载

 

posted @ 2019-01-26 21:33  JiYF  阅读(9238)  评论(16编辑  收藏  举报