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 }
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
複制上面的位址到浏覽器即可下載下傳