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
