时间：2021-07-01 10:21:17 帮助过：4人阅读
后台方法：
1 protected void BindGrid() 2 { 3 StringBuilder strWhere = new StringBuilder(); 4 BLL.base_servicecount countBll = new BLL.base_servicecount(); 5 strWhere.AppendFormat(" UserID=‘{0}‘ ", LoginUserModel.ID); 6 if (!string.IsNullOrEmpty(txtInput.Value.Trim())) 7 { 8 strWhere.AppendFormat(" AND (ProName like ‘%{0}%‘ or Brand like ‘%{0}%‘) ", txtInput.Value.Trim()); 9 } 10 AspNetPager1.RecordCount = countBll.GetRecordCount(strWhere.ToString()); 11 int itemStart = (AspNetPager1.CurrentPageIndex - 1) * AspNetPager1.PageSize; 12 gridProduct.DataSource = countBll.GetMvListByPage_MySql(strWhere.ToString(), "", itemStart, AspNetPager1.PageSize); 13 gridProduct.DataBind(); 14 } 15 protected void AspNetPager1_PageChanged(object sender, EventArgs e) 16 { 17 BindGrid(); 18 }
DAL方法：
1 /// <summary> 2 /// 分页 3 /// </summary> 4 public DataTable GetMvListByPage_MySql(string strWhere, string orderBy, int startIndex, int PageCount) 5 { 6 StringBuilder strSql = new StringBuilder(); 7 strSql.Append("SELECT * FROM mv_service_product "); 8 if (!string.IsNullOrEmpty(strWhere.Trim())) 9 { 10 strSql.Append(" WHERE " + strWhere); 11 } 12 if (!string.IsNullOrEmpty(orderBy.Trim())) 13 { 14 strSql.Append(" order by " + orderBy + " "); 15 } 16 else 17 { 18 strSql.Append(" order by Count desc "); 19 } 20 strSql.AppendFormat(" LIMIT {0},{1} ", startIndex, PageCount); 21 return DbHelperMySQL.Query(strSql.ToString()).Tables[0] != null ? DbHelperMySQL.Query(strSql.ToString()).Tables[0] : null; 22 }
DBHelperMySql类：
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 ZHUAO.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 /// 执行多条SQL语句，实现数据库事务。 150 /// </summary> 151 /// <param name="SQLStringList">多条SQL语句</param> 152 public static int ExecuteSqlTran(List<String> SQLStringList) 153 { 154 using (MySqlConnection conn = new MySqlConnection(connectionString)) 155 { 156 conn.Open(); 157 MySqlCommand cmd = new MySqlCommand(); 158 cmd.Connection = conn; 159 MySqlTransaction tx = conn.BeginTransaction(); 160 cmd.Transaction = tx; 161 try 162 { 163 int count = 0; 164 for (int n = 0; n < SQLStringList.Count; n++) 165 { 166 string strsql = SQLStringList[n]; 167 if (strsql.Trim().Length > 1) 168 { 169 cmd.CommandText = strsql; 170 count += cmd.ExecuteNonQuery(); 171 } 172 } 173 tx.Commit(); 174 return count; 175 } 176 catch 177 { 178 tx.Rollback(); 179 return 0; 180 } 181 } 182 } 183 /// <summary> 184 /// 执行带一个存储过程参数的的SQL语句。 185 /// </summary> 186 /// <param name="SQLString">SQL语句</param> 187 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> 188 /// <returns>影响的记录数</returns> 189 public static int ExecuteSql(string SQLString, string content) 190 { 191 using (MySqlConnection connection = new MySqlConnection(connectionString)) 192 { 193 MySqlCommand cmd = new MySqlCommand(SQLString, connection); 194 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText); 195 myParameter.Value = content; 196 cmd.Parameters.Add(myParameter); 197 try 198 { 199 connection.Open(); 200 int rows = cmd.ExecuteNonQuery(); 201 return rows; 202 } 203 catch (MySql.Data.MySqlClient.MySqlException e) 204 { 205 throw e; 206 } 207 finally 208 { 209 cmd.Dispose(); 210 connection.Close(); 211 } 212 } 213 } 214 /// <summary> 215 /// 执行带一个存储过程参数的的SQL语句。 216 /// </summary> 217 /// <param name="SQLString">SQL语句</param> 218 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> 219 /// <returns>影响的记录数</returns> 220 public static object ExecuteSqlGet(string SQLString, string content) 221 { 222 using (MySqlConnection connection = new MySqlConnection(connectionString)) 223 { 224 MySqlCommand cmd = new MySqlCommand(SQLString, connection); 225 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText); 226 myParameter.Value = content; 227 cmd.Parameters.Add(myParameter); 228 try 229 { 230 connection.Open(); 231 object obj = cmd.ExecuteScalar(); 232 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 233 { 234 return null; 235 } 236 else 237 { 238 return obj; 239 } 240 } 241 catch (MySql.Data.MySqlClient.MySqlException e) 242 { 243 throw e; 244 } 245 finally 246 { 247 cmd.Dispose(); 248 connection.Close(); 249 } 250 } 251 } 252 /// <summary> 253 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) 254 /// </summary> 255 /// <param name="strSQL">SQL语句</param> 256 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> 257 /// <returns>影响的记录数</returns> 258 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) 259 { 260 using (MySqlConnection connection = new MySqlConnection(connectionString)) 261 { 262 MySqlCommand cmd = new MySqlCommand(strSQL, connection); 263 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image); 264 myParameter.Value = fs; 265 cmd.Parameters.Add(myParameter); 266 try 267 { 268 connection.Open(); 269 int rows = cmd.ExecuteNonQuery(); 270 return rows; 271 } 272 catch (MySql.Data.MySqlClient.MySqlException e) 273 { 274 throw e; 275 } 276 finally 277 { 278 cmd.Dispose(); 279 connection.Close(); 280 } 281 } 282 } 283 284 /// <summary> 285 /// 执行一条计算查询结果语句，返回查询结果（object）。 286 /// </summary> 287 /// <param name="SQLString">计算查询结果语句</param> 288 /// <returns>查询结果（object）</returns> 289 public static object GetSingle(string SQLString) 290 { 291 using (MySqlConnection connection = new MySqlConnection(connectionString)) 292 { 293 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) 294 { 295 try 296 { 297 connection.Open(); 298 object obj = cmd.ExecuteScalar(); 299 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 300 { 301 return null; 302 } 303 else 304 { 305 return obj; 306 } 307 } 308 catch (MySql.Data.MySqlClient.MySqlException e) 309 { 310 connection.Close(); 311 throw e; 312 } 313 } 314 } 315 } 316 public static object GetSingle(string SQLString, int Times) 317 { 318 using (MySqlConnection connection = new MySqlConnection(connectionString)) 319 { 320 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) 321 { 322 try 323 { 324 connection.Open(); 325 cmd.CommandTimeout = Times; 326 object obj = cmd.ExecuteScalar(); 327 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 328 { 329 return null; 330 } 331 else 332 { 333 return obj; 334 } 335 } 336 catch (MySql.Data.MySqlClient.MySqlException e) 337 { 338 connection.Close(); 339 throw e; 340 } 341 } 342 } 343 } 344 /// <summary> 345 /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close ) 346 /// </summary> 347 /// <param name="strSQL">查询语句</param> 348 /// <returns>MySqlDataReader</returns> 349 public static MySqlDataReader ExecuteReader(string strSQL) 350 { 351 MySqlConnection connection = new MySqlConnection(connectionString); 352 MySqlCommand cmd = new MySqlCommand(strSQL, connection); 353 try 354 { 355 connection.Open(); 356 MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 357 return myReader; 358 } 359 catch (MySql.Data.MySqlClient.MySqlException e) 360 { 361 throw e; 362 } 363 364 } 365 /// <summary> 366 /// 执行查询语句，返回DataSet 367 /// </summary> 368 /// <param name="SQLString">查询语句</param> 369 /// <returns>DataSet</returns> 370 public static DataSet Query(string SQLString) 371 { 372 using (MySqlConnection connection = new MySqlConnection(connectionString)) 373 { 374 DataSet ds = new DataSet(); 375 try 376 { 377 connection.Open(); 378 MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection); 379 command.Fill(ds, "ds"); 380 } 381 catch (MySql.Data.MySqlClient.MySqlException ex) 382 { 383 throw new Exception(ex.Message); 384 } 385 return ds; 386 }