FoolWeb 各层代码实例
FoolWeb.DateEngine 数据库操作类库
这里现在只写了一个类用于操作mssql.将来支持别的数据库试直接扩展就行了.
下来带个代码段
1: /// <summary>通用数据库接口
2: /// </summary>
3: using System;
4: using System.Collections;
5: using System.Collections.Generic;
6: using System.Data;
7: using System.Data.SqlClient;
8: using System.Configuration;
9:
10: namespace FoolWeb.DateEngine {
11:
12: #region 事务属性
13: public enum EffentNextType {
14: /// <summary>
15: /// 对其他语句无任何影响
16: /// </summary>
17: None,
18: /// <summary>
19: /// 当前语句必须为"SELECT COUNT(1) FROM .."格式,如果存在则继续执行,不存在回滚事务
20: /// </summary>
21: WhenHaveContine,
22: /// <summary>
23: /// 当前语句必须为"SELECT COUNT(1) FROM .."格式,如果不存在则继续执行,存在回滚事务
24: /// </summary>
25: WhenNoHaveContine,
26: /// <summary>
27: /// 当前语句影响到的行数必须大于0,否则回滚事务
28: /// </summary>
29: ExcuteEffectRows,
30: /// <summary>
31: /// 引发事件-当前语句必须为"SELECT COUNT(1) FROM .."格式,如果不存在则继续执行,存在回滚事务
32: /// </summary>
33: SolicitationEvent
34: }
35:
36: #endregion
37: #region command定义
38: public class CommandInfo {
39: public object ShareObject=null;
40: public object OriginalData=null;
41: event EventHandler _solicitationEvent;
42:
43: public event EventHandler SolicitationEvent {
44: add {
45: _solicitationEvent+=value;
46: }
47: remove {
48: _solicitationEvent-=value;
49: }
50: }
51:
52: public void OnSolicitationEvent() {
53: if( _solicitationEvent!=null ) {
54: _solicitationEvent(this, new EventArgs());
55: }
56: }
57:
58: public string CommandText;
59: public System.Data.Common.DbParameter[] Parameters;
60: public EffentNextType EffentNextType=EffentNextType.None;
61:
62: public CommandInfo() {
63: }
64:
65: public CommandInfo( string sqlText, SqlParameter[] para ) {
66: this.CommandText=sqlText;
67: this.Parameters=para;
68: }
69:
70: public CommandInfo( string sqlText, SqlParameter[] para, EffentNextType type ) {
71: this.CommandText=sqlText;
72: this.Parameters=para;
73: this.EffentNextType=type;
74: }
75: }
76: #endregion
77: /// <summary>
78: /// 数据访问抽象基础类
79: /// </summary>
80: public abstract class MsSQLHelper {
81: #region 属性定义
82: //数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.
83: public static string connectionString="";
84: //构造函数默认数据库
85: public MsSQLHelper() {
86: connectionString=ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
87: }
88: //构造函数指定数据库
89: public MsSQLHelper( string _connstr ) {
90: connectionString=ConfigurationManager.ConnectionStrings[_connstr].ConnectionString;
91: }
92: #endregion
93: #region 公用方法
94: /// <summary>
95: /// 判断是否存在某表的某个字段
96: /// </summary>
97: /// <param name="tableName">表名称</param>
98: /// <param name="columnName">列名称</param>
99: /// <returns>是否存在</returns>
100: public static bool ColumnExists( string tableName, string columnName ) {
101: string sql="select count(1) from syscolumns where [id]=object_id(\""+tableName+"\") and [name]=\""+columnName+"\"";
102: object res=GetSingle(sql);
103: if( res==null ) {
104: return false;
105: }
106: return Convert.ToInt32(res)>0;
107: }
108: /// <summary>
109: /// 获取表中数字字段的最大值
110: /// </summary>
111: /// <param name="FieldName">字段名</param>
112: /// <param name="TableName">表名</param>
113: /// <returns>返回字段最大值max(空的话返回0)</returns>
114: public static int GetMaxID( string FieldName, string TableName ) {
115: string strsql="select max("+FieldName+") from "+TableName;
116: object obj=MsSQLHelper.GetSingle(strsql);
117: if( obj==null ) {
118: return 0;
119: } else {
120: return int.Parse(obj.ToString());
121: }
122: }
123: /// <summary>
124: /// 判断知否存在值
125: /// </summary>
126: /// <param name="strSql">sql语句</param>
127: /// <returns>存在true不存在false</returns>
128: public static bool Exists( string strSql ) {
129: object obj=MsSQLHelper.GetSingle(strSql);
130: int cmdresult;
131: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) {
132: cmdresult=0;
133: } else {
134: cmdresult=int.Parse(obj.ToString());
135: }
136: if( cmdresult==0 ) {
137: return false;
138: } else {
139: return true;
140: }
141: }
142: /// <summary>
143: /// 表是否存在
144: /// </summary>
145: /// <param name="TableName">表名</param>
146: /// <returns>存在true不存在false</returns>
147: public static bool TabExists( string TableName ) {
148: string strsql="select count(*) from sysobjects where id = object_id(N\"["+TableName+"]\") and OBJECTPROPERTY(id, N\"IsUserTable\") = 1";
149: object obj=MsSQLHelper.GetSingle(strsql);
150: int cmdresult;
151: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) {
152: cmdresult=0;
153: } else {
154: cmdresult=int.Parse(obj.ToString());
155: }
156: if( cmdresult==0 ) {
157: return false;
158: } else {
159: return true;
160: }
161: }
162: public static bool Exists( string strSql, params SqlParameter[] cmdParms ) {
163: object obj=MsSQLHelper.GetSingle(strSql, cmdParms);
164: int cmdresult;
165: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) {
166: cmdresult=0;
167: } else {
168: cmdresult=int.Parse(obj.ToString());
169: }
170: if( cmdresult==0 ) {
171: return false;
172: } else {
173: return true;
174: }
175: }
176: #endregion
177: #region 执行简单SQL语句
178: /// <summary>
179: /// 执行SQL语句,返回影响的记录数
180: /// </summary>
181: /// <param name="SQLString">SQL语句</param>
182: /// <returns>影响的记录数</returns>
183: public static int ExecuteSql( string SQLString ) {
184: using( SqlConnection connection=new SqlConnection(connectionString) ) {
185: using( SqlCommand cmd=new SqlCommand(SQLString, connection) ) {
186: try {
187: connection.Open();
188: int rows=cmd.ExecuteNonQuery();
189: return rows;
190: } catch( System.Data.SqlClient.SqlException e ) {
191: connection.Close();
192: throw e;
193: }
194: }
195: }
196: }
197: /// <summary>
198: /// 执行SQL语句,返回影响的记录数(延迟执行)
199: /// </summary>
200: /// <param name="SQLString">SQL语句</param>
201: /// <param name="Times">延迟时间</param>
202: /// <returns>影响的记录数</returns>
203: public static int ExecuteSqlByTime( string SQLString, int Times ) {
204: using( SqlConnection connection=new SqlConnection(connectionString) ) {
205: using( SqlCommand cmd=new SqlCommand(SQLString, connection) ) {
206: try {
207: connection.Open();
208: cmd.CommandTimeout=Times;
209: int rows=cmd.ExecuteNonQuery();
210: return rows;
211: } catch( System.Data.SqlClient.SqlException e ) {
212: connection.Close();
213: throw e;
214: }
215: }
216: }
217: }
218:
219: /// <summary>
220: /// 执行多条SQL语句,实现数据库事务。
221: /// </summary>
222: /// <param name="SQLStringList">多条SQL语句</param>
223: /// <returns>影响的记录数</returns>
224: public static int ExecuteSqlTran( List<String> SQLStringList ) {
225: using( SqlConnection conn=new SqlConnection(connectionString) ) {
226: conn.Open();
227: SqlCommand cmd=new SqlCommand();
228: cmd.Connection=conn;
229: SqlTransaction tx=conn.BeginTransaction();
230: cmd.Transaction=tx;
231: try {
232: int count=0;
233: for( int n=0 ; n<SQLStringList.Count ; n++ ) {
234: string strsql=SQLStringList[n];
235: if( strsql.Trim().Length>1 ) {
236: cmd.CommandText=strsql;
237: count+=cmd.ExecuteNonQuery();
238: }
239: }
240: tx.Commit();
241: return count;
242: } catch {
243: tx.Rollback();
244: return 0;
245: }
246: }
247: }
248: /// <summary>
249: /// 执行带一个存储过程参数的的SQL语句。
250: /// </summary>
251: /// <param name="SQLString">SQL语句</param>
252: /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
253: /// <returns>影响的记录数</returns>
254: public static int ExecuteSql( string SQLString, string content ) {
255: using( SqlConnection connection=new SqlConnection(connectionString) ) {
256: SqlCommand cmd=new SqlCommand(SQLString, connection);
257: System.Data.SqlClient.SqlParameter myParameter=new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
258: myParameter.Value=content;
259: cmd.Parameters.Add(myParameter);
260: try {
261: connection.Open();
262: int rows=cmd.ExecuteNonQuery();
263: return rows;
264: } catch( System.Data.SqlClient.SqlException e ) {
265: throw e;
266: } finally {
267: cmd.Dispose();
268: connection.Close();
269: }
270: }
271: }
272: /// <summary>
273: /// 执行带一个存储过程参数的的SQL语句。
274: /// </summary>
275: /// <param name="SQLString">SQL语句</param>
276: /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
277: /// <returns>返回查询结果第一列</returns>
278: public static object ExecuteSqlGet( string SQLString, string content ) {
279: using( SqlConnection connection=new SqlConnection(connectionString) ) {
280: SqlCommand cmd=new SqlCommand(SQLString, connection);
281: System.Data.SqlClient.SqlParameter myParameter=new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
282: myParameter.Value=content;
283: cmd.Parameters.Add(myParameter);
284: try {
285: connection.Open();
286: object obj=cmd.ExecuteScalar();
287: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) {
288: return null;
289: } else {
290: return obj;
291: }
292: } catch( System.Data.SqlClient.SqlException e ) {
293: throw e;
294: } finally {
295: cmd.Dispose();
296: connection.Close();
297: }
298: }
299: }
300: /// <summary>
301: /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
302: /// </summary>
303: /// <param name="strSQL">SQL语句</param>
304: /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
305: /// <returns>影响的记录数</returns>
306: public static int ExecuteSqlInsertImg( string strSQL, byte[] fs ) {
307: using( SqlConnection connection=new SqlConnection(connectionString) ) {
308: SqlCommand cmd=new SqlCommand(strSQL, connection);
309: System.Data.SqlClient.SqlParameter myParameter=new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
310: myParameter.Value=fs;
311: cmd.Parameters.Add(myParameter);
312: try {
313: connection.Open();
314: int rows=cmd.ExecuteNonQuery();
315: return rows;
316: } catch( System.Data.SqlClient.SqlException e ) {
317: throw e;
318: } finally {
319: cmd.Dispose();
320: connection.Close();
321: }
322: }
323: }
324: /// <summary>
325: /// 执行一条计算查询结果语句,返回查询结果(object)。
326: /// </summary>
327: /// <param name="SQLString">计算查询结果语句</param>
328: /// <returns>查询结果(object)</returns>
329: public static object GetSingle( string SQLString ) {
330: using( SqlConnection connection=new SqlConnection(connectionString) ) {
331: using( SqlCommand cmd=new SqlCommand(SQLString, connection) ) {
332: try {
333: connection.Open();
334: object obj=cmd.ExecuteScalar();
335: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) {
336: return null;
337: } else {
338: return obj;
339: }
340: } catch( System.Data.SqlClient.SqlException e ) {
341: connection.Close();
342: throw e;
343: }
344: }
345: }
346: }
347: public static object GetSingle( string SQLString, int Times ) {
348: using( SqlConnection connection=new SqlConnection(connectionString) ) {
349: using( SqlCommand cmd=new SqlCommand(SQLString, connection) ) {
350: try {
351: connection.Open();
352: cmd.CommandTimeout=Times;
353: object obj=cmd.ExecuteScalar();
354: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) {
355: return null;
356: } else {
357: return obj;
358: }
359: } catch( System.Data.SqlClient.SqlException e ) {
360: connection.Close();
361: throw e;
362: }
363: }
364: }
365: }
366: /// <summary>
367: /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
368: /// </summary>
369: /// <param name="strSQL">查询语句</param>
370: /// <returns>SqlDataReader</returns>
371: public static SqlDataReader ExecuteReader( string strSQL ) {
372: SqlConnection connection=new SqlConnection(connectionString);
373: SqlCommand cmd=new SqlCommand(strSQL, connection);
374: try {
375: connection.Open();
376: SqlDataReader myReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
377: return myReader;
378: } catch( System.Data.SqlClient.SqlException e ) {
379: throw e;
380: }
381: }
382: /// <summary>
383: /// 执行查询语句,返回DataSet
384: /// </summary>
385: /// <param name="SQLString">查询语句</param>
386: /// <returns>DataSet</returns>
387: public static DataSet Query( string SQLString ) {
388: using( SqlConnection connection=new SqlConnection(connectionString) ) {
389: DataSet ds=new DataSet();
390: try {
391: connection.Open();
392: SqlDataAdapter command=new SqlDataAdapter(SQLString, connection);
393: command.Fill(ds, "ds");
394: } catch( System.Data.SqlClient.SqlException ex ) {
395: throw new Exception(ex.Message);
396: }
397: return ds;
398: }
399: }
400: /// <summary>
401: /// 查询并得到数据集DataSet
402: /// </summary>
403: /// <param name="SQLString">查询语句</param>
404: /// <param name="Times"></param>
405: /// <returns></returns>
406: public static DataSet Query( string SQLString, int Times ) {
407: using( SqlConnection connection=new SqlConnection(connectionString) ) {
408: DataSet ds=new DataSet();
409: try {
410: connection.Open();
411: SqlDataAdapter command=new SqlDataAdapter(SQLString, connection);
412: command.SelectCommand.CommandTimeout=Times;
413: command.Fill(ds, "ds");
414: } catch( System.Data.SqlClient.SqlException ex ) {
415: throw new Exception(ex.Message);
416: }
417: return ds;
418: }
419: }
420: #endregion
421: #region 执行带参数的SQL语句
422: /// <summary>
423: /// 执行SQL语句,返回影响的记录数
424: /// </summary>
425: /// <param name="SQLString">SQL语句</param>
426: /// <returns>影响的记录数</returns>
427: public static int ExecuteSql( string SQLString, params SqlParameter[] cmdParms ) {
428: using( SqlConnection connection=new SqlConnection(connectionString) ) {
429: using( SqlCommand cmd=new SqlCommand() ) {
430: try {
431: PrepareCommand(cmd, connection, null, SQLString, cmdParms);
432: int rows=cmd.ExecuteNonQuery();
433: cmd.Parameters.Clear();
434: return rows;
435: } catch( System.Data.SqlClient.SqlException e ) {
436: throw e;
437: }
438: }
439: }
440: }
441: /// <summary>
442: /// 执行多条SQL语句,实现数据库事务。
443: /// </summary>
444: /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
445: public static void ExecuteSqlTran( Hashtable SQLStringList ) {
446: using( SqlConnection conn=new SqlConnection(connectionString) ) {
447: conn.Open();
448: using( SqlTransaction trans=conn.BeginTransaction() ) {
449: SqlCommand cmd=new SqlCommand();
450: try {
451: //循环
452: foreach( DictionaryEntry myDE in SQLStringList ) {
453: string cmdText=myDE.Key.ToString();
454: SqlParameter[] cmdParms=(SqlParameter[])myDE.Value;
455: PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
456: int val=cmd.ExecuteNonQuery();
457: cmd.Parameters.Clear();
458: }
459: trans.Commit();
460: } catch {
461: trans.Rollback();
462: throw;
463: }
464: }
465: }
466: }
467: /// <summary>
468: /// 执行多条SQL语句,实现数据库事务。
469: /// </summary>
470: /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
471: public static int ExecuteSqlTran( System.Collections.Generic.List<CommandInfo> cmdList ) {
472: using( SqlConnection conn=new SqlConnection(connectionString) ) {
473: conn.Open();
474: using( SqlTransaction trans=conn.BeginTransaction() ) {
475: SqlCommand cmd=new SqlCommand();
476: try {
477: int count=0;
478: //循环
479: foreach( CommandInfo myDE in cmdList ) {
480: string cmdText=myDE.CommandText;
481: SqlParameter[] cmdParms=(SqlParameter[])myDE.Parameters;
482: PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
483: if( myDE.EffentNextType==EffentNextType.WhenHaveContine||myDE.EffentNextType==EffentNextType.WhenNoHaveContine ) {
484: if( myDE.CommandText.ToLower().IndexOf("count(")==-1 ) {
485: trans.Rollback();
486: return 0;
487: }
488: object obj=cmd.ExecuteScalar();
489: bool isHave=false;
490: if( obj==null&&obj==DBNull.Value ) {
491: isHave=false;
492: }
493: isHave=Convert.ToInt32(obj)>0;
494: if( myDE.EffentNextType==EffentNextType.WhenHaveContine&&!isHave ) {
495: trans.Rollback();
496: return 0;
497: }
498: if( myDE.EffentNextType==EffentNextType.WhenNoHaveContine&&isHave ) {
499: trans.Rollback();
500: return 0;
501: }
502: continue;
503: }
504: int val=cmd.ExecuteNonQuery();
505: count+=val;
506: if( myDE.EffentNextType==EffentNextType.ExcuteEffectRows&&val==0 ) {
507: trans.Rollback();
508: return 0;
509: }
510: cmd.Parameters.Clear();
511: }
512: trans.Commit();
513: return count;
514: } catch {
515: trans.Rollback();
516: throw;
517: }
518: }
519: }
520: }
521: /// <summary>
522: /// 执行多条SQL语句,实现数据库事务。
523: /// </summary>
524: /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
525: public static void ExecuteSqlTranWithIndentity( System.Collections.Generic.List<CommandInfo> SQLStringList ) {
526: using( SqlConnection conn=new SqlConnection(connectionString) ) {
527: conn.Open();
528: using( SqlTransaction trans=conn.BeginTransaction() ) {
529: SqlCommand cmd=new SqlCommand();
530: try {
531: int indentity=0;
532: //循环
533: foreach( CommandInfo myDE in SQLStringList ) {
534: string cmdText=myDE.CommandText;
535: SqlParameter[] cmdParms=(SqlParameter[])myDE.Parameters;
536: foreach( SqlParameter q in cmdParms ) {
537: if( q.Direction==ParameterDirection.InputOutput ) {
538: q.Value=indentity;
539: }
540: }
541: PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
542: int val=cmd.ExecuteNonQuery();
543: foreach( SqlParameter q in cmdParms ) {
544: if( q.Direction==ParameterDirection.Output ) {
545: indentity=Convert.ToInt32(q.Value);
546: }
547: }
548: cmd.Parameters.Clear();
549: }
550: trans.Commit();
551: } catch {
552: trans.Rollback();
553: throw;
554: }
555: }
556: }
557: }
558: /// <summary>
559: /// 执行多条SQL语句,实现数据库事务。
560: /// </summary>
561: /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
562: public static void ExecuteSqlTranWithIndentity( Hashtable SQLStringList ) {
563: using( SqlConnection conn=new SqlConnection(connectionString) ) {
564: conn.Open();
565: using( SqlTransaction trans=conn.BeginTransaction() ) {
566: SqlCommand cmd=new SqlCommand();
567: try {
568: int indentity=0;
569: //循环
570: foreach( DictionaryEntry myDE in SQLStringList ) {
571: string cmdText=myDE.Key.ToString();
572: SqlParameter[] cmdParms=(SqlParameter[])myDE.Value;
573: foreach( SqlParameter q in cmdParms ) {
574: if( q.Direction==ParameterDirection.InputOutput ) {
575: q.Value=indentity;
576: }
577: }
578: PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
579: int val=cmd.ExecuteNonQuery();
580: foreach( SqlParameter q in cmdParms ) {
581: if( q.Direction==ParameterDirection.Output ) {
582: indentity=Convert.ToInt32(q.Value);
583: }
584: }
585: cmd.Parameters.Clear();
586: }
587: trans.Commit();
588: } catch {
589: trans.Rollback();
590: throw;
591: }
592: }
593: }
594: }
595: /// <summary>
596: /// 执行一条计算查询结果语句,返回查询结果(object)。
597: /// </summary>
598: /// <param name="SQLString">计算查询结果语句</param>
599: /// <returns>查询结果(object)</returns>
600: public static object GetSingle( string SQLString, params SqlParameter[] cmdParms ) {
601: using( SqlConnection connection=new SqlConnection(connectionString) ) {
602: using( SqlCommand cmd=new SqlCommand() ) {
603: try {
604: PrepareCommand(cmd, connection, null, SQLString, cmdParms);
605: object obj=cmd.ExecuteScalar();
606: cmd.Parameters.Clear();
607: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) {
608: return null;
609: } else {
610: return obj;
611: }
612: } catch( System.Data.SqlClient.SqlException e ) {
613: throw e;
614: }
615: }
616: }
617: }
618: /// <summary>
619: /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
620: /// </summary>
621: /// <param name="strSQL">查询语句</param>
622: /// <returns>SqlDataReader</returns>
623: public static SqlDataReader ExecuteReader( string SQLString, params SqlParameter[] cmdParms ) {
624: SqlConnection connection=new SqlConnection(connectionString);
625: SqlCommand cmd=new SqlCommand();
626: try {
627: PrepareCommand(cmd, connection, null, SQLString, cmdParms);
628: SqlDataReader myReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
629: cmd.Parameters.Clear();
630: return myReader;
631: } catch( System.Data.SqlClient.SqlException e ) {
632: throw e;
633: }
634: }
635: /// <summary>
636: /// 执行查询语句,返回DataSet
637: /// </summary>
638: /// <param name="SQLString">查询语句</param>
639: /// <returns>DataSet</returns>
640: public static DataSet Query( string SQLString, params SqlParameter[] cmdParms ) {
641: using( SqlConnection connection=new SqlConnection(connectionString) ) {
642: SqlCommand cmd=new SqlCommand();
643: PrepareCommand(cmd, connection, null, SQLString, cmdParms);
644: using( SqlDataAdapter da=new SqlDataAdapter(cmd) ) {
645: DataSet ds=new DataSet();
646: try {
647: da.Fill(ds, "ds");
648: cmd.Parameters.Clear();
649: } catch( System.Data.SqlClient.SqlException ex ) {
650: throw new Exception(ex.Message);
651: }
652: return ds;
653: }
654: }
655: }
656: private static void PrepareCommand( SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms ) {
657: if( conn.State!=ConnectionState.Open )
658: conn.Open();
659: cmd.Connection=conn;
660: cmd.CommandText=cmdText;
661: if( trans!=null )
662: cmd.Transaction=trans;
663: cmd.CommandType=CommandType.Text;//cmdType;
664: if( cmdParms!=null ) {
665: foreach( SqlParameter parameter in cmdParms ) {
666: if( ( parameter.Direction==ParameterDirection.InputOutput||parameter.Direction==ParameterDirection.Input )&&
667: ( parameter.Value==null ) ) {
668: parameter.Value=DBNull.Value;
669: }
670: cmd.Parameters.Add(parameter);
671: }
672: }
673: }
674: #endregion
675: #region 存储过程操作
676: /// <summary>
677: /// 执行存储过程,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
678: /// </summary>
679: /// <param name="storedProcName">存储过程名</param>
680: /// <returns>SqlDataReader</returns>
681: public static SqlDataReader RunProcedure( string storedProcName ) {
682: SqlConnection connection=new SqlConnection(connectionString);
683: SqlDataReader returnReader;
684: connection.Open();
685: SqlCommand command=new SqlCommand(storedProcName, connection);
686: command.CommandType=CommandType.StoredProcedure;
687: returnReader=command.ExecuteReader(CommandBehavior.CloseConnection);
688: return returnReader;
689: }
690:
691: /// <summary>
692: /// 执行存储过程,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
693: /// </summary>
694: /// <param name="storedProcName">存储过程名</param>
695: /// <param name="parameters">存储过程参数</param>
696: /// <returns>SqlDataReader</returns>
697: public static SqlDataReader RunProcedure( string storedProcName, IDataParameter[] parameters ) {
698: SqlConnection connection=new SqlConnection(connectionString);
699: SqlDataReader returnReader;
700: connection.Open();
701: SqlCommand command=BuildQueryCommand(connection, storedProcName, parameters);
702: command.CommandType=CommandType.StoredProcedure;
703: returnReader=command.ExecuteReader(CommandBehavior.CloseConnection);
704: return returnReader;
705: }
706: /// <summary>
707: /// 执行存储过程
708: /// </summary>
709: /// <param name="storedProcName">存储过程名</param>
710: /// <param name="parameters">存储过程参数</param>
711: /// <param name="tableName">DataSet结果中的表名</param>
712: /// <returns>DataSet</returns>
713: public static DataSet RunProcedure( string storedProcName, string tableName ) {
714: using( SqlConnection connection=new SqlConnection(connectionString) ) {
715: DataSet dataSet=new DataSet();
716: connection.Open();
717: SqlDataAdapter sqlDA=new SqlDataAdapter();
718: sqlDA.SelectCommand=new SqlCommand(storedProcName, connection);
719: sqlDA.Fill(dataSet, tableName);
720: connection.Close();
721: return dataSet;
722: }
723: }
724: /// <summary>
725: /// 执行存储过程
726: /// </summary>
727: /// <param name="storedProcName">存储过程名</param>
728: /// <param name="parameters">存储过程参数</param>
729: /// <param name="tableName">DataSet结果中的表名</param>
730: /// <returns>DataSet</returns>
731: public static DataSet RunProcedure( string storedProcName, IDataParameter[] parameters, string tableName ) {
732: using( SqlConnection connection=new SqlConnection(connectionString) ) {
733: DataSet dataSet=new DataSet();
734: connection.Open();
735: SqlDataAdapter sqlDA=new SqlDataAdapter();
736: sqlDA.SelectCommand=BuildQueryCommand(connection, storedProcName, parameters);
737: sqlDA.Fill(dataSet, tableName);
738: connection.Close();
739: return dataSet;
740: }
741: }
742: public static DataSet RunProcedure( string storedProcName, IDataParameter[] parameters, string tableName, int Times ) {
743: using( SqlConnection connection=new SqlConnection(connectionString) ) {
744: DataSet dataSet=new DataSet();
745: connection.Open();
746: SqlDataAdapter sqlDA=new SqlDataAdapter();
747: sqlDA.SelectCommand=BuildQueryCommand(connection, storedProcName, parameters);
748: sqlDA.SelectCommand.CommandTimeout=Times;
749: sqlDA.Fill(dataSet, tableName);
750: connection.Close();
751: return dataSet;
752: }
753: }
754: /// <summary>
755: /// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
756: /// </summary>
757: /// <param name="connection">数据库连接</param>
758: /// <param name="storedProcName">存储过程名</param>
759: /// <param name="parameters">存储过程参数</param>
760: /// <returns>SqlCommand</returns>
761: private static SqlCommand BuildQueryCommand( SqlConnection connection, string storedProcName, IDataParameter[] parameters ) {
762: SqlCommand command=new SqlCommand(storedProcName, connection);
763: command.CommandType=CommandType.StoredProcedure;
764: foreach( SqlParameter parameter in parameters ) {
765: if( parameter!=null ) {
766: // 检查未分配值的输出参数,将其分配以DBNull.Value.
767: if( ( parameter.Direction==ParameterDirection.InputOutput||parameter.Direction==ParameterDirection.Input )&&
768: ( parameter.Value==null ) ) {
769: parameter.Value=DBNull.Value;
770: }
771: command.Parameters.Add(parameter);
772: }
773: }
774: return command;
775: }
776: /// <summary>
777: /// 执行存储过程,返回影响的行数
778: /// </summary>
779: /// <param name="storedProcName">存储过程名</param>
780: /// <param name="parameters">存储过程参数</param>
781: /// <param name="rowsAffected">影响的行数</param>
782: /// <returns></returns>
783: public static int RunProcedure( string storedProcName, out int rowsAffected ) {
784: using( SqlConnection connection=new SqlConnection(connectionString) ) {
785: int result;
786: connection.Open();
787: SqlCommand command=new SqlCommand(storedProcName, connection);
788: rowsAffected=command.ExecuteNonQuery();
789: result=(int)command.Parameters["ReturnValue"].Value;
790: //Connection.Close();
791: return result;
792: }
793: }
794: /// <summary>
795: /// 执行存储过程,返回影响的行数
796: /// </summary>
797: /// <param name="storedProcName">存储过程名</param>
798: /// <param name="parameters">存储过程参数</param>
799: /// <param name="rowsAffected">影响的行数</param>
800: /// <returns></returns>
801: public static int RunProcedure( string storedProcName, IDataParameter[] parameters, out int rowsAffected ) {
802: using( SqlConnection connection=new SqlConnection(connectionString) ) {
803: int result;
804: connection.Open();
805: SqlCommand command=BuildIntCommand(connection, storedProcName, parameters);
806: rowsAffected=command.ExecuteNonQuery();
807: result=(int)command.Parameters["ReturnValue"].Value;
808: //Connection.Close();
809: return result;
810: }
811: }
812: /// <summary>
813: /// 创建 SqlCommand 对象实例(用来返回一个整数值)
814: /// </summary>
815: /// <param name="storedProcName">存储过程名</param>
816: /// <param name="parameters">存储过程参数</param>
817: /// <returns>SqlCommand 对象实例</returns>
818: private static SqlCommand BuildIntCommand( SqlConnection connection, string storedProcName, IDataParameter[] parameters ) {
819: SqlCommand command=BuildQueryCommand(connection, storedProcName, parameters);
820: command.Parameters.Add(new SqlParameter("ReturnValue",
821: SqlDbType.Int, 4, ParameterDirection.ReturnValue,
822: false, 0, 0, string.Empty, DataRowVersion.Default, null));
823: return command;
824: }
825: #endregion
826: }
827: }