首先创建数据库,建立一个用户登录表
2、visualStudio默认是不支持MySql的,要想通过Ado.Net 操作MySql
需要在管理NeGet包添加对MySql.Data 和 MySql.Data.Entity的引用
3、在app.Config中添加数据库连接信息
4、前端页面代码:
1 <WrapPanel>
2 <Button Content="是否存在" Name="IsExitBtn" Click="IsExitBtn_Click"
3 Width="60" Height="30" ></Button>
4 <Button Content="新增" Name="AddBtn" Click="AddBtn_Click"
5 Width="60" Height="30"></Button>
6 <Button Content="修改" Name="UpdateBtn" Click="UpdateBtn_Click"
7 Width="60" Height="30"></Button>
8 <Button Content="删除" Name="DelBtn" Click="DelBtn_Click"
9 Width="60" Height="30"></Button>
10 <Button Content="批量删除" Name="BatchDelBtn" Click="BatchDelBtn_Click"
11 Width="60" Height="30"></Button>
12 <Button Content="批量获取" Name="BatchGetListBtn" Click="BatchGetListBtn_Click"
13 Width="60" Height="30" ></Button>
14 </WrapPanel>
5、cs后台代码:
1 private void AddBtn_Click(object sender, RoutedEventArgs e)
2 {
3 #region 新增
4 StringBuilder addStrSql = new StringBuilder();
5 addStrSql.Append("insert into LoginUser(");
6 addStrSql.Append("UserId,Name,Age)");
7 addStrSql.Append(" values (");
8 addStrSql.Append("@UserId,@Name,@Age)");
9
10 MySqlParameter[] parameters = {
11 new MySqlParameter("@UserId", 3),
12 new MySqlParameter("@Name", "张三"),
13 new MySqlParameter("@Age", 32)};
14 var add = new CaseUserData().Add(addStrSql.ToString(), parameters);
15 #endregion
16 }
17
18 private void UpdateBtn_Click(object sender, RoutedEventArgs e)
19 {
20 #region 修改
21 StringBuilder updateStrSql = new StringBuilder();
22 updateStrSql.Append("update LoginUser set ");
23 updateStrSql.Append("Name=@Name,");
24 updateStrSql.Append("Age=@Age");
25 updateStrSql.Append(" WHERE UserId=@UserId");
26 MySqlParameter[] uparameters = {
27 new MySqlParameter("@UserId", 1),
28 new MySqlParameter("@Name", "王五"),
29 new MySqlParameter("@Age", 33) };
30 var update = new CaseUserData().Update(updateStrSql.ToString(), uparameters);
31 #endregion
32 }
33
34 private void DelBtn_Click(object sender, RoutedEventArgs e)
35 {
36 #region 删除一条数据
37 StringBuilder DelStrSql = new StringBuilder();
38 DelStrSql.Append("delete from LoginUser ");
39 DelStrSql.Append(" where UserId=@ID ");
40 MySqlParameter[] delparameters = {
41 new MySqlParameter("@ID", 1) };
42 var del = new CaseUserData().Delete(DelStrSql.ToString(), delparameters);
43 #endregion
44 }
45
46 private void BatchDelBtn_Click(object sender, RoutedEventArgs e)
47 {
48 #region 批量删除数据
49 List<int> idLists = new List<int>();
50 string ids = string.Join(",", idLists); //List数组的每个元素加上引号,如("12","32","5456","876455")
51 string s1 = string.Format("'{0}'", ids.Replace(",", "','"));
52
53 StringBuilder BatchDelStrSql = new StringBuilder();
54 BatchDelStrSql.Append("delete from LoginUser ");
55 BatchDelStrSql.Append(" where UserId in (" + s1 + ")");
56 var batchDel = new CaseUserData().DeleteList(BatchDelStrSql.ToString());
57 #endregion
58 }
59
60 private void BatchGetListBtn_Click(object sender, RoutedEventArgs e)
61 {
62 #region 批量获取数据
63 StringBuilder batchGetStrSql = new StringBuilder();
64 batchGetStrSql.Append("select UserId,Name,Age FROM LoginUser ");
65 batchGetStrSql.Append(" where Age > 30");
66 var batchGetList = new CaseUserData().GetList(batchGetStrSql.ToString());
67 #endregion
68 }
69
70 private void IsExitBtn_Click(object sender, RoutedEventArgs e)
71 {
72 #region 数据是否存在
73 StringBuilder strSql = new StringBuilder();
74 strSql.Append("select count(1) from LoginUser");
75 strSql.Append(" where UserId=@ID ");
76 var d = new CaseUserData().Exists(strSql.ToString(), 1);
77 #endregion
78 }
6、定义一些基本方法的接口:
1 using System;
2 using System.Data;
3 using System.Collections.Generic;
4 using MvvmFuncationApp.DbHelper;
5 using MySql.Data.MySqlClient;
6
7 namespace iPIS.Server.IDAL
8 {
9 /// <summary>
10 /// 接口层caseuserdata
11 /// </summary>
12 public interface ICaseUserData
13 {
14 #region 成员方法
15 /// <summary>
16 /// 是否存在该记录
17 /// </summary>
18 bool Exists(string StrSql,int ID);
19 /// <summary>
20 /// 增加一条数据
21 /// </summary>
22 bool Add(string strSql,MySqlParameter[] parameters);
23 /// <summary>
24 /// 更新一条数据
25 /// </summary>
26 bool Update(string strSql, MySqlParameter[] parameters);
27 /// <summary>
28 /// 删除一条数据
29 /// </summary>
30 bool Delete(string strSql, MySqlParameter[] parameters);
31 bool DeleteList(string strSql);
32 /// <summary>
33 /// 得到一个对象实体
34 /// </summary>
35 ParametersModel GetModel(int ID);
36 /// <summary>
37 /// 获得数据列表
38 /// </summary>
39 List<ParametersModel> GetList(string strWhere);
40
41 #endregion 成员方法
42 #region MethodEx
43
44 #endregion MethodEx
45 }
46 }
7、定义一个类,实现方法接口:
1 using iPIS.Server.IDAL;
2 using MvvmFuncationApp.DbHelper;
3 using MySql.Data.MySqlClient;
4 using System;
5 using System.Collections.Generic;
6 using System.Data;
7 using System.Runtime.Serialization;
8 using System.Text;
9 using System.Xml.Linq;
10
11 namespace iPIS.Server.Core
12 {
13 /// <summary>
14 /// 数据访问类
15 /// </summary>
16 public partial class CaseUserData : ICaseUserData
17 {
18 public CaseUserData()
19 { }
20 /// <summary>
21 /// 是否存在该记录
22 /// </summary>
23 public bool Exists(string StrSql,int ID)
24 {
25 MySqlParameter[] parameters = {
26 new MySqlParameter("@ID", MySqlDbType.Int32,10) };
27 parameters[0].Value = ID;
28
29 return DbHelperMySql.Exists(StrSql.ToString(), parameters);
30 }
31
32 /// <summary>
33 /// 增加一条数据
34 /// </summary>
35 public bool Add(string StrSql, MySqlParameter[] parameters)
36 {
37 int rows = DbHelperMySql.ExecuteSql(StrSql.ToString(), parameters);
38 if (rows > 0)
39 {
40 return true;
41 }
42 else
43 {
44 return false;
45 }
46 }
47
48 /// <summary>
49 /// 更新一条数据
50 /// </summary>
51 public bool Update(string StrSql, MySqlParameter[] parameters)
52 {
53 int rows = DbHelperMySql.ExecuteSql(StrSql.ToString(), parameters);
54 if (rows > 0)
55 {
56 return true;
57 }
58 else
59 {
60 return false;
61 }
62 }
63
64 /// <summary>
65 /// 删除一条数据
66 /// </summary>
67 public bool Delete(string StrSql,MySqlParameter[] parameters)
68 {
69 int rows = DbHelperMySql.ExecuteSql(StrSql.ToString(), parameters);
70 if (rows > 0)
71 {
72 return true;
73 }
74 else
75 {
76 return false;
77 }
78 }
79
80 /// <summary>
81 /// 批量删除数据
82 /// </summary>
83 public bool DeleteList(string BatchDelStrSql)
84 {
85 int rows = DbHelperMySql.ExecuteSql(BatchDelStrSql.ToString());
86 if (rows > 0)
87 {
88 return true;
89 }
90 else
91 {
92 return false;
93 }
94 }
95
96 /// <summary>
97 /// 得到一个对象实体
98 /// </summary>
99 public ParametersModel GetModel(int ID)
100 {
101 StringBuilder strSql = new StringBuilder();
102 strSql.Append("select UserId,Name,Age from LoginUser ");
103 strSql.Append(" where UserId=@ID ");
104 MySqlParameter[] parameters = {
105 new MySqlParameter("@ID", MySqlDbType.Int32,10) };
106 parameters[0].Value = ID;
107
108 ParametersModel model = new ParametersModel();
109 using (var ds = DbHelperMySql.ExecuteReader(strSql.ToString(), parameters))
110 {
111 while (ds.Read())
112 {
113 return DataRowToModel(ds);
114 }
115 }
116 return null;
117 }
118
119 /// <summary>
120 /// 得到一个对象实体
121 /// </summary>
122 public ParametersModel DataRowToModel(IDataReader row)
123 {
124 ParametersModel model = new ParametersModel();
125 if (row != null)
126 {
127 if (row["UserId"] != null && row["UserId"].ToString() != "")
128 {
129 model.ID = int.Parse(row["UserId"].ToString());
130 }
131 if (row["Name"] != null)
132 {
133 model.Name = row["Name"].ToString();
134 }
135 if (row["Age"] != null && row["Age"].ToString() != "")
136 {
137 model.Age = int.Parse(row["Age"].ToString());
138 }
139
140 }
141 return model;
142 }
143
144 /// <summary>
145 /// 获得数据列表
146 /// </summary>
147 public List<ParametersModel> GetList(string strSql)
148 {
149 using (var ds = DbHelperMySql.ExecuteReader(strSql.ToString()))
150 {
151 var list = new List<ParametersModel>();
152 while (ds.Read())
153 {
154 list.Add(DataRowToModel(ds));
155 }
156 return list;
157 }
158 }
159 }
160 }
8、数据库操作封装类:
1 using System;
2 using System.Collections;
3 using System.Collections.Generic;
4 using System.Data;
5 using System.Linq;
6 using System.Text;
7 using System.Threading.Tasks;
8 using MySql.Data.MySqlClient;
9
10 namespace MvvmFuncationApp.DbHelper
11 {
12 public abstract class DbHelperMySql
13 {
14 //数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.
15 public static string connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]; // "server=127.0.0.1;port=3306;database=lktest;uid=root;pwd=123456";
16
17 protected DbHelperMySql()
18 {
19 }
20
21 #region 公用方法
22 /// <summary>
23 /// 得到最大值
24 /// </summary>
25 /// <param name="fieldName"></param>
26 /// <param name="tableName"></param>
27 /// <returns></returns>
28 public static int GetMaxId(string fieldName, string tableName)
29 {
30 string strsql = "select max(" + fieldName + ")+1 from " + tableName;
31 object obj = GetSingle(strsql);
32 return obj == null ? 1 : int.Parse(obj.ToString());
33 }
34
35 /// <summary>
36 /// 是否存在(基于MySqlParameter)
37 /// </summary>
38 /// <param name="strSql"></param>
39 /// <param name="cmdParms"></param>
40 /// <returns></returns>
41 public static bool Exists(string strSql, params MySqlParameter[] cmdParms)
42 {
43 var obj = GetSingle(strSql, cmdParms);
44 int cmdresult;
45 if ((Equals(obj, null)) || (Equals(obj, DBNull.Value)))
46 {
47 cmdresult = 0;
48 }
49 else
50 {
51 cmdresult = int.Parse(obj.ToString());
52 }
53 return cmdresult != 0;
54 }
55 #endregion
56
57 #region 执行简单SQL语句
58
59 /// <summary>
60 /// 执行SQL语句,返回影响的记录数
61 /// </summary>
62 /// <param name="sqlString">SQL语句</param>
63 /// <returns>影响的记录数</returns>
64 public static int ExecuteSql(string sqlString)
65 {
66 using (var connection = new MySqlConnection(connectionString))
67 {
68 using (var cmd = new MySqlCommand(sqlString, connection))
69 {
70 try
71 {
72 connection.Open();
73 var rows = cmd.ExecuteNonQuery();
74 return rows;
75 }
76 catch (MySqlException e)
77 {
78 connection.Close();
79 throw e;
80 }
81 }
82 }
83 }
84
85 public static int ExecuteSqlByTime(string sqlString, int times)
86 {
87 using (var connection = new MySqlConnection(connectionString))
88 {
89 using (var cmd = new MySqlCommand(sqlString, connection))
90 {
91 try
92 {
93 connection.Open();
94 cmd.CommandTimeout = times;
95 var rows = cmd.ExecuteNonQuery();
96 return rows;
97 }
98 catch (MySqlException e)
99 {
100 connection.Close();
101 throw e;
102 }
103 }
104 }
105 }
106
107 /// <summary>
108 /// 执行多条SQL语句,实现数据库事务。
109 /// </summary>
110 /// <param name="sqlStringList">多条SQL语句</param>
111 public static int ExecuteSqlTran(List<String> sqlStringList)
112 {
113 using (var conn = new MySqlConnection(connectionString))
114 {
115 conn.Open();
116 var cmd = new MySqlCommand { Connection = conn };
117 var tx = conn.BeginTransaction();
118 cmd.Transaction = tx;
119 try
120 {
121 var count = 0;
122 foreach (var strsql in sqlStringList.Where(strsql => strsql.Trim().Length > 1))
123 {
124 cmd.CommandText = strsql;
125 count += cmd.ExecuteNonQuery();
126 }
127 tx.Commit();
128 return count;
129 }
130 catch (Exception ex)
131 {
132 tx.Rollback();
133 throw ex;
134 }
135 }
136 }
137
138 /// <summary>
139 /// 执行带一个存储过程参数的的SQL语句。
140 /// </summary>
141 /// <param name="sqlString">SQL语句</param>
142 /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
143 /// <returns>影响的记录数</returns>
144 public static int ExecuteSql(string sqlString, string content)
145 {
146 using (var connection = new MySqlConnection(connectionString))
147 {
148 var cmd = new MySqlCommand(sqlString, connection);
149 var myParameter = new MySqlParameter("@content", SqlDbType.NText) { Value = content };
150 cmd.Parameters.Add(myParameter);
151 try
152 {
153 connection.Open();
154 var rows = cmd.ExecuteNonQuery();
155 return rows;
156 }
157 catch (MySqlException e)
158 {
159 throw e;
160 }
161 finally
162 {
163 cmd.Dispose();
164 connection.Close();
165 }
166 }
167 }
168
169 /// <summary>
170 /// 执行带一个存储过程参数的的SQL语句。
171 /// </summary>
172 /// <param name="sqlString">SQL语句</param>
173 /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
174 /// <returns>影响的记录数</returns>
175 public static object ExecuteSqlGet(string sqlString, string content)
176 {
177 using (var connection = new MySqlConnection(connectionString))
178 {
179 var cmd = new MySqlCommand(sqlString, connection);
180 var myParameter = new MySqlParameter("@content", SqlDbType.NText) { Value = content };
181 cmd.Parameters.Add(myParameter);
182 try
183 {
184 connection.Open();
185 var obj = cmd.ExecuteScalar();
186 if ((Equals(obj, null)) || (Equals(obj, DBNull.Value)))
187 {
188 return null;
189 }
190 else
191 {
192 return obj;
193 }
194 }
195 catch (MySqlException e)
196 {
197 throw e;
198 }
199 finally
200 {
201 cmd.Dispose();
202 connection.Close();
203 }
204 }
205 }
206
207 /// <summary>
208 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
209 /// </summary>
210 /// <param name="strSql">SQL语句</param>
211 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
212 /// <returns>影响的记录数</returns>
213 public static int ExecuteSqlInsertImg(string strSql, byte[] fs)
214 {
215 using (var connection = new MySqlConnection(connectionString))
216 {
217 var cmd = new MySqlCommand(strSql, connection);
218 var myParameter = new MySqlParameter("@fs", SqlDbType.Image) { Value = fs };
219 cmd.Parameters.Add(myParameter);
220 try
221 {
222 connection.Open();
223 var rows = cmd.ExecuteNonQuery();
224 return rows;
225 }
226 catch (MySqlException e)
227 {
228 throw e;
229 }
230 finally
231 {
232 cmd.Dispose();
233 connection.Close();
234 }
235 }
236 }
237
238 /// <summary>
239 /// 执行一条计算查询结果语句,返回查询结果(object)。
240 /// </summary>
241 /// <param name="sqlString">计算查询结果语句</param>
242 /// <returns>查询结果(object)</returns>
243 public static object GetSingle(string sqlString)
244 {
245 using (var connection = new MySqlConnection(connectionString))
246 {
247 using (var cmd = new MySqlCommand(sqlString, connection))
248 {
249 try
250 {
251 connection.Open();
252 var obj = cmd.ExecuteScalar();
253 if ((Equals(obj, null)) || (Equals(obj, DBNull.Value)))
254 {
255 return null;
256 }
257 else
258 {
259 return obj;
260 }
261 }
262 catch (MySqlException e)
263 {
264 connection.Close();
265 throw e;
266 }
267 }
268 }
269 }
270
271 public static object GetSingle(string sqlString, int times)
272 {
273 using (var connection = new MySqlConnection(connectionString))
274 {
275 using (var cmd = new MySqlCommand(sqlString, connection))
276 {
277 try
278 {
279 connection.Open();
280 cmd.CommandTimeout = times;
281 var obj = cmd.ExecuteScalar();
282 if ((Equals(obj, null)) || (Equals(obj, DBNull.Value)))
283 {
284 return null;
285 }
286 else
287 {
288 return obj;
289 }
290 }
291 catch (MySqlException e)
292 {
293 connection.Close();
294 throw e;
295 }
296 }
297 }
298 }
299 /// <summary>
300 /// 执行查询语句,返回MySqlDataReader ( 注意:调用该方法后,一定要对MySqlDataReader进行Close )
301 /// </summary>
302 /// <param name="strSql">查询语句</param>
303 /// <returns>MySqlDataReader</returns>
304 public static MySqlDataReader ExecuteReader(string strSql)
305 {
306 var connection = new MySqlConnection(connectionString);
307 var cmd = new MySqlCommand(strSql, connection);
308 try
309 {
310 connection.Open();
311 var myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
312 return myReader;
313 }
314 catch (MySqlException e)
315 {
316 throw e;
317 }
318
319 }
320
321 /// <summary>
322 /// 执行查询语句,返回DataSet
323 /// </summary>
324 /// <param name="sqlString">查询语句</param>
325 /// <returns>DataSet</returns>
326 [Obsolete("不再使用该接口,请使用ExecuteReader(string strSql)")]
327 public static DataSet Query(string sqlString)
328 {
329 using (var connection = new MySqlConnection(connectionString))
330 {
331 var ds = new DataSet();
332 try
333 {
334 connection.Open();
335 var command = new MySqlDataAdapter(sqlString, connection);
336 command.Fill(ds, "ds");
337 }
338 catch (MySqlException ex)
339 {
340 throw new Exception(ex.Message);
341 }
342 return ds;
343 }
344 }
345
346 /// <summary>
347 /// 执行查询语句,返回DataSet
348 /// </summary>
349 /// <param name="sqlString"></param>
350 /// <param name="times">超时时间</param>
351 /// <returns></returns>
352 [Obsolete("不再使用该接口,请使用ExecuteReader(string strSql)")]
353 public static DataSet Query(string sqlString, int times)
354 {
355 using (var connection = new MySqlConnection(connectionString))
356 {
357 var ds = new DataSet();
358 try
359 {
360 connection.Open();
361 var command = new MySqlDataAdapter(sqlString, connection) { SelectCommand = { CommandTimeout = times } };
362 command.Fill(ds, "ds");
363 }
364 catch (MySqlException ex)
365 {
366 throw new Exception(ex.Message);
367 }
368 return ds;
369 }
370 }
371
372 /// <summary>
373 /// 删除案件
374 /// </summary>
375 /// <returns></returns>
376 public static int ExecuteProcedure(string caseid)
377 {
378 using (var connection = new MySqlConnection(connectionString))
379 {
380 using (var cmd = new MySqlCommand())
381 {
382 try
383 {
384 cmd.Connection = connection;
385 connection.Open();
386 cmd.CommandText = "sp_DeleteCaseInfo";
387 cmd.CommandType = CommandType.StoredProcedure;
388
389 MySqlParameter id = new MySqlParameter("?id", MySqlDbType.VarChar, 50);
390 id.Value = caseid;
391 cmd.Parameters.Add(id);
392
393 MySqlParameter result = new MySqlParameter("?result", MySqlDbType.Int32, 4);
394 result.Direction = ParameterDirection.Output;
395 cmd.Parameters.Add(result);
396
397 cmd.ExecuteNonQuery();
398 int state = int.Parse(result.Value.ToString());
399 return state;
400 }
401 catch (MySqlException e)
402 {
403 throw e;
404 }
405 }
406 }
407 }
408
409 #endregion
410
411 #region 执行带参数的SQL语句
412
413 /// <summary>
414 /// 执行SQL语句,返回影响的记录数
415 /// </summary>
416 /// <param name="sqlString">SQL语句</param>
417 /// <param name="cmdParms">参数化</param>
418 /// <returns>影响的记录数</returns>
419 public static int ExecuteSql(string sqlString, params MySqlParameter[] cmdParms)
420 {
421 using (var connection = new MySqlConnection(connectionString))
422 {
423 using (var cmd = new MySqlCommand())
424 {
425 try
426 {
427 PrepareCommand(cmd, connection, null, sqlString, cmdParms);
428 var rows = cmd.ExecuteNonQuery();
429 cmd.Parameters.Clear();
430 return rows;
431 }
432 catch (MySqlException e)
433 {
434 throw e;
435 }
436 }
437 }
438 }
439
440
441 /// <summary>
442 /// 执行多条SQL语句,实现数据库事务。
443 /// </summary>
444 /// <param name="sqlStringList">SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])</param>
445 public static void ExecuteSqlTran(Hashtable sqlStringList)
446 {
447 using (var conn = new MySqlConnection(connectionString))
448 {
449 conn.Open();
450 using (var trans = conn.BeginTransaction())
451 {
452 var cmd = new MySqlCommand();
453 try
454 {
455 //循环
456 foreach (DictionaryEntry myDe in sqlStringList)
457 {
458 var cmdText = myDe.Key.ToString();
459 var cmdParms = (MySqlParameter[])myDe.Value;
460 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
461 var val = cmd.ExecuteNonQuery();
462 cmd.Parameters.Clear();
463 }
464 trans.Commit();
465 }
466 catch
467 {
468 trans.Rollback();
469 throw;
470 }
471 }
472 }
473 }
474
475 /// <summary>
476 /// 执行多条SQL语句,实现数据库事务。
477 /// </summary>
478 /// <param name="cmdList">SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])</param>
479 public static int ExecuteSqlTran(List<CommandInfo> cmdList)
480 {
481 using (var conn = new MySqlConnection(connectionString))
482 {
483 conn.Open();
484 using (var trans = conn.BeginTransaction())
485 {
486 var cmd = new MySqlCommand();
487 try
488 {
489 var count = 0;
490 //循环
491 foreach (var myDe in cmdList)
492 {
493 string cmdText = myDe.CommandText;
494 var cmdParms = (MySqlParameter[])myDe.Parameters;
495 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
496
497 if (myDe.EffentNextType == EffentNextType.WhenHaveContine || myDe.EffentNextType == EffentNextType.WhenNoHaveContine)
498 {
499 if (myDe.CommandText.ToLower().IndexOf("count(") == -1)
500 {
501 trans.Rollback();
502 return 0;
503 }
504
505 object obj = cmd.ExecuteScalar();
506 bool isHave = false;
507 if (obj == null && obj == DBNull.Value)
508 {
509 isHave = false;
510 }
511 isHave = Convert.ToInt32(obj) > 0;
512
513 if (myDe.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
514 {
515 trans.Rollback();
516 return 0;
517 }
518 if (myDe.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
519 {
520 trans.Rollback();
521 return 0;
522 }
523 continue;
524 }
525 int val = cmd.ExecuteNonQuery();
526 count += val;
527 if (myDe.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
528 {
529 trans.Rollback();
530 return 0;
531 }
532 cmd.Parameters.Clear();
533 }
534 trans.Commit();
535 return count;
536 }
537 catch
538 {
539 trans.Rollback();
540 throw;
541 }
542 }
543 }
544 }
545
546 /// <summary>
547 /// 执行多条SQL语句,实现数据库事务。
548 /// </summary>
549 /// <param name="sqlStringList">SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])</param>
550 public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> sqlStringList)
551 {
552 using (var conn = new MySqlConnection(connectionString))
553 {
554 conn.Open();
555 using (var trans = conn.BeginTransaction())
556 {
557 var cmd = new MySqlCommand();
558 try
559 {
560 var indentity = 0;
561 //循环
562 foreach (var myDe in sqlStringList)
563 {
564 var cmdText = myDe.CommandText;
565 var cmdParms = (MySqlParameter[])myDe.Parameters;
566 foreach (var q in cmdParms)
567 {
568 if (q.Direction == ParameterDirection.InputOutput)
569 {
570 q.Value = indentity;
571 }
572 }
573 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
574 int val = cmd.ExecuteNonQuery();
575 foreach (var q in cmdParms)
576 {
577 if (q.Direction == ParameterDirection.Output)
578 {
579 indentity = Convert.ToInt32(q.Value);
580 }
581 }
582 cmd.Parameters.Clear();
583 }
584 trans.Commit();
585 }
586 catch
587 {
588 trans.Rollback();
589 throw;
590 }
591 }
592 }
593 }
594
595 /// <summary>
596 /// 执行多条SQL语句,实现数据库事务。
597 /// </summary>
598 /// <param name="sqlStringList">SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])</param>
599 public static void ExecuteSqlTranWithIndentity(Hashtable sqlStringList)
600 {
601 using (var conn = new MySqlConnection(connectionString))
602 {
603 conn.Open();
604 using (var trans = conn.BeginTransaction())
605 {
606 var cmd = new MySqlCommand();
607 try
608 {
609 var indentity = 0;
610 //循环
611 foreach (DictionaryEntry myDe in sqlStringList)
612 {
613 string cmdText = myDe.Key.ToString();
614 var cmdParms = (MySqlParameter[])myDe.Value;
615 foreach (MySqlParameter q in cmdParms)
616 {
617 if (q.Direction == ParameterDirection.InputOutput)
618 {
619 q.Value = indentity;
620 }
621 }
622 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
623 int val = cmd.ExecuteNonQuery();
624 foreach (MySqlParameter q in cmdParms)
625 {
626 if (q.Direction == ParameterDirection.Output)
627 {
628 indentity = Convert.ToInt32(q.Value);
629 }
630 }
631 cmd.Parameters.Clear();
632 }
633 trans.Commit();
634 }
635 catch
636 {
637 trans.Rollback();
638 throw;
639 }
640 }
641 }
642 }
643
644 /// <summary>
645 /// 执行一条计算查询结果语句,返回查询结果(object)。
646 /// </summary>
647 /// <param name="sqlString">计算查询结果语句</param>
648 /// <param name="cmdParms">参数化</param>
649 /// <returns>查询结果(object)</returns>
650 public static object GetSingle(string sqlString, params MySqlParameter[] cmdParms)
651 {
652 using (var connection = new MySqlConnection(connectionString))
653 {
654 using (var cmd = new MySqlCommand())
655 {
656 try
657 {
658 PrepareCommand(cmd, connection, null, sqlString, cmdParms);
659 var obj = cmd.ExecuteScalar();
660 cmd.Parameters.Clear();
661 if ((Equals(obj, null)) || (Equals(obj, DBNull.Value)))
662 {
663 return null;
664 }
665 else
666 {
667 return obj;
668 }
669 }
670 catch (MySqlException e)
671 {
672 throw e;
673 }
674 }
675 }
676 }
677
678 /// <summary>
679 /// 执行查询语句,返回MySqlDataReader ( 注意:调用该方法后,一定要对MySqlDataReader进行Close )
680 /// </summary>
681 /// <param name="sqlString">查询语句</param>
682 /// <param name="cmdParms">参数化</param>
683 /// <returns>MySqlDataReader</returns>
684 public static MySqlDataReader ExecuteReader(string sqlString, params MySqlParameter[] cmdParms)
685 {
686 var connection = new MySqlConnection(connectionString);
687 var cmd = new MySqlCommand();
688 try
689 {
690 PrepareCommand(cmd, connection, null, sqlString, cmdParms);
691 var myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
692 cmd.Parameters.Clear();
693 return myReader;
694 }
695 catch (MySqlException e)
696 {
697 throw e;
698 }
699 // finally
700 // {
701 // cmd.Dispose();
702 // connection.Close();
703 // }
704
705 }
706
707 /// <summary>
708 /// 执行查询语句,返回DataSet
709 /// </summary>
710 /// <param name="sqlString">查询语句</param>
711 /// <param name="cmdParms">参数化</param>
712 /// <returns>DataSet</returns>
713 [Obsolete("不再使用该接口,请使用ExecuteReader(string sqlString, params MySqlParameter[] cmdParms)")]
714 public static DataSet Query(string sqlString, params MySqlParameter[] cmdParms)
715 {
716 using (var connection = new MySqlConnection(connectionString))
717 {
718 var cmd = new MySqlCommand();
719 PrepareCommand(cmd, connection, null, sqlString, cmdParms);
720 using (var da = new MySqlDataAdapter(cmd))
721 {
722 var ds = new DataSet();
723 try
724 {
725 da.Fill(ds, "ds");
726 cmd.Parameters.Clear();
727 }
728 catch (MySqlException ex)
729 {
730 throw new Exception(ex.Message);
731 }
732 return ds;
733 }
734 }
735 }
736
737 private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
738 {
739 if (conn.State != ConnectionState.Open)
740 conn.Open();
741 cmd.Connection = conn;
742 cmd.CommandText = cmdText;
743 if (trans != null)
744 cmd.Transaction = trans;
745 cmd.CommandType = CommandType.Text;//cmdType;
746 if (cmdParms != null)
747 {
748
749
750 foreach (MySqlParameter parameter in cmdParms)
751 {
752 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
753 (parameter.Value == null))
754 {
755 parameter.Value = DBNull.Value;
756 }
757 cmd.Parameters.Add(parameter);
758 }
759 }
760 }
761
762 #endregion
763 }
764 }
9、数据库数据转模型:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MvvmFuncationApp.DbHelper
{
/// <summary>
/// 案件关联用户自定义数据
/// </summary>
[Serializable]
public partial class ParametersModel
{
public ParametersModel()
{ }
#region Model
private int _id;
private string _DataID;
private int _age;
private string _name;
private string _userdata;
private string _extended1;
private string _extended2;
/// <summary>
///
/// </summary>
public int ID
{
set { _id = value; }
get { return _id; }
}
/// <summary>
/// 关联数据ID,DataType=1 案件id;DataType=2 图片id
/// </summary>
public string DataID
{
set { _DataID = value; }
get { return _DataID; }
}
/// <summary>
/// 数据类型。详见枚举值
/// </summary>
public int Age
{
set { _age = value; }
get { return _age; }
}
/// <summary>
/// 数据名称
/// </summary>
public string Name
{
set { _name = value; }
get { return _name; }
}
/// <summary>
/// 数据详细内容
/// </summary>
public string UserData
{
set { _userdata = value; }
get { return _userdata; }
}
/// <summary>
///
/// </summary>
public string Extended1
{
set { _extended1 = value; }
get { return _extended1; }
}
/// <summary>
///
/// </summary>
public string Extended2
{
set { _extended2 = value; }
get { return _extended2; }
}
#endregion Model
}
}
可以直接使用,亲测没问题。