一个抽象工厂数据访问模块（a data access component using abstract factory pattern SQL/OLEDB/ODBC）

来到这里已经半个月了，感谢MSTC不惜重金聘请MVP来教我们这48个菜鸟（整体素质之低有时候自己都觉得丢人），两本基础书籍的学习完成后，作为练笔，写了一个分布式的图书管理系统，规定时间10天，我用4天就搞定了，如果不是练玩带写的话，估计会更快。

这里第一次用到了自己的抽象工厂模式（Abstract Factory Pattern），但实际上却很失败——系统所有的数据访问基本上都要求用SP来做，这使得我的工厂形同虚设
但无论如何，还是写一下这个工厂吧：

首先是抽象工厂类（AbstractDbFactory.cs）
 1using System;
 2using System.Collections.Generic;
 3using System.Text;
 4using System.Data;
 5
 6namespace DAL
 7{
 8    /// <summary> 
 9    /// 数据库抽象工厂接口 
10    /// </summary> 
11    public interface AbstractDbFactory
12    {
13        /// <summary> 
14        /// 建立默认连接 
15        /// </summary> 
16        /// <returns>数据库连接</returns> 
17        IDbConnection CreateConnection();
18
19        /// <summary> 
20        /// 根据连接字符串建立Connection对象 
21        /// </summary> 
22        /// <param name="strConn">连接字符串</param> 
23        /// <returns>Connection对象</returns> 
24        IDbConnection CreateConnection(string strConn);
25
26        /// <summary> 
27        /// 建立Command对象 
28        /// </summary> 
29        /// <returns>Command对象</returns> 
30        IDbCommand CreateCommand();
31
32        /// <summary> 
33        /// 建立DataAdapter对象 
34        /// </summary> 
35        /// <returns>DataAdapter对象</returns> 
36        IDbDataAdapter CreateDataAdapter();
37
38        /// <summary> 
39        /// 根据Connection建立Transaction 
40        /// </summary> 
41        /// <param name="myDbConnection">Connection对象</param> 
42        /// <returns>Transaction对象</returns> 
43        IDbTransaction CreateTransaction(IDbConnection myDbConnection);
44
45        /// <summary> 
46        /// 根据Command建立DataReader 
47        /// </summary> 
48        /// <param name="myDbCommand">Command对象</param> 
49        /// <returns>DataReader对象</returns> 
50        IDataReader CreateDataReader(IDbCommand myDbCommand);
51
52        /// <summary>
53        /// 根据参数名和参数值建立DataParamter
54        /// </summary>
55        /// <param name="strProcName">参数名称</param>
56        /// <param name="value">参数值</param>
57        /// <returns>DataParamter对象</returns>
58        IDataParameter CreateDataParamter(string strParaName,object value);
59
60        /// <summary> 
61        /// 获得连接字符串 
62        /// </summary> 
63        /// <returns>连接字符串</returns> 
64        string GetConnectionString();
65    }
66}

