自定义 SqlHelp

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Data.SqlClient;
  4 using System.Data;
  5 using System.Configuration;
  6 using System.Reflection;
  7 using System.Collections;
  8 namespace DataHelp
  9 {
 10     #region ADO.NET 访问数据库辅助类 +SqlHelp
 11     //Author:兵兵 +SqlHelp
 12     public class SqlHelp
 13     {
 14         /// <summary>
 15         /// DB连接字符串
 16         /// </summary>
 17         public static readonly string DB= ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
 18 
 19 
 20         #region ExecuteDataReader +ExecuteDataReader(string cmdText, List<SqlParameter> parameters,string connString)
 21         /// <summary>
 22         /// ExecuteDataReader(执行有参存储过程)
 23         /// </summary>
 24         /// <param name="cmdText">存储过程名称</param>
 25         /// <param name="parameters">参数列表</param>
 26         /// <param name="connString">连接字符串</param>
 27         /// <returns>SqlDataReader对象</returns>
 28         public static SqlDataReader ExecuteDataReader(string cmdText, List<SqlParameter> parameters, string connString)
 29         {
 30             SqlConnection conn = new SqlConnection(connString);
 31             SqlCommand cmd = new SqlCommand();
 32             CommandBuilder(cmdText, cmd, conn, parameters);
 33             SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 34             cmd.Parameters.Clear();
 35             return reader;
 36 
 37         }
 38         #endregion
 39 
 40         #region ExecuteDataReader +ExecuteDataReader(string cmdText,string connString)
 41         /// <summary>
 42         /// ExecuteDataReader(执行无参存储过程)
 43         /// </summary>
 44         /// <param name="cmdText">存储过程</param>
 45         /// <param name="connString">连接字符串</param>
 46         /// <returns>SqlDataReader对象</returns>
 47         public static SqlDataReader ExecuteDataReader(string cmdText, string connString)
 48         {
 49 
 50             SqlConnection conn = new SqlConnection(connString);
 51             SqlCommand cmd = new SqlCommand();
 52             CommandBuilder(cmdText, cmd, conn);
 53             SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 54             cmd.Parameters.Clear();
 55             return reader;
 56 
 57         }
 58         #endregion
 59 
 60         #region ExecuteNonQuery +ExecuteNonQuery(string cmdText, List<SqlParameter> parameters, string connString)
 61         /// <summary>
 62         /// ExecuteNonQuery(执行有参存储过程)
 63         /// </summary>
 64         /// <param name="cmdText">存储过程名称</param>
 65         /// <param name="parameters">参数列表</param>
 66         /// <param name="connString">连接字符串</param>
 67         /// <returns>数据库受影响的行数</returns>
 68         public static int ExecuteNonQuery(string cmdText, List<SqlParameter> parameters, string connString)
 69         {
 70             using (SqlConnection conn = new SqlConnection(connString))
 71             {
 72                 SqlCommand cmd = new SqlCommand();
 73                 CommandBuilder(cmdText, cmd, conn, parameters);
 74                 int result = cmd.ExecuteNonQuery();
 75                 cmd.Parameters.Clear();
 76                 return result;
 77             }
 78         }
 79         #endregion
 80 
 81         #region ExecuteNonQuery +ExecuteNonQuery(string cmdText, string connString)
 82         /// <summary>
 83         /// ExecuteNonQuery(执行无参存储过程)
 84         /// </summary>
 85         /// <param name="cmdText">存储过程名称</param>
 86         /// <param name="connString">连接字符串</param>
 87         /// <returns>数据库受影响的行数</returns>
 88         public static int ExecuteNonQuery(string cmdText, string connString)
 89         {
 90 
 91             using (SqlConnection conn = new SqlConnection(connString))
 92             {
 93                 SqlCommand cmd = new SqlCommand();
 94                 CommandBuilder(cmdText, cmd, conn);
 95                 int result = cmd.ExecuteNonQuery();
 96                 cmd.Parameters.Clear();
 97                 return result;
 98             }
 99 
100 
101         }
102         #endregion
103 
104         #region ExecuteScalar +ExecuteScalar(string cmdText, List<SqlParameter> parameters, string connString)
105         /// <summary>
106         /// ExecuteScalar(执行有参存储过程)
107         /// </summary>
108         /// <param name="cmdText">存储过程名称</param>
109         /// <param name="parameters">参数列表</param>
110         /// <param name="connString">连接字符串</param>
111         /// <returns>object</returns>
112         public static object ExecuteScalar(string cmdText, List<SqlParameter> parameters, string connString)
113         {
114 
115             using (SqlConnection conn = new SqlConnection(connString))
116             {
117                 SqlCommand cmd = new SqlCommand();
118                 CommandBuilder(cmdText, cmd, conn, parameters);
119                 object o = cmd.ExecuteScalar();
120                 cmd.Parameters.Clear();
121                 return o;
122             }
123 
124 
125         }
126         #endregion
127 
128         #region ExecuteScalar +ExecuteScalar(string cmdText, string connString)
129         /// <summary>
130         /// ExecuteScalar(执行无参存储过程)
131         /// </summary>
132         /// <param name="cmdText">存储过程名称</param>
133         /// <param name="connString">连接字符串</param>
134         /// <returns>object</returns>
135         public static object ExecuteScalar(string cmdText, string connString)
136         {
137 
138             using (SqlConnection conn = new SqlConnection(connString))
139             {
140                 SqlCommand cmd = new SqlCommand();
141                 CommandBuilder(cmdText, cmd, conn);
142                 object o = cmd.ExecuteScalar();
143                 cmd.Parameters.Clear();
144                 return o;
145             }
146 
147 
148         }
149         #endregion
150 
151         #region ExecuteDataTable +ExecuteDataTable(string cmdText, List<SqlParameter> parameters, string connString)
152         /// <summary>
153         /// ExecuteDataTable(用适配器执行有参存储过程)
154         /// </summary>
155         /// <param name="cmdText">存储过程名称</param>
156         /// <param name="parameters">参数列表</param>
157         /// <param name="connString">连接字符串</param>
158         /// <returns>DataTable</returns>
159         public static DataTable ExecuteDataTable(string cmdText, List<SqlParameter> parameters, string connString)
160         {
161 
162             using (SqlConnection conn = new SqlConnection(connString))
163             {
164                 SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);
165                 //命令类型为存储过程
166                 da.DeleteCommand.CommandType = CommandType.StoredProcedure;
167                 da.SelectCommand.Parameters.AddRange(parameters.ToArray());
168                 DataTable dt = new DataTable();
169                 da.Fill(dt);
170                 return dt;
171             }
172 
173 
174         }
175         #endregion
176 
177         #region ExecuteDataTable +ExecuteDataTable(string cmdText, string connString)
178         /// <summary>
179         /// ExecuteDataTable(用适配器执行无参存储过程)
180         /// </summary>
181         /// <param name="cmdText">存储过程名称</param>
182         /// <param name="connString">连接字符串</param>
183         /// <returns>DataTable</returns>
184         public static DataTable ExecuteDataTable(string cmdText, string connString)
185         {
186 
187             using (SqlConnection conn = new SqlConnection(connString))
188             {
189                 SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);
190                 //命令类型为存储过程
191                 da.DeleteCommand.CommandType = CommandType.StoredProcedure;
192                 DataTable dt = new DataTable();
193                 da.Fill(dt);
194                 return dt;
195             }
196         }
197         #endregion
198 
199         #region ExecuteDataTableProc(命令+适配器) +ExecuteDataTableProc(string cmdText, List<SqlParameter> parameters, string connString)
200         /// <summary>
201         /// ExecuteDataTableProc(执行有参存储过程)
202         /// </summary>
203         /// <param name="cmdText">存储过程名称</param>
204         /// <param name="parameters">参数列表</param>
205         /// <param name="connString">连接字符串</param>
206         /// <returns>DataTable</returns>
207         public static DataTable ExecuteDataTableProc(string cmdText, List<SqlParameter> parameters, string connString)
208         /// <summary>
209         {
210 
211             using (SqlConnection conn = new SqlConnection(connString))
212             {
213                 SqlCommand cmd = new SqlCommand();
214                 CommandBuilder(cmdText, cmd, conn, parameters);
215                 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
216                 DataTable dt = new DataTable();
217                 adapter.Fill(dt);
218                 cmd.Parameters.Clear();
219                 return dt;
220 
221             }
222 
223         }
224         #endregion
225 
226         #region ExecuteDataTableProc(命令+适配器) +ExecuteDataTableProc(string cmdText, string connString)
227         /// <summary>
228         /// ExecuteDataTableProc(执行无参存储过程)
229         /// </summary>
230         /// <param name="parameters">参数列表</param>
231         /// <param name="connString">连接字符串</param>
232         /// <returns>DataTable</returns>
233         public static DataTable ExecuteDataTableProc(string cmdText, string connString)
234         {
235 
236             using (SqlConnection conn = new SqlConnection(connString))
237             {
238                 SqlCommand cmd = new SqlCommand();
239                 CommandBuilder(cmdText, cmd, conn);
240                 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
241                 DataTable dt = new DataTable();
242                 adapter.Fill(dt);
243                 cmd.Parameters.Clear();
244                 return dt;
245 
246             }
247 
248         }
249         #endregion
250 
251         #region 准备命令对象 -CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn, List<SqlParameter> parameters)
252         /// <summary>
253         /// 准备命令对象(执行有参存储过程)
254         /// </summary>
255         /// <param name="cmdText">存储过程名称</param>
256         /// <param name="cmd">命令对象</param>
257         /// <param name="conn">连接对象</param>
258         /// <param name="parameters">参数列表</param>
259         private static void CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn, List<SqlParameter> parameters)
260         {
261 
262             if (conn.State == System.Data.ConnectionState.Closed)
263                 conn.Open();
264             cmd.Connection = conn;
265             cmd.CommandText = cmdText;
266             cmd.CommandType = System.Data.CommandType.StoredProcedure;
267             if (parameters.Count > 0)
268                 cmd.Parameters.AddRange(parameters.ToArray());
269 
270         }
271         #endregion
272 
273         #region 准备命令对象 -CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn)
274         /// <summary>
275         /// 准备命令对象(执行无参存储过程)
276         /// </summary>
277         /// <param name="cmdText">存储过程名称</param>
278         /// <param name="cmd">命令对象</param>
279         /// <param name="conn">连接对象</param>
280         private static void CommandBuilder(string cmdText, SqlCommand cmd, SqlConnection conn)
281         {
282 
283             if (conn.State == System.Data.ConnectionState.Closed)
284                 conn.Open();
285             cmd.Connection = conn;
286             cmd.CommandText = cmdText;
287             cmd.CommandType = System.Data.CommandType.StoredProcedure;
288 
289         }
290         #endregion
291 
292         #region 批插入 void BulkInsert( DataTable dt, string tableName, string connStr)
293         /// <summary>
294         /// 批插入 void BulkInsert( DataTable dt, string tableName, string connStr)
295         /// </summary>
296         /// <param name="dt">所有数据的表格</param>
297         /// <param name="tableName">表名</param>
298         public static int BulkInsert(DataTable dt, string tableName, string connStr)
299         {
300             int result = -1;
301             if (string.IsNullOrEmpty(tableName))
302                 throw new Exception("请指定你要插入的表名");
303             var count = dt.Rows.Count;
304             if (count == 0)
305                 return result;
306             SqlTransaction sqlBulkTran = null;
307             try
308             {
309                 using (SqlConnection conn = new SqlConnection(connStr))
310                 {
311                     if (conn.State == System.Data.ConnectionState.Closed)
312                         conn.Open();
313                     sqlBulkTran = conn.BeginTransaction();
314                     using (SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlBulkTran))
315                     {
316                         copy.DestinationTableName = tableName;//指定目标表
317                         copy.WriteToServer(dt);//将dt中的所有行复制到SqlBulkCopy对象的DestinationTableName指定的目标表中
318                         if (sqlBulkTran != null)
319                         {
320                             sqlBulkTran.Commit();
321                         }
322                         result = 1;
323                     }
324 
325                 }
326 
327             }
328             catch (Exception)
329             {
330                 if (sqlBulkTran != null)
331                 {
332                     sqlBulkTran.Rollback();
333                 }
334             }
335             finally
336             {
337                 sqlBulkTran = null;
338             }
339 
340             return result;
341         }
342         #endregion
343 
344          
345     }
346     #endregion
347 }
348 #region list 扩展方法 Author:高兵兵
349 public static class IListUtil
350 {
351     /// <summary>
352     /// 将集合类转换成DataTable 
353     /// </summary>
354     /// <param name="list">集合</param>
355     /// <returns></returns>
356     public static DataTable AsDataTable<T>(this IList<T> list)
357     {
358         DataTable result = new DataTable();
359         if (list.Count > 0)
360         {
361             PropertyInfo[] propertys = typeof(T).GetProperties();
362             foreach (PropertyInfo pi in propertys)
363             {
364                 result.Columns.Add(pi.Name, pi.PropertyType);
365             }
366 
367             for (int i = 0; i < list.Count; i++)
368             {
369                 ArrayList tempList = new ArrayList();
370                 foreach (var item in propertys)
371                 {
372                     object obj = item.GetValue(list[i], null);
373                     tempList.Add(obj);
374                 }
375 
376                 object[] array = tempList.ToArray();
377                 result.LoadDataRow(array, true);
378             }
379         }
380         return result;
381     }
382 
383 
384 }
385 #endregion