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 }
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 }
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 }
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
复制上面的地址到浏览器即可下载