代碼 ??1?using?System;
??2?using?System.Collections.Generic;
??3?using?System.Text;
??4?using?System.Data;
??5?using?System.Data.SqlClient;
??6?using?System.Collections;
??7?
??8?namespace?SystemDAO
??9?{
?10?????///?<summary>
?11?????///?數據庫的通用訪問代碼? ?12?????///?
?13?????///?此類為抽象類，
?14?????///?不允許實例化，在應用時直接調用即可
?15?????///?</summary>
?16?????public?abstract?class?SqlHelper
?17?????{
?18?????????///?<summary>
?19?????????///?數據庫連接字符串
?20?????????///?</summary>
?21?
?22?????????public?static?readonly?string?connectionString?=?System.Configuration.ConfigurationSettings.AppSettings["con"].ToString().Trim();
?23?????????//?Hashtable?to?store?cached?parameters
?24?????????private?static?Hashtable?parmCache?=?Hashtable.Synchronized(new?Hashtable());
?25?
?26?????????#region//ExecteNonQuery方法
?27?
?28?????????///?<summary>
?29?????????///執行一個不需要返回值的SqlCommand命令，通過指定專用的連接字符串。
?30?????????///?使用參數數組形式提供參數列表?
?31?????????///?</summary>
?32?????????///?<param?name="connectionString">一個有效的數據庫連接字符串</param>
?33?????????///?<param?name="cmdType">SqlCommand命令類型?(存儲過程，?T-SQL語句，?等等。)</param>
?34?????????///?<param?name="cmdText">存儲過程的名字或者?T-SQL?語句</param>
?35?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
?36?????????///?<returns>返回一個數值表示此SqlCommand命令執行后影響的行數</returns>
?37?????????public?static?int?ExecteNonQuery(string?connectionString,?CommandType?cmdType,?string?cmdText,?params?SqlParameter[]?commandParameters)
?38?????????{
?39?????????????SqlCommand?cmd?=?new?SqlCommand();
?40?????????????using?(SqlConnection?conn?=?new?SqlConnection(connectionString))
?41?????????????{
?42?????????????????//通過PrePareCommand方法將參數逐個加入到SqlCommand的參數集合中
?43?????????????????PrepareCommand(cmd,?conn,?null,?cmdType,?cmdText,?commandParameters);
?44?????????????????int?val?=?cmd.ExecuteNonQuery();
?45?????????????????//清空SqlCommand中的參數列表
?46?????????????????cmd.Parameters.Clear();
?47?????????????????return?val;
?48?????????????}
?49?????????}
?50?
?51?????????///?<summary>
?52?????????///執行一個不需要返回值的SqlCommand命令，通過指定專用的連接字符串。
?53?????????///?使用參數數組形式提供參數列表?
?54?????????///?</summary>
?55?????????///?<param?name="cmdType">SqlCommand命令類型?(存儲過程，?T-SQL語句，?等等。)</param>
?56?????????///?<param?name="cmdText">存儲過程的名字或者?T-SQL?語句</param>
?57?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
?58?????????///?<returns>返回一個數值表示此SqlCommand命令執行后影響的行數</returns>
?59?????????public?static?int?ExecteNonQuery(CommandType?cmdType,?string?cmdText,?params?SqlParameter[]?commandParameters)
?60?????????{
?61?????????????return?ExecteNonQuery(connectionString?,cmdType,?cmdText,?commandParameters);
?62?????????}
?63?
?64?????????///?<summary>
?65?????????///存儲過程專用
?66?????????///?</summary>
?67?????????///?<param?name="cmdText">存儲過程的名字</param>
?68?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
?69?????????///?<returns>返回一個數值表示此SqlCommand命令執行后影響的行數</returns>
?70?????????public?static?int?ExecteNonQueryProducts(string?cmdText,?params?SqlParameter[]?commandParameters)
?71?????????{
?72?????????????return?ExecteNonQuery(CommandType.StoredProcedure,?cmdText,?commandParameters);
?73?????????}
?74?
?75?????????///?<summary>
?76?????????///Sql語句專用
?77?????????///?</summary>
?78?????????///?<param?name="cmdText">T_Sql語句</param>
?79?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
?80?????????///?<returns>返回一個數值表示此SqlCommand命令執行后影響的行數</returns>
?81?????????public?static?int?ExecteNonQueryText(string?cmdText,?params?SqlParameter[]?commandParameters)
?82?????????{
?83?????????????return?ExecteNonQuery(CommandType.Text,?cmdText,?commandParameters);
?84?????????}
?85?
?86?????????#endregion
?87?????????#region//GetTable方法
?88?
?89?????????///?<summary>
?90?????????///?執行一條返回結果集的SqlCommand，通過一個已經存在的數據庫連接
?91?????????///?使用參數數組提供參數
?92?????????///?</summary>
?93?????????///?<param?name="connecttionString">一個現有的數據庫連接</param>
?94?????????///?<param?name="cmdTye">SqlCommand命令類型</param>
?95?????????///?<param?name="cmdText">存儲過程的名字或者?T-SQL?語句</param>
?96?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
?97?????????///?<returns>返回一個表集合(DataTableCollection)表示查詢得到的數據集</returns>
?98?????????public?static?DataTableCollection?GetTable(string?connecttionString,?CommandType?cmdTye,?string?cmdText,?SqlParameter[]?commandParameters)
?99?????????{
100?????????????SqlCommand?cmd?=?new?SqlCommand();
101?????????????DataSet?ds?=?new?DataSet();
102?????????????using?(SqlConnection?conn?=?new?SqlConnection(connecttionString))
103?????????????{
104?????????????????PrepareCommand(cmd,?conn,?null,?cmdTye,?cmdText,?commandParameters);
105?????????????????SqlDataAdapter?adapter?=?new?SqlDataAdapter();
106?????????????????adapter.SelectCommand?=?cmd;
107?????????????????adapter.Fill(ds);
108?????????????}
109?????????????DataTableCollection?table?=?ds.Tables;
110?????????????return?table;
111?????????}
112?
113?????????///?<summary>
114?????????///?執行一條返回結果集的SqlCommand，通過一個已經存在的數據庫連接
115?????????///?使用參數數組提供參數
116?????????///?</summary>
117?????????///?<param?name="cmdTye">SqlCommand命令類型</param>
118?????????///?<param?name="cmdText">存儲過程的名字或者?T-SQL?語句</param>
119?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
120?????????///?<returns>返回一個表集合(DataTableCollection)表示查詢得到的數據集</returns>
121?????????public?static?DataTableCollection?GetTable(CommandType?cmdTye,?string?cmdText,?SqlParameter[]?commandParameters)
122?????????{
123?????????????return?GetTable(cmdTye,?cmdText,?commandParameters);
124?????????}
125?
126?
127?????????///?<summary>
128?????????///?存儲過程專用
129?????????///?</summary>
130?????????///?<param?name="cmdText">存儲過程的名字或者?T-SQL?語句</param>
131?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
132?????????///?<returns>返回一個表集合(DataTableCollection)表示查詢得到的數據集</returns>
133?????????public?static?DataTableCollection?GetTableProducts(string?cmdText,?SqlParameter[]?commandParameters)
134?????????{
135?????????????return?GetTable(CommandType.StoredProcedure,?cmdText,?commandParameters);
136?????????}
137?
138?????????///?<summary>
139?????????///?Sql語句專用
140?????????///?</summary>
141?????????///?<param?name="cmdText">?T-SQL?語句</param>
142?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
143?????????///?<returns>返回一個表集合(DataTableCollection)表示查詢得到的數據集</returns>
144?????????public?static?DataTableCollection?GetTableText(string?cmdText,?SqlParameter[]?commandParameters)
145?????????{
146?????????????return?GetTable(CommandType.Text,?cmdText,?commandParameters);
147?????????}
148?????????#endregion
149?
150?
151?????????///?<summary>
152?????????///?為執行命令準備參數
153?????????///?</summary>
154?????????///?<param?name="cmd">SqlCommand?命令</param>
155?????????///?<param?name="conn">已經存在的數據庫連接</param>
156?????????///?<param?name="trans">數據庫事物處理</param>
157?????????///?<param?name="cmdType">SqlCommand命令類型?(存儲過程，?T-SQL語句，?等等。)</param>
158?????????///?<param?name="cmdText">Command?text，T-SQL語句?例如?Select?*?from?Products</param>
159?????????///?<param?name="cmdParms">返回帶參數的命令</param>
160?????????private?static?void?PrepareCommand(SqlCommand?cmd,?SqlConnection?conn,?SqlTransaction?trans,?CommandType?cmdType,?string?cmdText,?SqlParameter[]?cmdParms)
161?????????{
162?????????????//判斷數據庫連接狀態
163?????????????if?(conn.State?!=?ConnectionState.Open)
164?????????????????conn.Open();
165?????????????cmd.Connection?=?conn;
166?????????????cmd.CommandText?=?cmdText;
167?????????????//判斷是否需要事物處理
168?????????????if?(trans?!=?null)
169?????????????????cmd.Transaction?=?trans;
170?????????????cmd.CommandType?=?cmdType;
171?????????????if?(cmdParms?!=?null)
172?????????????{
173?????????????????foreach?(SqlParameter?parm?in?cmdParms)
174?????????????????????cmd.Parameters.Add(parm);
175?????????????}
176?????????}
177?
178?????????///?<summary>
179?????????///?Execute?a?SqlCommand?that?returns?a?resultset?against?the?database?specified?in?the?connection?string?
180?????????///?using?the?provided?parameters.
181?????????///?</summary>
182?????????///?<param?name="connectionString">一個有效的數據庫連接字符串</param>
183?????????///?<param?name="cmdType">SqlCommand命令類型?(存儲過程，?T-SQL語句，?等等。)</param>
184?????????///?<param?name="cmdText">存儲過程的名字或者?T-SQL?語句</param>
185?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
186?????????///?<returns>A?SqlDataReader?containing?the?results</returns>
187?????????public?static?SqlDataReader?ExecuteReader(string?connectionString,?CommandType?cmdType,?string?cmdText,?params?SqlParameter[]?commandParameters)
188?????????{
189?????????????SqlCommand?cmd?=?new?SqlCommand();
190?????????????SqlConnection?conn?=?new?SqlConnection(connectionString);
191?????????????//?we?use?a?try/catch?here?because?if?the?method?throws?an?exception?we?want?to?
192?????????????//?close?the?connection?throw?code,?because?no?datareader?will?exist,?hence?the?
193?????????????//?commandBehaviour.CloseConnection?will?not?work
194?????????????try
195?????????????{
196?????????????????PrepareCommand(cmd,?conn,?null,?cmdType,?cmdText,?commandParameters);
197?????????????????SqlDataReader?rdr?=?cmd.ExecuteReader(CommandBehavior.CloseConnection);
198?????????????????cmd.Parameters.Clear();
199?????????????????return?rdr;
200?????????????}
201?????????????catch
202?????????????{
203?????????????????conn.Close();
204?????????????????throw;
205?????????????}
206?????????}
207?????????#region//ExecuteDataSet方法
208?
209?????????///?<summary>
210?????????///?return?a?dataset
211?????????///?</summary>
212?????????///?<param?name="connectionString">一個有效的數據庫連接字符串</param>
213?????????///?<param?name="cmdType">SqlCommand命令類型?(存儲過程，?T-SQL語句，?等等。)</param>
214?????????///?<param?name="cmdText">存儲過程的名字或者?T-SQL?語句</param>
215?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
216?????????///?<returns>return?a?dataset</returns>
217?????????public?static?DataSet?ExecuteDataSet(string?connectionString,?CommandType?cmdType,?string?cmdText,?params?SqlParameter[]?commandParameters)
218?????????{
219?????????????SqlConnection?conn?=?new?SqlConnection(connectionString);
220?????????????SqlCommand?cmd?=?new?SqlCommand();
221?????????????try
222?????????????{
223?????????????????PrepareCommand(cmd,?conn,?null,?cmdType,?cmdText,?commandParameters);
224?????????????????SqlDataAdapter?da?=?new?SqlDataAdapter();
225?????????????????DataSet?ds?=?new?DataSet();
226?????????????????da.SelectCommand?=?cmd;
227?????????????????da.Fill(ds);
228?????????????????return?ds;
229?????????????}
230?????????????catch
231?????????????{
232?????????????????conn.Close();
233?????????????????throw;
234?????????????}
235?????????}
236?
237?
238?????????///?<summary>
239?????????///?返回一個DataSet
240?????????///?</summary>
241?????????///?<param?name="cmdType">SqlCommand命令類型?(存儲過程，?T-SQL語句，?等等。)</param>
242?????????///?<param?name="cmdText">存儲過程的名字或者?T-SQL?語句</param>
243?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
244?????????///?<returns>return?a?dataset</returns>
245?????????public?static?DataSet?ExecuteDataSet(CommandType?cmdType,?string?cmdText,?params?SqlParameter[]?commandParameters)
246?????????{
247?????????????return?ExecuteDataSet(connectionString,?cmdType,?cmdText,?commandParameters);
248?????????}
249?
250?????????///?<summary>
251?????????///?返回一個DataSet
252?????????///?</summary>
253?????????///?<param?name="cmdText">存儲過程的名字</param>
254?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
255?????????///?<returns>return?a?dataset</returns>
256?????????public?static?DataSet?ExecuteDataSetProducts(string?cmdText,?params?SqlParameter[]?commandParameters)
257?????????{
258?????????????return?ExecuteDataSet(connectionString,?CommandType.StoredProcedure,?cmdText,?commandParameters);
259?????????}
260?
261?????????///?<summary>
262?????????///?返回一個DataSet
263?????????///?</summary>
264?
265?????????///?<param?name="cmdText">T-SQL?語句</param>
266?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
267?????????///?<returns>return?a?dataset</returns>
268?????????public?static?DataSet?ExecuteDataSetText(string?cmdText,?params?SqlParameter[]?commandParameters)
269?????????{
270?????????????return?ExecuteDataSet(connectionString,?CommandType.Text,?cmdText,?commandParameters);
271?????????}
272?
273?
274?????????public?static?DataView?ExecuteDataSet(string?connectionString,?string?sortExpression,?string?direction,?CommandType?cmdType,?string?cmdText,?params?SqlParameter[]?commandParameters)
275?????????{
276?????????????SqlConnection?conn?=?new?SqlConnection(connectionString);
277?????????????SqlCommand?cmd?=?new?SqlCommand();
278?????????????try
279?????????????{
280?????????????????PrepareCommand(cmd,?conn,?null,?cmdType,?cmdText,?commandParameters);
281?????????????????SqlDataAdapter?da?=?new?SqlDataAdapter();
282?????????????????DataSet?ds?=?new?DataSet();
283?????????????????da.SelectCommand?=?cmd;
284?????????????????da.Fill(ds);
285?????????????????DataView?dv?=?ds.Tables[0].DefaultView;
286?????????????????dv.Sort?=?sortExpression?+?"?"?+?direction;
287?????????????????return?dv;
288?????????????}
289?????????????catch
290?????????????{
291?????????????????conn.Close();
292?????????????????throw;
293?????????????}
294?????????}
295?????????#endregion
296?
297?
298?????????#region?//?ExecuteScalar方法
299?
300?
301?????????///?<summary>
302?????????///?返回第一行的第一列
303?????????///?</summary>
304?????????///?<param?name="cmdType">SqlCommand命令類型?(存儲過程，?T-SQL語句，?等等。)</param>
305?????????///?<param?name="cmdText">存儲過程的名字或者?T-SQL?語句</param>
306?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
307?????????///?<returns>返回一個對象</returns>
308?????????public?static?object?ExecuteScalar(CommandType?cmdType,?string?cmdText,?params?SqlParameter[]?commandParameters)
309?????????{
310?????????????return?ExecuteScalar(SqlHelper.connectionString,?cmdType,?cmdText,?commandParameters);
311?????????}
312?
313?????????///?<summary>
314?????????///?返回第一行的第一列存儲過程專用
315?????????///?</summary>
316?????????///?<param?name="cmdText">存儲過程的名字</param>
317?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
318?????????///?<returns>返回一個對象</returns>
319?????????public?static?object?ExecuteScalarProducts(string?cmdText,?params?SqlParameter[]?commandParameters)
320?????????{
321?????????????return?ExecuteScalar(SqlHelper.connectionString,?CommandType.StoredProcedure,?cmdText,?commandParameters);
322?????????}
323?
324?????????///?<summary>
325?????????///?返回第一行的第一列Sql語句專用
326?????????///?</summary>
327?????????///?<param?name="cmdText">者?T-SQL?語句</param>
328?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
329?????????///?<returns>返回一個對象</returns>
330?????????public?static?object?ExecuteScalarText(string?cmdText,?params?SqlParameter[]?commandParameters)
331?????????{
332?????????????return?ExecuteScalar(SqlHelper.connectionString,?CommandType.Text,?cmdText,?commandParameters);
333?????????}
334?
335?????????///?<summary>
336?????????///?Execute?a?SqlCommand?that?returns?the?first?column?of?the?first?record?against?the?database?specified?in?the?connection?string?
337?????????///?using?the?provided?parameters.
338?????????///?</summary>
339?????????///?<remarks>
340?????????///?e.g.:??
341?????????///??Object?obj?=?ExecuteScalar(connString,?CommandType.StoredProcedure,?"PublishOrders",?new?SqlParameter("@prodid",?24));
342?????????///?</remarks>
343?????????///?<param?name="connectionString">一個有效的數據庫連接字符串</param>
344?????????///?<param?name="cmdType">SqlCommand命令類型?(存儲過程，?T-SQL語句，?等等。)</param>
345?????????///?<param?name="cmdText">存儲過程的名字或者?T-SQL?語句</param>
346?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
347?????????///?<returns>An?object?that?should?be?converted?to?the?expected?type?using?Convert.To{Type}</returns>
348?????????public?static?object?ExecuteScalar(string?connectionString,?CommandType?cmdType,?string?cmdText,?params?SqlParameter[]?commandParameters)
349?????????{
350?????????????SqlCommand?cmd?=?new?SqlCommand();
351?
352?????????????using?(SqlConnection?connection?=?new?SqlConnection(connectionString))
353?????????????{
354?????????????????PrepareCommand(cmd,?connection,?null,?cmdType,?cmdText,?commandParameters);
355?????????????????object?val?=?cmd.ExecuteScalar();
356?????????????????cmd.Parameters.Clear();
357?????????????????return?val;
358?????????????}
359?????????}
360?
361?????????///?<summary>
362?????????///?Execute?a?SqlCommand?that?returns?the?first?column?of?the?first?record?against?an?existing?database?connection?
363?????????///?using?the?provided?parameters.
364?????????///?</summary>
365?????????///?<remarks>
366?????????///?e.g.:??
367?????????///??Object?obj?=?ExecuteScalar(connString,?CommandType.StoredProcedure,?"PublishOrders",?new?SqlParameter("@prodid",?24));
368?????????///?</remarks>
369?????????///?<param?name="connectionString">一個有效的數據庫連接字符串</param>
370?????????///?<param?name="cmdType">SqlCommand命令類型?(存儲過程，?T-SQL語句，?等等。)</param>
371?????????///?<param?name="cmdText">存儲過程的名字或者?T-SQL?語句</param>
372?????????///?<param?name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
373?????????///?<returns>An?object?that?should?be?converted?to?the?expected?type?using?Convert.To{Type}</returns>
374?????????public?static?object?ExecuteScalar(SqlConnection?connection,?CommandType?cmdType,?string?cmdText,?params?SqlParameter[]?commandParameters)
375?????????{
376?????????????SqlCommand?cmd?=?new?SqlCommand();
377?????????????PrepareCommand(cmd,?connection,?null,?cmdType,?cmdText,?commandParameters);
378?????????????object?val?=?cmd.ExecuteScalar();
379?????????????cmd.Parameters.Clear();
380?????????????return?val;
381?????????}
382?
383?????????#endregion
384?
385?
386?????????///?<summary>
387?????????///?add?parameter?array?to?the?cache
388?????????///?</summary>
389?????????///?<param?name="cacheKey">Key?to?the?parameter?cache</param>
390?????????///?<param?name="cmdParms">an?array?of?SqlParamters?to?be?cached</param>
391?????????public?static?void?CacheParameters(string?cacheKey,?params?SqlParameter[]?commandParameters)
392?????????{
393?????????????parmCache[cacheKey]?=?commandParameters;
394?????????}
395?
396?????????///?<summary>
397?????????///?Retrieve?cached?parameters
398?????????///?</summary>
399?????????///?<param?name="cacheKey">key?used?to?lookup?parameters</param>
400?????????///?<returns>Cached?SqlParamters?array</returns>
401?????????public?static?SqlParameter[]?GetCachedParameters(string?cacheKey)
402?????????{
403?????????????SqlParameter[]?cachedParms?=?(SqlParameter[])parmCache[cacheKey];
404?????????????if?(cachedParms?==?null)
405?????????????????return?null;
406?????????????SqlParameter[]?clonedParms?=?new?SqlParameter[cachedParms.Length];
407?????????????for?(int?i?=?0,?j?=?cachedParms.Length;?i?<?j;?i++)
408?????????????????clonedParms[i]?=?(SqlParameter)((ICloneable)cachedParms[i]).Clone();
409?????????????return?clonedParms;
410?????????}
411?
412?
413?????????///?<summary>
414?????????///?檢查是否存在
415?????????///?</summary>
416?????????///?<param?name="strSql">Sql語句</param>
417?????????///?<returns>bool結果</returns>
418?????????public?static?bool?Exists(string?strSql)
419?????????{
420?????????????int?cmdresult?=?Convert.ToInt32(ExecuteScalar(connectionString,?CommandType.Text,?strSql,?null));
421?????????????if?(cmdresult?==?0)
422?????????????{
423?????????????????return?false;
424?????????????}
425?????????????else
426?????????????{
427?????????????????return?true;
428?????????????}
429?????????}
430?
431?????????///?<summary>
432?????????///?檢查是否存在
433?????????///?</summary>
434?????????///?<param?name="strSql">Sql語句</param>
435?????????///?<param?name="cmdParms">參數</param>
436?????????///?<returns>bool結果</returns>
437?????????public?static?bool?Exists(string?strSql,?params?SqlParameter[]?cmdParms)
438?????????{
439?????????????int?cmdresult?=?Convert.ToInt32(ExecuteScalar(connectionString,?CommandType.Text,?strSql,?cmdParms));
440?????????????if?(cmdresult?==?0)
441?????????????{
442?????????????????return?false;
443?????????????}
444?????????????else
445?????????????{
446?????????????????return?true;
447?????????????}
448?????????}
449?????}
450?
451?}
452?
453? 出處：http://www.cnblogs.com/sufei/archive/2010/01/14/1648026.html ?

?

轉載于:https://www.cnblogs.com/angleSJW/archive/2010/01/23/1654807.html

總結

以上是生活随笔為你收集整理的实用的SqlHelper类的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。