1
#define DEBUG
2
3using System;
4using System.ComponentModel;
5using System.Collections;
6using System.Diagnostics;
7using System.Data;
8using System.Data.SqlClient;
9using System.Configuration;
10
11using HRManager.DataAccessHelper;
12using HRManager.CommonComponent;
13
14namespace HRManager.DataAccessLayer
15
...{
16
/**//// <summary>
17
/// 类，用于数据访问的类。
18
/// </summary>
19 public class Database : IDisposable
20 ...{
21 /**//// <summary>
22 /// 保护变量，数据库连接。
23 /// </summary>
24 protected SqlConnection Connection;
25
26 /**//// <summary>
27 /// 保护变量，数据库连接串。
28 /// </summary>
29 protected String ConnectionString;
30
31 /**//// <summary>
32 /// 构造函数。
33 /// </summary>
34 /// <param name="DatabaseConnectionString">数据库连接串</param>
35 public Database()
36 ...{
37 ConnectionString = ConfigurationManager.AppSettings["DBConnectionString"];
38 }
39
40 /**//// <summary>
41 /// 析构函数，释放非托管资源
42 /// </summary>
43 ~Database()
44 ...{
45 try
46 ...{
47 if (Connection != null)
48 Connection.Close();
49 }
50 catch(Exception e)
51 ...{
52 myEventLog.Log.Debug(e.Message);
53 }
54 try
55 ...{
56 Dispose();
57 }
58 catch...{}
59 }
60
61 /**//// <summary>
62 /// 保护方法，打开数据库连接。
63 /// </summary>
64 protected void Open()
65 ...{
66 if (Connection == null)
67 ...{
68 try
69 ...{
70 Connection = new SqlConnection(ConnectionString);
71 }
72 catch(Exception e)
73 ...{
74 myEventLog.Log.Debug(e.Message);
75 }
76 }
77 if (Connection.State.Equals(ConnectionState.Closed))
78 ...{
79 try
80 ...{
81 Connection.Open();
82 }
83 catch(Exception e)
84 ...{
85 myEventLog.Log.Debug(e.Message);
86 }
87 }
88 }
89
90 /**//// <summary>
91 /// 公有方法，关闭数据库连接。
92 /// </summary>
93 public void Close()
94 ...{
95 try
96 ...{
97 if (Connection != null)
98 Connection.Close();
99 }
100 catch(Exception e)
101 ...{
102 myEventLog.Log.Debug(e.Message);
103 }
104 }
105
106 /**//// <summary>
107 /// 公有方法，释放资源。
108 /// </summary>
109 public void Dispose()
110 ...{
111 // 确保连接被关闭
112 try
113 ...{
114 if (Connection != null)
115 ...{
116 Connection.Dispose();
117 Connection = null;
118 }
119 }
120 catch(Exception e)
121 ...{
122 myEventLog.Log.Debug(e.Message);
123 }
124 }
125
126 /**//// <summary>
127 /// 公有方法，获取数据，返回一个SqlDataReader （调用后主意调用SqlDataReader.Close()）。
128 /// </summary>
129 /// <param name="SqlString">Sql语句</param>
130 /// <returns>SqlDataReader</returns>
131 public SqlDataReader GetDataReader(String SqlString)
132 ...{
133 Open();
134 try
135 ...{
136 SqlCommand cmd = new SqlCommand(SqlString,Connection);
137 return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
138#if DEBUG
139 myEventLog.Log.Info(SqlString);
140#endif
141 }
142 catch(Exception e)
143 ...{
144 myEventLog.Log.Debug("数据查询（GetDataReader）失败，SqlString=" + SqlString + ",系统异常信息：" + e.Message);
145 return null;
146 }
147 }
148
149 /**//// <summary>
150 /// 公有方法，获取数据，返回一个DataSet。
151 /// </summary>
152 /// <param name="SqlString">Sql语句</param>
153 /// <returns>DataSet</returns>
154 public DataSet GetDataSet(String SqlString)
155 ...{
156 DataSet dataset = new DataSet();
157 Open();
158 try
159 ...{
160 SqlDataAdapter adapter = new SqlDataAdapter(SqlString,Connection);
161 adapter.Fill(dataset);
162#if DEBUG
163 myEventLog.Log.Info(SqlString);
164#endif
165 }
166 catch(Exception e)
167 ...{
168 myEventLog.Log.Debug("数据查询（GetDataSet）失败，SqlString=" + SqlString + ",系统异常信息：" + e.Message);
169 }
170 finally
171 ...{
172 Close();
173 }
174 return dataset;
175 }
176
177 /**//// <summary>
178 /// 公有方法，获取数据，返回一个DataTable。
179 /// </summary>
180 /// <param name="SqlString">Sql语句</param>
181 /// <returns>DataTable</returns>
182 public DataTable GetDataTable(String SqlString)
183 ...{
184 DataSet dataset = GetDataSet(SqlString);
185 dataset.CaseSensitive = false;
186 return dataset.Tables[0];
187 }
188
189 /**//// <summary>
190 /// 公有方法，获取数据，返回一个DataRow。
191 /// </summary>
192 /// <param name="SqlString">Sql语句</param>
193 /// <returns>DataRow</returns>
194 public DataRow GetDataRow(String SqlString)
195 ...{
196 DataSet dataset = GetDataSet(SqlString);
197 dataset.CaseSensitive = false;
198 if (dataset.Tables[0].Rows.Count>0)
199 ...{
200 return dataset.Tables[0].Rows[0];
201 }
202 else
203 ...{
204 return null;
205 }
206 }
207
208 /**//// <summary>
209 /// 公有方法，执行Sql语句。
210 /// </summary>
211 /// <param name="SqlString">Sql语句</param>
212 /// <returns>对Update、Insert、Delete为影响到的行数，其他情况为-1</returns>
213 public int ExecuteSQL(String SqlString)
214 ...{
215 int count = -1;
216 Open();
217 try
218 ...{
219 SqlCommand cmd = new SqlCommand(SqlString,Connection);
220 count = cmd.ExecuteNonQuery();
221#if DEBUG
222 myEventLog.Log.Info(SqlString);
223#endif
224 }
225 catch(Exception e)
226 ...{
227 myEventLog.Log.Debug("执行数据操作（ExecuteSQL）失败，SqlString=" + SqlString + ",系统异常信息：" + e.Message);
228 count = -1;
229 }
230 finally
231 ...{
232 Close();
233 }
234 return count;
235 }
236
237 /**//// <summary>
238 /// 公有方法，执行一组Sql语句。
239 /// </summary>
240 /// <param name="SqlStrings">Sql语句组</param>
241 /// <returns>是否成功</returns>
242 public bool ExecuteSQL(String[] SqlStrings)
243 ...{
244 bool success = true;
245 Open();
246 SqlCommand cmd = new SqlCommand();
247 SqlTransaction trans = Connection.BeginTransaction();
248 cmd.Connection = Connection;
249 cmd.Transaction = trans;
250
251 int i=0;
252 try
253 ...{
254 foreach (String str in SqlStrings)
255 ...{
256 cmd.CommandText = str;
257 cmd.ExecuteNonQuery();
258 i++;
259#if DEBUG
260 myEventLog.Log.Info(str);
261#endif
262 }
263 trans.Commit();
264 }
265 catch(Exception e)
266 ...{
267 myEventLog.Log.Debug("执行数据操作（ExecuteSQL）失败，SqlString=" + SqlStrings[i] + ",系统异常信息：" + e.Message);
268 success = false;
269 trans.Rollback();
270 }
271 finally
272 ...{
273 Close();
274 }
275 return success;
276 }
277
278 /**//// <summary>
279 /// 公有方法，在一个数据表中插入一条记录。
280 /// </summary>
281 /// <param name="TableName">表名</param>
282 /// <param name="Cols">哈西表，键值为字段名，值为字段值</param>
283 /// <returns>是否成功</returns>
284 public bool Insert(String TableName,Hashtable Cols)
285 ...{
286 int Count = 0;
287
288 if (Cols.Count<=0)
289 ...{
290 return true;
291 }
292
293 String Fields = " (";
294 String Values = " Values(";
295 foreach(DictionaryEntry item in Cols)
296 ...{
297 if (Count!=0)
298 ...{
299 Fields += ",";
300 Values += ",";
301 }
302 Fields += "["+item.Key.ToString()+"]";
303 Values += item.Value.ToString();
304 Count ++;
305 }
306 Fields += ")";
307 Values += ")";
308
309 String SqlString = "Insert into "+TableName+Fields+Values;
310
311 String[] Sqls = ...{SqlString};
312 return ExecuteSQL(Sqls);
313 }
314
315
316 /**//// <summary>
317 /// 公有方法，更新一个数据表。
318 /// </summary>
319 /// <param name="TableName">表名</param>
320 /// <param name="Cols">哈西表，键值为字段名，值为字段值</param>
321 /// <param name="Where">Where子句</param>
322 /// <returns>是否成功</returns>
323 public bool Update(String TableName,Hashtable Cols,String Where)
324 ...{
325 int Count = 0;
326 if (Cols.Count<=0)
327 ...{
328 return true;
329 }
330 String Fields = " ";
331 foreach(DictionaryEntry item in Cols)
332 ...{
333 if (Count!=0)
334 ...{
335 Fields += ",";
336 }
337 Fields += "["+item.Key.ToString()+"]";
338 Fields += "=";
339 Fields += item.Value.ToString();
340 Count ++;
341 }
342 Fields += " ";
343
344 String SqlString = "Update "+TableName+" Set "+Fields+Where;
345
346 String[] Sqls = ...{SqlString};
347 return ExecuteSQL(Sqls);
348 }
349 }
350
}
51Job/App_Code/DataAccessLayeer/Database.cs[7K，2009-6-12 11:31:08]，打开代码结构图
打包下载该项目完整源码：无忧之源招聘系统及Log4Net源码