时间：2021-07-01 10:21:17 帮助过：12人阅读
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