Db - DataAccess
时间：2021-07-01 10:21:17
帮助过：9人阅读
/* Jonney Create 2013-8-12 */
2
3 /*using System.Data.OracleClient;*/
4 /*using System.Data.SQLite;*/
5 /*using MySql.Data.MySqlClient;*/
6 using System.Data.SqlClient;
7 using System;
8 using System.Collections.Generic;
9 using System.ComponentModel;
10 using System.Data;
11 using System.Reflection;
12
13 namespace Dade.DataCenter.CutBoard.Dal
14 {
15 public class SqlDataAccess : DataAccess
16 {
17 protected SqlDataAccess()
18 {
19 _databaseType =
DatabaseType.SqlServer;
20 _connString =
ConnStr;
21 _conn =
new SqlConnection(_connString);
22 _cmd =
_conn.CreateCommand();
23 _dataAdapter =
new SqlDataAdapter();
24 _dataAdapter.SelectCommand =
_cmd;
25 }
26
27 public static string ConnStr {
get;
set; }
28
29 public static DataAccess Instance
30 {
31 get {
return new SqlDataAccess(); }
32 }
33 }
34
35 public class OracleDataAccess : DataAccess
36 {
37 protected OracleDataAccess()
38 {
39 /*_databaseType = DatabaseType.Oracle;
40 _connString = ConnStr;
41 _conn = new OracleConnection(_connString);
42 _cmd = _conn.CreateCommand();
43 _dataAdapter = new OracleDataAdapter();
44 _dataAdapter.SelectCommand = _cmd;
45 _parameterChar = ":";*/
46 }
47
48 public static string ConnStr {
get;
set; }
49 public static DataAccess Instance
50 {
51 get {
return new OracleDataAccess(); }
52 }
53 }
54
55 public class SqliteDataAccess : DataAccess
56 {
57 protected SqliteDataAccess()
58 {
59 /*_databaseType = DatabaseType.Sqlite;
60 _connString = ConnStr;
61 _conn = new SQLiteConnection(_connString);
62 _cmd = _conn.CreateCommand();
63 _dataAdapter = new SQLiteDataAdapter();
64 _dataAdapter.SelectCommand = _cmd;*/
65 }
66
67 public static string ConnStr {
get;
set; }
68 public static DataAccess Instance
69 {
70 get {
return new SqliteDataAccess(); }
71 }
72 }
73
74 public class MySqlDataAccess : DataAccess
75 {
76 protected MySqlDataAccess()
77 {
78 /*_databaseType = DatabaseType.Mysql;
79 _connString = ConnStr;
80 _conn = new MySqlConnection(_connString);
81 _cmd = _conn.CreateCommand();
82 _dataAdapter = new MySqlDataAdapter();
83 _dataAdapter.SelectCommand = _cmd;*/
84 }
85
86 public static string ConnStr {
get;
set; }
87 public static DataAccess Instance
88 {
89 get {
return new MySqlDataAccess(); }
90 }
91
92 }
93
94 /// <summary>
95 /// 数据库类型
96 /// </summary>
97 public enum DatabaseType
98 {
99 SqlServer, Oracle, Mysql, Sqlite, PostgreSql, SqlCe, Access, Firebird
100 }
101
102 /// <summary>
103 /// 通用数据库访问抽象类
104 /// </summary>
105 public abstract class DataAccess : IDisposable
106 {
107 protected DatabaseType _databaseType;
108 protected string _connString;
109 protected bool _useTransaction;
110 protected IDbConnection _conn;
111 protected IDbTransaction _transaction;
112 protected IDbCommand _cmd;
113 protected IDbDataAdapter _dataAdapter;
114 protected string _parameterChar =
"@";
115
116 /// <summary>
117 /// 根据数据库类型返回参数
118 /// </summary>
119 /// <param name="parameterName"></param>
120 /// <param name="value"></param>
121 /// <returns></returns>
122 public IDbDataParameter GetParameter(
string parameterName,
object value)
123 {
124 try
125 {
126 IDbDataParameter parameter =
null;
127 switch (_databaseType)
128 {
129 case DatabaseType.SqlServer:
130 parameter =
new SqlParameter(parameterName, value ??
DBNull.Value);
131 break;
132 /*case DatabaseType.Mysql:
133 parameter = new MySqlParameter(parameterName, value ?? DBNull.Value);
134 break;*/
135 /*case DatabaseType.Oracle:
136 parameter = new OracleParameter(parameterName, value ?? DBNull.Value);
137 break;*/
138 /*case DatabaseType.Sqlite:
139 parameter = new SQLiteParameter(parameterName, value ?? DBNull.Value);
140 break;*/
141 default:
142 throw new Exception(
"DataAccess-->DatabaseType-->GetParameter()-->Not Implement");
143 }
144 return parameter;
145 }
146 catch
147 {
148 DoCatch();
149 throw;
150 }
151 }
152
153 /// <summary>
154 /// 异常处理
155 /// </summary>
156 protected void DoCatch()
157 {
158 if (_useTransaction && _transaction!=
null && _transaction.Connection !=
null)
159 {
160 _transaction.Rollback();
161 _transaction.Dispose();
162 _transaction =
null;
163 _useTransaction =
false;
164 }
165 Close();
166 }
167
168 /// <summary>
169 /// 获取是否使用事务
170 /// </summary>
171 public bool UseTransaction
172 {
173 get {
return _useTransaction; }
174 }
175
176 /// <summary>
177 /// 获取当前正在执行的事务
178 /// </summary>
179 public IDbTransaction Transaction
180 {
181 get {
return _transaction; }
182 }
183
184 /// <summary>
185 /// 获取或设置连接字符串
186 /// </summary>
187 public string ConnString
188 {
189 get {
return _connString; }
190 set
191 {
192 _connString =
value;
193 if (_conn !=
null && _conn.ConnectionString !=
value)
194 {
195 _conn.ConnectionString =
_connString;
196 }
197 }
198 }
199
200 /// <summary>
201 /// Ping服务器IP
202 /// </summary>
203 /// <param name="ip">目标主机IP</param>
204 /// <returns></returns>
205 public static bool Ping(
string ip)
206 {
207 try
208 {
209 var p =
new System.Net.NetworkInformation.Ping();
210 var options =
new System.Net.NetworkInformation.PingOptions { DontFragment =
true };
211 byte[] buffer = System.Text.Encoding.ASCII.GetBytes(
"Ping..");
212 const int timeout =
1000;
// Timeout 时间，单位：毫秒
213 System.Net.NetworkInformation.PingReply reply =
p.Send(ip, timeout, buffer, options);
214 return reply !=
null && reply.Status ==
System.Net.NetworkInformation.IPStatus.Success;
215 }
216 catch
217 {
218 return false;
219 }
220 }
221
222 /// <summary>
223 /// 测试连接字符串
224 /// </summary>
225 /// <returns></returns>
226 public bool TestConnect()
227 {
228 try
229 {
230 Open();
231 Close();
232 return true;
233 }
234 catch
235 {
236 DoCatch();
237 return false;
238 }
239 }
240
241 /// <summary>
242 /// 是否存在
243 /// </summary>
244 /// <param name="sql"></param>
245 /// <param name="parameters"></param>
246 /// <returns></returns>
247 public bool Exists(
string sql,
params IDbDataParameter[] parameters)
248 {
249 var result =
GetSingle(sql, parameters);
250 if (result!=
null && result!=DBNull.Value && result.ToString()!=
"0")
251 {
252 return true;
253 }
254 return false;
255 }
256
257 /// <summary>
258 /// 查询
259 /// </summary>
260 /// <param name="sql">查询语句</param>
261 /// <returns></returns>
262 public DataSet Query(
string sql)
263 {
264 try
265 {
266 DataSet ds =
new DataSet();
267 _cmd.Connection =
_conn;
268 _cmd.CommandType =
CommandType.Text;
269 _cmd.CommandText =
sql;
270 _cmd.Parameters.Clear();
271 _dataAdapter.SelectCommand =
_cmd;
272
273 if (_useTransaction)
274 {
275 _dataAdapter.Fill(ds);
276 }
277 else
278 {
279 Open();
280 _dataAdapter.Fill(ds);
281 Close();
282 }
283 return ds;
284 }
285 catch
286 {
287 DoCatch();
288 throw;
289 }
290 }
291
292 /// <summary>
293 /// 查询
294 /// </summary>
295 /// <param name="type">参数类型</param>
296 /// <param name="sql">查询语句</param>
297 /// <param name="parameters">参数列表</param>
298 /// <returns></returns>
299 public DataSet Query(CommandType type,
string sql,
params IDbDataParameter[] parameters)
300 {
301 try
302 {
303 DataSet ds =
new DataSet();
304 _cmd.Connection =
_conn;
305 _cmd.CommandType =
type;
306 _cmd.CommandText =
sql;
307 _dataAdapter.SelectCommand =
_cmd;
308 ReSetParameters(_cmd, parameters);
309 if (_useTransaction)
310 {
311 _dataAdapter.Fill(ds);
312 }
313 else
314 {
315 Open();
316 _dataAdapter.Fill(ds);
317 Close();
318 }
319 return ds;
320 }
321 catch
322 {
323 DoCatch();
324 throw;
325 }
326 }
327
328 /// <summary>
329 /// 查询
330 /// </summary>
331 /// <param name="sql">查询语句</param>
332 /// <param name="parameters">参数列表</param>
333 /// <returns></returns>
334 public DataSet Query(
string sql,
params IDbDataParameter[] parameters)
335 {
336 try
337 {
338 DataSet ds =
new DataSet();
339 _cmd.Connection =
_conn;
340 _cmd.CommandType =
CommandType.Text;
341 _cmd.CommandText =
sql;
342 _dataAdapter.SelectCommand =
_cmd;
343 ReSetParameters(_cmd, parameters);
344 if (_useTransaction)
345 {
346 _dataAdapter.Fill(ds);
347 }
348 else
349 {
350 Open();
351 _dataAdapter.Fill(ds);
352 Close();
353 }
354 return ds;
355 }
356 catch
357 {
358 DoCatch();
359 throw;
360 }
361 }
362
363 /// <summary>
364 /// 返回T类型的实体
365 /// </summary>
366 /// <typeparam name="T"></typeparam>
367 /// <param name="sql"></param>
368 /// <param name="parameters"></param>
369 /// <returns></returns>
370 public T QuerySingle<T>(
string sql,
params IDbDataParameter[] parameters)
371 {
372 try
373 {
374 DataSet ds =
new DataSet();
375 _cmd.Connection =
_conn;
376 _cmd.CommandType =
CommandType.Text;
377 _cmd.CommandText =
sql;
378 _dataAdapter.SelectCommand =
_cmd;
379 ReSetParameters(_cmd, parameters);
380 if (_useTransaction)
381 {
382 _dataAdapter.Fill(ds);
383 }
384 else
385 {
386 Open();
387 _dataAdapter.Fill(ds);
388 Close();
389 }
390 if (ds.Tables.Count>
0 && ds.Tables[
0].Rows.Count>
0)
391 {
392 return ds.Tables[
0].Rows[
0].ToEntity<T>
();
393 }
394 return default(T);
395 }
396 catch
397 {
398 DoCatch();
399 throw;
400 }
401 }
402
403 /// <summary>
404 /// 查询实体集合
405 /// </summary>
406 /// <typeparam name="T"></typeparam>
407 /// <param name="sql"></param>
408 /// <param name="parameters"></param>
409 /// <returns></returns>
410 public List<T> Query<T>(
string sql,
params IDbDataParameter[] parameters)
411 {
412 try
413 {
414 DataSet ds =
new DataSet();
415 _cmd.Connection =
_conn;
416 _cmd.CommandType =
CommandType.Text;
417 _cmd.CommandText =
sql;
418 _dataAdapter.SelectCommand =
_cmd;
419 ReSetParameters(_cmd, parameters);
420 if (_useTransaction)
421 {
422 _dataAdapter.Fill(ds);
423 }
424 else
425 {
426 Open();
427 _dataAdapter.Fill(ds);
428 Close();
429 }
430 if (ds.Tables.Count >
0 && ds.Tables[
0].Rows.Count >
0)
431 {
432 return ds.Tables[
0].ToList<T>
();
433 }
434 return null;
435 }
436 catch
437 {
438 DoCatch();
439 throw;
440 }
441 }
442
443 /// <summary>
444 /// 查询
445 /// </summary>
446 /// <param name="cmd">IDbCommand</param>
447 /// <param name="parameters">参数列表</param>
448 /// <returns></returns>
449 public DataSet Query(IDbCommand cmd,
params IDbDataParameter[] parameters)
450 {
451 try
452 {
453 DataSet ds =
new DataSet();
454 cmd.Connection =
_conn;
455 _dataAdapter.SelectCommand =
cmd;
456 ReSetParameters(cmd, parameters);
457 if (_useTransaction)
458 {
459 _dataAdapter.Fill(ds);
460 }
461 else
462 {
463 Open();
464 _dataAdapter.Fill(ds);
465 Close();
466 }
467 return ds;
468 }
469 catch
470 {
471 DoCatch();
472 throw;
473 }
474 }
475
476 /// <summary>
477 /// 执行sql
478 /// </summary>
479 /// <param name="sql"></param>
480 /// <returns></returns>
481 public int ExecuteSql(
string sql)
482 {
483 try
484 {
485 int result;
486 _cmd.CommandType =
CommandType.Text;
487 _cmd.CommandText =
sql;
488 _cmd.Connection =
_conn;
489 _cmd.Parameters.Clear();
490
491 if (_useTransaction)
492 {
493 result =
_cmd.ExecuteNonQuery();
494 }
495 else
496 {
497 Open();
498 result =
_cmd.ExecuteNonQuery();
499 Close();
500 }
501 return result;
502 }
503 catch
504 {
505