然后是通用工厂接口（Factory.cs）
 1using System;
 2using System.Collections.Generic;
 3using System.Text;
 4using System.Configuration;
 5using System.Xml;
 6
 7namespace DAL
 8{
 9    /// <summary> 
10    /// Factory类 
11    /// </summary> 
12    public sealed class Factory
13    {
14        private static volatile Factory singleFactory = null;
15        private static object syncObj = new object();
16        /// <summary> 
17        /// Factory类构造函数 
18        /// </summary> 
19        private Factory()
20        {
21        }
22
23        /// <summary> 
24        /// 获得Factory类的实例 
25        /// </summary> 
26        /// <returns>Factory类实例</returns> 
27        public static Factory GetInstance()
28        {
29            if (singleFactory == null)
30            {
31                lock (syncObj)
32                {
33                    if (singleFactory == null)
34                    {
35                        singleFactory = new Factory();
36                    }
37                }
38            }
39            return singleFactory;
40        }
41
42        ///// <summary>
43        ///// Read confing
44        ///// </summary>
45        ///// <param name="appKey">参数</param>
46        ///// <returns></returns>
47        //public string GetConfigValue(string appKey)
48        //{
49        //    XmlDocument xDoc = new XmlDocument();
50        //    try
51        //    {
52        //        xDoc.Load(".//DAL.dll.config");
53        //        //xDoc.Load(System.Windows.Forms.Application.ExecutablePath+".config");
54        //        XmlNode xNode;
55        //        XmlElement xElem;
56        //        xNode = xDoc.SelectSingleNode("//appSettings");
57        //        xElem = (XmlElement)xNode.SelectSingleNode("//add[@key='" + appKey + "']");
58        //        if (xElem != null)
59        //            return xElem.GetAttribute("value");
60        //        else
61        //            return "";
62        //    }
63        //    catch (Exception)
64        //    {
65        //        return "";
66        //    }
67        //} 
68
69        /// <summary> 
70        /// 建立Factory类实例 
71        /// </summary> 
72        /// <returns>Factory类实例</returns> 
73        public AbstractDbFactory CreateInstance()
74        {
75            AbstractDbFactory abstractDbFactory = null;
76            switch (ConfigurationSettings.AppSettings["DatabaseType"].ToLower())
77            {
78                case "sqlserver":
79                    {
80                        abstractDbFactory = new SqlFactory();
81                        break;
82                    }
83                case "oledb":
84                    {
85                        abstractDbFactory = new OleDbFactory();
86                        break;
87                    }
88                case "odbc":
89                    {
90                        abstractDbFactory = new OdbcFactory();
91                        break;
92                    }
93            }
94            return abstractDbFactory;
95        }
96    }
97}
98
99

具体工厂类，这里写一个SQL的作例子吧（SqlFactory.cs）
  1using System;
  2using System.Data;
  3using System.Data.SqlClient;
  4using System.Configuration;
  5
  6
  7namespace DAL
  8{
  9    /// <summary> 
 10    /// 针对SqlServer专用连接的工厂 
 11    /// </summary> 
 12    public class SqlFactory : AbstractDbFactory
 13    {
 14        /// <summary> 
 15        /// 构造函数 
 16        /// </summary> 
 17        public SqlFactory()
 18        {
 19        }
 20
 21        /// <summary> 
 22        /// 建立默认Connection对象 
 23        /// </summary> 
 24        /// <returns>Connection对象</returns> 
 25        public IDbConnection CreateConnection()
 26        {
 27            return new SqlConnection();
 28        }
 29
 30        /// <summary> 
 31        /// 根据连接字符串建立Connection对象 
 32        /// </summary> 
 33        /// <param name="strConn">连接字符串</param> 
 34        /// <returns>Connection对象</returns> 
 35        public IDbConnection CreateConnection(string strConn)
 36        {
 37            return new SqlConnection(strConn);
 38        }
 39
 40        /// <summary> 
 41        /// 建立Command对象 
 42        /// </summary> 
 43        /// <returns>Command对象</returns> 
 44        public IDbCommand CreateCommand()
 45        {
 46            return new SqlCommand();
 47        }
 48
 49        /// <summary> 
 50        /// 建立DataAdapter对象 
 51        /// </summary> 
 52        /// <returns>DataAdapter对象</returns> 
 53        public IDbDataAdapter CreateDataAdapter()
 54        {
 55            return new SqlDataAdapter();
 56        }
 57
 58        /// <summary> 
 59        /// 根据Connection建立Transaction 
 60        /// </summary> 
 61        /// <param name="myDbConnection">Connection对象</param> 
 62        /// <returns>Transaction对象</returns> 
 63        public IDbTransaction CreateTransaction(IDbConnection myDbConnection)
 64        {
 65            return myDbConnection.BeginTransaction();
 66        }
 67
 68        /// <summary> 
 69        /// 根据Command建立DataReader 
 70        /// </summary> 
 71        /// <param name="myDbCommand">Command对象</param> 
 72        /// <returns>DataReader对象</returns> 
 73        public IDataReader CreateDataReader(IDbCommand myDbCommand)
 74        {
 75            return myDbCommand.ExecuteReader();
 76        }
 77
 78        /// <summary>
 79        /// 根据参数名和参数值建立DataParamter
 80        /// </summary>
 81        /// <param name="strProcName">参数名称</param>
 82        /// <param name="value">参数值</param>
 83        /// <returns>DataParamter对象</returns>
 84        public IDataParameter CreateDataParamter(string strParaName, object value)
 85        {
 86            return new SqlParameter(strParaName, value);
 87        }
 88
 89
 90        /// <summary> 
 91        /// 获得连接字符串 
 92        /// </summary> 
 93        /// <returns>连接字符串</returns> 
 94        public string GetConnectionString()
 95        {
 96            string strServer = ConfigurationSettings.AppSettings["SqlServerServer"];
 97            string strDatabase = ConfigurationSettings.AppSettings["SqlServerDatabase"];
 98            string strUid = ConfigurationSettings.AppSettings["SqlServerUid"];
 99            string strPwd = ConfigurationSettings.AppSettings["SqlServerPwd"];
100            string strConnectionString = "Server = " + strServer + "; Database = " + strDatabase + "; Uid = " + strUid + "; Pwd = " + strPwd + ";";
101            return strConnectionString;
102        }
103
104
105
106    }
107}
108

