1
using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Configuration;
5using System.Data;
6using System.Data.SqlClient;
7using System.Data.OleDb;
8using System.Data.Odbc;
9using System.Data.Common;
10//该源码下载自www.51aspx.com(５１aｓpｘ．ｃｏｍ)
11
12namespace GustBook.DBUtility
13
...{
14
/**//// <summary>
15
/// 用来创建DataBase对象的静态类
16
/// </summary>
17 public static class DBHelper
18 ...{
19 public static DataBase CreateData(string DataNameInConfigfile)
20 ...{
21 string pn = ConfigurationManager.ConnectionStrings[DataNameInConfigfile].ProviderName; //读取配置文件
22 string cs = ConfigurationManager.ConnectionStrings[DataNameInConfigfile].ConnectionString;
23
24 //下面判断数据库类型并创建相应的对象
25
26 if (pn.ToUpper().Contains("OLEDB"))
27 ...{
28 OleDbDataAdapter oledbda = new OleDbDataAdapter();
29 oledbda.SelectCommand = new OleDbCommand();
30 oledbda.SelectCommand.Connection = new OleDbConnection(cs);
31 return new DataBase(oledbda);
32 }
33 if (pn.ToUpper().Contains("SQL"))
34 ...{
35 SqlDataAdapter sqlda = new SqlDataAdapter();
36 sqlda.SelectCommand = new SqlCommand();
37 sqlda.SelectCommand.Connection = new SqlConnection(cs);
38 return new DataBase(sqlda);
39 }
40 if (pn.ToUpper().Contains("ODBC"))
41 ...{
42 OdbcDataAdapter odbcda = new OdbcDataAdapter();
43 odbcda.SelectCommand = new OdbcCommand();
44 odbcda.SelectCommand.Connection = new OdbcConnection(cs);
45 return new DataBase(odbcda);
46 }
47 return null;
48 }
49
50 public static DataBase CreateData(string ConnectionString, string ProviderName)
51 ...{
52 //以下判断数据库类型并创建相应的对象
53 if (ProviderName.ToUpper().Contains("OLEDB"))
54 ...{
55 OleDbDataAdapter oledbda = new OleDbDataAdapter();
56 oledbda.SelectCommand = new OleDbCommand();
57 oledbda.SelectCommand.Connection = new OleDbConnection(ConnectionString);
58 return new DataBase(oledbda);
59 }
60 if (ProviderName.ToUpper().Contains("SQL"))
61 ...{
62 SqlDataAdapter sqlda = new SqlDataAdapter();
63 sqlda.SelectCommand = new SqlCommand();
64 sqlda.SelectCommand.Connection = new SqlConnection(ConnectionString);
65 return new DataBase(sqlda);
66 }
67 if (ProviderName.ToUpper().Contains("ODBC"))
68 ...{
69 OdbcDataAdapter odbcda = new OdbcDataAdapter();
70 odbcda.SelectCommand = new OdbcCommand();
71 odbcda.SelectCommand.Connection = new OdbcConnection(ConnectionString);
72 return new DataBase(odbcda);
73 }
74 return null;
75 }
76 }
77
78
79
80 /**//// <summary>
81 /// 执行主要操作的类
82 /// </summary>
83 public class DataBase
84 ...{
85 private DbDataAdapter mDataAdapter; //指向传入的DbDataAdapter的引用
86 private DbCommand mCommand; //指向传入的DbDataAdapter.SelectCommand的引用
87
88 /**//// <summary>
89 /// 构造函数
90 /// </summary>
91 /// <param name="DDA">获得一个实例化了的DbDataAdapter的派生类</param>
92 public DataBase(DbDataAdapter DDA)
93 ...{
94 mDataAdapter = DDA;
95 mCommand = DDA.SelectCommand;
96 }
97
98 /**//// <summary>
99 /// 判断一个stirng是否为储存过程
100 /// </summary>
101 /// <param name="SQLText">目标string</param>
102 /// <returns>返回是否为储存过程的调用</returns>
103 private bool IsProcedure(string SQLText)
104 ...{
105 if (SQLText.Contains(" "))
106 ...{
107 string[] tmp;
108 tmp = SQLText.Split(' ');
109 if (tmp[0].ToUpper() == "EXECUTE" || tmp[0].ToUpper() == "EXEC")
110 ...{
111 return true;
112 }
113 else
114 ...{
115 return false;
116 }
117 }
118 else
119 ...{
120 return true;
121 }
122 }
123
124 /**//// <summary>
125 /// 执行数据库命令返回受影响的行数
126 /// </summary>
127 /// <param name="SQLText">SQL语句</param>
128 /// <returns>受影响的行数</returns>
129 public int ExecuteNonQuery(string SQLText)
130 ...{
131 if (IsProcedure(SQLText)) ...{ mCommand.CommandType = CommandType.StoredProcedure; } else ...{ mCommand.CommandType = CommandType.Text; }
132 mCommand.CommandText = SQLText;
133 try
134 ...{
135 mCommand.Connection.Open();
136 return mCommand.ExecuteNonQuery();
137 }
138 finally
139 ...{
140 mCommand.Connection.Close();
141 ClearParameters();
142 }
143 }
144
145 /**//// <summary>
146 /// 执行数据库命令返回DataReader
147 /// </summary>
148 /// <param name="SQLText">SQL命令</param>
149 /// <returns>返回DataReader</returns>
150 public DbDataReader ExecuteReader(string SQLText)
151 ...{
152 if (IsProcedure(SQLText)) ...{ mCommand.CommandType = CommandType.StoredProcedure; } else ...{ mCommand.CommandType = CommandType.Text; }
153 mCommand.CommandText = SQLText;
154 mCommand.Connection.Open();
155 try
156 ...{
157 return mCommand.ExecuteReader(CommandBehavior.CloseConnection);
158 }
159 finally
160 ...{ ClearParameters(); }
161 }
162
163 /**//// <summary>
164 /// 执行统计的方法
165 /// </summary>
166 /// <param name="SQLText">SQL命令</param>
167 /// <returns>返回object对象代表统计数据或者结果的第一列第一行</returns>
168 public object ExecuteScalar(string SQLText)
169 ...{
170 if (IsProcedure(SQLText)) ...{ mCommand.CommandType = CommandType.StoredProcedure; } else ...{ mCommand.CommandType = CommandType.Text; }
171 mCommand.CommandText = SQLText;
172 try
173 ...{
174 mCommand.Connection.Open();
175 return mCommand.ExecuteScalar();
176 }
177 finally
178 ...{
179 mCommand.Connection.Close();
180 ClearParameters();
181 }
182 }
183
184 /**//// <summary>
185 /// 执行查询返回填充的DataSet对象
186 /// </summary>
187 /// <param name="SQLText">SQl命令</param>
188 /// <param name="VisualTableName">虚拟表名</param>
189 /// <param name="StartIndex">制定返回多少行以后的数据</param>
190 /// <param name="Count">制定总共返回多少行</param>
191 /// <returns>返回按要求填充了的DataSet</returns>
192 public DataSet ExecuteDataSet(string SQLText, string VisualTableName, int StartIndex, int Count)
193 ...{
194 DataSet ds = new DataSet();
195 if (IsProcedure(SQLText)) ...{ mCommand.CommandType = CommandType.StoredProcedure; } else ...{ mCommand.CommandType = CommandType.Text; }
196 mCommand.CommandText = SQLText;
197 try
198 ...{
199 mCommand.Connection.Open();
200 mDataAdapter.Fill(ds, StartIndex, Count, VisualTableName);
201 return ds;
202 }
203 finally
204 ...{
205 mCommand.Connection.Close();
206 ClearParameters();
207 }
208 }
209 //下面为重载调用,不包含实际代码
210 public DataSet ExecuteDataSet(string SQLText, int StartIndex, int Count) ...{ return ExecuteDataSet(SQLText, "Table1", StartIndex, Count); }
211 public DataSet ExecuteDataSet(string SQLText, string VisualTableName) ...{ return ExecuteDataSet(SQLText, VisualTableName, 0, 0); }
212 public DataSet ExecuteDataSet(string SQLText) ...{ return ExecuteDataSet(SQLText, "Table1", 0, 0); }
213
214 /**//// <summary>
215 /// 添加一个参数
216 /// </summary>
217 /// <param name="ParameterName">参数的名称</param>
218 /// <param name="Value">参数的值</param>
219 /// <param name="Type">参数值的类型</param>
220 /// <param name="Size">参数值的大小</param>
221 /// <param name="Direction">参数的返回类型</param>
222 /// <returns>返回添加后的参数对象DbParameter</returns>
223 public DbParameter AddParameter(string ParameterName, object Value, DbType Type, int Size, ParameterDirection Direction)
224 ...{
225 DbParameter dbp = mCommand.CreateParameter();
226 dbp.ParameterName = ParameterName;
227 dbp.Value = Value;
228 dbp.DbType = Type;
229 if (Size != 0) ...{ dbp.Size = Size; }
230 dbp.Direction = Direction;
231 mCommand.Parameters.Add(dbp);
232 return dbp;
233 }
234 //下面为重载调用,不包含实际代码
235 public DbParameter AddParameter(string ParameterName, object Value, DbType Type, int Size) ...{ return AddParameter(ParameterName, Value, Type, Size, ParameterDirection.Input); }
236 public DbParameter AddParameter(string ParameterName, object Value, ParameterDirection Direction) ...{ return AddParameter(ParameterName, Value, DbType.Object, 0, Direction); }
237 public DbParameter AddParameter(string ParameterName, object Value) ...{ return AddParameter(ParameterName, Value, DbType.Object, 0, ParameterDirection.Input); }
238 public DbParameter AddParameter(string ParameterName, string Value) ...{ return AddParameter(ParameterName, Value, DbType.String, 0, ParameterDirection.Input); }
239 public DbParameter AddParameter(string ParameterName, Int32 Value) ...{ return AddParameter(ParameterName, Value, DbType.Int32, 0, ParameterDirection.Input); }
240 public DbParameter AddParameter(string ParameterName, Int16 Value) ...{ return AddParameter(ParameterName, Value, DbType.Int16, 0, ParameterDirection.Input); }
241 public DbParameter AddParameter(string ParameterName, Boolean Value) ...{ return AddParameter(ParameterName, Value, DbType.Boolean, 0, ParameterDirection.Input); }
242
MVCGuestBook/DBUtility/DBHelper.cs，打开代码结构图