C# SqlDBHelper帮助类

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Data.SqlClient;
  6 using System.Data;
  7 using System.Configuration;
  8 
  9 namespace ClassLibrary
 10 {
 11     /// <summary>
 12     /// SqlDBHelper帮助类
 13     /// </summary>
 14     public class SqlDBHelper
 15     {
 16         public static SqlConnection connection;
 17         public static SqlConnection Connection
 18         {
 19             get
 20             {
 21                 string connectionString = ConfigurationManager.ConnectionStrings["mybookshop"].ConnectionString;
 22                 if (connection == null)
 23                 {
 24                     connection = new SqlConnection(connectionString);
 25                     connection.Open();
 26                 }
 27                 else if (connection.State == System.Data.ConnectionState.Closed)
 28                 {
 29                     connection = new SqlConnection(connectionString);
 30                     connection.Open();
 31                 }
 32                 else if (connection.State == System.Data.ConnectionState.Broken)
 33                 {
 34                     connection.Close();
 35                     connection.Open();
 36                 }
 37                 return connection;
 38             }
 39         }
 40       
 41         /// <summary>
 42         /// 单个数据增，删，改
 43         /// </summary>
 44         /// <param name="safeSql"></param>
 45         /// <returns></returns>
 46         public static int ExecuteCommand(string safeSql)
 47         {
 48             try
 49             {
 50                 using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
 51                 {
 52                     int result = cmd.ExecuteNonQuery();
 53                     return result;
 54                 }
 55             }
 56             catch (SqlException ex)
 57             {
 58                 throw ex;
 59             }
 60         }
 61      
 62         /// <summary>
 63         /// 带多个参数的增，删，改
 64         /// </summary>
 65         /// <param name="safeSql"></param>
 66         /// <param name="values"></param>
 67         /// <returns></returns>
 68         public static int ExecuteCommand(string safeSql, params SqlParameter[] values)
 69         {
 70             try
 71             {
 72                 using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
 73                 {
 74                     cmd.Parameters.AddRange(values);
 75                     return cmd.ExecuteNonQuery();
 76                 }
 77             }
 78             catch (SqlException ex)
 79             {
 80                 throw ex;
 81             }
 82         }
 83      
 84         /// <summary>
 85         /// 带多个参数的增，删，改
 86         /// </summary>
 87         /// <param name="safeSql"></param>
 88         /// <param name="values"></param>
 89         /// <returns></returns>
 90         public static int ExecuteCommand(string safeSql, CommandType type, params SqlParameter[] values)
 91         {
 92             try
 93             {
 94                 using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
 95                 {
 96                     cmd.CommandType = type;
 97                     cmd.Parameters.AddRange(values);
 98                     cmd.ExecuteNonQuery();
 99                     return cmd.ExecuteNonQuery();
100                 }
101             }
102             catch (SqlException ex)
103             {
104                 throw ex;
105             }
106         }
107      
108         /// <summary>
109         /// 带多个参数的增，删，改
110         /// </summary>
111         /// <param name="safeSql"></param>
112         /// <param name="values"></param>
113         /// <returns></returns>
114         public static int ExecuteCommand(string safeSql, CommandType type, int index)
115         {
116             try
117             {
118                 using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
119                 {
120                     cmd.CommandType = type;
121                     SqlParameter paramOne = new SqlParameter("@rid", SqlDbType.Int);
122                     paramOne.Value = index;
123                     cmd.Parameters.Add(paramOne);
124                     return cmd.ExecuteNonQuery();
125                 }
126             }
127             catch (SqlException ex)
128             {
129                 throw ex;
130             }
131         }
132      
133         /// <summary>
134         /// 查单个值
135         /// </summary>
136         /// <param name="safeSql"></param>
137         /// <returns></returns>
138         public static int GetScalar(string safeSql)
139         {
140             try
141             {
142                 using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
143                 {
144                     int result = Convert.ToInt32(cmd.ExecuteScalar());
145                     return result;
146                 }
147             }
148             catch (SqlException ex)
149             {
150                 throw ex;
151             }
152         }
153      
154         /// <summary>
155         /// 带参数的查询语句
156         /// </summary>
157         /// <param name="sql"></param>
158         /// <param name="values"></param>
159         /// <returns></returns>
160         public static int GetScalar(string sql, params SqlParameter[] values)
161         {
162             try
163             {
164                 using (SqlCommand cmd = new SqlCommand(sql, Connection))
165                 {
166                     cmd.Parameters.AddRange(values);
167                     int result = Convert.ToInt32(cmd.ExecuteScalar());
168                     return result;
169                 }
170             }
171             catch (SqlException ex)
172             {
173                 throw ex;
174             }
175         }
176      
177         /// <summary>
178         /// 带执行类型的ExecuteScalar
179         /// </summary>
180         /// <param name="sql"></param>
181         /// <param name="type"></param>
182         /// <param name="values"></param>
183         /// <returns></returns>
184         public static int GetScalar(string sql, CommandType type, params SqlParameter[] values)
185         {
186             try
187             {
188                 using (SqlCommand cmd = new SqlCommand(sql, Connection))
189                 {
190                     cmd.CommandType = type;
191                     cmd.Parameters.AddRange(values);
192                     int result = Convert.ToInt32(cmd.ExecuteScalar());
193                     return result;
194                 }
195             }
196             catch (SqlException ex)
197             {
198                 throw ex;
199             }
200         }
201      
202         /// <summary>
203         /// 查询表,获取多个记录
204         /// </summary>
205         /// <param name="safeSql"></param>
206         /// <returns></returns>
207         public static SqlDataReader GetReader(string safeSql)
208         {
209             try
210             {
211                 using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
212                 {
213                     SqlDataReader reader = cmd.ExecuteReader();
214                     return reader;
215                 }
216 
217             }
218             catch (SqlException ex)
219             {
220                 throw ex;
221             }
222         }
223      
224         /// <summary>
225         /// 带参数的-查询表,获取多个记录
226         /// </summary>
227         /// <param name="sql"></param>
228         /// <param name="values"></param>
229         /// <returns></returns>
230         public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
231         {
232             try
233             {
234                 using (SqlCommand cmd = new SqlCommand(sql, Connection))
235                 {
236                     cmd.Parameters.AddRange(values);
237                     SqlDataReader reader = cmd.ExecuteReader();
238                     return reader;
239                 }
240             }
241             catch (SqlException)
242             {
243                 throw;
244             }
245         }
246      
247         /// <summary>
248         /// 查询表,获取多个记录---语句，类型，参数
249         /// </summary>
250         /// <param name="safeSql"></param>
251         /// <param name="cmdType"></param>
252         /// <param name="values"></param>
253         /// <returns></returns>
254         public static SqlDataReader GetReader(string safeSql, CommandType cmdType, params SqlParameter[] values)
255         {
256             try
257             {
258                 using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
259                 {
260                     cmd.CommandType = cmdType;
261                     cmd.Parameters.AddRange(values);
262                     SqlDataReader reader = cmd.ExecuteReader();
263                     return reader;
264                 }
265             }
266             catch (SqlException ex)
267             {
268                 throw ex;
269             }
270         }
271       
272         /// <summary>
273         /// 返回datatable
274         /// </summary>
275         /// <param name="safeSql"></param>
276         /// <returns></returns>
277         public static DataTable GetDataSet(string safeSql)
278         {
279             DataSet ds = new DataSet();
280             SqlCommand cmd = new SqlCommand(safeSql, Connection);
281             SqlDataAdapter da = new SqlDataAdapter(cmd);
282             da.Fill(ds);
283             return ds.Tables[0];
284         }
285       
286         /// <summary>
287         ///  返回dataTable ,带参数使用
288         /// </summary>
289         /// <param name="sql"></param>
290         /// <param name="values"></param>
291         /// <returns></returns>
292         public static DataTable GetDataSet(string sql, params SqlParameter[] values)
293         {
294             DataSet ds = new DataSet();
295             SqlCommand cmd = new SqlCommand(sql, Connection);
296             cmd.Parameters.AddRange(values);
297             SqlDataAdapter da = new SqlDataAdapter(cmd);
298             da.Fill(ds);
299             return ds.Tables[0];
300         }      
301     }
302 }