.NET通用访问数据库类
在数据库应用程序的开发中，往往一个应用程序要从多个数据库中取得所要的业务数据。对于ADO.NET更是如此，一个DataSet，可能是从几个数据库中取得的数据集合。为此开发一个访问数据库通用接口显得更为方便、灵活。下面是一个针对.NET提供的四种访问数据库的通用类，每个类都有具体实现了一些常用访问数据库的方法。
DataProvider.cs 此类返回一个访问数据库的接口实例。
1
#define DEBUG
2using System;
3namespace DataProviders
4
{
5
///
6
/// 提供对数据库访问的通用类。
7
///
8 public class DataProvider
9 {
10 ///
11 /// 数据库枚举类型
12 ///
13 public enum DataProviderType
14 {
15 OdbcDataProvider = 0,
16 OleDbDataProvider = 1,
17 OracleDataProvider = 2,
18 SqlDataProvider = 3
19 }
20
21 ///
22 /// 建立访问数据库的实例
23 ///
24 /// 数据库枚举类型
25 ///
26 public static IDataProvider CreateDataProvider(DataProviderType dataProviderType)
27 {
28 switch (dataProviderType)
29 {
30 case DataProviderType.OdbcDataProvider:
31 return new OdbcDataProvider();
32 case DataProviderType.OleDbDataProvider:
33 return new OleDbDataProvider();
34 case DataProviderType.OracleDataProvider:
35 return new OracleDataProvider();
36 case DataProviderType.SqlDataProvider:
37 return new SqlDataProvider();
38 default:
39#if DEBUG
40 System.Diagnostics.Debug.WriteLine("dataProviderType 类型不存在!");
41#endif
42 return null;
43 }
44 }
45
46 }
47
}
48
#define DEBUG 2
using System; 3
namespace DataProviders 4
{ 
5
/// 
6
/// 提供对数据库访问的通用类。 7
/// 8
public class DataProvider 9
{ 10
/// 11
/// 数据库枚举类型 12
/// 13
public enum DataProviderType 14
{ 15
OdbcDataProvider = 0, 16
OleDbDataProvider = 1, 17
OracleDataProvider = 2, 18
SqlDataProvider = 3 19
} 20
21
/// 22
/// 建立访问数据库的实例 23
/// 24
/// 数据库枚举类型 25
/// 26
public static IDataProvider CreateDataProvider(DataProviderType dataProviderType) 27
{ 28
switch (dataProviderType) 29
{ 30
case DataProviderType.OdbcDataProvider: 31
return new OdbcDataProvider(); 32
case DataProviderType.OleDbDataProvider: 33
return new OleDbDataProvider(); 34
case DataProviderType.OracleDataProvider: 35
return new OracleDataProvider(); 36
case DataProviderType.SqlDataProvider: 37
return new SqlDataProvider(); 38
default: 39
#if DEBUG 40
System.Diagnostics.Debug.WriteLine("dataProviderType 类型不存在!"); 41
#endif 42
return null; 43
} 44
} 45
46
} 47
} 48
IDataProvider.cs 访问数据库的接口类
1using System;
2
3namespace DataProviders
4{
5 ///
6 /// 对数据库访问的通用接口
7 ///
8 public interface IDataProvider
9 {
10 ///
11 /// 执行 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数
12 ///
13 int ExecuteNonQuery(string sql);
14
15 ///
16 /// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行
17 ///
18 object ExecuteScalar(string sql);
19
20 ///
21 /// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回
22 ///
23 System.Data.DataSet RetriveDataSet(string sql);
24
25 ///
26 /// 执行Sql数组语句查询，并将查询返回的结果作为一个数据集返回
27 ///
28 System.Data.DataSet RetriveDataSet(string[] sql, params string[] tableName);
29
30 ///
31 /// 更新库
32 ///
33 ///
34 ///
35 ///
36 System.Data.DataSet UpdateDataSet(string sql, System.Data.DataSet hasChangesDataSet);
37
38 ///
39 /// 执行Dispose
40 ///
41 void Dispose();
42 }
43}
44
using System; 2
3
namespace DataProviders 4
{ 5
/// 6
/// 对数据库访问的通用接口 7
/// 8
public interface IDataProvider 9
{ 10
/// 11
/// 执行 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数 12
/// 13
int ExecuteNonQuery(string sql); 14
15
/// 16
/// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行 17
/// 18
object ExecuteScalar(string sql); 19
20
/// 21
/// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回 22
/// 23
System.Data.DataSet RetriveDataSet(string sql); 24
25
/// 26
/// 执行Sql数组语句查询，并将查询返回的结果作为一个数据集返回 27
/// 28
System.Data.DataSet RetriveDataSet(string[] sql, params string[] tableName); 29
30
/// 31
/// 更新库 32
/// 33
/// 34
/// 35
/// 36
System.Data.DataSet UpdateDataSet(string sql, System.Data.DataSet hasChangesDataSet); 37
38
/// 39
/// 执行Dispose 40
/// 41
void Dispose(); 42
} 43
} 44
OracleDataProvider.cs 访问Oracle的类
1#define DEBUG
2using System;
3using System.Data;
4using System.Data.OracleClient;
5
6namespace DataProviders
7{
8 ///
9 /// OracleDataProvider 的摘要说明。
10 ///
11 internal class OracleDataProvider : IDataProvider
12 {
13 private System.Data.OracleClient.OracleConnection oracleConnection;
14 private System.Data.OracleClient.OracleCommand oracleCommand;
15 private string connectionString;
16 public OracleDataProvider() : this(null)
17 {
18 //
19 // TODO: 在此处添加构造函数逻辑
20 //
21 }
22
23 public OracleDataProvider(string connectionString)
24 {
25 if (connectionString == null || connectionString.Trim() == string.Empty)
26 {
27 System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
28 this.connectionString = (string)(configurationAppSettings.GetValue("oracleConnectionString", typeof(string)));
29 }
30 else
31 {
32 this.connectionString = connectionString;
33 }
34 }
35
36 ///
37 /// Oracle 连接字符串 "User Id=southfence;Data Source=FENCEORA;Password=southfence;Persist Security Info=true;"
38 ///
39 public string ConnectionString
40 {
41 get{
42 return this.connectionString;
43 }
44 set{
45 this.connectionString = value;
46 }
47 }
48
49 ///
50 /// 返回一个带有连接字符串的Oracle Connection.
51 ///
52 /// OracleConnection
53 private OracleConnection GetOracleConnection()
54 {
55 try
56 {
57 return new OracleConnection(this.connectionString);
58 }
59 catch (Exception ex)
60 {
61#if DEBUG
62 System.Diagnostics.Debug.WriteLine(ex.ToString());
63#endif
64 return null;
65 }
66 }
67
68 ///
69 /// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1
70 ///
71 /// UPDATE、INSERT 和 DELETE 语句
72 public int ExecuteNonQuery(string sql)
73 {
74 using(oracleConnection = this.GetOracleConnection())
75 {
76 if (oracleConnection == null)
77 return -1;
78 int rv = -1;
79 OracleTransaction oracleTransaction = null;
80 try
81 {
82 if (oracleConnection.State == System.Data.ConnectionState.Closed)
83 oracleConnection.Open();
84 oracleCommand = new OracleCommand(sql, oracleConnection);
85 oracleTransaction = oracleConnection.BeginTransaction();
86 oracleCommand.Transaction = oracleTransaction;
87 rv = oracleCommand.ExecuteNonQuery();
88 oracleTransaction.Commit();
89 }
90 catch (Exception ex)
91 {
92#if DEBUG
93 System.Diagnostics.Debug.WriteLine(ex.ToString());
94#endif
95 oracleTransaction.Rollback();
96 rv = -1;
97 }
98
99 return rv;
100 }
101 }
102
103 ///
104 /// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
105 ///
106 /// SELECT 语句
107 /// .NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用
108 public object ExecuteScalar(string sql)
109 {
110 using(oracleConnection = this.GetOracleConnection())
111 {
112 if (oracleConnection == null)
113 return null;
114 try
115 {
116 if (oracleConnection.State == System.Data.ConnectionState.Closed)
117 oracleConnection.Open();
118 oracleCommand = new OracleCommand(sql, oracleConnection);
119 return oracleCommand.ExecuteScalar();
120 }
121 catch (Exception ex)
122 {
123#if DEBUG
124 System.Diagnostics.Debug.WriteLine(ex.ToString());
125#endif
126 return null;
127 }
128 }
129 }
130
131 ///
132 /// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回
133 ///
134 /// SELECT 语句
135 /// 数据集 DataSet
136 public DataSet RetriveDataSet(string sql)
137 {
138 if (sql == null || sql == string.Empty)
139 {
140#if DEBUG
141 System.Diagnostics.Debug.WriteLine("sql 为空");
142#endif
143 return null;
144 }
145 using(oracleConnection = this.GetOracleConnection())
146 {
147 if (oracleConnection == null)
148 return null;
149 using( OracleDataAdapter da = new OracleDataAdapter(sql, oracleConnection))
150 {
151 DataSet ds = new DataSet();
152 try
153 {
154 da.Fill(ds);
155 }
156 catch (Exception ex)
157 {
158#if DEBUG
159 System.Diagnostics.Debug.WriteLine(ex.ToString());
160#endif
161 }
162 return ds;
163 }
164 }
165 }
166
167 ///
168 /// 执行Sql数组语句查询，并将查询返回的结果作为一个数据集返回
169 ///
170 /// Select 语句数组
171 /// TableName
172 /// 数据集 DataSet
173 public DataSet RetriveDataSet(string[] sql, params string[] tableName)
174 {
175 int sqlLength;
176 sqlLength = sql.Length;
177 if ( sqlLength == 0)
178 {
179#if DEBUG
180 System.Diagnostics.Debug.WriteLine("sql 为空");
181#endif
182 return null;
183 }
184 using(oracleConnection = this.GetOracleConnection())
185 {
186 if (oracleConnection == null)
187 return null;
188 DataSet ds = new DataSet();
189 int tableNameLength = tableName.Length;
190 for (int i = 0; i < sqlLength; i++)
191 {
192 using(OracleDataAdapter da = new OracleDataAdapter(sql[i], oracleConnection))
193 {
194 try
195 {
196 if (i < tableNameLength)
197 da.Fill(ds, tableName[i]);
198 else
199 da.Fill(ds, "table" + i);
200 }
201 catch (Exception ex)
202 {
203#if DEBUG
204 System.Diagnostics.Debug.WriteLine(ex.ToString());
205#endif
206 return null;
207 }
208 }
209 }
210 return ds;
211 }
212 }
213
214 ///
215 /// 更新数据集.
216 /// 过程:客户层(dataSet.GetChanges()) -- 修改 --> 数据服务层(hasChangesDataSet.update()) -- 更新--> 数据层(hasChangesDataSet) 
217 /// 数据层(hasChangesDataSet) -- 新数据 --> 数据服务层 (hasChangesDataSet) -- 合并 -- > 客户层(dataSet.Merge(hasChangesDataSet))
218 ///
219 ///
220 ///
221 public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet)
222 {
223
224 }
225
226 ///
227 /// 执行Sql数组语句查询，并将查询返回的结果作为一个数据读取器返回
228 ///
229 ///
230 /// OracleDataReader
231 public OracleDataReader RetriveDataReader(string sql)
232 {
233 if (sql == null || sql == string.Empty)
234 {
235#if DEBUG
236 System.Diagnostics.Debug.WriteLine("sql 为空");
237#endif
238 return null;
239 }
240 using(oracleConnection = this.GetOracleConnection())
241 {
242 if (oracleConnection == null)
243 return null;
244 using(oracleCommand = new OracleCommand(sql, oracleConnection))
245 {
246 try
247 {
248 OracleDataReader oracleDataReader = oracleCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
249 return oracleDataReader;
250 }
251 catch (Exception ex)
252 {
253#if DEBUG
254 System.Diagnostics.Debug.WriteLine(ex.ToString());
255#endif
256 return null;
257
258 }
259 }
260 }
261 }
262
263 public void Dispose()
264 {
265 this.connectionString = null;
266 this.oracleCommand.Dispose();
267 this.oracleConnection.Dispose();
268 }
269 }
270}
#define DEBUG 2
using System; 3
using System.Data; 4
using System.Data.OracleClient; 5
6
namespace DataProviders 7
{ 8
/// 9
/// OracleDataProvider 的摘要说明。 10
/// 11
internal class OracleDataProvider : IDataProvider 12
{ 13
private System.Data.OracleClient.OracleConnection oracleConnection; 14
private System.Data.OracleClient.OracleCommand oracleCommand; 15
private string connectionString; 16
public OracleDataProvider() : this(null) 17
{ 18
// 19
// TODO: 在此处添加构造函数逻辑 20
// 21
} 22
23
public OracleDataProvider(string connectionString) 24
{ 25
if (connectionString == null || connectionString.Trim() == string.Empty) 26
{ 27
System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader(); 28
this.connectionString = (string)(configurationAppSettings.GetValue("oracleConnectionString", typeof(string))); 29
} 30
else 31
{ 32
this.connectionString = connectionString; 33
} 34
} 35
36
/// 37
/// Oracle 连接字符串 "User Id=southfence;Data Source=FENCEORA;Password=southfence;Persist Security Info=true;" 38
/// 39
public string ConnectionString 40
{ 41
get{ 42
return this.connectionString; 43
} 44
set{ 45
this.connectionString = value; 46
} 47
} 48
49
/// 50
/// 返回一个带有连接字符串的Oracle Connection. 51
/// 52
/// OracleConnection 53
private OracleConnection GetOracleConnection() 54
{ 55
try 56
{ 57
return new OracleConnection(this.connectionString); 58
} 59
catch (Exception ex) 60
{ 61
#if DEBUG 62
System.Diagnostics.Debug.WriteLine(ex.ToString()); 63
#endif 64
return null; 65
} 66
} 67
68
/// 69
/// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1 70
/// 71
/// UPDATE、INSERT 和 DELETE 语句 72
public int ExecuteNonQuery(string sql) 73
{ 74
using(oracleConnection = this.GetOracleConnection()) 75
{ 76
if (oracleConnection == null) 77
return -1; 78
int rv = -1; 79
OracleTransaction oracleTransaction = null; 80
try 81
{ 82
if (oracleConnection.State == System.Data.ConnectionState.Closed) 83
oracleConnection.Open(); 84
oracleCommand = new OracleCommand(sql, oracleConnection); 85
oracleTransaction = oracleConnection.BeginTransaction(); 86
oracleCommand.Transaction = oracleTransaction; 87
rv = oracleCommand.ExecuteNonQuery(); 88
oracleTransaction.Commit(); 89
} 90
catch (Exception ex) 91
{ 92
#if DEBUG 93
System.Diagnostics.Debug.WriteLine(ex.ToString()); 94
#endif 95
oracleTransaction.Rollback(); 96
rv = -1; 97
} 98
99
return rv; 100
} 101
} 102
103
/// 104
/// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。 105
/// 106
/// SELECT 语句 107
/// .NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用 108
public object ExecuteScalar(string sql) 109
{ 110
using(oracleConnection = this.GetOracleConnection()) 111
{ 112
if (oracleConnection == null) 113
return null; 114
try 115
{ 116
if (oracleConnection.State == System.Data.ConnectionState.Closed) 117
oracleConnection.Open(); 118
oracleCommand = new OracleCommand(sql, oracleConnection); 119
return oracleCommand.ExecuteScalar(); 120
} 121
catch (Exception ex) 122
{ 123
#if DEBUG 124
System.Diagnostics.Debug.WriteLine(ex.ToString()); 125
#endif 126
return null; 127
} 128
} 129
} 130
131
/// 132
/// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回 133
/// 134
/// SELECT 语句 135
/// 数据集 DataSet 136
public DataSet RetriveDataSet(string sql) 137
{ 138
if (sql == null || sql == string.Empty) 139
{ 140
#if DEBUG 141
System.Diagnostics.Debug.WriteLine("sql 为空"); 142
#endif 143
return null; 144
} 145
using(oracleConnection = this.GetOracleConnection()) 146
{ 147
if (oracleConnection == null) 148
return null; 149
using( OracleDataAdapter da = new OracleDataAdapter(sql, oracleConnection)) 150
{ 151
DataSet ds = new DataSet(); 152
try 153
{ 154
da.Fill(ds); 155
} 156
catch (Exception ex) 157
{ 158
#if DEBUG 159
System.Diagnostics.Debug.WriteLine(ex.ToString()); 160
#endif 161
} 162
return ds; 163
} 164
} 165
} 166
167
/// 168
/// 执行Sql数组语句查询，并将查询返回的结果作为一个数据集返回 169
/// 170
/// Select 语句数组 171
/// TableName 172
/// 数据集 DataSet 173
public DataSet RetriveDataSet(string[] sql, params string[] tableName) 174
{ 175
int sqlLength; 176
sqlLength = sql.Length; 177
if ( sqlLength == 0) 178
{ 179
#if DEBUG 180
System.Diagnostics.Debug.WriteLine("sql 为空"); 181
#endif 182
return null; 183
} 184
using(oracleConnection = this.GetOracleConnection()) 185
{ 186
if (oracleConnection == null) 187
return null; 188
DataSet ds = new DataSet(); 189
int tableNameLength = tableName.Length; 190
for (int i = 0; i < sqlLength; i++) 191
{ 192
using(OracleDataAdapter da = new OracleDataAdapter(sql[i], oracleConnection)) 193
{ 194
try 195
{ 196
if (i < tableNameLength) 197
da.Fill(ds, tableName[i]); 198
else 199
da.Fill(ds, "table" + i); 200
} 201
catch (Exception ex) 202
{ 203
#if DEBUG 204
System.Diagnostics.Debug.WriteLine(ex.ToString()); 205
#endif 206
return null; 207
} 208
} 209
} 210
return ds; 211
} 212
} 213
214
/// 215
/// 更新数据集. 216
/// 过程:客户层(dataSet.GetChanges()) -- 修改 --> 数据服务层(hasChangesDataSet.update()) -- 更新--> 数据层(hasChangesDataSet) 217
/// 数据层(hasChangesDataSet) -- 新数据 --> 数据服务层 (hasChangesDataSet) -- 合并 -- > 客户层(dataSet.Merge(hasChangesDataSet)) 218
/// 219
/// 220
/// 221
public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet) 222
{ 223
224
} 225
226
/// 227
/// 执行Sql数组语句查询，并将查询返回的结果作为一个数据读取器返回 228
/// 229
/// 230
/// OracleDataReader 231
public OracleDataReader RetriveDataReader(string sql) 232
{ 233
if (sql == null || sql == string.Empty) 234
{ 235
#if DEBUG 236
System.Diagnostics.Debug.WriteLine("sql 为空"); 237
#endif 238
return null; 239
} 240
using(oracleConnection = this.GetOracleConnection()) 241
{ 242
if (oracleConnection == null) 243
return null; 244
using(oracleCommand = new OracleCommand(sql, oracleConnection)) 245
{ 246
try 247
{ 248
OracleDataReader oracleDataReader = oracleCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection); 249
return oracleDataReader; 250
} 251
catch (Exception ex) 252
{ 253
#if DEBUG 254
System.Diagnostics.Debug.WriteLine(ex.ToString()); 255
#endif 256
return null; 257
258
} 259
} 260
} 261
} 262
263
public void Dispose() 264
{ 265
this.connectionString = null; 266
this.oracleCommand.Dispose(); 267
this.oracleConnection.Dispose(); 268
} 269
} 270
} SqlDataProvider.cs 访问SQL Server的类
1#define DEBUG
2using System;
3using System.Data;
4using System.Data.SqlClient;
5
6namespace DataProviders
7{
8 /// <summary>
9 /// SqlDataProvider 的摘要说明。
10 /// </summary>
11 internal class SqlDataProvider : IDataProvider
12 {
13 private System.Data.SqlClient.SqlConnection sqlConnection;
14 private System.Data.SqlClient.SqlCommand sqlCommand;
15 private string connectionString;
16 public SqlDataProvider() : this(null)
17 {
18 //
19 // TODO: 在此处添加构造函数逻辑
20 //
21 }
22
23 public SqlDataProvider(string connectionString)
24 {
25 if (connectionString == null || connectionString.Trim() == string.Empty)
26 {
27 System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
28 this.connectionString = (string)(configurationAppSettings.GetValue("sqlConnectionString", typeof(string)));
29 }
30 else
31 {
32 this.connectionString = connectionString;
33 }
34 }
35
36 /// <summary>
37 /// SQL 连接字符串
38 /// </summary>
39 public string ConnectionString
40 {
41 get
42 {
43 return this.connectionString;
44 }
45 set
46 {
47 this.connectionString = value;
48 }
49 }
50
51 /// <summary>
52 /// 返回一个带有连接字符串的SQL Connection.
53 /// </summary>
54 /// <returns>OracleConnection</returns>
55 private SqlConnection GetSqlConnection()
56 {
57 try
58 {
59 return new SqlConnection(this.connectionString);
60 }
61 catch (Exception ex)
62 {
63#if DEBUG
64 System.Diagnostics.Debug.WriteLine(ex.ToString());
65#endif
66 return null;
67 }
68 }
69
70 /// <summary>
71 /// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1
72 /// </summary>
73 /// <param name="Sql">UPDATE、INSERT 和 DELETE 语句</param>
74 public int ExecuteNonQuery(string sql)
75 {
76 using(sqlConnection = this.GetSqlConnection())
77 {
78 if (sqlConnection == null)
79 return -1;
80 try
81 {
82 if (sqlConnection.State == System.Data.ConnectionState.Closed)
83 sqlConnection.Open();
84 sqlCommand = new SqlCommand(sql, sqlConnection);
85 return sqlCommand.ExecuteNonQuery();
86 }
87 catch (Exception ex)
88 {
89#if DEBUG
90 System.Diagnostics.Debug.WriteLine(ex.ToString());
91#endif
92 return -1;
93 }
94 }
95 }
96
97 /// <summary>
98 /// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
99 /// </summary>
100 /// <param name="sql">SELECT 语句</param>
101 /// <returns>.NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用</returns>
102 public object ExecuteScalar(string sql)
103 {
104 using(sqlConnection = this.GetSqlConnection())
105 {
106 if (sqlConnection == null)
107 return null;
108 try
109 {
110 if (sqlConnection.State == System.Data.ConnectionState.Closed)
111 sqlConnection.Open();
112 sqlCommand = new SqlCommand(sql, sqlConnection);
113 return sqlCommand.ExecuteScalar();
114 }
115 catch (Exception ex)
116 {
117#if DEBUG
118 System.Diagnostics.Debug.WriteLine(ex.ToString());
119#endif
120 return null;
121 }
122 }
123 }
124
125 /// <summary>
126 /// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回
127 /// </summary>
128 /// <param name="sql">SELECT 语句</param>
129 /// <returns>数据集 DataSet</returns>
130 public DataSet RetriveDataSet(string sql)
131 {
132 if (sql == null || sql == string.Empty)
133 {
134#if DEBUG
135 System.Diagnostics.Debug.WriteLine("sql 为空");
136#endif
137 return null;
138 }
139 using(sqlConnection = this.GetSqlConnection())
140 {
141 if (sqlConnection == null)
142 return null;
143 using(SqlDataAdapter da = new SqlDataAdapter(sql, sqlConnection))
144 {
145 DataSet ds = new DataSet();
146 try
147 {
148 da.Fill(ds);
149 }
150 catch (Exception ex)
151 {
152#if DEBUG
153 System.Diagnostics.Debug.WriteLine(ex.ToString());
154#endif
155 }
156 return ds;
157 }
158 }
159 }
160
161 /// <summary>
162 /// 执行Sql数组语句查询，并将查询返回的结果作为一个数据集返回
163 /// </summary>
164 /// <param name="sql">Select 语句数组</param>
165 /// <param name="tableName">TableName</param>
166 /// <returns>数据集 DataSet</returns>
167 public DataSet RetriveDataSet(string[] sql, params string[] tableName)
168 {
169 int sqlLength;
170 sqlLength = sql.Length;
171 if ( sqlLength == 0)
172 {
173#if DEBUG
174 System.Diagnostics.Debug.WriteLine("sql 为空");
175#endif
176 return null;
177 }
178 using(sqlConnection = this.GetSqlConnection())
179 {
180 if (sqlConnection == null)
181 return null;
182 DataSet ds = new DataSet();
183 int tableNameLength = tableName.Length;
184 for (int i = 0; i < sqlLength; i++)
185 {
186 using(SqlDataAdapter da = new SqlDataAdapter(sql[i], sqlConnection))
187 {
188 try
189 {
190 if (i < tableNameLength)
191 da.Fill(ds, tableName[i]);
192 else
193 da.Fill(ds, "table" + i);
194 }
195 catch (Exception ex)
196 {
197#if DEBUG
198 System.Diagnostics.Debug.WriteLine(ex.ToString());
199#endif
200 return null;
201 }
202 }
203 }
204 return ds;
205 }
206 }
207
208 public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet)
209 {
210 return null;
211 }
212
213 public void Dispose()
214 {
215 this.connectionString = null;
216 this.sqlCommand.Dispose();
217 this.sqlConnection.Dispose();
218 }
219
220 }
221}
222
223OdbcDataProvider.cs 提供ODBC连接访问的类
224
225#define DEBUG
226using System;
227using System.Data;
228using System.Data.Odbc;
229namespace FenceKing.DataProviders
230{
231 /// <summary>
232 /// OdbcDataProvider 的摘要说明。
233 /// </summary>
234 internal class OdbcDataProvider : IDataProvider
235 {
236 private System.Data.Odbc.OdbcConnection odbcConnection;
237 private System.Data.Odbc.OdbcCommand odbcCommand;
238 private string connectionString;
239 public OdbcDataProvider() : this(null)
240 {
241 //
242 // TODO: 在此处添加构造函数逻辑
243 //
244 }
245
246 public OdbcDataProvider(string connectionString)
247 {
248 if (connectionString == null || connectionString.Trim() == string.Empty)
249 {
250 System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
251 this.connectionString = (string)(configurationAppSettings.GetValue("odbcConnectionString", typeof(string)));
252 }
253 else
254 {
255 this.connectionString = connectionString;
256 }
257 }
258
259 /// <summary>
260 /// Oracle 连接字符串 "User Id=southfence;Data Source=FENCEORA;Password=southfence;Persist Security Info=true;"
261 /// </summary>
262 public string ConnectionString
263 {
264 get
265 {
266 return this.connectionString;
267 }
268 set
269 {
270 this.connectionString = value;
271 }
272 }
273
274 /// <summary>
275 /// 返回一个带有连接字符串的Odbc Connection.
276 /// </summary>
277 /// <returns>OracleConnection</returns>
278 private OdbcConnection GetOdbcConnection()
279 {
280 try
281 {
282 return new OdbcConnection(this.connectionString);
283 }
284 catch (Exception ex)
285 {
286#if DEBUG
287 System.Diagnostics.Debug.WriteLine(ex.ToString());
288#endif
289 return null;
290 }
291 }
292
293 /// <summary>
294 /// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1
295 /// </summary>
296 /// <param name="Sql">UPDATE、INSERT 和 DELETE 语句</param>
297 public int ExecuteNonQuery(string sql)
298 {
299 using(odbcConnection = this.GetOdbcConnection())
300 {
301 if (odbcConnection == null)
302 return -1;
303 try
304 {
305 if (odbcConnection.State == System.Data.ConnectionState.Closed)
306 odbcConnection.Open();
307 odbcCommand = new OdbcCommand(sql, odbcConnection);
308 return odbcCommand.ExecuteNonQuery();
309 }
310 catch (Exception ex)
311 {
312#if DEBUG
313 System.Diagnostics.Debug.WriteLine(ex.ToString());
314#endif
315 return -1;
316 }
317 }
318 }
319
320 /// <summary>
321 /// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
322 /// </summary>
323 /// <param name="sql">SELECT 语句</param>
324 /// <returns>.NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用</returns>
325 public object ExecuteScalar(string sql)
326 {
327 using(odbcConnection = this.GetOdbcConnection())
328 {
329 if (odbcConnection == null)
330 return null;
331 try
332 {
333 if (odbcConnection.State == System.Data.ConnectionState.Closed)
334 odbcConnection.Open();
335 odbcCommand = new OdbcCommand(sql, odbcConnection);
336 return odbcCommand.ExecuteScalar();
337 }
338 catch (Exception ex)
339 {
340#if DEBUG
341 System.Diagnostics.Debug.WriteLine(ex.ToString());
342#endif
343 return null;
344 }
345 }
346 }
347
348 /// <summary>
349 /// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回
350 /// </summary>
351 /// <param name="selectSql">SELECT 语句</param>
352 /// <returns>数据集 DataSet</returns>
353 public DataSet RetriveDataSet(string sql)
354 {
355 if (sql == null || sql == string.Empty)
356 {
357#if DEBUG
358 System.Diagnostics.Debug.WriteLine("sql 为空");
359#endif
360 return null;
361 }
362 using(odbcConnection = this.GetOdbcConnection())
363 {
364 if (odbcConnection == null)
365 return null;
366 using( OdbcDataAdapter da = new OdbcDataAdapter(sql, odbcConnection))
367 {
368 DataSet ds = new DataSet();
369 try
370 {
371 da.Fill(ds);
372 }
373 catch (Exception ex)
374 {
375#if DEBUG
376 System.Diagnostics.Debug.WriteLine(ex.ToString());
377#endif
378 return null;
379 }
380 return ds;
381 }
382 }
383 }
384
385
386 /// <summary>
387 /// 执行Sql数组语句查询，并将查询返回的结果作为一个数据集返回
388 /// </summary>
389 /// <param name="sql">Select 语句数组</param>
390 /// <param name="tableName">TableName</param>
391 /// <returns>数据集 DataSet</returns>
392 public DataSet RetriveDataSet(string[] sql, params string[] tableName)
393 {
394 int sqlLength;
395 sqlLength = sql.Length;
396 if ( sqlLength == 0)
397 {
398#if DEBUG
399 System.Diagnostics.Debug.WriteLine("sql 为空");
400#endif
401 return null;
402 }
403 using(odbcConnection = this.GetOdbcConnection())
404 {
405 if (odbcConnection == null)
406 return null;
407 DataSet ds = new DataSet();
408 int tableNameLength = tableName.Length;
409 for (int i = 0; i < sqlLength; i++)
410 {
411 using(OdbcDataAdapter da = new OdbcDataAdapter(sql[i], odbcConnection))
412 {
413 try
414 {
415 if (i < tableNameLength)
416 da.Fill(ds, tableName[i]);
417 else
418 da.Fill(ds, "table" + i);
419 }
420 catch (Exception ex)
421 {
422#if DEBUG
423 System.Diagnostics.Debug.WriteLine(ex.ToString());
424#endif
425 }
426 }
427 }
428 return ds;
429 }
430 }
431
432 public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet)
433 {
434 return null;
435 }
436
437
438 public void Dispose()
439 {
440 this.connectionString = null;
441 this.odbcCommand.Dispose();
442 this.odbcConnection.Dispose();
443 }
444 }
445}
#define DEBUG 2
using System; 3
using System.Data; 4
using System.Data.SqlClient; 5
6
namespace DataProviders 7
{ 8
/// <summary> 9
/// SqlDataProvider 的摘要说明。 10
/// </summary> 11
internal class SqlDataProvider : IDataProvider 12
{ 13
private System.Data.SqlClient.SqlConnection sqlConnection; 14
private System.Data.SqlClient.SqlCommand sqlCommand; 15
private string connectionString; 16
public SqlDataProvider() : this(null) 17
{ 18
// 19
// TODO: 在此处添加构造函数逻辑 20
// 21
} 22
23
public SqlDataProvider(string connectionString) 24
{ 25
if (connectionString == null || connectionString.Trim() == string.Empty) 26
{ 27
System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader(); 28
this.connectionString = (string)(configurationAppSettings.GetValue("sqlConnectionString", typeof(string))); 29
} 30
else 31
{ 32
this.connectionString = connectionString; 33
} 34
} 35
36
/// <summary> 37
/// SQL 连接字符串 38
/// </summary> 39
public string ConnectionString 40
{ 41
get 42
{ 43
return this.connectionString; 44
} 45
set 46
{ 47
this.connectionString = value; 48
} 49
} 50
51
/// <summary> 52
/// 返回一个带有连接字符串的SQL Connection. 53
/// </summary> 54
/// <returns>OracleConnection</returns> 55
private SqlConnection GetSqlConnection() 56
{ 57
try 58
{ 59
return new SqlConnection(this.connectionString); 60
} 61
catch (Exception ex) 62
{ 63
#if DEBUG 64
System.Diagnostics.Debug.WriteLine(ex.ToString()); 65
#endif 66
return null; 67
} 68
} 69
70
/// <summary> 71
/// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1 72
/// </summary> 73
/// <param name="Sql">UPDATE、INSERT 和 DELETE 语句</param> 74
public int ExecuteNonQuery(string sql) 75
{ 76
using(sqlConnection = this.GetSqlConnection()) 77
{ 78
if (sqlConnection == null) 79
return -1; 80
try 81
{ 82
if (sqlConnection.State == System.Data.ConnectionState.Closed) 83
sqlConnection.Open(); 84
sqlCommand = new SqlCommand(sql, sqlConnection); 85
return sqlCommand.ExecuteNonQuery(); 86
} 87
catch (Exception ex) 88
{ 89
#if DEBUG 90
System.Diagnostics.Debug.WriteLine(ex.ToString()); 91
#endif 92
return -1; 93
} 94
} 95
} 96
97
/// <summary> 98
/// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。 99
/// </summary> 100
/// <param name="sql">SELECT 语句</param> 101
/// <returns>.NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用</returns> 102
public object ExecuteScalar(string sql) 103
{ 104
using(sqlConnection = this.GetSqlConnection()) 105
{ 106
if (sqlConnection == null) 107
return null; 108
try 109
{ 110
if (sqlConnection.State == System.Data.ConnectionState.Closed) 111
sqlConnection.Open(); 112
sqlCommand = new SqlCommand(sql, sqlConnection); 113
return sqlCommand.ExecuteScalar(); 114
} 115
catch (Exception ex) 116
{ 117
#if DEBUG 118
System.Diagnostics.Debug.WriteLine(ex.ToString()); 119
#endif 120
return null; 121
} 122
} 123
} 124
125
/// <summary> 126
/// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回 127
/// </summary> 128
/// <param name="sql">SELECT 语句</param> 129
/// <returns>数据集 DataSet</returns> 130
public DataSet RetriveDataSet(string sql) 131
{ 132
if (sql == null || sql == string.Empty) 133
{ 134
#if DEBUG 135
System.Diagnostics.Debug.WriteLine("sql 为空"); 136
#endif 137
return null; 138
} 139
using(sqlConnection = this.GetSqlConnection()) 140
{ 141
if (sqlConnection == null) 142
return null; 143
using(SqlDataAdapter da = new SqlDataAdapter(sql, sqlConnection)) 144
{ 145
DataSet ds = new DataSet(); 146
try 147
{ 148
da.Fill(ds); 149
} 150
catch (Exception ex) 151
{ 152
#if DEBUG 153
System.Diagnostics.Debug.WriteLine(ex.ToString()); 154
#endif 155
} 156
return ds; 157
} 158
} 159
} 160
161
/// <summary> 162
/// 执行Sql数组语句查询，并将查询返回的结果作为一个数据集返回 163
/// </summary> 164
/// <param name="sql">Select 语句数组</param> 165
/// <param name="tableName">TableName</param> 166
/// <returns>数据集 DataSet</returns> 167
public DataSet RetriveDataSet(string[] sql, params string[] tableName) 168
{ 169
int sqlLength; 170
sqlLength = sql.Length; 171
if ( sqlLength == 0) 172
{ 173
#if DEBUG 174
System.Diagnostics.Debug.WriteLine("sql 为空"); 175
#endif 176
return null; 177
} 178
using(sqlConnection = this.GetSqlConnection()) 179
{ 180
if (sqlConnection == null) 181
return null; 182
DataSet ds = new DataSet(); 183
int tableNameLength = tableName.Length; 184
for (int i = 0; i < sqlLength; i++) 185
{ 186
using(SqlDataAdapter da = new SqlDataAdapter(sql[i], sqlConnection)) 187
{ 188
try 189
{ 190
if (i < tableNameLength) 191
da.Fill(ds, tableName[i]); 192
else 193
da.Fill(ds, "table" + i); 194
} 195
catch (Exception ex) 196
{ 197
#if DEBUG 198
System.Diagnostics.Debug.WriteLine(ex.ToString()); 199
#endif 200
return null; 201
} 202
} 203
} 204
return ds; 205
} 206
} 207
208
public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet) 209
{ 210
return null; 211
} 212
213
public void Dispose() 214
{ 215
this.connectionString = null; 216
this.sqlCommand.Dispose(); 217
this.sqlConnection.Dispose(); 218
} 219
220
} 221
} 222
223
OdbcDataProvider.cs 提供ODBC连接访问的类 224
225
#define DEBUG 226
using System; 227
using System.Data; 228
using System.Data.Odbc; 229
namespace FenceKing.DataProviders 230
{ 231
/// <summary> 232
/// OdbcDataProvider 的摘要说明。 233
/// </summary> 234
internal class OdbcDataProvider : IDataProvider 235
{ 236
private System.Data.Odbc.OdbcConnection odbcConnection; 237
private System.Data.Odbc.OdbcCommand odbcCommand; 238
private string connectionString; 239
public OdbcDataProvider() : this(null) 240
{ 241
// 242
// TODO: 在此处添加构造函数逻辑 243
// 244
} 245
246
public OdbcDataProvider(string connectionString) 247
{ 248
if (connectionString == null || connectionString.Trim() == string.Empty) 249
{ 250
System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader(); 251
this.connectionString = (string)(configurationAppSettings.GetValue("odbcConnectionString", typeof(string))); 252
} 253
else 254
{ 255
this.connectionString = connectionString; 256
} 257
} 258
259
/// <summary> 260
/// Oracle 连接字符串 "User Id=southfence;Data Source=FENCEORA;Password=southfence;Persist Security Info=true;" 261
/// </summary> 262
public string ConnectionString 263
{ 264
get 265
{ 266
return this.connectionString; 267
} 268
set 269
{ 270
this.connectionString = value; 271
} 272
} 273
274
/// <summary> 275
/// 返回一个带有连接字符串的Odbc Connection. 276
/// </summary> 277
/// <returns>OracleConnection</returns> 278
private OdbcConnection GetOdbcConnection() 279
{ 280
try 281
{ 282
return new OdbcConnection(this.connectionString); 283
} 284
catch (Exception ex) 285
{ 286
#if DEBUG 287
System.Diagnostics.Debug.WriteLine(ex.ToString()); 288
#endif 289
return null; 290
} 291
} 292
293
/// <summary> 294
/// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1 295
/// </summary> 296
/// <param name="Sql">UPDATE、INSERT 和 DELETE 语句</param> 297
public int ExecuteNonQuery(string sql) 298
{ 299
using(odbcConnection = this.GetOdbcConnection()) 300
{ 301
if (odbcConnection == null) 302
return -1; 303
try 304
{ 305
if (odbcConnection.State == System.Data.ConnectionState.Closed) 306
odbcConnection.Open(); 307
odbcCommand = new OdbcCommand(sql, odbcConnection); 308
return odbcCommand.ExecuteNonQuery(); 309
} 310
catch (Exception ex) 311
{ 312
#if DEBUG 313
System.Diagnostics.Debug.WriteLine(ex.ToString()); 314
#endif 315
return -1; 316
} 317
} 318
} 319
320
/// <summary> 321
/// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。 322
/// </summary> 323
/// <param name="sql">SELECT 语句</param> 324
/// <returns>.NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用</returns> 325
public object ExecuteScalar(string sql) 326
{ 327
using(odbcConnection = this.GetOdbcConnection()) 328
{ 329
if (odbcConnection == null) 330
return null; 331
try 332
{ 333
if (odbcConnection.State == System.Data.ConnectionState.Closed) 334
odbcConnection.Open(); 335
odbcCommand = new OdbcCommand(sql, odbcConnection); 336
return odbcCommand.ExecuteScalar(); 337
} 338
catch (Exception ex) 339
{ 340
#if DEBUG 341
System.Diagnostics.Debug.WriteLine(ex.ToString()); 342
#endif 343
return null; 344
} 345
} 346
} 347
348
/// <summary> 349
/// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回 350
/// </summary> 351
/// <param name="selectSql">SELECT 语句</param> 352
/// <returns>数据集 DataSet</returns> 353
public DataSet RetriveDataSet(string sql) 354
{ 355
if (sql == null || sql == string.Empty) 356
{ 357
#if DEBUG 358
System.Diagnostics.Debug.WriteLine("sql 为空"); 359
#endif 360
return null; 361
} 362
using(odbcConnection = this.GetOdbcConnection()) 363
{ 364
if (odbcConnection == null) 365
return null; 366
using( OdbcDataAdapter da = new OdbcDataAdapter(sql, odbcConnection)) 367
{ 368
DataSet ds = new DataSet(); 369
try 370
{ 371
da.Fill(ds); 372
} 373
catch (Exception ex) 374
{ 375
#if DEBUG 376
System.Diagnostics.Debug.WriteLine(ex.ToString()); 377
#endif 378
return null; 379
} 380
return ds; 381
} 382
} 383
} 384
385
386
/// <summary> 387
/// 执行Sql数组语句查询，并将查询返回的结果作为一个数据集返回 388
/// </summary> 389
/// <param name="sql">Select 语句数组</param> 390
/// <param name="tableName">TableName</param> 391
/// <returns>数据集 DataSet</returns> 392
public DataSet RetriveDataSet(string[] sql, params string[] tableName) 393
{ 394
int sqlLength; 395
sqlLength = sql.Length; 396
if ( sqlLength == 0) 397
{ 398
#if DEBUG 399
System.Diagnostics.Debug.WriteLine("sql 为空"); 400
#endif 401
return null; 402
} 403
using(odbcConnection = this.GetOdbcConnection()) 404
{ 405
if (odbcConnection == null) 406
return null; 407
DataSet ds = new DataSet(); 408
int tableNameLength = tableName.Length; 409
for (int i = 0; i < sqlLength; i++) 410
{ 411
using(OdbcDataAdapter da = new OdbcDataAdapter(sql[i], odbcConnection)) 412
{ 413
try 414
{ 415
if (i < tableNameLength) 416
da.Fill(ds, tableName[i]); 417
else 418
da.Fill(ds, "table" + i); 419
} 420
catch (Exception ex) 421
{ 422
#if DEBUG 423
System.Diagnostics.Debug.WriteLine(ex.ToString()); 424
#endif 425
} 426
} 427
} 428
return ds; 429
} 430
} 431
432
public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet) 433
{ 434
return null; 435
} 436
437
438
public void Dispose() 439
{ 440
this.connectionString = null; 441
this.odbcCommand.Dispose(); 442
this.odbcConnection.Dispose(); 443
} 444
} 445
} OleDbDataProvider.cs 提供OLEDB连接访问的类
1#define DEBUG
2using System;
3using System.Data;
4using System.Data.OleDb;
5
6namespace DataProviders
7{
8 ///
9 /// OleDbDataProvider 的摘要说明。
10 ///
11 internal class OleDbDataProvider : IDataProvider
12 {
13
14 private System.Data.OleDb.OleDbConnection oleDbConnection;
15 private System.Data.OleDb.OleDbCommand oleDbCommand;
16 private string connectionString;
17 public OleDbDataProvider() : this(null)
18 {
19 //
20 // TODO: 在此处添加构造函数逻辑
21 //
22 }
23
24 public OleDbDataProvider(string connectionString)
25 {
26 if (connectionString == null || connectionString.Trim() == string.Empty)
27 {
28 System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
29 this.connectionString = (string)(configurationAppSettings.GetValue("oleDbConnectionString", typeof(string)));
30 }
31 else
32 {
33 this.connectionString = connectionString;
34 }
35 }
36
37 ///
38 /// OleDb 连接字符串
39 ///
40 public string ConnectionString
41 {
42 get
43 {
44 return this.connectionString;
45 }
46 set
47 {
48 this.connectionString = value;
49 }
50 }
51
52 ///
53 /// 返回一个带有连接字符串的OleDb Connection.
54 ///
55 /// OracleConnection
56 private OleDbConnection GetOleDbConnection()
57 {
58 try
59 {
60 return new OleDbConnection(this.connectionString);
61 }
62 catch (Exception ex)
63 {
64#if DEBUG
65 System.Diagnostics.Debug.WriteLine(ex.ToString());
66#endif
67 return null;
68 }
69 }
70
71 ///
72 /// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1
73 ///
74 /// UPDATE、INSERT 和 DELETE 语句
75 public int ExecuteNonQuery(string sql)
76 {
77 using(oleDbConnection = this.GetOleDbConnection())
78 {
79 if (oleDbConnection == null)
80 return -1;
81 try
82 {
83 if (oleDbConnection.State == System.Data.ConnectionState.Closed)
84 oleDbConnection.Open();
85 oleDbCommand = new OleDbCommand(sql, oleDbConnection);
86 return oleDbCommand.ExecuteNonQuery();
87 }
88 catch (Exception ex)
89 {
90#if DEBUG
91 System.Diagnostics.Debug.WriteLine(ex.ToString());
92#endif
93 return -1;
94 }
95 }
96 }
97
98 ///
99 /// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
100 ///
101 /// SELECT 语句
102 /// .NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用
103 public object ExecuteScalar(string sql)
104 {
105 using(oleDbConnection = this.GetOleDbConnection())
106 {
107 if (oleDbConnection == null)
108 return null;
109 try
110 {
111 if (oleDbConnection.State == System.Data.ConnectionState.Closed)
112 oleDbConnection.Open();
113 oleDbCommand = new OleDbCommand(sql, oleDbConnection);
114 return oleDbCommand.ExecuteScalar();
115 }
116 catch (Exception ex)
117 {
118#if DEBUG
119 System.Diagnostics.Debug.WriteLine(ex.ToString());
120#endif
121 return null;
122 }
123 }
124 }
125
126 ///
127 /// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回
128 ///
129 /// SELECT 语句
130 /// 数据集 DataSet
131 public DataSet RetriveDataSet(string sql)
132 {
133 if (sql == null || sql == string.Empty)
134 {
135#if DEBUG
136 System.Diagnostics.Debug.WriteLine("sql 为空");
137#endif
138 return null;
139 }
140 using(oleDbConnection = this.GetOleDbConnection())
141 {
142 if (oleDbConnection == null)
143 return null;
144 using(OleDbDataAdapter da = new OleDbDataAdapter(sql, oleDbConnection))
145 {
146 DataSet ds = new DataSet();
147 try
148 {
149 da.Fill(ds);
150 }
151 catch (Exception ex)
152 {
153#if DEBUG
154 System.Diagnostics.Debug.WriteLine(ex.ToString());
155#endif
156 }
157 return ds;
158 }
159 }
160 }
161
162
163 ///
164 /// 执行Sql数组语句查询，并将查询返回的结果作为一个数据集返回
165 ///
166 /// Select 语句数组
167 /// TableName
168 /// 数据集 DataSet
169 public DataSet RetriveDataSet(string[] sql, params string[] tableName)
170 {
171 int sqlLength;
172 sqlLength = sql.Length;
173 if ( sqlLength == 0)
174 {
175#if DEBUG
176 System.Diagnostics.Debug.WriteLine("sql 为空");
177#endif
178 return null;
179 }
180 using(oleDbConnection = this.GetOleDbConnection())
181 {
182 if (oleDbConnection == null)
183 return null;
184 DataSet ds = new DataSet();
185 int tableNameLength = tableName.Length;
186 for (int i = 0; i < sqlLength; i++)
187 {
188 using(OleDbDataAdapter da = new OleDbDataAdapter(sql[i], oleDbConnection))
189 {
190 try
191 {
192 if (i < tableNameLength)
193 da.Fill(ds, tableName[i]);
194 else
195 da.Fill(ds, "table" + i);
196 }
197 catch (Exception ex)
198 {
199#if DEBUG
200 System.Diagnostics.Debug.WriteLine(ex.ToString());
201#endif
202 return null;
203 }
204 }
205 }
206 return ds;
207 }
208 }
209
210 public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet)
211 {
212 return null;
213 }
214
215 public void Dispose()
216 {
217 this.connectionString = null;
218 this.oleDbCommand.Dispose();
219 this.oleDbConnection.Dispose();
220 }
221 }
222}
#define DEBUG 2
using System; 3
using System.Data; 4
using System.Data.OleDb; 5
6
namespace DataProviders 7
{ 8
/// 9
/// OleDbDataProvider 的摘要说明。 10
/// 11
internal class OleDbDataProvider : IDataProvider 12
{ 13
14
private System.Data.OleDb.OleDbConnection oleDbConnection; 15
private System.Data.OleDb.OleDbCommand oleDbCommand; 16
private string connectionString; 17
public OleDbDataProvider() : this(null) 18
{ 19
// 20
// TODO: 在此处添加构造函数逻辑 21
// 22
} 23
24
public OleDbDataProvider(string connectionString) 25
{ 26
if (connectionString == null || connectionString.Trim() == string.Empty) 27
{ 28
System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader(); 29
this.connectionString = (string)(configurationAppSettings.GetValue("oleDbConnectionString", typeof(string))); 30
} 31
else 32
{ 33
this.connectionString = connectionString; 34
} 35
} 36
37
/// 38
/// OleDb 连接字符串 39
/// 40
public string ConnectionString 41
{ 42
get 43
{ 44
return this.connectionString; 45
} 46
set 47
{ 48
this.connectionString = value; 49
} 50
} 51
52
/// 53
/// 返回一个带有连接字符串的OleDb Connection. 54
/// 55
/// OracleConnection 56
private OleDbConnection GetOleDbConnection() 57
{ 58
try 59
{ 60
return new OleDbConnection(this.connectionString); 61
} 62
catch (Exception ex) 63
{ 64
#if DEBUG 65
System.Diagnostics.Debug.WriteLine(ex.ToString()); 66
#endif 67
return null; 68
} 69
} 70
71
/// 72
/// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1 73
/// 74
/// UPDATE、INSERT 和 DELETE 语句 75
public int ExecuteNonQuery(string sql) 76
{ 77
using(oleDbConnection = this.GetOleDbConnection()) 78
{ 79
if (oleDbConnection == null) 80
return -1; 81
try 82
{ 83
if (oleDbConnection.State == System.Data.ConnectionState.Closed) 84
oleDbConnection.Open(); 85
oleDbCommand = new OleDbCommand(sql, oleDbConnection); 86
return oleDbCommand.ExecuteNonQuery(); 87
} 88
catch (Exception ex) 89
{ 90
#if DEBUG 91
System.Diagnostics.Debug.WriteLine(ex.ToString()); 92
#endif 93
return -1; 94
} 95
} 96
} 97
98
/// 99
/// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。 100
/// 101
/// SELECT 语句 102
/// .NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用 103
public object ExecuteScalar(string sql) 104
{ 105
using(oleDbConnection = this.GetOleDbConnection()) 106
{ 107
if (oleDbConnection == null) 108
return null; 109
try 110
{ 111
if (oleDbConnection.State == System.Data.ConnectionState.Closed) 112
oleDbConnection.Open(); 113
oleDbCommand = new OleDbCommand(sql, oleDbConnection); 114
return oleDbCommand.ExecuteScalar(); 115
} 116
catch (Exception ex) 117
{ 118
#if DEBUG 119
System.Diagnostics.Debug.WriteLine(ex.ToString()); 120
#endif 121
return null; 122
} 123
} 124
} 125
126
/// 127
/// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回 128
/// 129
/// SELECT 语句 130
/// 数据集 DataSet 131
public DataSet RetriveDataSet(string sql) 132
{ 133
if (sql == null || sql == string.Empty) 134
{ 135
#if DEBUG 136
System.Diagnostics.Debug.WriteLine("sql 为空"); 137
#endif 138
return null; 139
} 140
using(oleDbConnection = this.GetOleDbConnection()) 141
{ 142
if (oleDbConnection == null) 143
return null; 144
using(OleDbDataAdapter da = new OleDbDataAdapter(sql, oleDbConnection)) 145
{ 146
DataSet ds = new DataSet(); 147
try 148
{ 149
da.Fill(ds); 150
} 151
catch (Exception ex) 152
{ 153
#if DEBUG 154
System.Diagnostics.Debug.WriteLine(ex.ToString()); 155
#endif 156
} 157
return ds; 158
} 159
} 160
} 161
162
163
/// 164
/// 执行Sql数组语句查询，并将查询返回的结果作为一个数据集返回 165
/// 166
/// Select 语句数组 167
/// TableName 168
/// 数据集 DataSet 169
public DataSet RetriveDataSet(string[] sql, params string[] tableName) 170
{ 171
int sqlLength; 172
sqlLength = sql.Length; 173
if ( sqlLength == 0) 174
{ 175
#if DEBUG 176
System.Diagnostics.Debug.WriteLine("sql 为空"); 177
#endif 178
return null; 179
} 180
using(oleDbConnection = this.GetOleDbConnection()) 181
{ 182
if (oleDbConnection == null) 183
return null; 184
DataSet ds = new DataSet(); 185
int tableNameLength = tableName.Length; 186
for (int i = 0; i < sqlLength; i++) 187
{ 188
using(OleDbDataAdapter da = new OleDbDataAdapter(sql[i], oleDbConnection)) 189
{ 190
try 191
{ 192
if (i < tableNameLength) 193
da.Fill(ds, tableName[i]); 194
else 195
da.Fill(ds, "table" + i); 196
} 197
catch (Exception ex) 198
{ 199
#if DEBUG 200
System.Diagnostics.Debug.WriteLine(ex.ToString()); 201
#endif 202
return null; 203
} 204
} 205
} 206
return ds; 207
} 208
} 209
210
public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet) 211
{ 212
return null; 213
} 214
215
public void Dispose() 216
{ 217
this.connectionString = null; 218
this.oleDbCommand.Dispose(); 219
this.oleDbConnection.Dispose(); 220
} 221
} 222
} App.config 应用程序配置文件
xml 代码
有了以上这几个类，对访问各种数据库会变得比较方便和易于管理代码，配置文件也可以随时更改，但是缺点在于配置文件使用System.Configuration只能读取，而无法在程序中改写，而把它当作xml来处理又稍显复杂，不过好在还可以用系统API调用，将属性用ini文件存储，效率高而且使用也比较简单。
1xml version="1.0" encoding="utf-8"?>
2<configuration>
3 <appSettings>
4 <add key="oracleConnectionString" value="Data Source=myoracledb;User ID=me;Password=mypwd;Persist Security Info=true;" />
5 <add key="sqlConnectionString" value="uid=sa;pwd=;initial catalog=mymsdb;data source=127.0.0.1;Connect Timeout=900" />
6 <add key="oleDbConnectionString" value="" />
7 <add key="odbcConnectionString" value="" />
8 appSettings>
9configuration>
xml version="1.0" encoding="utf-8"?> 2
<configuration> 3
<appSettings> 4
<add key="oracleConnectionString" value="Data Source=myoracledb;User ID=me;Password=mypwd;Persist Security Info=true;" /> 5
<add key="sqlConnectionString" value="uid=sa;pwd=;initial catalog=mymsdb;data source=127.0.0.1;Connect Timeout=900" /> 6
<add key="oleDbConnectionString" value="" /> 7
<add key="odbcConnectionString" value="" /> 8
appSettings> 9
configuration>
一部个人杂志
