SQLHelp.cs[转]

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Text;
  4 using System.Data;
  5 using System.Data.SqlClient;
  6 using System.Configuration;
  7 
  8 public static class SQLHelp
  9 {
 10     private static string constr = ConfigurationManager.ConnectionStrings["constr"].ToString();
 11     /// <summary>
 12     /// 用于提交Insert Update Delete 返回受影响的行数
 13     /// </summary>
 14     /// <param name="cmdType">操作类型StoreProcdeure 或者是 sql语句</param>
 15     /// <param name="sql">sql语句或者存储过程的名称</param>
 16     /// <param name="sps">参数的数组，没有参数传递为Null值</param>
 17     /// <returns></returns>
 18     public static int ExecuteNonQuery(CommandType cmdType, string sql, params SqlParameter[] sps)
 19     {
 20         try
 21         {
 22             using (SqlConnection con = new SqlConnection(constr))
 23             {
 24                 using (SqlCommand cmd = new SqlCommand(sql, con))
 25                 {
 26                     cmd.CommandType = cmdType; //
 27                     if (sps != null)  //
 28                     {
 29                         foreach (SqlParameter var in sps)
 30                         {
 31                             cmd.Parameters.Add(var);
 32                         }
 33                     }
 34                     con.Open();
 35                     int count = cmd.ExecuteNonQuery();
 36                     con.Close();
 37                     return count;
 38                 }
 39             }
 40         }
 41         catch (Exception ex)
 42         {
 43             throw ex;
 44         }
 45     }
 46  
 47     /// <summary>
 48     /// 用于提交select 返回 SqlDataReader ,读取完成后需要关闭SqlDataReader
 49     /// </summary>
 50     /// <param name="cmdType">操作类型StoreProcdeure 或者是 sql语句</param>
 51     /// <param name="sql">sql语句或者存储过程的名称</param>
 52     /// <param name="sps">参数的数组，没有参数传递为Null值</param>
 53     /// <returns></returns>
 54     public static SqlDataReader ExecuteReader(CommandType cmdType, string sql, params SqlParameter[] sps)
 55     {
 56         try
 57         {
 58             SqlConnection con = new SqlConnection(constr);
 59             using (SqlCommand cmd = new SqlCommand(sql, con))
 60             {
 61                 cmd.CommandType = cmdType; //
 62                 if (sps != null)  //
 63                 {
 64                     foreach (SqlParameter var in sps)
 65                     {
 66                         cmd.Parameters.Add(var);
 67                     }
 68                 }
 69                 con.Open();
 70                 //关闭读取器,将自动关闭连接对象
 71                 SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 72                 return dr;
 73             }
 74         }
 75         catch (Exception ex)
 76         {
 77             throw ex;
 78         }
 79     }
 80  
 81 
 82     /// <summary>
 83     /// 用于提交select中的聚合函数,返回第一行,第一列的值
 84     /// </summary>
 85     /// <param name="cmdType">操作类型StoreProcdeure 或者是 sql语句</param>
 86     /// <param name="sql">sql语句或者存储过程的名称</param>
 87     /// <param name="sps">参数的数组，没有参数传递为Null值</param>
 88     /// <returns></returns>
 89     public static object ExecuteScalar(CommandType cmdType, string sql, params SqlParameter[] sps)
 90     {
 91         try
 92         {
 93             using (SqlConnection con = new SqlConnection(constr))
 94             {
 95                 using (SqlCommand cmd = new SqlCommand(sql, con))
 96                 {
 97                     cmd.CommandType = cmdType; //
 98                     if (sps != null)  //
 99                     {
100                         foreach (SqlParameter var in sps)
101                         {
102                             cmd.Parameters.Add(var);
103                         }
104                     }
105                     con.Open();
106                     object o = cmd.ExecuteScalar();
107                     con.Close();
108                     return o;
109                 }
110             }
111         }
112         catch (Exception ex)
113         {
114             throw ex;
115         }
116     }
117     /// <summary>
118     /// 用于提交select 返回 DataSet ,数据集中默认只有一张表格
119     /// </summary>
120     /// <param name="cmdType">操作类型StoreProcdeure 或者是 sql语句</param>
121     /// <param name="sql">sql语句或者存储过程的名称</param>
122     /// <param name="sps">参数的数组，没有参数传递为Null值</param>
123     /// <returns></returns>
124     public static DataSet ExecuteDataSet(CommandType cmdType, string sql, params SqlParameter[] sps)
125     {
126         try
127         {
128             using (SqlConnection con = new SqlConnection(constr))
129             {
130                 using (SqlDataAdapter da = new SqlDataAdapter())
131                 {
132                     SqlCommand cmd = new SqlCommand(sql, con);
133                     cmd.CommandType = cmdType; //
134                     if (sps != null)  //
135                     {
136                         foreach (SqlParameter var in sps)
137                         {
138                             cmd.Parameters.Add(var);
139                         }
140                     }
141                     da.SelectCommand = cmd;
142                     DataSet ds = new DataSet();
143                     da.Fill(ds);
144                     return ds;
145                 }
146             }
147         }
148         catch (Exception ex)
149         {
150             throw ex;
151         }
152     }
153 
154 }
155  

SQLHelp.cs

转自 http://blog.sina.com.cn/s/blog_6111b4170100fbaf.html

转载于:https://www.cnblogs.com/neohdy/archive/2013/02/23/2923730.html