最后就可以写数据访问类了，这里最好采用“事务”（DbAccess.cs）
  1using System;
  2using System.Data;
  3using System.Data.SqlClient;
  4
  5namespace DAL
  6{
  7    /// <summary> 
  8    /// DbAccess类,即进行数据库访问时需要调用的类 
  9    /// </summary> 
 10    public sealed class DbAccess
 11    {
 12        /// <summary> 
 13        /// DbAccess构造函数 
 14        /// </summary> 
 15        private DbAccess()
 16        {
 17        }
 18
 19        /// <summary> 
 20        /// 无条件查询操作,即查询表中所有记录 
 21        /// </summary> 
 22        /// <param name="strTableName">表名</param> 
 23        /// <param name="strColumn">列名组</param> 
 24        /// <returns>无条件查询结果</returns> 
 25        public static DataSet SelectAll(string strTableName, string[] strColumn)
 26        {
 27            DataSet ds = new DataSet();
 28            Factory factory = Factory.GetInstance();
 29            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
 30            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
 31            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
 32            concreteDbConn.Open();
 33            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
 34            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
 35            concreteDbCommand.Connection = concreteDbConn;
 36            concreteDbCommand.Transaction = concreteDbTrans;
 37            IDbDataAdapter concreteDbAdapter = abstractDbFactory.CreateDataAdapter();
 38            try
 39            {
 40                string strSql = "SELECT ";
 41                for (int i = 0; i < strColumn.Length - 1; i++)
 42                {
 43                    strSql += (strColumn[i] + ", ");
 44                }
 45                strSql += (strColumn[strColumn.Length - 1] + " FROM " + strTableName);
 46                concreteDbCommand.CommandText = strSql;
 47                concreteDbAdapter.SelectCommand = concreteDbCommand;
 48                concreteDbAdapter.Fill(ds);
 49                concreteDbTrans.Commit();
 50            }
 51            catch
 52            {
 53                concreteDbTrans.Rollback();
 54                ds.Clear();
 55                throw;
 56            }
 57            finally
 58            {
 59                concreteDbConn.Close();
 60            }
 61            return ds;
 62        }
 63
 64        /// <summary> 
 65        /// 条件查询操作 
 66        /// </summary> 
 67        /// <param name="strTableName">表名</param> 
 68        /// <param name="strColumn">列名组</param> 
 69        /// <param name="strCondition">条件</param> 
 70        /// <returns>条件查询结果</returns> 
 71        public static DataSet Select(string strTableName, string[] strColumn, string strCondition)
 72        {
 73            DataSet ds = new DataSet();
 74            Factory factory = Factory.GetInstance();
 75            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
 76            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
 77            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
 78            concreteDbConn.Open();
 79            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
 80            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
 81            concreteDbCommand.Connection = concreteDbConn;
 82            concreteDbCommand.Transaction = concreteDbTrans;
 83            IDbDataAdapter concreteDbAdapter = abstractDbFactory.CreateDataAdapter();
 84            try
 85            {
 86                string strSql = "SELECT ";
 87                for (int i = 0; i < strColumn.Length - 1; i++)
 88                {
 89                    strSql += (strColumn[i] + ", ");
 90                }
 91                strSql += (strColumn[strColumn.Length - 1] + " FROM " + strTableName + " WHERE " + strCondition);
 92                concreteDbCommand.CommandText = strSql;
 93                concreteDbAdapter.SelectCommand = concreteDbCommand;
 94                concreteDbAdapter.Fill(ds);
 95                concreteDbTrans.Commit();
 96            }
 97            catch
 98            {
 99                concreteDbTrans.Rollback();
100                ds.Clear();
101                throw;
102            }
103            finally
104            {
105                concreteDbConn.Close();
106            }
107            return ds;
108        }
109
110        /// <summary> 
111        /// 单条记录的插入操作 
112        /// </summary> 
113        /// <param name="strTableName">表名</param> 
114        /// <param name="strColumn">列名组</param> 
115        /// <param name="strvalue">值组</param> 
116        public static void Insert(string strTableName, string[] strColumn, object[] strvalue)
117        {
118            Factory factory = Factory.GetInstance();
119            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
120            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
121            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
122            concreteDbConn.Open();
123            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
124            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
125            concreteDbCommand.Connection = concreteDbConn;
126            concreteDbCommand.Transaction = concreteDbTrans;
127            try
128            {
129                string strSql = "INSERT INTO " + strTableName + " (";
130                for (int i = 0; i < strColumn.Length - 1; i++)
131                {
132                    strSql += (strColumn[i] + ", ");
133                }
134                strSql += (strColumn[strColumn.Length - 1] + ") valueS (’");
135                for (int i = 0; i < strvalue.Length - 1; i++)
136                {
137                    strSql += (strvalue[i] + "’, ’");
138                }
139                strSql += (strvalue[strvalue.Length - 1] + "’)");
140                concreteDbCommand.CommandText = strSql;
141                concreteDbCommand.ExecuteNonQuery();
142                concreteDbTrans.Commit();
143            }
144            catch
145            {
146                concreteDbTrans.Rollback();
147                throw;
148            }
149            finally
150            {
151                concreteDbConn.Close();
152            }
153        }
154
155        /// <summary> 
156        /// 批量记录的插入操作,即可一次向多张表中插入不同的批量记录 
157        /// </summary> 
158        /// <param name="ds">批量记录组成的DataSet,DataSet中的各个DataTable名为表名,各DataTable中的DataColumn名为列名</param> 
159        public static void InsertSet(ref DataSet ds)
160        {
161            Factory factory = Factory.GetInstance();
162            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
163            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
164            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
165            concreteDbConn.Open();
166            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
167            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
168            concreteDbCommand.Connection = concreteDbConn;
169            concreteDbCommand.Transaction = concreteDbTrans;
170            try
171            {
172                foreach (DataTable dt in ds.Tables)
173                {
174                    foreach (DataRow dr in dt.Rows)
175                    {
176                        string strSql = "INSERT INTO " + dt.TableName + " (";
177                        for (int i = 0; i < dt.Columns.Count - 1; i++)
178                        {
179                            strSql += (dt.Columns[i].Caption + ", ");
180                        }
181                        strSql += (dt.Columns[dt.Columns.Count - 1].Caption + ") valueS (’");
182                        for (int i = 0; i < dt.Columns.Count - 1; i++)
183                        {
184                            strSql += (dr[i] + "’, ’");
185                        }
186                        strSql += (dr[dt.Columns.Count - 1] + "’)");
187                        concreteDbCommand.CommandText = strSql;
188                        concreteDbCommand.ExecuteNonQuery();
189                    }
190                }
191                concreteDbTrans.Commit();
192            }
193            catch
194            {
195                concreteDbTrans.Rollback();
196                throw;
197            }
198
199            finally
200            {
201                concreteDbConn.Close();
202            }
203        }
204
205        /// <summary> 
206        /// 无条件删除操作,即删除表中所有记录 
207        /// </summary> 
208        /// <param name="strTableName">表名</param> 
209        public static void DeleteAll(string strTableName)
210        {
211            Factory factory = Factory.GetInstance();
212            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
213            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
214            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
215            concreteDbConn.Open();
216            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
217            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
218            concreteDbCommand.Connection = concreteDbConn;
219            concreteDbCommand.Transaction = concreteDbTrans;
220            try
221            {
222                string strSql = "DELETE FROM " + strTableName;
223                concreteDbCommand.CommandText = strSql;
224                concreteDbCommand.ExecuteNonQuery();
225                concreteDbTrans.Commit();
226            }
227            catch
228            {
229                concreteDbTrans.Rollback();
230                throw;
231            }
232            finally
233            {
234                concreteDbConn.Close();
235            }
236        }
237
238        /// <summary> 
239        /// 条件删除操作 
240        /// </summary> 
241        /// <param name="strTableName">表名</param> 
242        /// <param name="strCondition">条件</param> 
243        public static void Delete(string strTableName, string strCondition)
244        {
245            Factory factory = Factory.GetInstance();
246            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
247            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
248            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
249            concreteDbConn.Open();
250            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
251            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
252            concreteDbCommand.Connection = concreteDbConn;
253            concreteDbCommand.Transaction = concreteDbTrans;
254            try
255            {
256                string strSql = "DELETE FROM " + strTableName + " WHERE " + strCondition;
257                concreteDbCommand.CommandText = strSql;
258                concreteDbCommand.ExecuteNonQuery();
259                concreteDbTrans.Commit();
260            }
261            catch
262            {
263                concreteDbTrans.Rollback();
264                throw;
265            }
266            finally
267            {
268                concreteDbConn.Close();
269            }
270        }
271
272        /// <summary> 
273        /// 无条件更新操作,即更新表中所有记录 
274        /// </summary> 
275        /// <param name="strTableName">表名</param> 
276        /// <param name="strColumn">列名组</param> 
277        /// <param name="strvalue">值组</param> 
278        public static void UpdateAll(string strTableName, string[] strColumn, object[] strvalue)
279        {
280            Factory factory = Factory.GetInstance();
281            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
282            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
283            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
284            concreteDbConn.Open();
285            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
286            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
287            concreteDbCommand.Connection = concreteDbConn;
288            concreteDbCommand.Transaction = concreteDbTrans;
289            try
290            {
291                string strSql = "UPDATE " + strTableName + " SET ";
292                for (int i = 0; i < strColumn.Length - 1; i++)
293                {
294                    strSql += (strColumn[i] + " = ’" + strvalue[i] + "’, ");
295                }
296                strSql += (strColumn[strColumn.Length - 1] + " = ’" + strvalue[strvalue.Length - 1] + "’ ");
297                concreteDbCommand.CommandText = strSql;
298                concreteDbCommand.ExecuteNonQuery();
299                concreteDbTrans.Commit();
300            }
301            catch
302            {
303                concreteDbTrans.Rollback();
304                throw;
305            }
306            finally
307            {
308                concreteDbConn.Close();
309            }
310        }
311
312        /// <summary> 
313        /// 条件更新操作 
314        /// </summary> 
315        /// <param name="strTableName">表名</param> 
316        /// <param name="strColumn">列名组</param> 
317        /// <param name="strvalue">值组</param> 
318        /// <param name="strCondition">条件</param> 
319        public static void Update(string strTableName, string[] strColumn, object[] strvalue, string strCondition)
320        {
321            Factory factory = Factory.GetInstance();
322            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
323            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
324            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
325            concreteDbConn.Open();
326            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
327            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
328            concreteDbCommand.Connection = concreteDbConn;
329            concreteDbCommand.Transaction = concreteDbTrans;
330            try
331            {
332                string strSql = "UPDATE " + strTableName + " SET ";
333                for (int i = 0; i < strColumn.Length - 1; i++)
334                {
335                    strSql += (strColumn[i] + " = ’" + strvalue[i] + "’, ");
336                }
337                strSql += (strColumn[strColumn.Length - 1] + " = ’" + strvalue[strvalue.Length - 1] + "’ " + " WHERE " + strCondition);
338                concreteDbCommand.CommandText = strSql;
339                concreteDbCommand.ExecuteNonQuery();
340                concreteDbTrans.Commit();
341            }
342            catch
343            {
344                concreteDbTrans.Rollback();
345                throw;
346            }
347            finally
348            {
349                concreteDbConn.Close();
350            }
351        }
352
353        /// <summary> 
354        ///  多参数存储过程询操作
355        /// </summary> 
356        /// <param name="strTableName">参数名列表</param> 
357        /// <param name="strColumn">参数值列表</param> 
358        /// <returns>查询所得结果</returns> 
359        public static DataSet SelectAllProc(string strProcname, string[] strParam, object[] strValue)
360        {
361            DataSet ds = new DataSet();
362            Factory factory = Factory.GetInstance();
363            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
364            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
365            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
366            concreteDbConn.Open();
367            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
368            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
369            concreteDbCommand.Connection = concreteDbConn;
370            concreteDbCommand.Transaction = concreteDbTrans;
371            concreteDbCommand.CommandText = strProcname;
372            concreteDbCommand.CommandType = CommandType.StoredProcedure;
373            IDbDataAdapter concreteDbAdapter = abstractDbFactory.CreateDataAdapter();
374            try
375            {
376                concreteDbAdapter.SelectCommand = concreteDbCommand;
377                for (int i = 0; i < strParam.Length; i++)
378                {
379                    IDataParameter adminid = abstractDbFactory.CreateDataParamter(strParam[i], strValue[i]);
380                    concreteDbAdapter.SelectCommand.Parameters.Add(adminid);
381                }
382                concreteDbAdapter.Fill(ds);
383                concreteDbTrans.Commit();
384            }
385            catch
386            {
387                concreteDbTrans.Rollback();
388                ds.Clear();
389                throw;
390            }
391            finally
392            {
393                concreteDbConn.Close();
394            }
395            return ds;
396        }
397
398        /// <summary>
399        /// 单参数存储过程调用
400        /// </summary>
401        /// <param name="strProcname">存储过程名</param>
402        /// <param name="strParam">参数名</param>
403        /// <param name="strValue">参数值</param>
404        /// <returns>查询结果</returns>
405        public static DataSet SelectProc(string strProcname, string strParam, string strValue)
406        {
407            DataSet ds = new DataSet();
408            Factory factory = Factory.GetInstance();
409            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
410            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
411            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
412            concreteDbConn.Open();
413            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
414            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
415            concreteDbCommand.Connection = concreteDbConn;
416            concreteDbCommand.Transaction = concreteDbTrans;
417            concreteDbCommand.CommandText = strProcname;
418            concreteDbCommand.CommandType = CommandType.StoredProcedure;
419            IDataParameter concreteDataParameter = abstractDbFactory.CreateDataParamter(strParam, strValue);
420            IDbDataAdapter concreteDbAdapter = abstractDbFactory.CreateDataAdapter();
421            try
422            {
423                concreteDbAdapter.SelectCommand = concreteDbCommand;
424                concreteDbAdapter.SelectCommand.Parameters.Add(concreteDataParameter);
425                concreteDbAdapter.Fill(ds);
426                concreteDbTrans.Commit();
427            }
428            catch
429            {
430                concreteDbTrans.Rollback();
431                ds.Clear();
432                throw;
433            }
434            finally
435            {
436                concreteDbConn.Close();
437            }
438            return ds;
439        }
440
441        /// <summary>
442        /// 无返回值的存储过程
443        /// </summary>
444        /// <param name="strProcname">存储过程名</param>
445        /// <param name="strParam">参数列表</param>
446        /// <param name="strValue">参数值列表</param>
447        public static void NoReturnProc(string strProcname, string[] strParam, object[] strValue)
448        {
449            Factory factory = Factory.GetInstance();
450            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
451            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
452            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
453            concreteDbConn.Open();
454            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
455            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
456            concreteDbCommand.Connection = concreteDbConn;
457            concreteDbCommand.Transaction = concreteDbTrans;
458            concreteDbCommand.CommandText = strProcname;
459            concreteDbCommand.CommandType = CommandType.StoredProcedure;
460            try
461            {
462                for (int i = 0; i < strParam.Length; i++)
463                {
464                    IDataParameter adminid = abstractDbFactory.CreateDataParamter(strParam[i], strValue[i]);
465                    concreteDbCommand.Parameters.Add(adminid);
466                }
467                concreteDbCommand.ExecuteNonQuery();
468                concreteDbTrans.Commit();
469            }
470            catch
471            {
472                concreteDbTrans.Rollback();
473                throw;
474            }
475            finally
476            {
477                concreteDbConn.Close();
478            }
479        }
480
481        /// <summary>
482        /// 用户登录验证
483        /// </summary>
484        /// <param name="strProcname">存储过程名</param>
485        /// <param name="strID">用户ID</param>
486        /// <param name="btPassword">密码</param>
487        /// <returns></returns>
488        public static DataSet Login(string strProcname, string strID, byte[] btPassword)
489        {
490            DataSet ds = new DataSet();
491            Factory factory = Factory.GetInstance();
492            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
493            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
494            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
495            concreteDbConn.Open();
496            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
497            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
498            concreteDbCommand.Connection = concreteDbConn;
499            concreteDbCommand.Transaction = concreteDbTrans;
500            concreteDbCommand.CommandText = strProcname;
501            concreteDbCommand.CommandType = CommandType.StoredProcedure;
502            IDbDataAdapter concreteDbAdapter = abstractDbFactory.CreateDataAdapter();
503            try
504            {
505                concreteDbAdapter.SelectCommand = concreteDbCommand;
506                IDataParameter concreteParametersID = abstractDbFactory.CreateDataParamter("@AdminID", SqlDbType.Char);
507                SqlParameter concreteParametersPW = new SqlParameter("@Password", SqlDbType.Binary);
508                concreteParametersID.Value = strID;
509                concreteParametersPW.Value = btPassword;
510                concreteDbAdapter.SelectCommand.Parameters.Add(concreteParametersID);
511                concreteDbAdapter.SelectCommand.Parameters.Add(concreteParametersPW);
512                concreteDbAdapter.Fill(ds);
513                concreteDbTrans.Commit();
514            }
515            catch
516            {
517                concreteDbTrans.Rollback();
518                ds.Clear();
519                throw;
520            }
521            finally
522            {
523                concreteDbConn.Close();
524            }
525            return ds;
526        }
527
528        /// <summary>
529        /// 用户登录验证
530        /// </summary>
531        /// <param name="strProcname">存储过程名</param>
532        /// <param name="strID">用户ID</param>
533        /// <param name="btPassword">密码</param>
534        /// <returns></returns>
535        public static DataSet ULogin(string strProcname, string strID, byte[] btPassword)
536        {
537            DataSet ds = new DataSet();
538            Factory factory = Factory.GetInstance();
539            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
540            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
541            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
542            concreteDbConn.Open();
543            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
544            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
545            concreteDbCommand.Connection = concreteDbConn;
546            concreteDbCommand.Transaction = concreteDbTrans;
547            concreteDbCommand.CommandText = strProcname;
548            concreteDbCommand.CommandType = CommandType.StoredProcedure;
549            IDbDataAdapter concreteDbAdapter = abstractDbFactory.CreateDataAdapter();
550            try
551            {
552                concreteDbAdapter.SelectCommand = concreteDbCommand;
553                IDataParameter concreteParametersID = abstractDbFactory.CreateDataParamter("@UserID", SqlDbType.Char);
554                SqlParameter concreteParametersPW = new SqlParameter("@Password", SqlDbType.Binary);
555                concreteParametersID.Value = strID;
556                concreteParametersPW.Value = btPassword;
557                concreteDbAdapter.SelectCommand.Parameters.Add(concreteParametersID);
558                concreteDbAdapter.SelectCommand.Parameters.Add(concreteParametersPW);
559                concreteDbAdapter.Fill(ds);
560                concreteDbTrans.Commit();
561            }
562            catch
563            {
564                concreteDbTrans.Rollback();
565                ds.Clear();
566                throw;
567            }
568            finally
569            {
570                concreteDbConn.Close();
571            }
572            return ds;
573        }
574    }
575}
576

posted @ 2007-08-18 08:30  LanceZhang  阅读(1209)  评论(4)    收藏  举报