本项目除用到"实时数据库"外, 还需要用Oracle数据库存储大量的配置信息和生成的数据,而且对Oracle的读取相当的频繁,在项目开始之处,数据访问就是一个很令人烦恼的问题,仅仅数据访问类就修改了好多版本,直到目前正在使用的这个版本.同时为了应付开发过程中不时需要读取SqlServer和Access数据库,所以就写成三种数据源的通用访问类,虽然有点四不象,不过挺省事的,嘻嘻!
此模块分为两个CS文件:
DataFactory.cs
1
using System;
2using System.Data;
3using System.Data.Common;
4using System.Data.SqlClient;
5using System.Data.OleDb;
6using System.Data.OracleClient;
7using System.Collections;
8
9namespace REAP.Utility
10
{
11
public enum DataBaseType
12
{
13 Access,
14 SQLServer,
15 Oracle
16
}
17
18 /**//**//**//// <summary>
19 /// DataFactory 的摘要说明。
20 /// </summary>
21 class DataFactory
22 {
23 public DataFactory()
24 { }
25
26 public static IDbConnection CreateConnection(string ConnectionString, DataBaseType dbtype)
27 {
28 IDbConnection cnn;
29
30 switch (dbtype)
31 {
32 case DataBaseType.Access:
33 cnn = new OleDbConnection(ConnectionString);
34 break;
35
36 case DataBaseType.SQLServer:
37 cnn = new SqlConnection(ConnectionString);
38 break;
39
40 case DataBaseType.Oracle:
41 cnn = new OracleConnection(ConnectionString);
42 break;
43
44 default:
45 cnn = new SqlConnection(ConnectionString);
46 break;
47 }
48 return cnn;
49 }
50
51 public static IDbCommand CreateCommand(DataBaseType dbtype, IDbConnection cnn)
52 {
53 IDbCommand cmd;
54 switch (dbtype)
55 {
56 case DataBaseType.Access:
57 cmd = new OleDbCommand("", (OleDbConnection)cnn);
58 break;
59
60 case DataBaseType.SQLServer:
61 cmd = new SqlCommand("", (SqlConnection)cnn);
62 break;
63
64 case DataBaseType.Oracle:
65 cmd = new OracleCommand("", (OracleConnection)cnn);
66 break;
67 default:
68 cmd = new SqlCommand("", (SqlConnection)cnn);
69 break;
70 }
71
72 return cmd;
73 }
74
75 public static IDbCommand CreateCommand(string CommandText, DataBaseType dbtype, IDbConnection cnn)
76 {
77 IDbCommand cmd;
78 switch (dbtype)
79 {
80 case DataBaseType.Access:
81 cmd = new OleDbCommand(CommandText, (OleDbConnection)cnn);
82 break;
83
84 case DataBaseType.SQLServer:
85 cmd = new SqlCommand(CommandText, (SqlConnection)cnn);
86 break;
87
88 case DataBaseType.Oracle:
89 cmd = new OracleCommand(CommandText, (OracleConnection)cnn);
90 break;
91 default:
92 cmd = new SqlCommand(CommandText, (SqlConnection)cnn);
93 break;
94 }
95
96 return cmd;
97 }
98
99 public static DbDataAdapter CreateAdapter(IDbCommand cmd, DataBaseType dbtype)
100 {
101 DbDataAdapter da;
102 switch (dbtype)
103 {
104 case DataBaseType.Access:
105 da = new OleDbDataAdapter((OleDbCommand)cmd);
106 break;
107
108 case DataBaseType.SQLServer:
109 da = new SqlDataAdapter((SqlCommand)cmd);
110 break;
111
112 case DataBaseType.Oracle:
113 da = new OracleDataAdapter((OracleCommand)cmd);
114 break;
115
116 default:
117 da = new SqlDataAdapter((SqlCommand)cmd);
118 break;
119 }
120
121 return da;
122 }
123
124 public static IDataParameter CreateParameter(DataBaseType dbtype)
125 {
126 IDataParameter param = null;
127 switch (dbtype)
128 {
129 case DataBaseType.Access:
130 param = new OleDbParameter();
131 break;
132
133 case DataBaseType.SQLServer:
134 param = new SqlParameter();
135 break;
136
137 case DataBaseType.Oracle:
138 param = new OracleParameter();
139 break;
140
141 default:
142 param = new SqlParameter();
143 break;
144 }
145
146 return param;
147 }
148 }
149
}
150
using System; 2
using System.Data; 3
using System.Data.Common; 4
using System.Data.SqlClient; 5
using System.Data.OleDb; 6
using System.Data.OracleClient; 7
using System.Collections;8
9
namespace REAP.Utility10
{11
public enum DataBaseType12
{13
Access,14
SQLServer,15
Oracle16
}17
18
/**//**//**//// <summary>19
/// DataFactory 的摘要说明。20
/// </summary>21
class DataFactory22
{23
public DataFactory()24
{ }25
26
public static IDbConnection CreateConnection(string ConnectionString, DataBaseType dbtype)27
{28
IDbConnection cnn;29
30
switch (dbtype)31
{32
case DataBaseType.Access:33
cnn = new OleDbConnection(ConnectionString);34
break;35
36
case DataBaseType.SQLServer:37
cnn = new SqlConnection(ConnectionString);38
break;39
40
case DataBaseType.Oracle:41
cnn = new OracleConnection(ConnectionString);42
break;43
44
default:45
cnn = new SqlConnection(ConnectionString);46
break;47
}48
return cnn;49
}50
51
public static IDbCommand CreateCommand(DataBaseType dbtype, IDbConnection cnn)52
{53
IDbCommand cmd;54
switch (dbtype)55
{56
case DataBaseType.Access:57
cmd = new OleDbCommand("", (OleDbConnection)cnn);58
break;59
60
case DataBaseType.SQLServer:61
cmd = new SqlCommand("", (SqlConnection)cnn);62
break;63
64
case DataBaseType.Oracle:65
cmd = new OracleCommand("", (OracleConnection)cnn);66
break;67
default:68
cmd = new SqlCommand("", (SqlConnection)cnn);69
break;70
}71
72
return cmd;73
}74
75
public static IDbCommand CreateCommand(string CommandText, DataBaseType dbtype, IDbConnection cnn)76
{77
IDbCommand cmd;78
switch (dbtype)79
{80
case DataBaseType.Access:81
cmd = new OleDbCommand(CommandText, (OleDbConnection)cnn);82
break;83
84
case DataBaseType.SQLServer:85
cmd = new SqlCommand(CommandText, (SqlConnection)cnn);86
break;87
88
case DataBaseType.Oracle:89
cmd = new OracleCommand(CommandText, (OracleConnection)cnn);90
break;91
default:92
cmd = new SqlCommand(CommandText, (SqlConnection)cnn);93
break;94
}95
96
return cmd;97
}98
99
public static DbDataAdapter CreateAdapter(IDbCommand cmd, DataBaseType dbtype)100
{101
DbDataAdapter da;102
switch (dbtype)103
{104
case DataBaseType.Access:105
da = new OleDbDataAdapter((OleDbCommand)cmd);106
break;107
108
case DataBaseType.SQLServer:109
da = new SqlDataAdapter((SqlCommand)cmd);110
break;111
112
case DataBaseType.Oracle:113
da = new OracleDataAdapter((OracleCommand)cmd);114
break;115
116
default:117
da = new SqlDataAdapter((SqlCommand)cmd);118
break;119
}120
121
return da;122
}123
124
public static IDataParameter CreateParameter(DataBaseType dbtype)125
{126
IDataParameter param = null;127
switch (dbtype)128
{129
case DataBaseType.Access:130
param = new OleDbParameter();131
break;132
133
case DataBaseType.SQLServer:134
param = new SqlParameter();135
break;136
137
case DataBaseType.Oracle:138
param = new OracleParameter();139
break;140
141
default:142
param = new SqlParameter();143
break;144
}145
146
return param;147
}148
}149
}150
DBAccess.cs
1using System;
2using System.Data;
3using System.Data.Common;
4using System.Data.SqlClient;
5using System.Data.OleDb;
6using System.Data.OracleClient;
7using System.Configuration;
8
9namespace REAP.Utility
10{
11 /**//**//**//// <summary>
12 /// 由于可能会在多种数据源，如ORACLE，SQLSERVER，ACCESS等之间进行切换，
13 /// 所以将数据源连接字符串和数据源类型定义为类属性，在默认情况下有配置文件定义；
14 /// 当需要在两种不同的数据源之间进行切换时，可以重新为属性赋值。
15 /// </summary>
16 public class DBAccess
17 {
18 属性设置属性设置#region 属性设置
19 private string _ConnectionString = "";
20 private DataBaseType _DataSourceType = DataBaseType.Oracle;
21
22 /**//**//**//// <summary>
23 /// 数据源连接字符串
24 /// </summary>
25 public string ConnectionString
26 {
27 get
28 {
29 if (_ConnectionString == "")
30 {
31 _ConnectionString = ConfigurationSettings.AppSettings["StrConn"];
32 }
33 return _ConnectionString;
34 }
35 set
36 {
37 _ConnectionString = value;
38 }
39 }
40
41 /**//**//**//// <summary>
42 /// 数据库库类型（默认情况下为Oracle）
43 /// </summary>
44 public DataBaseType DataSourceType
45 {
46 get
47 {
48 return _DataSourceType;
49 }
50 set
51 {
52 _DataSourceType = value;
53 }
54 }
55
56 public DBAccess()
57 {}
58 #endregion
59
60 DataSet生成操作DataSet生成操作#region DataSet生成操作
61
62 /**//**//**//// <summary>
63 /// 根据SQL语句创建DataSet数据集；
64 /// 可以执行多条SELECT查询语句，查询语句之间用分号标记，如下所示：
65 /// SELECT * FROM TABLE1;SELECT * FROM TABLE2
66 /// </summary>
67 /// <param name="sqlQuery">SQL语句</param>
68 /// <returns>返回DataSet数据集</returns>
69 public DataSet GetDataSet(string sqlQuery)
70 {
71 IDbConnection cn = DataFactory.CreateConnection( _ConnectionString, _DataSourceType );
72 IDbCommand cmd = null;
73 DbDataAdapter da = null;
74 DataSet dsResult = new DataSet();
75 try
76 {
77 string[] strSqls = sqlQuery.Split(';');
78 foreach (string strSql in strSqls)
79 {
80 cmd = DataFactory.CreateCommand(strSql, _DataSourceType, cn);
81 da = DataFactory.CreateAdapter(cmd, _DataSourceType);
82 DataSet ds = new DataSet();
83 da.Fill(ds);
84
85 if (strSqls.Length == 1)
86 {
87 dsResult = ds;
88 }
89 else
90 {
91 DataTable dt = ds.Tables[0].Clone();
92 foreach (DataRow dr in ds.Tables[0].Rows)
93 {
94 dt.ImportRow(dr);
95 }
96 dsResult.Tables.Add(dt);
97 }
98 }
99 }
100 finally
101 {
102 da.Dispose();
103 cmd.Dispose();
104 cn.Close();
105 cn.Dispose();
106 }
107
108 return dsResult;
109 }
110
111 /**//**//**//// <summary>
112 /// 执行SELECT查询语句，并将结果以TABLE的形式加入到指定DataSet数据集；
113 /// 可以执行多条SELECT查询语句，查询语句之间用分号标记，如下所示：
114 /// SELECT * FROM TABLE1;SELECT * FROM TABLE2
115 /// </summary>
116 /// <param name="sqlQuery">SQL语句</param>
117 /// <param name="dsTarget">已存在的DataSet数据集</param>
118 /// <returns>返回DataSet数据集</returns>
119 public DataSet GetDataSet(string sqlQuery,DataSet dsTarget)
120 {
121 IDbConnection cn = DataFactory.CreateConnection(_ConnectionString, _DataSourceType);
122 IDbCommand cmd = null;
123 DbDataAdapter da = null;
124 try
125 {
126 string[] strSqls = sqlQuery.Split(';');
127 foreach (string strSql in strSqls)
128 {
129 cmd = DataFactory.CreateCommand(strSql, _DataSourceType, cn);
130 da = DataFactory.CreateAdapter(cmd, _DataSourceType);
131 DataSet ds = new DataSet();
132 da.Fill(ds);
133
134 DataTable dt = ds.Tables[0].Clone();
135 foreach (DataRow dr in ds.Tables[0].Rows)
136 {
137 dt.ImportRow(dr);
138 }
139 dsTarget.Tables.Add(dt);
140 }
141 }
142 finally
143 {
144 da.Dispose();
145 cmd.Dispose();
146 cn.Close();
147 cn.Dispose();
148 }
149 return dsTarget;
150 }
151
152 #endregion
153
154 SQL执行操作SQL执行操作#region SQL执行操作
155
156 /**//**//**//// <summary>
157 /// 根据SQL语句执行ExecuteNonQuery操作
158 /// </summary>
159 /// <param name="sqlQuery">SQL语句</param>
160 /// <returns>返回bool表示是否成功</returns>
161 public bool ExecuteNonQuery(string sqlQuery)
162 {
163 IDbConnection cn = DataFactory.CreateConnection(_ConnectionString, _DataSourceType);
164 cn.Open();
165 IDbCommand cmd = DataFactory.CreateCommand(sqlQuery, _DataSourceType, cn);
166
167 try
168 {
169 cmd.ExecuteNonQuery();
170 return true;
171 }
172 catch (Exception ex)
173 {
174 string strEx = ex.Message;
175 return false;
176 }
177 finally
178 {
179 cmd.Dispose();
180 cn.Close();
181 cn.Dispose();
182 }
183 }
184
185 #endregion
186
187 DataReader操作DataReader操作#region DataReader操作
188
189 /**//**//**//// <summary>
190 /// 根据SQL语句创建DataReader
191 /// </summary>
192 /// <param name="sqlQuery">SQL语句</param>
193 /// <returns>返回DataReader</returns>
194 public IDataReader GetDataReader(string sqlQuery)
195 {
196 IDbConnection cn = DataFactory.CreateConnection(_ConnectionString, _DataSourceType);
197 IDbCommand cmd = DataFactory.CreateCommand(sqlQuery, _DataSourceType, cn);
198 IDataReader da = cmd.ExecuteReader(CommandBehavior.CloseConnection);
199
200 return da;
201 }
202
203 #endregion
204
205 //其他功能,故意省略
206 }
207}
208
209
using System; 2
using System.Data; 3
using System.Data.Common; 4
using System.Data.SqlClient; 5
using System.Data.OleDb; 6
using System.Data.OracleClient;7
using System.Configuration;8
9
namespace REAP.Utility10
{11
/**//**//**//// <summary>12
/// 由于可能会在多种数据源，如ORACLE，SQLSERVER，ACCESS等之间进行切换，13
/// 所以将数据源连接字符串和数据源类型定义为类属性，在默认情况下有配置文件定义；14
/// 当需要在两种不同的数据源之间进行切换时，可以重新为属性赋值。15
/// </summary>16
public class DBAccess17
{18
属性设置属性设置#region 属性设置19
private string _ConnectionString = "";20
private DataBaseType _DataSourceType = DataBaseType.Oracle;21
22
/**//**//**//// <summary>23
/// 数据源连接字符串24
/// </summary>25
public string ConnectionString26
{27
get28
{29
if (_ConnectionString == "")30
{31
_ConnectionString = ConfigurationSettings.AppSettings["StrConn"];32
}33
return _ConnectionString;34
}35
set36
{37
_ConnectionString = value;38
}39
}40
41
/**//**//**//// <summary>42
/// 数据库库类型（默认情况下为Oracle）43
/// </summary>44
public DataBaseType DataSourceType45
{46
get47
{48
return _DataSourceType;49
}50
set51
{52
_DataSourceType = value;53
}54
}55
56
public DBAccess()57
{}58
#endregion59
60
DataSet生成操作DataSet生成操作#region DataSet生成操作61
62
/**//**//**//// <summary>63
/// 根据SQL语句创建DataSet数据集；64
/// 可以执行多条SELECT查询语句，查询语句之间用分号标记，如下所示：65
/// SELECT * FROM TABLE1;SELECT * FROM TABLE266
/// </summary>67
/// <param name="sqlQuery">SQL语句</param>68
/// <returns>返回DataSet数据集</returns>69
public DataSet GetDataSet(string sqlQuery)70
{71
IDbConnection cn = DataFactory.CreateConnection( _ConnectionString, _DataSourceType );72
IDbCommand cmd = null;73
DbDataAdapter da = null;74
DataSet dsResult = new DataSet();75
try76
{77
string[] strSqls = sqlQuery.Split(';');78
foreach (string strSql in strSqls)79
{80
cmd = DataFactory.CreateCommand(strSql, _DataSourceType, cn);81
da = DataFactory.CreateAdapter(cmd, _DataSourceType);82
DataSet ds = new DataSet();83
da.Fill(ds);84
85
if (strSqls.Length == 1)86
{87
dsResult = ds;88
}89
else90
{91
DataTable dt = ds.Tables[0].Clone();92
foreach (DataRow dr in ds.Tables[0].Rows)93
{94
dt.ImportRow(dr);95
}96
dsResult.Tables.Add(dt);97
}98
}99
}100
finally101
{ 102
da.Dispose();103
cmd.Dispose();104
cn.Close();105
cn.Dispose();106
}107
108
return dsResult;109
}110
111
/**//**//**//// <summary>112
/// 执行SELECT查询语句，并将结果以TABLE的形式加入到指定DataSet数据集；113
/// 可以执行多条SELECT查询语句，查询语句之间用分号标记，如下所示：114
/// SELECT * FROM TABLE1;SELECT * FROM TABLE2115
/// </summary>116
/// <param name="sqlQuery">SQL语句</param>117
/// <param name="dsTarget">已存在的DataSet数据集</param>118
/// <returns>返回DataSet数据集</returns>119
public DataSet GetDataSet(string sqlQuery,DataSet dsTarget)120
{121
IDbConnection cn = DataFactory.CreateConnection(_ConnectionString, _DataSourceType);122
IDbCommand cmd = null;123
DbDataAdapter da = null;124
try125
{126
string[] strSqls = sqlQuery.Split(';');127
foreach (string strSql in strSqls)128
{129
cmd = DataFactory.CreateCommand(strSql, _DataSourceType, cn);130
da = DataFactory.CreateAdapter(cmd, _DataSourceType);131
DataSet ds = new DataSet();132
da.Fill(ds);133
134
DataTable dt = ds.Tables[0].Clone();135
foreach (DataRow dr in ds.Tables[0].Rows)136
{137
dt.ImportRow(dr);138
}139
dsTarget.Tables.Add(dt);140
}141
}142
finally143
{144
da.Dispose();145
cmd.Dispose();146
cn.Close();147
cn.Dispose();148
}149
return dsTarget;150
}151
152
#endregion153
154
SQL执行操作SQL执行操作#region SQL执行操作155
156
/**//**//**//// <summary>157
/// 根据SQL语句执行ExecuteNonQuery操作158
/// </summary>159
/// <param name="sqlQuery">SQL语句</param>160
/// <returns>返回bool表示是否成功</returns>161
public bool ExecuteNonQuery(string sqlQuery)162
{163
IDbConnection cn = DataFactory.CreateConnection(_ConnectionString, _DataSourceType);164
cn.Open();165
IDbCommand cmd = DataFactory.CreateCommand(sqlQuery, _DataSourceType, cn);166
167
try168
{169
cmd.ExecuteNonQuery();170
return true;171
}172
catch (Exception ex)173
{174
string strEx = ex.Message;175
return false;176
}177
finally178
{179
cmd.Dispose();180
cn.Close();181
cn.Dispose();182
}183
}184
185
#endregion186
187
DataReader操作DataReader操作#region DataReader操作188
189
/**//**//**//// <summary>190
/// 根据SQL语句创建DataReader191
/// </summary>192
/// <param name="sqlQuery">SQL语句</param>193
/// <returns>返回DataReader</returns>194
public IDataReader GetDataReader(string sqlQuery)195
{196
IDbConnection cn = DataFactory.CreateConnection(_ConnectionString, _DataSourceType);197
IDbCommand cmd = DataFactory.CreateCommand(sqlQuery, _DataSourceType, cn);198
IDataReader da = cmd.ExecuteReader(CommandBehavior.CloseConnection);199
200
return da;201
}202
203
#endregion204
205
//其他功能,故意省略206
}207
}208
209
举例如下:
默认情况下是访问Oracle数据库,数据库连接字符串已经在Config文件中定义,所以不需要再设置其ConnectionString和DataSourceType属性,此时返回一个DataSet的代码如下:
DBAccess db = new DBAccess();
//同时执行两条查询语句
string strSql = "SELECT * FROM TABLE1;SELECT * FROM TABLE2";
DataSet ds = db.GetDataSet(strSql);
但是如果在程序中需要临时访问SqlServer数据库,则需要设置属性,此时代码如下:
DBAccess db = new DBAccess();
db.ConnectionString = "server=localhost;UID=sa;PWD=123456;DATABASE=Money;connect timeout=120";
db.DataSourceType = DataBaseType.SQLServer;
该文章转载自网络大本营：http://www.xrss.cn/Info/14114.Html
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
