sqlHelper类

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

public class SqlHelper
{

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

连接字符串

 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

执行查询返回datatable +DataTable ExecuteDataTable(string sql, params SqlParameter[] param)

 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

02.返回非查询命令ExecuteNoQuery受影响的行数 +int

 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

03.执行查询或者非查询，返回结果集的第一行第一列 +object

 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

04.返回 SqlDataReader + SqlDataReader ExecuteDataReader(string sql, params SqlParameter[] param)

 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

05.存储过程返回数据

 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

06.分页返回数据

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

07.执行事务public static int ExecuteTransaction(IDictionary<string, SqlParameter[]> dics)

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

}
}
}

08.public static int ExecuteUpdate(DataSet ds, params SqlParameter[] param)

}

作者：梦亦晓，转载请注明出处

如果此文能给您提供帮助，请点击右下角的【推荐】

如果您对此文有不同的见解或者意见，欢迎留言讨论