1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 //新添命名空间
  6 using System.Data;
  7 using System.Configuration;
  8 using System.Data.OleDb;
  9 
 10 namespace HomeShop.DbUtility
 11 {
 12     //功能：OleDb数据访问基础类；
 13     //作者：夏春涛；
 14     //日期：2011-12-05；
 15     public class OleDbHelper
 16     {
 17         //数据库连接字符串
 18         private string connectionString = "";
 19         public string ConnectionString
 20         {
 21             get { return connectionString; }
 22         }
 23 
 24         //数据库连接对象
 25         private OleDbConnection connection;
 26 
 27         //默认构造函数，从应用程序配置文件中读取第1个数据库连接字符串
 28         public OleDbHelper()
 29         {
 30             this.connectionString = ConfigurationManager.ConnectionStrings[1].ConnectionString;
 31             this.connection = new OleDbConnection(this.connectionString);
 32         }
 33 
 34         //重载构造函数，根据入参初始化数据库连接字符串
 35         public OleDbHelper(string connectionString)
 36         {
 37             this.connectionString = connectionString;
 38             this.connection = new OleDbConnection(this.connectionString);
 39         }
 40 
 41         //执行SQL命令（Select），获取返回数据
 42         public DataTable ExecuteQuery(string selectCommandText, params OleDbParameter[] cmdParameters)
 43         {
 44             OleDbCommand command = new OleDbCommand();
 45             PrepareCommand(command, null, CommandType.Text, selectCommandText, cmdParameters);
 46             OleDbDataAdapter adapter = new OleDbDataAdapter(command);
 47             DataTable dataTable = new DataTable();
 48             adapter.Fill(dataTable);
 49             connection.Close();
 50 
 51             return dataTable;
 52         }
 53 
 54         //执行多条SQL命令（Insert/Delete/Update），以数据库事务方式
 55         public int ExecuteNonQueryTrans(List<OleDbCmdTextAndParams> listCmdTextAndParams)
 56         {
 57             int rowsCountAffected = 0;            
 58             //开始数据库事务
 59             OleDbTransaction trans = BeginTransaction();
 60             OleDbCommand command = new OleDbCommand();            
 61             try
 62             {
 63                 //循环执行每一个CommandTextAndParameters对象
 64                 foreach (OleDbCmdTextAndParams cmdTextAndParams in listCmdTextAndParams)
 65                 {
 66                     PrepareCommand(command, trans, CommandType.Text, cmdTextAndParams.CommandText, cmdTextAndParams.CommandParameters);                    
 67                     rowsCountAffected += command.ExecuteNonQuery();
 68                     //清空参数列表，避免参数重名问题
 69                     command.Parameters.Clear();
 70                 }
 71                 trans.Commit();//提交数据库事务
 72             }
 73             catch
 74             {
 75                 trans.Rollback();//回滚数据库事务
 76                 throw;
 77             }
 78             connection.Close();
 79 
 80             return rowsCountAffected;
 81         }
 82 
 83         //开始（生成）一个数据库事务
 84         public OleDbTransaction BeginTransaction()
 85         {
 86             if (connection.State != ConnectionState.Open)
 87                 connection.Open();
 88             OleDbTransaction trans = connection.BeginTransaction();
 89 
 90             return trans;
 91         }
 92 
 93         //执行SQL命令（Insert/Delete/Update）
 94         public int ExecuteNonQuery(string cmdText, params OleDbParameter[] cmdParameters)
 95         {
 96             int rowsCountAffected = ExecuteNonQuery(null, cmdText, cmdParameters);
 97             connection.Close();
 98             return rowsCountAffected;
 99         }
100 
101         //在指定的数据库事务中执行SQL命令，最后要手工提交事务
102         public int ExecuteNonQuery(OleDbTransaction inTransaction, string cmdText, params OleDbParameter[] cmdParameters)
103         {
104             int rowsCountAffected = 0;
105             OleDbCommand command = new OleDbCommand();
106             PrepareCommand(command, inTransaction, CommandType.Text, cmdText, cmdParameters);
107             rowsCountAffected += command.ExecuteNonQuery();
108             command.Parameters.Clear();//清空参数列表，避免参数重名问题
109             //connection.Close();//在数据库事务结束时才能关闭连接
110 
111             return rowsCountAffected;
112         }
113 
114         //获取查询结果集中的第一行的第一列内容
115         public Object ExecuteScalar(string selectCommandText, params OleDbParameter[] cmdParameters)
116         {
117             Object obj = ExecuteScalar(null, selectCommandText, cmdParameters);
118             connection.Close();
119             return obj;
120         }
121 
122         //获取查询结果集中的第一行的第一列内容
123         public Object ExecuteScalar(OleDbTransaction inTransaction, string selectCommandText, params OleDbParameter[] cmdParameters)
124         {
125             OleDbCommand command = new OleDbCommand();
126             PrepareCommand(command, inTransaction, CommandType.Text, selectCommandText, cmdParameters);
127             Object obj = command.ExecuteScalar();
128             command.Parameters.Clear();//清空参数列表，避免参数重名问题
129             //connection.Close();//在数据库事务结束时才能关闭连接
130 
131             return obj;
132         }
133 
134         //关闭数据库连接
135         public void Close()
136         {
137             if (connection.State != ConnectionState.Open)
138                 connection.Close();
139         }
140 
141         /****************************************************************************************/
142        
143         //预处理Command对象
144         private void PrepareCommand(OleDbCommand command, OleDbTransaction inTransation, 
145             CommandType cmdType, string cmdText, OleDbParameter[] cmdParameters)
146         {
147             if (connection.State != ConnectionState.Open)
148                 connection.Open();
149 
150             command.Connection = connection;
151             command.CommandType = cmdType;
152             command.CommandText = cmdText;
153 
154             if (inTransation != null)
155                 command.Transaction = inTransation;
156 
157             if (cmdParameters != null)
158             {
159                 foreach (OleDbParameter param in cmdParameters)
160                     command.Parameters.Add(param);
161             }
162         }
163     }
164 }