sqlHelper类
下面代码是本人封装的一个sqlHelper类。主要功能是对数据库的CURD。以及存储过程、事物、分页的实现。(首先要引入命名空间using System.Configuration;)
public class SqlHelper
{
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#region 连接字符串 /// <summary> /// 连接数据库的字符串 /// </summary> private static string conStr = ConfigurationManager.ConnectionStrings["sqlconStr"].ConnectionString; #endregion
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 #region 01.执行查询返回datatable +DataTable ExecuteDataTable(string sql, params SqlParameter[] param) 2 /// <summary> 3 /// 返回datatable 4 /// </summary> 5 /// <param name="sql">sql语句</param> 6 /// <param name="param">数组型参数,sql语句需要替换的参数</param> 7 /// <returns>DataTable</returns> 8 public static DataTable ExecuteDataTable(string sql, params SqlParameter[] param) 9 { 10 DataTable dt = new DataTable(); 11 using (SqlConnection con = new SqlConnection(conStr)) 12 { 13 //con.ConnectionString = conStr; 14 using (SqlCommand com = new SqlCommand(sql, con)) 15 { 16 //com.CommandText = sql; 17 //com.Connection = con; 18 //com.CommandType = CommandType.Text; 19 //com.CommandTimeout = Convert.ToInt32(ConfigurationSettings.AppSettings["CommandTimeout"]); 20 com.Parameters.AddRange(param); 21 //创建适配器对象(卡车,它会自动开关连接通道) 22 SqlDataAdapter adapter = new SqlDataAdapter(com); 23 //adapter.SelectCommand = com;//也可以用这种方式联系command对象 24 adapter.Fill(dt); 25 com.Parameters.Clear(); 26 return dt; 27 } 28 } 29 } 30 #endregion
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 #region 02.返回非查询命令ExecuteNoQuery受影响的行数 +int ExecuteNoQuery(string sql, params SqlParameter[] param) 2 /// <summary> 3 /// 返回非查询命令ExecuteNoQuery受影响的行数 4 /// </summary> 5 /// <param name="sql">sql语句</param> 6 /// <param name="param">数组型参数,sql语句需要替换的参数</param> 7 /// <returns>返回受影响的行数</returns> 8 public static int ExecuteNoQuery(string sql, params SqlParameter[] param) 9 { 10 using (SqlConnection con = new SqlConnection(conStr)) 11 { 12 using (SqlCommand com = new SqlCommand(sql, con)) 13 { 14 com.Parameters.AddRange(param); 15 con.Open(); 16 int count= com.ExecuteNonQuery(); 17 com.Parameters.Clear(); 18 return count; 19 } 20 } 21 } 22 #endregion
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 #region 03.执行查询或者非查询, 返回结果集的第一行第一列 +object ExecuteScalar(string sql, params SqlParameter[] param) 2 /// <summary> 3 /// 执行查询或者非查询, 返回结果集的第一行第一列 4 /// </summary> 5 /// <param name="sql">sql语句</param> 6 /// <param name="param">数组型参数,sql语句需要替换的参数</param> 7 /// <returns>返回查询结果集的第一行第一列</returns> 8 public static object ExecuteScalar(string sql, params SqlParameter[] param) 9 { 10 using (SqlConnection con = new SqlConnection(conStr)) 11 { 12 using (SqlCommand com = new SqlCommand(sql, con)) 13 { 14 com.Parameters.AddRange(param); 15 con.Open(); 16 object o= com.ExecuteScalar(); 17 com.Parameters.Clear(); 18 return o; 19 20 } 21 } 22 } 23 #endregion
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 #region 04.返回 SqlDataReader + SqlDataReader ExecuteDataReader(string sql, params SqlParameter[] param) 2 /// <summary> 3 /// 返回datareader 4 /// </summary> 5 /// <param name="sql"></param> 6 /// <param name="param"></param> 7 /// <returns></returns> 8 public static SqlDataReader ExecuteDataReader(string sql, params SqlParameter[] param) 9 { 10 //因为 SqlDataReader 是基于连接的,所以不能在这里用using释放掉连接,不然返回的SqlDataReader就无法从数据库服务器一条一条的读取数据了 11 SqlConnection con = new SqlConnection(conStr); 12 SqlCommand com = new SqlCommand(sql, con); 13 if (param != null) 14 { 15 com.Parameters.AddRange(param); 16 } 17 con.Open(); 18 //因为当读取完数据后又需要释放连接(连接是宝贵的),所以在这里为ExecuteReader()方法加了一个枚举型参数CommandBehavior.CloseConnection,让datareader关闭时,自动的关闭connection的连接。 19 SqlDataReader reader= com.ExecuteReader(CommandBehavior.CloseConnection); 20 com.Parameters.Clear(); 21 return reader; 22 } 23 #endregion
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 #region 05.存储过程 返回数据 2 /// <summary> 3 /// 返回DataTable和pageCount 4 /// </summary> 5 /// <param name="sql"></param> 6 /// <param name="pageCount"></param> 7 /// <returns></returns> 8 public static DataTable ExecuteProc(string sql, int pageSize, int pageIndex, out int pageCount) 9 { 10 using (SqlConnection con = new SqlConnection(conStr)) 11 { 12 //string sql = "usp_GetPageData"; 13 using (SqlCommand com = new SqlCommand(sql, con)) 14 { 15 com.CommandType = CommandType.StoredProcedure;//设置sql语句类型为存储过程 16 17 //设置存储过程的输入参数 18 com.Parameters.AddWithValue("@pageSize", pageSize); 19 com.Parameters.AddWithValue("@pageIndex", pageIndex); 20 21 //输出参数的设置 22 SqlParameter sp = com.Parameters.Add("@pageCount", SqlDbType.Int); 23 sp.Direction = ParameterDirection.Output; 24 25 26 DataTable dt = new DataTable(); 27 using (SqlDataAdapter adaper = new SqlDataAdapter(com)) 28 { 29 adaper.Fill(dt); 30 com.Parameters.Clear(); 31 pageCount = Convert.ToInt32(sp.Value);//获取输出参数的值 32 return dt; 33 } 34 } 35 } 36 } 37 #endregion
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 #region 06.分页返回数据 2 /// <summary> 3 /// 分页返回数据 4 /// </summary> 5 /// <param name="sql">查询命令</param> 6 /// <param name="orderByKey">按降序或者升序获取数据 如:id desc</param> 7 /// <param name="pageIndex">要获取哪页的数据</param> 8 /// <param name="pageSize">页的大小</param> 9 /// <param name="totalRows">输出型参数:总数据行数目</param> 10 /// <param name="PageCount">输出型参数:总页数</param> 11 /// <param name="param">查询命令参数</param> 12 /// <returns>数据行集合dtatable</returns> 13 public static DataTable ExecuteSplitQueryData(string sql, string orderByKey, int pageIndex, int pageSize, out long totalRows, out long PageCount, params SqlParameter[] param) 14 { 15 16 string str = string.Format("select row_number()over(order by {0}) as rownumber,* from ({1}) as t", orderByKey, sql); 17 return ExecuteSplitQuery(str, orderByKey, pageIndex, pageSize, out totalRows, out PageCount, param); 18 19 } 20 private static DataTable ExecuteSplitQuery(string sql, string orderByKey, int pageIndex, int pageSize, out long totalRows, out long PageCount, params SqlParameter[] param) 21 { 22 //获取总行数 23 object count = ExecuteScalar(string.Format("select count(1) from ({0})t", sql), param); 24 totalRows = Convert.ToInt32(count); 25 26 //获取总页数 27 long pagecount = totalRows / pageSize; 28 long laterNum = totalRows % pageSize; 29 if (laterNum>0) 30 { 31 pagecount++; 32 } 33 PageCount = pagecount; 34 35 //获取分页后的数据 36 string str = string.Format("select * from ({0})t ", sql); 37 str += string.Format("where t.rownumber between {0} and {1} order by {2}", pageIndex * pageSize - pageSize + 1, pageIndex * pageSize, orderByKey); 38 return ExecuteDataTable(str, param); 39 } 40 #endregion
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public static int ExecuteTransaction(IDictionary<string, SqlParameter[]> dics) { int n = 0; SqlConnection con = new SqlConnection(); con.ConnectionString = conStr; SqlCommand cmd = new SqlCommand(); cmd.Connection = con; con.Open(); SqlTransaction myTransaction = con.BeginTransaction(); cmd.Transaction = myTransaction; try { foreach (var dic in dics) { cmd.CommandText = dic.Key; cmd.Parameters.Clear(); if (dic.Value != null) { cmd.Parameters.AddRange(dic.Value); } n += cmd.ExecuteNonQuery(); //循环执行命令 } myTransaction.Commit(); //提交数据库事务 return n; } catch (System.Exception ex) { myTransaction.Rollback(); throw ex; } finally { con.Close(); } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public static int ExecuteUpdate(DataSet ds, params SqlParameter[] param) { using (SqlConnection con=new SqlConnection()) { con.ConnectionString = conStr; con.Open(); using (SqlCommand com=new SqlCommand()) { com.Connection = con; com.Parameters.AddRange(param); SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = com; SqlCommandBuilder builder = new SqlCommandBuilder(); builder.DataAdapter = adapter; return adapter.Update(ds); } } }
}
作者:梦亦晓,转载请注明出处
如果此文能给您提供帮助,请点击右下角的【推荐】
如果您对此文有不同的见解或者意见,欢迎留言讨论