在ASPNET里，我们需要和数据库进行交互。我们必须编写一个通用的类库。而且我们需要写一个和数据库无关的程序。
于是我们有了DBHelper。
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Web;
5: using mon;
6: using System.Configuration;
7: using System.Data;
8: using System.Globalization;
9: using System.Web.UI.WebControls;
10:
11: namespace ASPNETTeach5
12: {
13:/// <summary>
14:/// 数据库操作命令委托
15:/// </summary>
16:/// <param name="dbCommand"></param>
17:/// <returns></returns>
18:public delegate object CommandDelegate(DbCommand dbCommand);
19:/// <summary>
20:/// DbDataReader命令委托
21:/// </summary>
22:/// <param name="dbDataReader"></param>
23:public delegate void DBDataReaderDelegate(DbDataReader dbDataReader);
24:public class DbHelper
25:{
26:private static DbHelper instance;
27:private ConnectionStringSettings connectionStringSettings = null;
28:private DbProviderFactory dbProviderFactory = null;
29:
30:public static string DataConnenctionString = "con";
31:
32:public DbHelper() {
33: connectionStringSettings = ConfigurationManager.ConnectionStrings[DataConnenctionString];
34:
35: if (connectionStringSettings != null) {
36: if (!string.IsNullOrEmpty(connectionStringSettings.ConnectionString) && !string.IsNullOrEmpty(connectionStringSettings.ProviderName))
37: {
38: dbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
39: }
40: }
41:}
42:
43:public DbHelper(string dbConnectionSettingName) {
44: if (string.IsNullOrEmpty(dbConnectionSettingName)) {
45: dbConnectionSettingName = DataConnenctionString;
46: }
47: connectionStringSettings = ConfigurationManager.ConnectionStrings[DataConnenctionString];
48: if (connectionStringSettings != null)
49: {
50: if (!string.IsNullOrEmpty(connectionStringSettings.ConnectionString) && !string.IsNullOrEmpty(connectionStringSettings.ProviderName))
51: {
52: dbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
53: }
54: }
55:}
56:
57:/// <summary>
58:/// 获取Dbhelper对象
59:/// </summary>
60:public static DbHelper Instance {
61: get {
62: if (instance == null)
63: instance = new DbHelper();
64: return instance;
65: }
66:}
67:/// <summary>
68:/// 获取链接串
69:/// </summary>
70:public string ConnectString {
71: get {
72: return connectionStringSettings.ConnectionString;
73: }
74:}
75:
76:/// <summary>
77:/// 提供者字符串
78:/// </summary>
79:public string ProviderString {
80: get { return connectionStringSettings.ProviderName; }
81:}
82:/// <summary>
83:/// 获取工厂
84:/// </summary>
85:public DbProviderFactory ProviderFactory { get { return dbProviderFactory; } }
86:
87:
88:
89:/// <summary>
90:/// 创建链接
91:/// </summary>
92:/// <returns></returns>
93:public DbConnection CreateConnection() {
94: if (dbProviderFactory == null)
95: return null;
96: else
97: {
98: DbConnection dbConnection = dbProviderFactory.CreateConnection();
99:
100: dbConnection.ConnectionString = connectionStringSettings.ConnectionString;
101: return dbConnection;
102: }
103:}
104:
105:/// <summary>
106:/// 执行非查询语句
107:/// </summary>
108:/// <param name="commandType"></param>
109:/// <param name="sql"></param>
110:/// <returns></returns>
111:public int ExecuteNonQuery(CommandType commandType,string sql) {
112: CommandDelegate cd = delegate(DbCommand cmd)
113: {
114: try
115: {
116: return cmd.ExecuteNonQuery();
117: }
118: catch
119: {
120: return -1;
121: }
122: };
123: return (int)ExecuteCmdCallback(commandType, sql, cd);
124:}
125:
126:/// <summary>
127:/// 带参数的非查询语句
128:/// </summary>
129:/// <param name="commandType"></param>
130:/// <param name="sql"></param>
131:/// <param name="pars"></param>
132:/// <returns></returns>
133:public int ExecuteNonQuery(CommandType commandType,string sql,DbParameter[] pars){
134: CommandDelegate cd = delegate(DbCommand cmd)
135: {
136: return cmd.ExecuteNonQuery();
137: };
138: return (int)ExecuteCmdCallback(commandType, sql, cd, pars);
139:}
140:
141:/// <summary>
142:/// 执行带参数与委托命令的查询语句，并返回相关的委托命令
143:/// </summary>
144:/// <param name="commandType">命令类型</param>
145:/// <param name="sql">SQL语句</param>
146:/// <param name="cd">Cmd委托</param>
147:/// <param name="pars">参数集合</param>
148:/// <returns></returns>
149:private object ExecuteCmdCallback(CommandType commandType, string sql, CommandDelegate cd, DbParameter[] pars) {
150: using (DbConnection dbCnn = CreateConnection()) {
151: using (DbCommand cmd = dbProviderFactory.CreateCommand()) {
152: mandType = commandType;
153: mandText = sql;
154: cmd.Connection = dbCnn;
155: cmd.Parameters.AddRange(pars);
156: dbCnn.Open();
157: return cd(cmd);
158:
159: }
160: }
161:}
162:/// <summary>
163:/// 执行带参数与委托命令的查询语句，并返回相关的委托命令
164:/// </summary>
165:/// <param name="commandType">命令类型</param>
166:/// <param name="sql">SQL语句</param>
167:/// <param name="cd">Cmd委托</param>
168:/// <returns></returns>
169:private object ExecuteCmdCallback(CommandType commandType, string sql, CommandDelegate commandDelegate)
170:{
171: using (DbConnection dbCon = CreateConnection())
172: {
173: using (DbCommand cmd = dbProviderFactory.CreateCommand())
174: {
175: cmd.Connection = dbCon;
176: mandType = commandType;
177: mandText = sql;
178: dbCon.Open();
179: return commandDelegate(cmd);
180: }
181: }
182:}
183:private object ExecuteCmdCallback(CommandType commandType, CommandDelegate commandDelegate)
184:{
185: using (DbConnection dbCon = CreateConnection())
186: {
187: using (DbCommand cmd = dbProviderFactory.CreateCommand())
188: {
189: cmd.Connection = dbCon;
190: mandType = commandType;
191: dbCon.Open();
192: return commandDelegate(cmd);
193: }
194: }
195:}
196:public bool GetReadData(string sql, DBDataReaderDelegate readdelegate)
197:{
198: bool result = false;
199: CommandDelegate cd = delegate(DbCommand cmd)
200: {
201: using (DbDataReader datareader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
202: {
203:
204: readdelegate(datareader);
205: return true;
206: }
207: };
208: result = (bool)ExecuteCmdCallback(CommandType.Text, sql, cd);
209: return result;
210:}
211:
212:
213:public DataTable GetDataTable(CommandType commandType, string sql) {
214:
215: CommandDelegate cd = delegate(DbCommand cmd)
216: {
217: using (DbDataReader dr = cmd.ExecuteReader())
218: {
219: DataTable dt = new DataTable();
220: dt.Locale = CultureInfo.InvariantCulture;
221: dt.Load(dr);
222: return dt;
223: }
224: };
225: return ExecuteCmdCallback(CommandType.Text, sql, cd) as DataTable;
226:}
227:
228:/// <summary>
229:/// 获取第一行第一列数据
230:/// </summary>
231:/// <param name="sql"></param>
232:/// <returns></returns>
233:public string GetScalar(string sql) {
234: CommandDelegate cd = delegate(DbCommand cmd)
235: {
236: return cmd.ExecuteScalar();
237: };
238: object value = ExecuteCmdCallback(CommandType.Text, sql, cd);
239: if (value == null)
240: return "";
241: return value.ToString();
242:}
243:
244:
245:/// <summary>
246:/// 获取第一行第一列数据
247:/// </summary>
248:/// <param name="sql"></param>
249:/// <returns></returns>
250:public object GetObject(string sql)
251:{
252: CommandDelegate cd = delegate(DbCommand cmd)
253: {
254: return cmd.ExecuteScalar();
255: };
256: return ExecuteCmdCallback(CommandType.Text, sql, cd);
257:
258:}
259:
260:public DataSet GetDataSet(CommandType commandType, string sql)
261:{
262: CommandDelegate cd = delegate(DbCommand cmd)
263: {
264: using (DbDataAdapter da = dbProviderFactory.CreateDataAdapter())
265: {
266: DataSet ds = new DataSet();
267: ds.Locale = CultureInfo.InvariantCulture;
268: da.SelectCommand = cmd;
269: da.Fill(ds);
270: return ds;
271: }
272: };
273: return (DataSet)ExecuteCmdCallback(CommandType.Text, sql, cd);
274:}
275:}
276: }
我们如何使用呢。首先我们需要在配置文件里，配置数据库连接
1: <configuration>
2: <connectionStrings>
3:<add name="con" connectionString="server=.;database=BookManagement;uid=sa;pwd=sa;pooling=true" providerName="System.Data.SqlClient"/>
4: </connectionStrings>
5:<system.web>
6: <compilation debug="true" targetFramework="4.0" />
7:</system.web>
8: </configuration>
使用方式
1: DbHelper.Instance.GetDataTable(mandType.Text, "select * from books");
我们后面的内容需要这个dbhelper来进行。
