ASP.NET通用数据库访问类（兼容多种数据库）
利用c# 2.0数据库访问工厂重写了一个通用数据库访问类，利用web.config的providerName可轻松切换数据库 web.config文件配置 appSettings/ connectionStrings add name="Provider" connectionString="Provider=Microsof
利用c# 2.0数据库访问工厂重写了一个通用数据库访问类，利用web.config的 providerName可轻松切换数据库
web.config文件配置
<appSettings/>
<connectionStrings>
<add name="Provider" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;data source=" providerName="System.Data.OleDb"/>
<!--<add name="Provier" connectionString="server=local;database=aa;user=sa;pwd=" providerName="System.Data.SqlClient"/>-->
<add name="DataPath" connectionString="~\\App_Data\\xx.mdb"/>
</connectionStrings>
dbhelper.cs
利用c# 2.0数据库访问工厂重写了一个通用数据库访问类，利用web.config的 providerName可轻松切换数据库
Code1 public class DbHelper
2 {
3 private DbConnection Connection;
4 private DbProviderFactory provider;
5 private CommandType _CommandType;
6 public CommandType CommandType
7 {
8 get { return _CommandType; }
9 set { _CommandType = value; }
10 }
11 /// <summary>
12 /// 初始化Dbconnection对象
13 /// </summary>
14 /// <returns></returns>
15 private DbConnection buildInitConnection()
16 {
17 string connectionString = ConfigurationManager.ConnectionStrings["Provider"].ConnectionString;
18 if (ConfigurationManager.ConnectionStrings["Provider"].ProviderName == "System.Data.OleDb")
19 {
20 connectionString += HttpContext.Current.Server.MapPath(ConfigurationManager.ConnectionStrings["DataPath"].ConnectionString);
21 }
22 provider = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings["Provider"].ProviderName);
23 Connection = provider.CreateConnection();
24 Connection.ConnectionString = connectionString;
25 return Connection;
26
27 }
28 /// <summary>
29 /// 默认构造函数(初始化DbConnection)
30 /// </summary>
31 public DbHelper()
32 {
33 buildInitConnection();
34 this._CommandType = CommandType.Text;
35 }
36 /// <summary>
37 /// 初始化DbCommand对象
38 /// </summary>
39 /// <returns></returns>
40 private DbCommand BuildInitCommand()
41 {
42 DbCommand Command = provider.CreateCommand();
43 Command.Connection = Connection;
44 Command.CommandType = this._CommandType;
45 return Command;
46 }
47 /// <summary>
48 /// 初始化DbCommand对象
49 /// </summary>
50 /// <param name="commandText">T-Sql语句</param>
51 /// <returns>DbCommand对象</returns>
52 private DbCommand BuildInitCommand(string commandText)
53 {
54 DbCommand Command = provider.CreateCommand();
55 Command.Connection = Connection;
56 Command.CommandType = this._CommandType;
57 Command.CommandText = commandText;
58 return Command;
59 }
60 /// <summary>
61 /// 初始化DbCommand对象
62 /// </summary>
63 /// <param name="commandText">T-Sql语句</param>
64 /// <param name="op">DbParameter数组</param>
65 /// <returns>DbCommand对象</returns>
66 private DbCommand BuildInitCommand(string commandText, DbParameter[] op)
67 {
68 DbCommand Command = provider.CreateCommand();
69 Command.Connection = Connection;
70 Command.CommandType = this._CommandType;
71 Command.CommandText = commandText;
72 foreach (DbParameter p in op)
73 {
74 Command.Parameters.Add(p);
75 }
76 return Command;
77 }
78 /// <summary>
79 /// 执行T-Sql语句,返回受影响的行数
80 /// </summary>
81 /// <param name="commandText">T-Sql语句</param>
82 /// <returns></returns>
83 public int ExecuteNonQuery(string commandText)
84 {
85 int flagValue;
86 DbCommand Command = BuildInitCommand(commandText);
87 Connection.Open();
88 flagValue = Command.ExecuteNonQuery();
89 Connection.Close();
90 return flagValue;
91 }
92 /// <summary>
93 /// 执行带数的T-Sql语句,返回受影响的行数
94 /// </summary>
95 /// <param name="commandText">T-Sql语句</param>
96 /// <param name="op"></param>
97 /// <returns></returns>
98 public int ExecuteNonQuery(string commandText, DbParameter[] op)
99 {
100 int flagValue;
101 DbCommand Command = BuildInitCommand(commandText, op);
102 Connection.Open();
103 flagValue = Command.ExecuteNonQuery();
104 Connection.Close();
105 return flagValue;
106 }
107 /// <summary>
108 /// 执行T-Sql语句,返回第一行第一列
109 /// </summary>
110 /// <param name="commandText">T-Sql语句</param>
111 /// <returns></returns>
112 public Object ExecuteScalar(string commandText)
113 {
114 DbCommand Command = BuildInitCommand(commandText);
115 Connection.Open();
116 object flagValue = Command.ExecuteScalar();
117 Connection.Close();
118 return flagValue;
119 }
120 /// <summary>
121 /// 执行带参数的T-Sql语句,返回第一行第一列
122 /// </summary>
123 /// <param name="commandText">T-Sql语句</param>
124 /// <param name="op"></param>
125 /// <returns></returns>
126 public object ExecuteScalar(string commandText, DbParameter[] op)
127 {
128 DbCommand Command = BuildInitCommand(commandText, op);
129 Connection.Open();
130 object flagValue = Command.ExecuteScalar();
131 Connection.Close();
132 return flagValue;
133 }
134 /// <summary>
135 /// 执行T-Sql语句，返回DbDataReader
136 /// </summary>
137 /// <param name="commandText">T-Sql语句</param>
138 /// <returns></returns>
139 public DbDataReader ExecuteReader(string commandText)
140 {
141 DbCommand Command = BuildInitCommand(commandText);
142 Connection.Open();
143 DbDataReader Reader = Command.ExecuteReader(CommandBehavior.CloseConnection);
144 return Reader;
145 }
146 /// <summary>
147 /// 执行带参数的T-Sql语句，返回DbDataReader
148 /// </summary>
149 /// <param name="commandText">T-Sql语句</param>
150 /// <param name="op"></param>
151 /// <returns></returns>
152 public DbDataReader ExecuteReader(string commandText, DbParameter[] op)
153 {
154 DbCommand Command = BuildInitCommand(commandText, op);
155 Connection.Open();
156 DbDataReader Reader = Command.ExecuteReader(CommandBehavior.CloseConnection);
157 return Reader;
158 }
159 /// <summary>
160 /// 执行T-Sql语句,返回DataSet;
161 /// </summary>
162 /// <param name="commandText"></param>
163 /// <param name="tableName"></param>
164 /// <returns></returns>
165 public DataSet ExecuteDataSet(string commandText, string tableName)
166 {
167 DbDataAdapter Adapter = provider.CreateDataAdapter();
168 Adapter.SelectCommand = BuildInitCommand(commandText);
169 DataSet Ds = new DataSet();
170 Connection.Open();
171 Adapter.Fill(Ds, tableName);
172 Connection.Close();
173 return Ds;
174 }
175 /// <summary>
176 /// 执行带参数的T-Sql语句,返回DataSet;
177 /// </summary>
178 /// <param name="commandText"></param>
179 /// <param name="op"></param>
180 /// <param name="tableName"></param>
181 /// <returns></returns>
182 public DataSet ExecuteDataSet(string commandText, DbParameter[] op, string tableName)
183 {
184 DbDataAdapter Adapter = provider.CreateDataAdapter();
185 Adapter.SelectCommand = BuildInitCommand(commandText, op);
186 DataSet Ds = new DataSet();
187 Connection.Open();
188 Adapter.Fill(Ds, tableName);
189 Connection.Close();
190 return Ds;
191 }
192 /// <summary>
193 /// 执行数据库事务
194 /// </summary>
195 /// <param name="sqlStringList"></param>
196 public void ExecuteTransaction(System.Collections.ArrayList sqlStringList)
197 {
198 Connection.Open();
199 DbTransaction tran = Connection.BeginTransaction();
200 DbCommand Command = BuildInitCommand();
201 Command.Transaction = tran;
202 try
203 {
204 for (int i = 0; i < sqlStringList.Count; i++)
205 {
206 Command.CommandText = sqlStringList[i].ToString();
207 Command.ExecuteNonQuery();
208 }
209 tran.Commit();
210 Connection.Close();
211 }
212 catch (System.Data.Common.DbException E)
213 {
214 tran.Rollback();
215 throw new Exception(E.Message);
216 }
217 }
218 }
一个小问题：事务处理函数只能执行纯SQL语句，如果想同时执行纯sql语句和带参的SQL语句，则不行。该如何改。请高手们指教
精彩图集
精彩文章