下面代码是本人封装的一个sqlHelper类。主要功能是对数据库的CURD。以及存储过程、事物、分页的实现。（首先要引入命名空间using System.Configuration;）

public class SqlHelper

{

#region 连接字符串/// 
/// 连接数据库的字符串/// 
private static string conStr = ConfigurationManager.ConnectionStrings["sqlconStr"].ConnectionString; #endregion

 

1 #region 01.执行查询返回datatable +DataTable ExecuteDataTable(string sql, params SqlParameter[] param) 2 /// 
 3 /// 返回datatable 4 /// 
 5 /// sql语句 6 /// 数组型参数，sql语句需要替换的参数 7 /// 
      
       DataTable
       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

 

1 #region 02.返回非查询命令ExecuteNoQuery受影响的行数 +int ExecuteNoQuery(string sql, params SqlParameter[] param) 2 /// 
 3 /// 返回非查询命令ExecuteNoQuery受影响的行数 4 /// 
 5 /// sql语句 6 /// 数组型参数，sql语句需要替换的参数 7 /// 
      
       返回受影响的行数
       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

 

1 #region 03.执行查询或者非查询， 返回结果集的第一行第一列 +object ExecuteScalar(string sql, params SqlParameter[] param) 2 /// 
 3 /// 执行查询或者非查询， 返回结果集的第一行第一列 4 /// 
 5 /// sql语句 6 /// 数组型参数，sql语句需要替换的参数 7 /// 
      
       返回查询结果集的第一行第一列
       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

 

1 #region 04.返回 SqlDataReader + SqlDataReader ExecuteDataReader(string sql, params SqlParameter[] param) 2 /// 
 3 /// 返回datareader 4 /// 
 5 ///  6 ///  7 /// 
       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

 

1 #region 05.存储过程 返回数据 2 /// 
 3 /// 返回DataTable和pageCount 4 /// 
 5 ///  6 ///  7 /// 
       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

 

1 #region 06.分页返回数据  2 /// 
 3 /// 分页返回数据 4 /// 
 5 /// 查询命令 6 /// 按降序或者升序获取数据 如：id desc 7 /// 要获取哪页的数据 8 /// 页的大小 9 /// 输出型参数：总数据行数目10 /// 输出型参数：总页数11 /// 查询命令参数12 /// 
      
       数据行集合dtatable
      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

 

public static int ExecuteTransaction(IDictionary
      
        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();}}
      

 

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);}}}

 

}