1、C# SqlHelper
1 public static class SqlHelper
2 {
3 private static readonly string conStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
4
5 /// <summary>
6 /// 執行增删改的
7 /// </summary>
8 /// <param name="sql"></param>
9 /// <param name="cmdType"></param>
10 /// <param name="pms"></param>
11 /// <returns></returns>
12 public static int ExecuteNonQuery(string sql, CommandType cmdType = CommandType.Text, params SqlParameter[] pms)
13 {
14 using (SqlConnection con = new SqlConnection(conStr))
15 {
16 using (SqlCommand cmd = new SqlCommand(sql, con))
17 {
18 cmd.CommandType = cmdType;
19 if (pms != null)
20 {
21 cmd.Parameters.AddRange(pms);
22 }
23 con.Open();
24 return cmd.ExecuteNonQuery();
25 }
26 }
27 }
28
29 /// <summary>
30 /// 封裝一個執行傳回單個值的方法
31 /// </summary>
32 /// <param name="sql"></param>
33 /// <param name="cmdType"></param>
34 /// <param name="pms"></param>
35 /// <returns></returns>
36 public static object ExecuteScalar(string sql, CommandType cmdType = CommandType.Text, params SqlParameter[] pms)
37 {
38 using (SqlConnection con = new SqlConnection(conStr))
39 {
40 using (SqlCommand cmd = new SqlCommand(sql, con))
41 {
42 cmd.CommandType = cmdType;
43 if (pms != null)
44 {
45 cmd.Parameters.AddRange(pms);
46 }
47 con.Open();
48 return cmd.ExecuteScalar();
49 }
50 }
51 }
52
53 /// <summary>
54 /// 傳回SqlDataReader對象的方法
55 /// </summary>
56 /// <param name="sql"></param>
57 /// <param name="cmdType"></param>
58 /// <param name="pms"></param>
59 /// <returns></returns>
60 public static SqlDataReader ExecuteReader(string sql, CommandType cmdType = CommandType.Text, params SqlParameter[] pms)
61 {
62 SqlConnection con = new SqlConnection(conStr);
63 using (SqlCommand cmd = new SqlCommand(sql, con))
64 {
65 cmd.CommandType = cmdType;
66 if (pms != null)
67 {
68 cmd.Parameters.AddRange(pms);
69 }
70 try
71 {
72 con.Open();
73 //這裡第二個參數代表在函數外部，如果掉了close()方法，則con會随之一起銷毀
74 return cmd.ExecuteReader(CommandBehavior.CloseConnection);
75 }
76 catch (Exception)
77 {
78 con.Close();
79 con.Dispose();
80 throw;
81 }
82 }
83 }
84
85
86 /// <summary>
87 /// //執行操作SQL語句,傳回DataTable
88 /// </summary>
89 /// <param name="sql"></param>
90 /// <param name="cmdType"></param>
91 /// <param name="pms"></param>
92 /// <returns></returns>
93 public static DataTable ExecuteToDataTable(string sql,CommandType cmdType = CommandType.Text,params SqlParameter[] pms)
94 {
95 DataTable dt = new DataTable();
96 using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr))
97 {
98 adapter.SelectCommand.CommandType = cmdType;
99 if (pms != null)
100 {
101 adapter.SelectCommand.Parameters.AddRange(pms);
102 }
103 adapter.Fill(dt);
104 }
105
106 return dt;
107 }
108
109 /// <summary>
110 /// 執行操作SQL語句,傳回DataSet
111 /// </summary>
112 /// <param name="sql"></param>
113 public static DataSet ExecuteToDataSet(string sql, CommandType cmdType = CommandType.Text, params IDataParameter[] pms)
114 {
115 DataSet ds = new DataSet();
116 using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr))
117 {
118 adapter.SelectCommand.CommandType = cmdType;
119 if (pms != null)
120 {
121 adapter.SelectCommand.Parameters.AddRange(pms);
122 }
123 adapter.Fill(ds);
124 }
125 return ds;
126 }
127
128 /// <summary>
129 /// DataTable 轉換為List 集合
130 /// </summary>
131 /// <typeparam name="T">類型</typeparam>
132 /// <param name="dt">DataTable</param>
133 /// <returns></returns>
134 public static IList<T> DataTableToList<T>(DataTable dt) where T : class,new()
135 {
136 //建立一個屬性的清單
137 List<PropertyInfo> prlist = new List<PropertyInfo>();
138 //擷取T的類型執行個體 反射的入口
139 Type t = typeof(T);
140 //獲得T 的所有的Public 屬性 并找出T屬性和DataTable的列名稱相同的屬性(PropertyInfo) 并加入到屬性清單
141 Array.ForEach<PropertyInfo>(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); });
142 //建立傳回的集合
143 List<T> oblist = new List<T>();
144
145 foreach (DataRow row in dt.Rows)
146 {
147 //建立T的執行個體
148 T ob = new T();
149 //找到對應的資料 并指派
150 prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); });
151 //放入到傳回的集合中.
152 oblist.Add(ob);
153 }
154 return oblist;
155 }
156
157 /// <summary>
158 /// List集合 轉換為一個DataTable
159 /// </summary>
160 /// <typeparam name="T"></typeparam>
161 /// <param name="value"></param>
162 /// <returns></returns>
163 public static DataTable ListToDataTable<T>(IEnumerable<T> value) where T : class
164 {
165 //建立屬性的集合
166 List<PropertyInfo> pList = new List<PropertyInfo>();
167 //獲得反射的入口
168 Type type = typeof(T);
169 DataTable dt = new DataTable();
170 //把所有的public屬性加入到集合 并添加DataTable的列
171 Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name); });
172 foreach (var item in value)
173 {
174 //建立一個DataRow執行個體
175 DataRow row = dt.NewRow();
176 //給row 指派
177 pList.ForEach(p => row[p.Name] = p.GetValue(item, null));
178 //加入到DataTable
179 dt.Rows.Add(row);
180 }
181 return dt;
182 }
183
184 //IDataReder轉實體類清單
185 public static List<T> ReaderToList<T>(IDataReader DataReader)
186 {
187 using (DataReader)
188 {
189 List<string> field = new List<string>(DataReader.FieldCount);
190 for (int i = 0; i < DataReader.FieldCount; i++)
191 {
192 field.Add(DataReader.GetName(i).ToLower());
193 }
194 List<T> list = new List<T>();
195 while (DataReader.Read())
196 {
197 T model = Activator.CreateInstance<T>();
198 foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
199 {
200 if (field.Contains(property.Name.ToLower()))
201 {
202 //if (!IsNullOrDBNull(DataReader[property.Name]))
203 if (DataReader[property.Name] != DBNull.Value)
204 {
205 property.SetValue(model, HackType(DataReader[property.Name], property.PropertyType), null);
206 }
207 }
208 }
209 list.Add(model);
210 }
211 return list;
212 }
213 }
214
215 /// <summary>
216 /// IDataReder轉實體類清單 空字元串和dbnull都傳回null
217 /// </summary>
218 /// <typeparam name="T"></typeparam>
219 /// <param name="DataReader"></param>
220 /// <returns></returns>
221 public static List<T> ReaderToListNull<T>(IDataReader DataReader)
222 {
223 using (DataReader)
224 {
225 List<string> field = new List<string>(DataReader.FieldCount);
226 for (int i = 0; i < DataReader.FieldCount; i++)
227 {
228 field.Add(DataReader.GetName(i).ToLower());
229 }
230 List<T> list = new List<T>();
231 while (DataReader.Read())
232 {
233 T model = Activator.CreateInstance<T>();
234 foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
235 {
236 if (field.Contains(property.Name.ToLower()))
237 {
238 if (!IsNullOrDBNull(DataReader[property.Name]))
239 //if (DataReader[property.Name] != DBNull.Value)
240 {
241 property.SetValue(model, HackType(DataReader[property.Name], property.PropertyType), null);
242 }
243 }
244 }
245 list.Add(model);
246 }
247 return list;
248 }
249 }
250
251 //IDataReder轉實體類清單
252 public static List<dynamic> ReaderToDynamicList(IDataReader DataReader)
253 {
254 using (DataReader)
255 {
256 List<dynamic> list = new List<dynamic>();
257 while (DataReader.Read())
258 {
259 dynamic DataObject = new ExpandoObject();
260 var DataRow = DataObject as IDictionary<string, object>;
261
262 for (int i = 0; i < DataReader.FieldCount; i++)
263 {
264 DataRow.Add(DataReader.GetName(i).ToUpper(), DataReader[i]);
265 }
266 list.Add(DataRow);
267 }
268 return list;
269 }
270 }
271
272 //這個類對可空類型進行判斷轉換，要不然會報錯
273 private static object HackType(object value, Type conversionType)
274 {
275 if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
276 {
277 if (value == null)
278 return null;
279
280 System.ComponentModel.NullableConverter nullableConverter = new System.ComponentModel.NullableConverter(conversionType);
281 conversionType = nullableConverter.UnderlyingType;
282 }
283 return Convert.ChangeType(value, conversionType);
284 }
285
286 private static bool IsNullOrDBNull(object obj)
287 {
288 return ((obj is DBNull) || string.IsNullOrEmpty(obj.ToString())) ? true : false;
289 }
290 } View Code
2、c# SqlHelper微軟版本
1 using System;
2 using System.Collections;
3 using System.Collections.Generic;
4 using System.Configuration;
5 using System.Data;
6 using System.Data.SqlClient;
7 using System.Linq;
8 using System.Text;
9 using System.Threading.Tasks;
10 using System.Xml;
11
12 namespace ConsoleApplication1
13 {
14 /// <summary>
15 /// SqlServer資料通路幫助類
16 /// </summary>
17 public sealed class SqlHelper
18 {
19 #region 私有構造函數和方法
20
21 private SqlHelper() { }
22
23 /// <summary>
24 /// 将SqlParameter參數數組(參數值)配置設定給SqlCommand指令.
25 /// 這個方法将給任何一個參數配置設定DBNull.Value;
26 /// 該操作将阻止預設值的使用.
27 /// </summary>
28 /// <param name="command">指令名</param>
29 /// <param name="commandParameters">SqlParameters數組</param>
30 private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
31 {
32 if (command == null) throw new ArgumentNullException("command");
33 if (commandParameters != null)
34 {
35 foreach (SqlParameter p in commandParameters)
36 {
37 if (p != null)
38 {
39 // 檢查未配置設定值的輸出參數,将其配置設定以DBNull.Value.
40 if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
41 (p.Value == null))
42 {
43 p.Value = DBNull.Value;
44 }
45 command.Parameters.Add(p);
46 }
47 }
48 }
49 }
50
51 /// <summary>
52 /// 将DataRow類型的列值配置設定到SqlParameter參數數組.
53 /// </summary>
54 /// <param name="commandParameters">要配置設定值的SqlParameter參數數組</param>
55 /// <param name="dataRow">将要配置設定給存儲過程參數的DataRow</param>
56 private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
57 {
58 if ((commandParameters == null) || (dataRow == null))
59 {
60 return;
61 }
62
63 int i = 0;
64 // 設定參數值
65 foreach (SqlParameter commandParameter in commandParameters)
66 {
67 // 建立參數名稱,如果不存在,隻抛出一個異常.
68 if (commandParameter.ParameterName == null ||
69 commandParameter.ParameterName.Length <= 1)
70 throw new Exception(
71 string.Format("請提供參數{0}一個有效的名稱{1}.", i, commandParameter.ParameterName));
72 // 從dataRow的表中擷取為參數數組中數組名稱的列的索引.
73 // 如果存在和參數名稱相同的列,則将列值賦給目前名稱的參數.
74 if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
75 commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
76 i++;
77 }
78 }
79
80 /// <summary>
81 /// 将一個對象數組配置設定給SqlParameter參數數組.
82 /// </summary>
83 /// <param name="commandParameters">要配置設定值的SqlParameter參數數組</param>
84 /// <param name="parameterValues">将要配置設定給存儲過程參數的對象數組</param>
85 private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
86 {
87 if ((commandParameters == null) || (parameterValues == null))
88 {
89 return;
90 }
91
92 // 確定對象數組個數與參數個數比對,如果不比對,抛出一個異常.
93 if (commandParameters.Length != parameterValues.Length)
94 {
95 throw new ArgumentException("參數值個數與參數不比對.");
96 }
97
98 // 給參數指派
99 for (int i = 0, j = commandParameters.Length; i < j; i++)
100 {
101 // If the current array value derives from IDbDataParameter, then assign its Value property
102 if (parameterValues[i] is IDbDataParameter)
103 {
104 IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
105 if (paramInstance.Value == null)
106 {
107 commandParameters[i].Value = DBNull.Value;
108 }
109 else
110 {
111 commandParameters[i].Value = paramInstance.Value;
112 }
113 }
114 else if (parameterValues[i] == null)
115 {
116 commandParameters[i].Value = DBNull.Value;
117 }
118 else
119 {
120 commandParameters[i].Value = parameterValues[i];
121 }
122 }
123 }
124
125 /// <summary>
126 /// 預處理使用者提供的指令,資料庫連接配接/事務/指令類型/參數
127 /// </summary>
128 /// <param name="command">要處理的SqlCommand</param>
129 /// <param name="connection">資料庫連接配接</param>
130 /// <param name="transaction">一個有效的事務或者是null值</param>
131 /// <param name="commandType">指令類型 (存儲過程,指令文本, 其它.)</param>
132 /// <param name="commandText">存儲過程名或都T-SQL指令文本</param>
133 /// <param name="commandParameters">和指令相關聯的SqlParameter參數數組,如果沒有參數為'null'</param>
134 /// <param name="mustCloseConnection"><c>true</c> 如果連接配接是打開的,則為true,其它情況下為false.</param>
135 private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
136 {
137 if (command == null) throw new ArgumentNullException("command");
138 if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
139
140 // If the provided connection is not open, we will open it
141 if (connection.State != ConnectionState.Open)
142 {
143 mustCloseConnection = true;
144 connection.Open();
145 }
146 else
147 {
148 mustCloseConnection = false;
149 }
150
151 // 給指令配置設定一個資料庫連接配接.
152 command.Connection = connection;
153
154 // 設定指令文本(存儲過程名或SQL語句)
155 command.CommandText = commandText;
156
157 // 配置設定事務
158 if (transaction != null)
159 {
160 if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
161 command.Transaction = transaction;
162 }
163
164 // 設定指令類型.
165 command.CommandType = commandType;
166
167 // 配置設定指令參數
168 if (commandParameters != null)
169 {
170 AttachParameters(command, commandParameters);
171 }
172 return;
173 }
174
175 #endregion 私有構造函數和方法結束
176
177 #region 資料庫連接配接
178 /// <summary>
179 /// 一個有效的資料庫連接配接字元串
180 /// </summary>
181 /// <returns></returns>
182 public static string GetConnSting()
183 {
184 return ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
185 }
186 /// <summary>
187 /// 一個有效的資料庫連接配接對象
188 /// </summary>
189 /// <returns></returns>
190 public static SqlConnection GetConnection()
191 {
192 SqlConnection Connection = new SqlConnection(SqlHelper.GetConnSting());
193 return Connection;
194 }
195 #endregion
196
197 #region ExecuteNonQuery指令
198
199 /// <summary>
200 /// 執行指定連接配接字元串,類型的SqlCommand.
201 /// </summary>
202 /// <remarks>
203 /// 示例:
204 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
205 /// </remarks>
206 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
207 /// <param name="commandType">指令類型 (存儲過程,指令文本, 其它.)</param>
208 /// <param name="commandText">存儲過程名稱或SQL語句</param>
209 /// <returns>傳回指令影響的行數</returns>
210 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
211 {
212 return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
213 }
214
215 /// <summary>
216 /// 執行指定連接配接字元串,類型的SqlCommand.如果沒有提供參數,不傳回結果.
217 /// </summary>
218 /// <remarks>
219 /// 示例:
220 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
221 /// </remarks>
222 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
223 /// <param name="commandType">指令類型 (存儲過程,指令文本, 其它.)</param>
224 /// <param name="commandText">存儲過程名稱或SQL語句</param>
225 /// <param name="commandParameters">SqlParameter參數數組</param>
226 /// <returns>傳回指令影響的行數</returns>
227 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
228 {
229 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
230
231 using (SqlConnection connection = new SqlConnection(connectionString))
232 {
233 connection.Open();
234
235 return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
236 }
237 }
238
239 /// <summary>
240 /// 執行指定連接配接字元串的存儲過程,将對象數組的值賦給存儲過程參數,
241 /// 此方法需要在參數緩存方法中探索參數并生成參數.
242 /// </summary>
243 /// <remarks>
244 /// 這個方法沒有提供通路輸出參數和傳回值.
245 /// 示例:
246 /// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
247 /// </remarks>
248 /// <param name="connectionString">一個有效的資料庫連接配接字元串/param>
249 /// <param name="spName">存儲過程名稱</param>
250 /// <param name="parameterValues">配置設定到存儲過程輸入參數的對象數組</param>
251 /// <returns>傳回受影響的行數</returns>
252 public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
253 {
254 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
255 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
256
257 // 如果存在參數值
258 if ((parameterValues != null) && (parameterValues.Length > 0))
259 {
260 // 從探索存儲過程參數(加載到緩存)并配置設定給存儲過程參數數組.
261 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
262
263 // 給存儲過程參數指派
264 AssignParameterValues(commandParameters, parameterValues);
265
266 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
267 }
268 else
269 {
270 // 沒有參數情況下
271 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
272 }
273 }
274
275 /// <summary>
276 /// 執行指定資料庫連接配接對象的指令
277 /// </summary>
278 /// <remarks>
279 /// 示例:
280 /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
281 /// </remarks>
282 /// <param name="connection">一個有效的資料庫連接配接對象</param>
283 /// <param name="commandType">指令類型(存儲過程,指令文本或其它.)</param>
284 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
285 /// <returns>傳回影響的行數</returns>
286 public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
287 {
288 return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
289 }
290
291 /// <summary>
292 /// 執行指定資料庫連接配接對象的指令
293 /// </summary>
294 /// <remarks>
295 /// 示例:
296 /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
297 /// </remarks>
298 /// <param name="connection">一個有效的資料庫連接配接對象</param>
299 /// <param name="commandType">指令類型(存儲過程,指令文本或其它.)</param>
300 /// <param name="commandText">T存儲過程名稱或T-SQL語句</param>
301 /// <param name="commandParameters">SqlParamter參數數組</param>
302 /// <returns>傳回影響的行數</returns>
303 public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
304 {
305 if (connection == null) throw new ArgumentNullException("connection");
306
307 // 建立SqlCommand指令,并進行預處理
308 SqlCommand cmd = new SqlCommand();
309 bool mustCloseConnection = false;
310 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
311
312 // Finally, execute the command
313 int retval = cmd.ExecuteNonQuery();
314
315 // 清除參數,以便再次使用.
316 cmd.Parameters.Clear();
317 if (mustCloseConnection)
318 connection.Close();
319 return retval;
320 }
321
322 /// <summary>
323 /// 執行指定資料庫連接配接對象的指令,将對象數組的值賦給存儲過程參數.
324 /// </summary>
325 /// <remarks>
326 /// 此方法不提供通路存儲過程輸出參數和傳回值
327 /// 示例:
328 /// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
329 /// </remarks>
330 /// <param name="connection">一個有效的資料庫連接配接對象</param>
331 /// <param name="spName">存儲過程名</param>
332 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
333 /// <returns>傳回影響的行數</returns>
334 public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
335 {
336 if (connection == null) throw new ArgumentNullException("connection");
337 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
338
339 // 如果有參數值
340 if ((parameterValues != null) && (parameterValues.Length > 0))
341 {
342 // 從緩存中加載存儲過程參數
343 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
344
345 // 給存儲過程配置設定參數值
346 AssignParameterValues(commandParameters, parameterValues);
347
348 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
349 }
350 else
351 {
352 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
353 }
354 }
355
356 /// <summary>
357 /// 執行帶事務的SqlCommand.
358 /// </summary>
359 /// <remarks>
360 /// 示例.:
361 /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
362 /// </remarks>
363 /// <param name="transaction">一個有效的資料庫連接配接對象</param>
364 /// <param name="commandType">指令類型(存儲過程,指令文本或其它.)</param>
365 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
366 /// <returns>傳回影響的行數/returns>
367 public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
368 {
369 return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
370 }
371
372 /// <summary>
373 /// 執行帶事務的SqlCommand(指定參數).
374 /// </summary>
375 /// <remarks>
376 /// 示例:
377 /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
378 /// </remarks>
379 /// <param name="transaction">一個有效的資料庫連接配接對象</param>
380 /// <param name="commandType">指令類型(存儲過程,指令文本或其它.)</param>
381 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
382 /// <param name="commandParameters">SqlParamter參數數組</param>
383 /// <returns>傳回影響的行數</returns>
384 public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
385 {
386 if (transaction == null) throw new ArgumentNullException("transaction");
387 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
388
389 // 預處理
390 SqlCommand cmd = new SqlCommand();
391 bool mustCloseConnection = false;
392 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
393
394 // 執行
395 int retval = cmd.ExecuteNonQuery();
396
397 // 清除參數集,以便再次使用.
398 cmd.Parameters.Clear();
399 return retval;
400 }
401
402 /// <summary>
403 /// 執行帶事務的SqlCommand(指定參數值).
404 /// </summary>
405 /// <remarks>
406 /// 此方法不提供通路存儲過程輸出參數和傳回值
407 /// 示例:
408 /// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
409 /// </remarks>
410 /// <param name="transaction">一個有效的資料庫連接配接對象</param>
411 /// <param name="spName">存儲過程名</param>
412 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
413 /// <returns>傳回受影響的行數</returns>
414 public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
415 {
416 if (transaction == null) throw new ArgumentNullException("transaction");
417 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
418 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
419
420 // 如果有參數值
421 if ((parameterValues != null) && (parameterValues.Length > 0))
422 {
423 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
424 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
425
426 // 給存儲過程參數指派
427 AssignParameterValues(commandParameters, parameterValues);
428
429 // 調用重載方法
430 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
431 }
432 else
433 {
434 // 沒有參數值
435 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
436 }
437 }
438
439 #endregion ExecuteNonQuery方法結束
440
441 #region ExecuteDataset方法
442
443 /// <summary>
444 /// 執行指定資料庫連接配接字元串的指令,傳回DataSet.
445 /// </summary>
446 /// <remarks>
447 /// 示例:
448 /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
449 /// </remarks>
450 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
451 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
452 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
453 /// <returns>傳回一個包含結果集的DataSet</returns>
454 public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
455 {
456 return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
457 }
458
459 /// <summary>
460 /// 執行指定資料庫連接配接字元串的指令,傳回DataSet.
461 /// </summary>
462 /// <remarks>
463 /// 示例:
464 /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
465 /// </remarks>
466 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
467 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
468 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
469 /// <param name="commandParameters">SqlParamters參數數組</param>
470 /// <returns>傳回一個包含結果集的DataSet</returns>
471 public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
472 {
473 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
474
475 // 建立并打開資料庫連接配接對象,操作完成釋放對象.
476 using (SqlConnection connection = new SqlConnection(connectionString))
477 {
478 connection.Open();
479
480 // 調用指定資料庫連接配接字元串重載方法.
481 return ExecuteDataset(connection, commandType, commandText, commandParameters);
482 }
483 }
484
485 /// <summary>
486 /// 執行指定資料庫連接配接字元串的指令,直接提供參數值,傳回DataSet.
487 /// </summary>
488 /// <remarks>
489 /// 此方法不提供通路存儲過程輸出參數和傳回值.
490 /// 示例:
491 /// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
492 /// </remarks>
493 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
494 /// <param name="spName">存儲過程名</param>
495 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
496 /// <returns>傳回一個包含結果集的DataSet</returns>
497 public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
498 {
499 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
500 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
501
502 if ((parameterValues != null) && (parameterValues.Length > 0))
503 {
504 // 從緩存中檢索存儲過程參數
505 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
506
507 // 給存儲過程參數配置設定值
508 AssignParameterValues(commandParameters, parameterValues);
509
510 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
511 }
512 else
513 {
514 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
515 }
516 }
517
518 /// <summary>
519 /// 執行指定資料庫連接配接對象的指令,傳回DataSet.
520 /// </summary>
521 /// <remarks>
522 /// 示例:
523 /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
524 /// </remarks>
525 /// <param name="connection">一個有效的資料庫連接配接對象</param>
526 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
527 /// <param name="commandText">存儲過程名或T-SQL語句</param>
528 /// <returns>傳回一個包含結果集的DataSet</returns>
529 public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
530 {
531 return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
532 }
533
534 /// <summary>
535 /// 執行指定資料庫連接配接對象的指令,指定存儲過程參數,傳回DataSet.
536 /// </summary>
537 /// <remarks>
538 /// 示例:
539 /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
540 /// </remarks>
541 /// <param name="connection">一個有效的資料庫連接配接對象</param>
542 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
543 /// <param name="commandText">存儲過程名或T-SQL語句</param>
544 /// <param name="commandParameters">SqlParamter參數數組</param>
545 /// <returns>傳回一個包含結果集的DataSet</returns>
546 public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
547 {
548 if (connection == null) throw new ArgumentNullException("connection");
549
550 // 預處理
551 SqlCommand cmd = new SqlCommand();
552 bool mustCloseConnection = false;
553 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
554
555 // 建立SqlDataAdapter和DataSet.
556 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
557 {
558 DataSet ds = new DataSet();
559
560 // 填充DataSet.
561 da.Fill(ds);
562
563 cmd.Parameters.Clear();
564
565 if (mustCloseConnection)
566 connection.Close();
567
568 return ds;
569 }
570 }
571
572 /// <summary>
573 /// 執行指定資料庫連接配接對象的指令,指定參數值,傳回DataSet.
574 /// </summary>
575 /// <remarks>
576 /// 此方法不提供通路存儲過程輸入參數和傳回值.
577 /// 示例.:
578 /// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
579 /// </remarks>
580 /// <param name="connection">一個有效的資料庫連接配接對象</param>
581 /// <param name="spName">存儲過程名</param>
582 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
583 /// <returns>傳回一個包含結果集的DataSet</returns>
584 public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
585 {
586 if (connection == null) throw new ArgumentNullException("connection");
587 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
588
589 if ((parameterValues != null) && (parameterValues.Length > 0))
590 {
591 // 比緩存中加載存儲過程參數
592 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
593
594 // 給存儲過程參數配置設定值
595 AssignParameterValues(commandParameters, parameterValues);
596
597 return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
598 }
599 else
600 {
601 return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
602 }
603 }
604
605 /// <summary>
606 /// 執行指定事務的指令,傳回DataSet.
607 /// </summary>
608 /// <remarks>
609 /// 示例:
610 /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
611 /// </remarks>
612 /// <param name="transaction">事務</param>
613 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
614 /// <param name="commandText">存儲過程名或T-SQL語句</param>
615 /// <returns>傳回一個包含結果集的DataSet</returns>
616 public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
617 {
618 return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
619 }
620
621 /// <summary>
622 /// 執行指定事務的指令,指定參數,傳回DataSet.
623 /// </summary>
624 /// <remarks>
625 /// 示例:
626 /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
627 /// </remarks>
628 /// <param name="transaction">事務</param>
629 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
630 /// <param name="commandText">存儲過程名或T-SQL語句</param>
631 /// <param name="commandParameters">SqlParamter參數數組</param>
632 /// <returns>傳回一個包含結果集的DataSet</returns>
633 public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
634 {
635 if (transaction == null) throw new ArgumentNullException("transaction");
636 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
637
638 // 預處理
639 SqlCommand cmd = new SqlCommand();
640 bool mustCloseConnection = false;
641 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
642
643 // 建立 DataAdapter & DataSet
644 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
645 {
646 DataSet ds = new DataSet();
647 da.Fill(ds);
648 cmd.Parameters.Clear();
649 return ds;
650 }
651 }
652
653 /// <summary>
654 /// 執行指定事務的指令,指定參數值,傳回DataSet.
655 /// </summary>
656 /// <remarks>
657 /// 此方法不提供通路存儲過程輸入參數和傳回值.
658 /// 示例.:
659 /// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
660 /// </remarks>
661 /// <param name="transaction">事務</param>
662 /// <param name="spName">存儲過程名</param>
663 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
664 /// <returns>傳回一個包含結果集的DataSet</returns>
665 public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
666 {
667 if (transaction == null) throw new ArgumentNullException("transaction");
668 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
669 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
670
671 if ((parameterValues != null) && (parameterValues.Length > 0))
672 {
673 // 從緩存中加載存儲過程參數
674 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
675
676 // 給存儲過程參數配置設定值
677 AssignParameterValues(commandParameters, parameterValues);
678
679 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
680 }
681 else
682 {
683 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
684 }
685 }
686
687 #endregion ExecuteDataset資料集指令結束
688
689 #region ExecuteReader 資料閱讀器
690
691 /// <summary>
692 /// 枚舉,辨別資料庫連接配接是由SqlHelper提供還是由調用者提供
693 /// </summary>
694 private enum SqlConnectionOwnership
695 {
696 /// <summary>由SqlHelper提供連接配接</summary>
697 Internal,
698 /// <summary>由調用者提供連接配接</summary>
699 External
700 }
701
702 /// <summary>
703 /// 執行指定資料庫連接配接對象的資料閱讀器.
704 /// </summary>
705 /// <remarks>
706 /// 如果是SqlHelper打開連接配接,當連接配接關閉DataReader也将關閉.
707 /// 如果是調用都打開連接配接,DataReader由調用都管理.
708 /// </remarks>
709 /// <param name="connection">一個有效的資料庫連接配接對象</param>
710 /// <param name="transaction">一個有效的事務,或者為 'null'</param>
711 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
712 /// <param name="commandText">存儲過程名或T-SQL語句</param>
713 /// <param name="commandParameters">SqlParameters參數數組,如果沒有參數則為'null'</param>
714 /// <param name="connectionOwnership">辨別資料庫連接配接對象是由調用者提供還是由SqlHelper提供</param>
715 /// <returns>傳回包含結果集的SqlDataReader</returns>
716 private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
717 {
718 if (connection == null) throw new ArgumentNullException("connection");
719
720 bool mustCloseConnection = false;
721 // 建立指令
722 SqlCommand cmd = new SqlCommand();
723 try
724 {
725 PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
726
727 // 建立資料閱讀器
728 SqlDataReader dataReader;
729
730 if (connectionOwnership == SqlConnectionOwnership.External)
731 {
732 dataReader = cmd.ExecuteReader();
733 }
734 else
735 {
736 dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
737 }
738
739 // 清除參數,以便再次使用..
740 // HACK: There is a problem here, the output parameter values are fletched
741 // when the reader is closed, so if the parameters are detached from the command
742 // then the SqlReader can磘 set its values.
743 // When this happen, the parameters can磘 be used again in other command.
744 bool canClear = true;
745 foreach (SqlParameter commandParameter in cmd.Parameters)
746 {
747 if (commandParameter.Direction != ParameterDirection.Input)
748 canClear = false;
749 }
750
751 if (canClear)
752 {
753 cmd.Parameters.Clear();
754 }
755
756 return dataReader;
757 }
758 catch
759 {
760 if (mustCloseConnection)
761 connection.Close();
762 throw;
763 }
764 }
765
766 /// <summary>
767 /// 執行指定資料庫連接配接字元串的資料閱讀器.
768 /// </summary>
769 /// <remarks>
770 /// 示例:
771 /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
772 /// </remarks>
773 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
774 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
775 /// <param name="commandText">存儲過程名或T-SQL語句</param>
776 /// <returns>傳回包含結果集的SqlDataReader</returns>
777 public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
778 {
779 return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
780 }
781
782 /// <summary>
783 /// 執行指定資料庫連接配接字元串的資料閱讀器,指定參數.
784 /// </summary>
785 /// <remarks>
786 /// 示例:
787 /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
788 /// </remarks>
789 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
790 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
791 /// <param name="commandText">存儲過程名或T-SQL語句</param>
792 /// <param name="commandParameters">SqlParamter參數數組(new SqlParameter("@prodid", 24))</param>
793 /// <returns>傳回包含結果集的SqlDataReader</returns>
794 public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
795 {
796 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
797 SqlConnection connection = null;
798 try
799 {
800 connection = new SqlConnection(connectionString);
801 connection.Open();
802
803 return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
804 }
805 catch
806 {
807 // If we fail to return the SqlDatReader, we need to close the connection ourselves
808 if (connection != null) connection.Close();
809 throw;
810 }
811
812 }
813
814 /// <summary>
815 /// 執行指定資料庫連接配接字元串的資料閱讀器,指定參數值.
816 /// </summary>
817 /// <remarks>
818 /// 此方法不提供通路存儲過程輸出參數和傳回值參數.
819 /// 示例:
820 /// SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
821 /// </remarks>
822 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
823 /// <param name="spName">存儲過程名</param>
824 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
825 /// <returns>傳回包含結果集的SqlDataReader</returns>
826 public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
827 {
828 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
829 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
830
831 if ((parameterValues != null) && (parameterValues.Length > 0))
832 {
833 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
834
835 AssignParameterValues(commandParameters, parameterValues);
836
837 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
838 }
839 else
840 {
841 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
842 }
843 }
844
845 /// <summary>
846 /// 執行指定資料庫連接配接對象的資料閱讀器.
847 /// </summary>
848 /// <remarks>
849 /// 示例:
850 /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
851 /// </remarks>
852 /// <param name="connection">一個有效的資料庫連接配接對象</param>
853 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
854 /// <param name="commandText">存儲過程名或T-SQL語句</param>
855 /// <returns>傳回包含結果集的SqlDataReader</returns>
856 public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
857 {
858 return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null);
859 }
860
861 /// <summary>
862 /// [調用者方式]執行指定資料庫連接配接對象的資料閱讀器,指定參數.
863 /// </summary>
864 /// <remarks>
865 /// 示例:
866 /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
867 /// </remarks>
868 /// <param name="connection">一個有效的資料庫連接配接對象</param>
869 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
870 /// <param name="commandText">指令類型 (存儲過程,指令文本或其它)</param>
871 /// <param name="commandParameters">SqlParamter參數數組</param>
872 /// <returns>傳回包含結果集的SqlDataReader</returns>
873 public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
874 {
875 return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
876 }
877
878 /// <summary>
879 /// [調用者方式]執行指定資料庫連接配接對象的資料閱讀器,指定參數值.
880 /// </summary>
881 /// <remarks>
882 /// 此方法不提供通路存儲過程輸出參數和傳回值參數.
883 /// 示例:
884 /// SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
885 /// </remarks>
886 /// <param name="connection">一個有效的資料庫連接配接對象</param>
887 /// <param name="spName">T存儲過程名</param>
888 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
889 /// <returns>傳回包含結果集的SqlDataReader</returns>
890 public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
891 {
892 if (connection == null) throw new ArgumentNullException("connection");
893 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
894
895 if ((parameterValues != null) && (parameterValues.Length > 0))
896 {
897 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
898
899 AssignParameterValues(commandParameters, parameterValues);
900
901 return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
902 }
903 else
904 {
905 return ExecuteReader(connection, CommandType.StoredProcedure, spName);
906 }
907 }
908
909 /// <summary>
910 /// [調用者方式]執行指定資料庫事務的資料閱讀器,指定參數值.
911 /// </summary>
912 /// <remarks>
913 /// 示例:
914 /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
915 /// </remarks>
916 /// <param name="transaction">一個有效的連接配接事務</param>
917 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
918 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
919 /// <returns>傳回包含結果集的SqlDataReader</returns>
920 public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
921 {
922 return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null);
923 }
924
925 /// <summary>
926 /// [調用者方式]執行指定資料庫事務的資料閱讀器,指定參數.
927 /// </summary>
928 /// <remarks>
929 /// 示例:
930 /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
931 /// </remarks>
932 /// <param name="transaction">一個有效的連接配接事務</param>
933 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
934 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
935 /// <param name="commandParameters">配置設定給指令的SqlParamter參數數組</param>
936 /// <returns>傳回包含結果集的SqlDataReader</returns>
937 public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
938 {
939 if (transaction == null) throw new ArgumentNullException("transaction");
940 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
941
942 return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
943 }
944
945 /// <summary>
946 /// [調用者方式]執行指定資料庫事務的資料閱讀器,指定參數值.
947 /// </summary>
948 /// <remarks>
949 /// 此方法不提供通路存儲過程輸出參數和傳回值參數.
950 ///
951 /// 示例:
952 /// SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
953 /// </remarks>
954 /// <param name="transaction">一個有效的連接配接事務</param>
955 /// <param name="spName">存儲過程名稱</param>
956 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
957 /// <returns>傳回包含結果集的SqlDataReader</returns>
958 public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues)
959 {
960 if (transaction == null) throw new ArgumentNullException("transaction");
961 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
962 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
963
964 // 如果有參數值
965 if ((parameterValues != null) && (parameterValues.Length > 0))
966 {
967 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
968
969 AssignParameterValues(commandParameters, parameterValues);
970
971 return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
972 }
973 else
974 {
975 // 沒有參數值
976 return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
977 }
978 }
979
980 #endregion ExecuteReader資料閱讀器
981
982 #region ExecuteScalar 傳回結果集中的第一行第一列
983
984 /// <summary>
985 /// 執行指定資料庫連接配接字元串的指令,傳回結果集中的第一行第一列.
986 /// </summary>
987 /// <remarks>
988 /// 示例:
989 /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
990 /// </remarks>
991 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
992 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
993 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
994 /// <returns>傳回結果集中的第一行第一列</returns>
995 public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
996 {
997 // 執行參數為空的方法
998 return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);
999 }
1000
1001 /// <summary>
1002 /// 執行指定資料庫連接配接字元串的指令,指定參數,傳回結果集中的第一行第一列.
1003 /// </summary>
1004 /// <remarks>
1005 /// 示例:
1006 /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
1007 /// </remarks>
1008 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
1009 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1010 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
1011 /// <param name="commandParameters">配置設定給指令的SqlParamter參數數組</param>
1012 /// <returns>傳回結果集中的第一行第一列</returns>
1013 public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1014 {
1015 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1016 // 建立并打開資料庫連接配接對象,操作完成釋放對象.
1017 using (SqlConnection connection = new SqlConnection(connectionString))
1018 {
1019 connection.Open();
1020
1021 // 調用指定資料庫連接配接字元串重載方法.
1022 return ExecuteScalar(connection, commandType, commandText, commandParameters);
1023 }
1024 }
1025
1026 /// <summary>
1027 /// 執行指定資料庫連接配接字元串的指令,指定參數值,傳回結果集中的第一行第一列.
1028 /// </summary>
1029 /// <remarks>
1030 /// 此方法不提供通路存儲過程輸出參數和傳回值參數.
1031 ///
1032 /// 示例:
1033 /// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
1034 /// </remarks>
1035 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
1036 /// <param name="spName">存儲過程名稱</param>
1037 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
1038 /// <returns>傳回結果集中的第一行第一列</returns>
1039 public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
1040 {
1041 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1042 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1043
1044 // 如果有參數值
1045 if ((parameterValues != null) && (parameterValues.Length > 0))
1046 {
1047 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
1048 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
1049
1050 // 給存儲過程參數指派
1051 AssignParameterValues(commandParameters, parameterValues);
1052
1053 // 調用重載方法
1054 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
1055 }
1056 else
1057 {
1058 // 沒有參數值
1059 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
1060 }
1061 }
1062
1063 /// <summary>
1064 /// 執行指定資料庫連接配接對象的指令,傳回結果集中的第一行第一列.
1065 /// </summary>
1066 /// <remarks>
1067 /// 示例:
1068 /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
1069 /// </remarks>
1070 /// <param name="connection">一個有效的資料庫連接配接對象</param>
1071 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1072 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
1073 /// <returns>傳回結果集中的第一行第一列</returns>
1074 public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
1075 {
1076 // 執行參數為空的方法
1077 return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
1078 }
1079
1080 /// <summary>
1081 /// 執行指定資料庫連接配接對象的指令,指定參數,傳回結果集中的第一行第一列.
1082 /// </summary>
1083 /// <remarks>
1084 /// 示例:
1085 /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
1086 /// </remarks>
1087 /// <param name="connection">一個有效的資料庫連接配接對象</param>
1088 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1089 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
1090 /// <param name="commandParameters">配置設定給指令的SqlParamter參數數組</param>
1091 /// <returns>傳回結果集中的第一行第一列</returns>
1092 public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1093 {
1094 if (connection == null) throw new ArgumentNullException("connection");
1095
1096 // 建立SqlCommand指令,并進行預處理
1097 SqlCommand cmd = new SqlCommand();
1098
1099 bool mustCloseConnection = false;
1100 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
1101
1102 // 執行SqlCommand指令,并傳回結果.
1103 object retval = cmd.ExecuteScalar();
1104
1105 // 清除參數,以便再次使用.
1106 cmd.Parameters.Clear();
1107
1108 if (mustCloseConnection)
1109 connection.Close();
1110
1111 return retval;
1112 }
1113
1114 /// <summary>
1115 /// 執行指定資料庫連接配接對象的指令,指定參數值,傳回結果集中的第一行第一列.
1116 /// </summary>
1117 /// <remarks>
1118 /// 此方法不提供通路存儲過程輸出參數和傳回值參數.
1119 ///
1120 /// 示例:
1121 /// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
1122 /// </remarks>
1123 /// <param name="connection">一個有效的資料庫連接配接對象</param>
1124 /// <param name="spName">存儲過程名稱</param>
1125 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
1126 /// <returns>傳回結果集中的第一行第一列</returns>
1127 public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
1128 {
1129 if (connection == null) throw new ArgumentNullException("connection");
1130 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1131
1132 // 如果有參數值
1133 if ((parameterValues != null) && (parameterValues.Length > 0))
1134 {
1135 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
1136 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1137
1138 // 給存儲過程參數指派
1139 AssignParameterValues(commandParameters, parameterValues);
1140
1141 // 調用重載方法
1142 return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
1143 }
1144 else
1145 {
1146 // 沒有參數值
1147 return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
1148 }
1149 }
1150
1151 /// <summary>
1152 /// 執行指定資料庫事務的指令,傳回結果集中的第一行第一列.
1153 /// </summary>
1154 /// <remarks>
1155 /// 示例:
1156 /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
1157 /// </remarks>
1158 /// <param name="transaction">一個有效的連接配接事務</param>
1159 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1160 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
1161 /// <returns>傳回結果集中的第一行第一列</returns>
1162 public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
1163 {
1164 // 執行參數為空的方法
1165 return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
1166 }
1167
1168 /// <summary>
1169 /// 執行指定資料庫事務的指令,指定參數,傳回結果集中的第一行第一列.
1170 /// </summary>
1171 /// <remarks>
1172 /// 示例:
1173 /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
1174 /// </remarks>
1175 /// <param name="transaction">一個有效的連接配接事務</param>
1176 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1177 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
1178 /// <param name="commandParameters">配置設定給指令的SqlParamter參數數組</param>
1179 /// <returns>傳回結果集中的第一行第一列</returns>
1180 public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1181 {
1182 if (transaction == null) throw new ArgumentNullException("transaction");
1183 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1184
1185 // 建立SqlCommand指令,并進行預處理
1186 SqlCommand cmd = new SqlCommand();
1187 bool mustCloseConnection = false;
1188 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
1189
1190 // 執行SqlCommand指令,并傳回結果.
1191 object retval = cmd.ExecuteScalar();
1192
1193 // 清除參數,以便再次使用.
1194 cmd.Parameters.Clear();
1195 return retval;
1196 }
1197
1198 /// <summary>
1199 /// 執行指定資料庫事務的指令,指定參數值,傳回結果集中的第一行第一列.
1200 /// </summary>
1201 /// <remarks>
1202 /// 此方法不提供通路存儲過程輸出參數和傳回值參數.
1203 ///
1204 /// 示例:
1205 /// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
1206 /// </remarks>
1207 /// <param name="transaction">一個有效的連接配接事務</param>
1208 /// <param name="spName">存儲過程名稱</param>
1209 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
1210 /// <returns>傳回結果集中的第一行第一列</returns>
1211 public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
1212 {
1213 if (transaction == null) throw new ArgumentNullException("transaction");
1214 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1215 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1216
1217 // 如果有參數值
1218 if ((parameterValues != null) && (parameterValues.Length > 0))
1219 {
1220 // PPull the parameters for this stored procedure from the parameter cache ()
1221 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
1222
1223 // 給存儲過程參數指派
1224 AssignParameterValues(commandParameters, parameterValues);
1225
1226 // 調用重載方法
1227 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
1228 }
1229 else
1230 {
1231 // 沒有參數值
1232 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
1233 }
1234 }
1235
1236 #endregion ExecuteScalar
1237
1238 #region ExecuteXmlReader XML閱讀器
1239 /// <summary>
1240 /// 執行指定資料庫連接配接對象的SqlCommand指令,并産生一個XmlReader對象做為結果集傳回.
1241 /// </summary>
1242 /// <remarks>
1243 /// 示例:
1244 /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
1245 /// </remarks>
1246 /// <param name="connection">一個有效的資料庫連接配接對象</param>
1247 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1248 /// <param name="commandText">存儲過程名稱或T-SQL語句 using "FOR XML AUTO"</param>
1249 /// <returns>傳回XmlReader結果集對象.</returns>
1250 public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
1251 {
1252 // 執行參數為空的方法
1253 return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
1254 }
1255
1256 /// <summary>
1257 /// 執行指定資料庫連接配接對象的SqlCommand指令,并産生一個XmlReader對象做為結果集傳回,指定參數.
1258 /// </summary>
1259 /// <remarks>
1260 /// 示例:
1261 /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
1262 /// </remarks>
1263 /// <param name="connection">一個有效的資料庫連接配接對象</param>
1264 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1265 /// <param name="commandText">存儲過程名稱或T-SQL語句 using "FOR XML AUTO"</param>
1266 /// <param name="commandParameters">配置設定給指令的SqlParamter參數數組</param>
1267 /// <returns>傳回XmlReader結果集對象.</returns>
1268 public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1269 {
1270 if (connection == null) throw new ArgumentNullException("connection");
1271
1272 bool mustCloseConnection = false;
1273 // 建立SqlCommand指令,并進行預處理
1274 SqlCommand cmd = new SqlCommand();
1275 try
1276 {
1277 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
1278
1279 // 執行指令
1280 XmlReader retval = cmd.ExecuteXmlReader();
1281
1282 // 清除參數,以便再次使用.
1283 cmd.Parameters.Clear();
1284
1285 return retval;
1286 }
1287 catch
1288 {
1289 if (mustCloseConnection)
1290 connection.Close();
1291 throw;
1292 }
1293 }
1294
1295 /// <summary>
1296 /// 執行指定資料庫連接配接對象的SqlCommand指令,并産生一個XmlReader對象做為結果集傳回,指定參數值.
1297 /// </summary>
1298 /// <remarks>
1299 /// 此方法不提供通路存儲過程輸出參數和傳回值參數.
1300 ///
1301 /// 示例:
1302 /// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);
1303 /// </remarks>
1304 /// <param name="connection">一個有效的資料庫連接配接對象</param>
1305 /// <param name="spName">存儲過程名稱 using "FOR XML AUTO"</param>
1306 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
1307 /// <returns>傳回XmlReader結果集對象.</returns>
1308 public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
1309 {
1310 if (connection == null) throw new ArgumentNullException("connection");
1311 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1312
1313 // 如果有參數值
1314 if ((parameterValues != null) && (parameterValues.Length > 0))
1315 {
1316 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
1317 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1318
1319 // 給存儲過程參數指派
1320 AssignParameterValues(commandParameters, parameterValues);
1321
1322 // 調用重載方法
1323 return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
1324 }
1325 else
1326 {
1327 // 沒有參數值
1328 return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
1329 }
1330 }
1331
1332 /// <summary>
1333 /// 執行指定資料庫事務的SqlCommand指令,并産生一個XmlReader對象做為結果集傳回.
1334 /// </summary>
1335 /// <remarks>
1336 /// 示例:
1337 /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");
1338 /// </remarks>
1339 /// <param name="transaction">一個有效的連接配接事務</param>
1340 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1341 /// <param name="commandText">存儲過程名稱或T-SQL語句 using "FOR XML AUTO"</param>
1342 /// <returns>傳回XmlReader結果集對象.</returns>
1343 public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
1344 {
1345 // 執行參數為空的方法
1346 return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
1347 }
1348
1349 /// <summary>
1350 /// 執行指定資料庫事務的SqlCommand指令,并産生一個XmlReader對象做為結果集傳回,指定參數.
1351 /// </summary>
1352 /// <remarks>
1353 /// 示例:
1354 /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
1355 /// </remarks>
1356 /// <param name="transaction">一個有效的連接配接事務</param>
1357 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1358 /// <param name="commandText">存儲過程名稱或T-SQL語句 using "FOR XML AUTO"</param>
1359 /// <param name="commandParameters">配置設定給指令的SqlParamter參數數組</param>
1360 /// <returns>傳回XmlReader結果集對象.</returns>
1361 public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1362 {
1363 if (transaction == null) throw new ArgumentNullException("transaction");
1364 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1365
1366 // 建立SqlCommand指令,并進行預處理
1367 SqlCommand cmd = new SqlCommand();
1368 bool mustCloseConnection = false;
1369 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
1370
1371 // 執行指令
1372 XmlReader retval = cmd.ExecuteXmlReader();
1373
1374 // 清除參數,以便再次使用.
1375 cmd.Parameters.Clear();
1376 return retval;
1377 }
1378
1379 /// <summary>
1380 /// 執行指定資料庫事務的SqlCommand指令,并産生一個XmlReader對象做為結果集傳回,指定參數值.
1381 /// </summary>
1382 /// <remarks>
1383 /// 此方法不提供通路存儲過程輸出參數和傳回值參數.
1384 ///
1385 /// 示例:
1386 /// XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36);
1387 /// </remarks>
1388 /// <param name="transaction">一個有效的連接配接事務</param>
1389 /// <param name="spName">存儲過程名稱</param>
1390 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
1391 /// <returns>傳回一個包含結果集的DataSet.</returns>
1392 public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues)
1393 {
1394 if (transaction == null) throw new ArgumentNullException("transaction");
1395 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1396 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1397
1398 // 如果有參數值
1399 if ((parameterValues != null) && (parameterValues.Length > 0))
1400 {
1401 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
1402 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
1403
1404 // 給存儲過程參數指派
1405 AssignParameterValues(commandParameters, parameterValues);
1406
1407 // 調用重載方法
1408 return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
1409 }
1410 else
1411 {
1412 // 沒有參數值
1413 return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
1414 }
1415 }
1416
1417 #endregion ExecuteXmlReader 閱讀器結束
1418
1419 #region FillDataset 填充資料集
1420 /// <summary>
1421 /// 執行指定資料庫連接配接字元串的指令,映射資料表并填充資料集.
1422 /// </summary>
1423 /// <remarks>
1424 /// 示例:
1425 /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
1426 /// </remarks>
1427 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
1428 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1429 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
1430 /// <param name="dataSet">要填充結果集的DataSet執行個體</param>
1431 /// <param name="tableNames">表映射的資料表數組
1432 /// 使用者定義的表名 (可有是實際的表名.)</param>
1433 public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
1434 {
1435 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1436 if (dataSet == null) throw new ArgumentNullException("dataSet");
1437
1438 // 建立并打開資料庫連接配接對象,操作完成釋放對象.
1439 using (SqlConnection connection = new SqlConnection(connectionString))
1440 {
1441 connection.Open();
1442
1443 // 調用指定資料庫連接配接字元串重載方法.
1444 FillDataset(connection, commandType, commandText, dataSet, tableNames);
1445 }
1446 }
1447
1448 /// <summary>
1449 /// 執行指定資料庫連接配接字元串的指令,映射資料表并填充資料集.指定指令參數.
1450 /// </summary>
1451 /// <remarks>
1452 /// 示例:
1453 /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
1454 /// </remarks>
1455 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
1456 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1457 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
1458 /// <param name="commandParameters">配置設定給指令的SqlParamter參數數組</param>
1459 /// <param name="dataSet">要填充結果集的DataSet執行個體</param>
1460 /// <param name="tableNames">表映射的資料表數組
1461 /// 使用者定義的表名 (可有是實際的表名.)
1462 /// </param>
1463 public static void FillDataset(string connectionString, CommandType commandType,
1464 string commandText, DataSet dataSet, string[] tableNames,
1465 params SqlParameter[] commandParameters)
1466 {
1467 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1468 if (dataSet == null) throw new ArgumentNullException("dataSet");
1469 // 建立并打開資料庫連接配接對象,操作完成釋放對象.
1470 using (SqlConnection connection = new SqlConnection(connectionString))
1471 {
1472 connection.Open();
1473
1474 // 調用指定資料庫連接配接字元串重載方法.
1475 FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
1476 }
1477 }
1478
1479 /// <summary>
1480 /// 執行指定資料庫連接配接字元串的指令,映射資料表并填充資料集,指定存儲過程參數值.
1481 /// </summary>
1482 /// <remarks>
1483 /// 此方法不提供通路存儲過程輸出參數和傳回值參數.
1484 ///
1485 /// 示例:
1486 /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24);
1487 /// </remarks>
1488 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
1489 /// <param name="spName">存儲過程名稱</param>
1490 /// <param name="dataSet">要填充結果集的DataSet執行個體</param>
1491 /// <param name="tableNames">表映射的資料表數組
1492 /// 使用者定義的表名 (可有是實際的表名.)
1493 /// </param>
1494 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
1495 public static void FillDataset(string connectionString, string spName,
1496 DataSet dataSet, string[] tableNames,
1497 params object[] parameterValues)
1498 {
1499 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1500 if (dataSet == null) throw new ArgumentNullException("dataSet");
1501 // 建立并打開資料庫連接配接對象,操作完成釋放對象.
1502 using (SqlConnection connection = new SqlConnection(connectionString))
1503 {
1504 connection.Open();
1505
1506 // 調用指定資料庫連接配接字元串重載方法.
1507 FillDataset(connection, spName, dataSet, tableNames, parameterValues);
1508 }
1509 }
1510
1511 /// <summary>
1512 /// 執行指定資料庫連接配接對象的指令,映射資料表并填充資料集.
1513 /// </summary>
1514 /// <remarks>
1515 /// 示例:
1516 /// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
1517 /// </remarks>
1518 /// <param name="connection">一個有效的資料庫連接配接對象</param>
1519 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1520 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
1521 /// <param name="dataSet">要填充結果集的DataSet執行個體</param>
1522 /// <param name="tableNames">表映射的資料表數組
1523 /// 使用者定義的表名 (可有是實際的表名.)
1524 /// </param>
1525 public static void FillDataset(SqlConnection connection, CommandType commandType,
1526 string commandText, DataSet dataSet, string[] tableNames)
1527 {
1528 FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
1529 }
1530
1531 /// <summary>
1532 /// 執行指定資料庫連接配接對象的指令,映射資料表并填充資料集,指定參數.
1533 /// </summary>
1534 /// <remarks>
1535 /// 示例:
1536 /// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
1537 /// </remarks>
1538 /// <param name="connection">一個有效的資料庫連接配接對象</param>
1539 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1540 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
1541 /// <param name="dataSet">要填充結果集的DataSet執行個體</param>
1542 /// <param name="tableNames">表映射的資料表數組
1543 /// 使用者定義的表名 (可有是實際的表名.)
1544 /// </param>
1545 /// <param name="commandParameters">配置設定給指令的SqlParamter參數數組</param>
1546 public static void FillDataset(SqlConnection connection, CommandType commandType,
1547 string commandText, DataSet dataSet, string[] tableNames,
1548 params SqlParameter[] commandParameters)
1549 {
1550 FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
1551 }
1552
1553 /// <summary>
1554 /// 執行指定資料庫連接配接對象的指令,映射資料表并填充資料集,指定存儲過程參數值.
1555 /// </summary>
1556 /// <remarks>
1557 /// 此方法不提供通路存儲過程輸出參數和傳回值參數.
1558 ///
1559 /// 示例:
1560 /// FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36);
1561 /// </remarks>
1562 /// <param name="connection">一個有效的資料庫連接配接對象</param>
1563 /// <param name="spName">存儲過程名稱</param>
1564 /// <param name="dataSet">要填充結果集的DataSet執行個體</param>
1565 /// <param name="tableNames">表映射的資料表數組
1566 /// 使用者定義的表名 (可有是實際的表名.)
1567 /// </param>
1568 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
1569 public static void FillDataset(SqlConnection connection, string spName,
1570 DataSet dataSet, string[] tableNames,
1571 params object[] parameterValues)
1572 {
1573 if (connection == null) throw new ArgumentNullException("connection");
1574 if (dataSet == null) throw new ArgumentNullException("dataSet");
1575 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1576
1577 // 如果有參數值
1578 if ((parameterValues != null) && (parameterValues.Length > 0))
1579 {
1580 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
1581 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1582
1583 // 給存儲過程參數指派
1584 AssignParameterValues(commandParameters, parameterValues);
1585
1586 // 調用重載方法
1587 FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
1588 }
1589 else
1590 {
1591 // 沒有參數值
1592 FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
1593 }
1594 }
1595
1596 /// <summary>
1597 /// 執行指定資料庫事務的指令,映射資料表并填充資料集.
1598 /// </summary>
1599 /// <remarks>
1600 /// 示例:
1601 /// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
1602 /// </remarks>
1603 /// <param name="transaction">一個有效的連接配接事務</param>
1604 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1605 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
1606 /// <param name="dataSet">要填充結果集的DataSet執行個體</param>
1607 /// <param name="tableNames">表映射的資料表數組
1608 /// 使用者定義的表名 (可有是實際的表名.)
1609 /// </param>
1610 public static void FillDataset(SqlTransaction transaction, CommandType commandType,
1611 string commandText,
1612 DataSet dataSet, string[] tableNames)
1613 {
1614 FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
1615 }
1616
1617 /// <summary>
1618 /// 執行指定資料庫事務的指令,映射資料表并填充資料集,指定參數.
1619 /// </summary>
1620 /// <remarks>
1621 /// 示例:
1622 /// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
1623 /// </remarks>
1624 /// <param name="transaction">一個有效的連接配接事務</param>
1625 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1626 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
1627 /// <param name="dataSet">要填充結果集的DataSet執行個體</param>
1628 /// <param name="tableNames">表映射的資料表數組
1629 /// 使用者定義的表名 (可有是實際的表名.)
1630 /// </param>
1631 /// <param name="commandParameters">配置設定給指令的SqlParamter參數數組</param>
1632 public static void FillDataset(SqlTransaction transaction, CommandType commandType,
1633 string commandText, DataSet dataSet, string[] tableNames,
1634 params SqlParameter[] commandParameters)
1635 {
1636 FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
1637 }
1638
1639 /// <summary>
1640 /// 執行指定資料庫事務的指令,映射資料表并填充資料集,指定存儲過程參數值.
1641 /// </summary>
1642 /// <remarks>
1643 /// 此方法不提供通路存儲過程輸出參數和傳回值參數.
1644 ///
1645 /// 示例:
1646 /// FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36);
1647 /// </remarks>
1648 /// <param name="transaction">一個有效的連接配接事務</param>
1649 /// <param name="spName">存儲過程名稱</param>
1650 /// <param name="dataSet">要填充結果集的DataSet執行個體</param>
1651 /// <param name="tableNames">表映射的資料表數組
1652 /// 使用者定義的表名 (可有是實際的表名.)
1653 /// </param>
1654 /// <param name="parameterValues">配置設定給存儲過程輸入參數的對象數組</param>
1655 public static void FillDataset(SqlTransaction transaction, string spName,
1656 DataSet dataSet, string[] tableNames,
1657 params object[] parameterValues)
1658 {
1659 if (transaction == null) throw new ArgumentNullException("transaction");
1660 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1661 if (dataSet == null) throw new ArgumentNullException("dataSet");
1662 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1663
1664 // 如果有參數值
1665 if ((parameterValues != null) && (parameterValues.Length > 0))
1666 {
1667 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
1668 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
1669
1670 // 給存儲過程參數指派
1671 AssignParameterValues(commandParameters, parameterValues);
1672
1673 // 調用重載方法
1674 FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
1675 }
1676 else
1677 {
1678 // 沒有參數值
1679 FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
1680 }
1681 }
1682
1683 /// <summary>
1684 /// [私有方法][内部調用]執行指定資料庫連接配接對象/事務的指令,映射資料表并填充資料集,DataSet/TableNames/SqlParameters.
1685 /// </summary>
1686 /// <remarks>
1687 /// 示例:
1688 /// FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
1689 /// </remarks>
1690 /// <param name="connection">一個有效的資料庫連接配接對象</param>
1691 /// <param name="transaction">一個有效的連接配接事務</param>
1692 /// <param name="commandType">指令類型 (存儲過程,指令文本或其它)</param>
1693 /// <param name="commandText">存儲過程名稱或T-SQL語句</param>
1694 /// <param name="dataSet">要填充結果集的DataSet執行個體</param>
1695 /// <param name="tableNames">表映射的資料表數組
1696 /// 使用者定義的表名 (可有是實際的表名.)
1697 /// </param>
1698 /// <param name="commandParameters">配置設定給指令的SqlParamter參數數組</param>
1699 private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType,
1700 string commandText, DataSet dataSet, string[] tableNames,
1701 params SqlParameter[] commandParameters)
1702 {
1703 if (connection == null) throw new ArgumentNullException("connection");
1704 if (dataSet == null) throw new ArgumentNullException("dataSet");
1705
1706 // 建立SqlCommand指令,并進行預處理
1707 SqlCommand command = new SqlCommand();
1708 bool mustCloseConnection = false;
1709 PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
1710
1711 // 執行指令
1712 using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
1713 {
1714
1715 // 追加表映射
1716 if (tableNames != null && tableNames.Length > 0)
1717 {
1718 string tableName = "Table";
1719 for (int index = 0; index < tableNames.Length; index++)
1720 {
1721 if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
1722 dataAdapter.TableMappings.Add(tableName, tableNames[index]);
1723 tableName += (index + 1).ToString();
1724 }
1725 }
1726
1727 // 填充資料集使用預設表名稱
1728 dataAdapter.Fill(dataSet);
1729
1730 // 清除參數,以便再次使用.
1731 command.Parameters.Clear();
1732 }
1733
1734 if (mustCloseConnection)
1735 connection.Close();
1736 }
1737 #endregion
1738
1739 #region UpdateDataset 更新資料集
1740 /// <summary>
1741 /// 執行資料集更新到資料庫,指定inserted, updated, or deleted指令.
1742 /// </summary>
1743 /// <remarks>
1744 /// 示例:
1745 /// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
1746 /// </remarks>
1747 /// <param name="insertCommand">[追加記錄]一個有效的T-SQL語句或存儲過程</param>
1748 /// <param name="deleteCommand">[删除記錄]一個有效的T-SQL語句或存儲過程</param>
1749 /// <param name="updateCommand">[更新記錄]一個有效的T-SQL語句或存儲過程</param>
1750 /// <param name="dataSet">要更新到資料庫的DataSet</param>
1751 /// <param name="tableName">要更新到資料庫的DataTable</param>
1752 public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
1753 {
1754 if (insertCommand == null) throw new ArgumentNullException("insertCommand");
1755 if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
1756 if (updateCommand == null) throw new ArgumentNullException("updateCommand");
1757 if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
1758
1759 // 建立SqlDataAdapter,當操作完成後釋放.
1760 using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
1761 {
1762 // 設定資料擴充卡指令
1763 dataAdapter.UpdateCommand = updateCommand;
1764 dataAdapter.InsertCommand = insertCommand;
1765 dataAdapter.DeleteCommand = deleteCommand;
1766
1767 // 更新資料集改變到資料庫
1768 dataAdapter.Update(dataSet, tableName);
1769
1770 // 送出所有改變到資料集.
1771 dataSet.AcceptChanges();
1772 }
1773 }
1774 #endregion
1775
1776 #region CreateCommand 建立一條SqlCommand指令
1777 /// <summary>
1778 /// 建立SqlCommand指令,指定資料庫連接配接對象,存儲過程名和參數.
1779 /// </summary>
1780 /// <remarks>
1781 /// 示例:
1782 /// SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
1783 /// </remarks>
1784 /// <param name="connection">一個有效的資料庫連接配接對象</param>
1785 /// <param name="spName">存儲過程名稱</param>
1786 /// <param name="sourceColumns">源表的列名稱數組</param>
1787 /// <returns>傳回SqlCommand指令</returns>
1788 public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns)
1789 {
1790 if (connection == null) throw new ArgumentNullException("connection");
1791 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1792
1793 // 建立指令
1794 SqlCommand cmd = new SqlCommand(spName, connection);
1795 cmd.CommandType = CommandType.StoredProcedure;
1796
1797 // 如果有參數值
1798 if ((sourceColumns != null) && (sourceColumns.Length > 0))
1799 {
1800 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
1801 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1802
1803 // 将源表的列到映射到DataSet指令中.
1804 for (int index = 0; index < sourceColumns.Length; index++)
1805 commandParameters[index].SourceColumn = sourceColumns[index];
1806
1807 // Attach the discovered parameters to the SqlCommand object
1808 AttachParameters(cmd, commandParameters);
1809 }
1810
1811 return cmd;
1812 }
1813 #endregion
1814
1815 #region ExecuteNonQueryTypedParams 類型化參數(DataRow)
1816 /// <summary>
1817 /// 執行指定連接配接資料庫連接配接字元串的存儲過程,使用DataRow做為參數值,傳回受影響的行數.
1818 /// </summary>
1819 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
1820 /// <param name="spName">存儲過程名稱</param>
1821 /// <param name="dataRow">使用DataRow作為參數值</param>
1822 /// <returns>傳回影響的行數</returns>
1823 public static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow)
1824 {
1825 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1826 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1827
1828 // 如果row有值,存儲過程必須初始化.
1829 if (dataRow != null && dataRow.ItemArray.Length > 0)
1830 {
1831 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
1832 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
1833
1834 // 配置設定參數值
1835 AssignParameterValues(commandParameters, dataRow);
1836
1837 return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
1838 }
1839 else
1840 {
1841 return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
1842 }
1843 }
1844
1845 /// <summary>
1846 /// 執行指定連接配接資料庫連接配接對象的存儲過程,使用DataRow做為參數值,傳回受影響的行數.
1847 /// </summary>
1848 /// <param name="connection">一個有效的資料庫連接配接對象</param>
1849 /// <param name="spName">存儲過程名稱</param>
1850 /// <param name="dataRow">使用DataRow作為參數值</param>
1851 /// <returns>傳回影響的行數</returns>
1852 public static int ExecuteNonQueryTypedParams(SqlConnection connection, String spName, DataRow dataRow)
1853 {
1854 if (connection == null) throw new ArgumentNullException("connection");
1855 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1856
1857 // 如果row有值,存儲過程必須初始化.
1858 if (dataRow != null && dataRow.ItemArray.Length > 0)
1859 {
1860 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
1861 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1862
1863 // 配置設定參數值
1864 AssignParameterValues(commandParameters, dataRow);
1865
1866 return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
1867 }
1868 else
1869 {
1870 return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
1871 }
1872 }
1873
1874 /// <summary>
1875 /// 執行指定連接配接資料庫事物的存儲過程,使用DataRow做為參數值,傳回受影響的行數.
1876 /// </summary>
1877 /// <param name="transaction">一個有效的連接配接事務 object</param>
1878 /// <param name="spName">存儲過程名稱</param>
1879 /// <param name="dataRow">使用DataRow作為參數值</param>
1880 /// <returns>傳回影響的行數</returns>
1881 public static int ExecuteNonQueryTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
1882 {
1883 if (transaction == null) throw new ArgumentNullException("transaction");
1884 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1885 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1886
1887 // Sf the row has values, the store procedure parameters must be initialized
1888 if (dataRow != null && dataRow.ItemArray.Length > 0)
1889 {
1890 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
1891 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
1892
1893 // 配置設定參數值
1894 AssignParameterValues(commandParameters, dataRow);
1895
1896 return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
1897 }
1898 else
1899 {
1900 return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
1901 }
1902 }
1903 #endregion
1904
1905 #region ExecuteDatasetTypedParams 類型化參數(DataRow)
1906 /// <summary>
1907 /// 執行指定連接配接資料庫連接配接字元串的存儲過程,使用DataRow做為參數值,傳回DataSet.
1908 /// </summary>
1909 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
1910 /// <param name="spName">存儲過程名稱</param>
1911 /// <param name="dataRow">使用DataRow作為參數值</param>
1912 /// <returns>傳回一個包含結果集的DataSet.</returns>
1913 public static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow)
1914 {
1915 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1916 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1917
1918 //如果row有值,存儲過程必須初始化.
1919 if (dataRow != null && dataRow.ItemArray.Length > 0)
1920 {
1921 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
1922 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
1923
1924 // 配置設定參數值
1925 AssignParameterValues(commandParameters, dataRow);
1926
1927 return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
1928 }
1929 else
1930 {
1931 return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
1932 }
1933 }
1934
1935 /// <summary>
1936 /// 執行指定連接配接資料庫連接配接對象的存儲過程,使用DataRow做為參數值,傳回DataSet.
1937 /// </summary>
1938 /// <param name="connection">一個有效的資料庫連接配接對象</param>
1939 /// <param name="spName">存儲過程名稱</param>
1940 /// <param name="dataRow">使用DataRow作為參數值</param>
1941 /// <returns>傳回一個包含結果集的DataSet.</returns>
1942 ///
1943 public static DataSet ExecuteDatasetTypedParams(SqlConnection connection, String spName, DataRow dataRow)
1944 {
1945 if (connection == null) throw new ArgumentNullException("connection");
1946 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1947
1948 // 如果row有值,存儲過程必須初始化.
1949 if (dataRow != null && dataRow.ItemArray.Length > 0)
1950 {
1951 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
1952 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1953
1954 // 配置設定參數值
1955 AssignParameterValues(commandParameters, dataRow);
1956
1957 return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
1958 }
1959 else
1960 {
1961 return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName);
1962 }
1963 }
1964
1965 /// <summary>
1966 /// 執行指定連接配接資料庫事務的存儲過程,使用DataRow做為參數值,傳回DataSet.
1967 /// </summary>
1968 /// <param name="transaction">一個有效的連接配接事務 object</param>
1969 /// <param name="spName">存儲過程名稱</param>
1970 /// <param name="dataRow">使用DataRow作為參數值</param>
1971 /// <returns>傳回一個包含結果集的DataSet.</returns>
1972 public static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
1973 {
1974 if (transaction == null) throw new ArgumentNullException("transaction");
1975 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1976 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1977
1978 // 如果row有值,存儲過程必須初始化.
1979 if (dataRow != null && dataRow.ItemArray.Length > 0)
1980 {
1981 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
1982 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
1983
1984 // 配置設定參數值
1985 AssignParameterValues(commandParameters, dataRow);
1986
1987 return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
1988 }
1989 else
1990 {
1991 return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
1992 }
1993 }
1994
1995 #endregion
1996
1997 #region ExecuteReaderTypedParams 類型化參數(DataRow)
1998 /// <summary>
1999 /// 執行指定連接配接資料庫連接配接字元串的存儲過程,使用DataRow做為參數值,傳回DataReader.
2000 /// </summary>
2001 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
2002 /// <param name="spName">存儲過程名稱</param>
2003 /// <param name="dataRow">使用DataRow作為參數值</param>
2004 /// <returns>傳回包含結果集的SqlDataReader</returns>
2005 public static SqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow)
2006 {
2007 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2008 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2009
2010 // 如果row有值,存儲過程必須初始化.
2011 if (dataRow != null && dataRow.ItemArray.Length > 0)
2012 {
2013 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
2014 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
2015
2016 // 配置設定參數值
2017 AssignParameterValues(commandParameters, dataRow);
2018
2019 return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
2020 }
2021 else
2022 {
2023 return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
2024 }
2025 }
2026
2027
2028 /// <summary>
2029 /// 執行指定連接配接資料庫連接配接對象的存儲過程,使用DataRow做為參數值,傳回DataReader.
2030 /// </summary>
2031 /// <param name="connection">一個有效的資料庫連接配接對象</param>
2032 /// <param name="spName">存儲過程名稱</param>
2033 /// <param name="dataRow">使用DataRow作為參數值</param>
2034 /// <returns>傳回包含結果集的SqlDataReader</returns>
2035 public static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
2036 {
2037 if (connection == null) throw new ArgumentNullException("connection");
2038 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2039
2040 // 如果row有值,存儲過程必須初始化.
2041 if (dataRow != null && dataRow.ItemArray.Length > 0)
2042 {
2043 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
2044 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
2045
2046 // 配置設定參數值
2047 AssignParameterValues(commandParameters, dataRow);
2048
2049 return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
2050 }
2051 else
2052 {
2053 return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName);
2054 }
2055 }
2056
2057 /// <summary>
2058 /// 執行指定連接配接資料庫事物的存儲過程,使用DataRow做為參數值,傳回DataReader.
2059 /// </summary>
2060 /// <param name="transaction">一個有效的連接配接事務 object</param>
2061 /// <param name="spName">存儲過程名稱</param>
2062 /// <param name="dataRow">使用DataRow作為參數值</param>
2063 /// <returns>傳回包含結果集的SqlDataReader</returns>
2064 public static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
2065 {
2066 if (transaction == null) throw new ArgumentNullException("transaction");
2067 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
2068 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2069
2070 // 如果row有值,存儲過程必須初始化.
2071 if (dataRow != null && dataRow.ItemArray.Length > 0)
2072 {
2073 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
2074 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
2075
2076 // 配置設定參數值
2077 AssignParameterValues(commandParameters, dataRow);
2078
2079 return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
2080 }
2081 else
2082 {
2083 return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName);
2084 }
2085 }
2086 #endregion
2087
2088 #region ExecuteScalarTypedParams 類型化參數(DataRow)
2089 /// <summary>
2090 /// 執行指定連接配接資料庫連接配接字元串的存儲過程,使用DataRow做為參數值,傳回結果集中的第一行第一列.
2091 /// </summary>
2092 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
2093 /// <param name="spName">存儲過程名稱</param>
2094 /// <param name="dataRow">使用DataRow作為參數值</param>
2095 /// <returns>傳回結果集中的第一行第一列</returns>
2096 public static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow)
2097 {
2098 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2099 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2100
2101 // 如果row有值,存儲過程必須初始化.
2102 if (dataRow != null && dataRow.ItemArray.Length > 0)
2103 {
2104 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
2105 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
2106
2107 // 配置設定參數值
2108 AssignParameterValues(commandParameters, dataRow);
2109
2110 return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
2111 }
2112 else
2113 {
2114 return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
2115 }
2116 }
2117
2118 /// <summary>
2119 /// 執行指定連接配接資料庫連接配接對象的存儲過程,使用DataRow做為參數值,傳回結果集中的第一行第一列.
2120 /// </summary>
2121 /// <param name="connection">一個有效的資料庫連接配接對象</param>
2122 /// <param name="spName">存儲過程名稱</param>
2123 /// <param name="dataRow">使用DataRow作為參數值</param>
2124 /// <returns>傳回結果集中的第一行第一列</returns>
2125 public static object ExecuteScalarTypedParams(SqlConnection connection, String spName, DataRow dataRow)
2126 {
2127 if (connection == null) throw new ArgumentNullException("connection");
2128 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2129
2130 // 如果row有值,存儲過程必須初始化.
2131 if (dataRow != null && dataRow.ItemArray.Length > 0)
2132 {
2133 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
2134 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
2135
2136 // 配置設定參數值
2137 AssignParameterValues(commandParameters, dataRow);
2138
2139 return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
2140 }
2141 else
2142 {
2143 return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName);
2144 }
2145 }
2146
2147 /// <summary>
2148 /// 執行指定連接配接資料庫事務的存儲過程,使用DataRow做為參數值,傳回結果集中的第一行第一列.
2149 /// </summary>
2150 /// <param name="transaction">一個有效的連接配接事務 object</param>
2151 /// <param name="spName">存儲過程名稱</param>
2152 /// <param name="dataRow">使用DataRow作為參數值</param>
2153 /// <returns>傳回結果集中的第一行第一列</returns>
2154 public static object ExecuteScalarTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
2155 {
2156 if (transaction == null) throw new ArgumentNullException("transaction");
2157 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
2158 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2159
2160 // 如果row有值,存儲過程必須初始化.
2161 if (dataRow != null && dataRow.ItemArray.Length > 0)
2162 {
2163 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
2164 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
2165
2166 // 配置設定參數值
2167 AssignParameterValues(commandParameters, dataRow);
2168
2169 return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
2170 }
2171 else
2172 {
2173 return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
2174 }
2175 }
2176 #endregion
2177
2178 #region ExecuteXmlReaderTypedParams 類型化參數(DataRow)
2179 /// <summary>
2180 /// 執行指定連接配接資料庫連接配接對象的存儲過程,使用DataRow做為參數值,傳回XmlReader類型的結果集.
2181 /// </summary>
2182 /// <param name="connection">一個有效的資料庫連接配接對象</param>
2183 /// <param name="spName">存儲過程名稱</param>
2184 /// <param name="dataRow">使用DataRow作為參數值</param>
2185 /// <returns>傳回XmlReader結果集對象.</returns>
2186 public static XmlReader ExecuteXmlReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
2187 {
2188 if (connection == null) throw new ArgumentNullException("connection");
2189 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2190
2191 // 如果row有值,存儲過程必須初始化.
2192 if (dataRow != null && dataRow.ItemArray.Length > 0)
2193 {
2194 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
2195 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
2196
2197 // 配置設定參數值
2198 AssignParameterValues(commandParameters, dataRow);
2199
2200 return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
2201 }
2202 else
2203 {
2204 return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
2205 }
2206 }
2207
2208 /// <summary>
2209 /// 執行指定連接配接資料庫事務的存儲過程,使用DataRow做為參數值,傳回XmlReader類型的結果集.
2210 /// </summary>
2211 /// <param name="transaction">一個有效的連接配接事務 object</param>
2212 /// <param name="spName">存儲過程名稱</param>
2213 /// <param name="dataRow">使用DataRow作為參數值</param>
2214 /// <returns>傳回XmlReader結果集對象.</returns>
2215 public static XmlReader ExecuteXmlReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
2216 {
2217 if (transaction == null) throw new ArgumentNullException("transaction");
2218 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
2219 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2220
2221 // 如果row有值,存儲過程必須初始化.
2222 if (dataRow != null && dataRow.ItemArray.Length > 0)
2223 {
2224 // 從緩存中加載存儲過程參數,如果緩存中不存在則從資料庫中檢索參數資訊并加載到緩存中. ()
2225 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
2226
2227 // 配置設定參數值
2228 AssignParameterValues(commandParameters, dataRow);
2229
2230 return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
2231 }
2232 else
2233 {
2234 return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
2235 }
2236 }
2237 #endregion
2238
2239 }
2240
2241 /// <summary>
2242 /// SqlHelperParameterCache提供緩存存儲過程參數,并能夠在運作時從存儲過程中探索參數.
2243 /// </summary>
2244 public sealed class SqlHelperParameterCache
2245 {
2246 #region 私有方法,字段,構造函數
2247 // 私有構造函數,妨止類被執行個體化.
2248 private SqlHelperParameterCache() { }
2249
2250 // 這個方法要注意
2251 private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
2252
2253 /// <summary>
2254 /// 探索運作時的存儲過程,傳回SqlParameter參數數組.
2255 /// 初始化參數值為 DBNull.Value.
2256 /// </summary>
2257 /// <param name="connection">一個有效的資料庫連接配接</param>
2258 /// <param name="spName">存儲過程名稱</param>
2259 /// <param name="includeReturnValueParameter">是否包含傳回值參數</param>
2260 /// <returns>傳回SqlParameter參數數組</returns>
2261 private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
2262 {
2263 if (connection == null) throw new ArgumentNullException("connection");
2264 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2265
2266 SqlCommand cmd = new SqlCommand(spName, connection);
2267 cmd.CommandType = CommandType.StoredProcedure;
2268
2269 connection.Open();
2270 // 檢索cmd指定的存儲過程的參數資訊,并填充到cmd的Parameters參數集中.
2271 SqlCommandBuilder.DeriveParameters(cmd);
2272 connection.Close();
2273 // 如果不包含傳回值參數,将參數集中的每一個參數删除.
2274 if (!includeReturnValueParameter)
2275 {
2276 cmd.Parameters.RemoveAt(0);
2277 }
2278
2279 // 建立參數數組
2280 SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
2281 // 将cmd的Parameters參數集複制到discoveredParameters數組.
2282 cmd.Parameters.CopyTo(discoveredParameters, 0);
2283
2284 // 初始化參數值為 DBNull.Value.
2285 foreach (SqlParameter discoveredParameter in discoveredParameters)
2286 {
2287 discoveredParameter.Value = DBNull.Value;
2288 }
2289 return discoveredParameters;
2290 }
2291
2292 /// <summary>
2293 /// SqlParameter參數數組的深層拷貝.
2294 /// </summary>
2295 /// <param name="originalParameters">原始參數數組</param>
2296 /// <returns>傳回一個同樣的參數數組</returns>
2297 private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
2298 {
2299 SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
2300
2301 for (int i = 0, j = originalParameters.Length; i < j; i++)
2302 {
2303 clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
2304 }
2305
2306 return clonedParameters;
2307 }
2308
2309 #endregion 私有方法,字段,構造函數結束
2310
2311 #region 緩存方法
2312
2313 /// <summary>
2314 /// 追加參數數組到緩存.
2315 /// </summary>
2316 /// <param name="connectionString">一個有效的資料庫連接配接字元串</param>
2317 /// <param name="commandText">存儲過程名或SQL語句</param>
2318 /// <param name="commandParameters">要緩存的參數數組</param>
2319 public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
2320 {
2321 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2322 if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
2323
2324 string hashKey = connectionString + ":" + commandText;
2325
2326 paramCache[hashKey] = commandParameters;
2327 }
2328
2329 /// <summary>
2330 /// 從緩存中擷取參數數組.
2331 /// </summary>
2332 /// <param name="connectionString">一個有效的資料庫連接配接字元</param>
2333 /// <param name="commandText">存儲過程名或SQL語句</param>
2334 /// <returns>參數數組</returns>
2335 public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
2336 {
2337 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2338 if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
2339
2340 string hashKey = connectionString + ":" + commandText;
2341
2342 SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
2343 if (cachedParameters == null)
2344 {
2345 return null;
2346 }
2347 else
2348 {
2349 return CloneParameters(cachedParameters);
2350 }
2351 }
2352
2353 #endregion 緩存方法結束
2354
2355 #region 檢索指定的存儲過程的參數集
2356
2357 /// <summary>
2358 /// 傳回指定的存儲過程的參數集
2359 /// </summary>
2360 /// <remarks>
2361 /// 這個方法将查詢資料庫,并将資訊存儲到緩存.
2362 /// </remarks>
2363 /// <param name="connectionString">一個有效的資料庫連接配接字元</param>
2364 /// <param name="spName">存儲過程名</param>
2365 /// <returns>傳回SqlParameter參數數組</returns>
2366 public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
2367 {
2368 return GetSpParameterSet(connectionString, spName, false);
2369 }
2370
2371 /// <summary>
2372 /// 傳回指定的存儲過程的參數集
2373 /// </summary>
2374 /// <remarks>
2375 /// 這個方法将查詢資料庫,并将資訊存儲到緩存.
2376 /// </remarks>
2377 /// <param name="connectionString">一個有效的資料庫連接配接字元.</param>
2378 /// <param name="spName">存儲過程名</param>
2379 /// <param name="includeReturnValueParameter">是否包含傳回值參數</param>
2380 /// <returns>傳回SqlParameter參數數組</returns>
2381 public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
2382 {
2383 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2384 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2385
2386 using (SqlConnection connection = new SqlConnection(connectionString))
2387 {
2388 return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
2389 }
2390 }
2391
2392 /// <summary>
2393 /// [内部]傳回指定的存儲過程的參數集(使用連接配接對象).
2394 /// </summary>
2395 /// <remarks>
2396 /// 這個方法将查詢資料庫,并将資訊存儲到緩存.
2397 /// </remarks>
2398 /// <param name="connection">一個有效的資料庫連接配接字元</param>
2399 /// <param name="spName">存儲過程名</param>
2400 /// <returns>傳回SqlParameter參數數組</returns>
2401 internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
2402 {
2403 return GetSpParameterSet(connection, spName, false);
2404 }
2405
2406 /// <summary>
2407 /// [内部]傳回指定的存儲過程的參數集(使用連接配接對象)
2408 /// </summary>
2409 /// <remarks>
2410 /// 這個方法将查詢資料庫,并将資訊存儲到緩存.
2411 /// </remarks>
2412 /// <param name="connection">一個有效的資料庫連接配接對象</param>
2413 /// <param name="spName">存儲過程名</param>
2414 /// <param name="includeReturnValueParameter">
2415 /// 是否包含傳回值參數
2416 /// </param>
2417 /// <returns>傳回SqlParameter參數數組</returns>
2418 internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
2419 {
2420 if (connection == null) throw new ArgumentNullException("connection");
2421 using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone())
2422 {
2423 return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
2424 }
2425 }
2426
2427 /// <summary>
2428 /// [私有]傳回指定的存儲過程的參數集(使用連接配接對象)
2429 /// </summary>
2430 /// <param name="connection">一個有效的資料庫連接配接對象</param>
2431 /// <param name="spName">存儲過程名</param>
2432 /// <param name="includeReturnValueParameter">是否包含傳回值參數</param>
2433 /// <returns>傳回SqlParameter參數數組</returns>
2434 private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
2435 {
2436 if (connection == null) throw new ArgumentNullException("connection");
2437 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2438
2439 string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
2440
2441 SqlParameter[] cachedParameters;
2442
2443 cachedParameters = paramCache[hashKey] as SqlParameter[];
2444 if (cachedParameters == null)
2445 {
2446 SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
2447 paramCache[hashKey] = spParameters;
2448 cachedParameters = spParameters;
2449 }
2450
2451 return CloneParameters(cachedParameters);
2452 }
2453
2454 #endregion 參數集檢索結束
2455
2456 }
2457 }