发布个人版的C#数据库操作类源码
一直以来，我都是习惯用Vb.net来开发网站。但最近与俺相处了3年的同事调走了，部分的项目由我来接管，他较擅长C#,因此近段时间恶补C#的东西。以前我写过几个Vb.net版的数据库访问类——其实我觉得叫模块更为合适，前天试着把它转为C#版。不妥之处望大家能提出改进意见，具体原码如下：
1
/// <summary>
2
/// SqlDbAccessVCsharp 的摘要说明。
3
/// </summary>
4
public class SqlDbAccessVCsharp
5 {
6
7
/// <summary>
8 /// _ConectionString 局部变量：连接字符串。
9
/// </summary>
10 private string _ConectionString;
11 /// <summary>
12 /// _QuryValue 局部变量：查询结果，数据类型32位整型数。
13 /// </summary>
14 private Int32 _IntQuryValue;
15 /// <summary>
16 /// SqlConnection局部变量：查询结果，数据类型字符串。
17 /// </summary>
18 private String _StrQuryValue;
19 /// <summary>
20 /// SqlConnection 局部变量：数据库连接。
21 /// </summary>
22 private SqlConnection CurrentConnection=new SqlConnection();
23 /// <summary>
24 /// SqlCmd 局部变量：SqlCommand。
25 /// </summary>
26 private SqlCommand SqlCmd ;
27 /// <summary>
28 /// MyDataSet 局部变量：DataSet。
29 /// </summary>
30 private DataSet MyDataSet=new DataSet();
31 /// <summary>
32 /// MySqlDataReader 局部变量：SqlDataReader。
33 /// </summary>
34 private SqlDataReader MySqlDataReader;
35 /// <summary>
36 /// MySqlDataAdapter 局部变量：SqlDataAdapter。
37 /// </summary>
38 private SqlDataAdapter MySqlDataAdapter;
39 /// <summary>
40 /// _CurrentPageCode 局部变量：String。
41 /// </summary>
42 private int _CurrentPageCode;
43 /// <summary>
44 /// ConectionString 属性：连接字符串。
45 /// </summary>
46 public string ConectionString
47 {
48 get{
49 return _ConectionString;
50 }
51 set{
52 _ConectionString=value;
53 }
54 }
55 /// <summary>
56 /// IntQuryValue 属性：整型查询结果。
57 /// </summary>
58 public Int32 IntQuryValue
59 {
60 get
61 {
62 return _IntQuryValue;
63 }
64 set
65 {
66 _IntQuryValue=value;
67 }
68 }
69 /// <summary>
70 /// StrQuryValue 属性：字符串查询结果。
71 /// </summary>
72 public string StrQuryValue
73 {
74 get
75 {
76 return _StrQuryValue;
77 }
78 set
79 {
80 _StrQuryValue=value;
81 }
82 }
83 /// <summary>
84 /// CurrentPageCode 属性：当前页。
85 /// </summary>
86 public int CurrentPageCode
87 {
88 get
89 {
90 return _CurrentPageCode;
91 }
92 set
93 {
94 _CurrentPageCode=value;
95 }
96 }
97 /// <summary>
98 /// ConnectToDb 打开数据库连接。
99 /// </summary>
100 /// <paramref name="Errmsg">
101 /// 返回值：错误信息。
102 /// </paramref>
103 public bool ConnectToDb(ref string ErrMsg)
104 {
105 try
106
107 {
108
109 if ( ( CurrentConnection.State== ConnectionState.Broken)||( CurrentConnection.State== ConnectionState.Closed))
110 {
111 CurrentConnection.ConnectionString =ConectionString;
112 CurrentConnection.Open();
113 }
114 return true;
115 }
116 catch (SqlException e)
117 {
118 ErrMsg = e.ToString();
119 ErrMsg = ErrMsg.Replace("\r", "");
120 ErrMsg = ErrMsg.Replace("\n", "");
121 return false;
122 }
123 }
124 /// <summary>
125 /// CloseDb 关闭数据库连接。
126 /// </summary>
127 /// <paramref name="Errmsg">
128 /// 返回值：错误信息。
129 /// </paramref>
130 public bool CloseDb(ref string ErrMsg)
131 {
132 try
133
134 {
135 if ( ( CurrentConnection.State!= ConnectionState.Broken) &&( CurrentConnection.State!= ConnectionState.Closed))
136 {
137 CurrentConnection.Close();
138 CurrentConnection.Dispose();
139 }
140 return true;
141 }
142 catch (SqlException e)
143 {
144 ErrMsg = e.ToString();
145 ErrMsg = ErrMsg.Replace("\r", "");
146 ErrMsg = ErrMsg.Replace("\n", "");
147 return false;
148 }
149 }
150 /// <summary>
151 /// ExecuteSql 执行Sql命令。
152 /// </summary>
153 /// <param name="SqlStr">
154 /// 传入参数:Sql语句
155 /// </param>
156 /// <paramref name="Errmg">
157 /// 引用参数：错误信息
158 /// </paramref>
159 public bool ExecuteSql(string SqlStr,ref string ErrMsg)
160 {
161
162
163 try
164 {
165 if (ConnectToDb(ref ErrMsg) == true)
166 {
167 SqlCmd=new SqlCommand(SqlStr,CurrentConnection);
168 SqlCmd.ExecuteNonQuery();
169 SqlCmd.Dispose();
170 CloseDb(ref ErrMsg);
171 return true;
172 }
173 else
174 return false;
175 }
176 catch (SqlException e)
177 {
178 ErrMsg = e.ToString();
179 ErrMsg = ErrMsg.Replace("\r", "");
180 ErrMsg = ErrMsg.Replace("\n", "");
181 return false;
182 }
183
184
185 }
186 /// <summary>
187 /// QuerySingleIntValue 查询出一个单值。
188 /// </summary>
189 /// <param name="SqlStr">
190 /// 传入参数:Sql语句。
191 /// </param>
192 /// <paramref name="Errmg">
193 /// 引用参数：错误信息。
194 /// </paramref>
195 public bool QuerySingleIntValue(string SqlStr,ref string ErrMsg)
196 {
197
198 try
199 {
200 if (ConnectToDb(ref ErrMsg) == true)
201 {
202 SqlCmd=new SqlCommand(SqlStr,CurrentConnection);
203 MySqlDataReader=SqlCmd.ExecuteReader();
204 while(MySqlDataReader.Read())
205 {
206 IntQuryValue =Convert.ToInt32(MySqlDataReader[0]);
207 }
208 MySqlDataReader.Close();
209 SqlCmd.Dispose();
210 CloseDb(ref ErrMsg);
211 return true;
212 }
213 return false;
214 }
215 catch (SqlException e)
216 {
217 ErrMsg = e.ToString();
218 ErrMsg = ErrMsg.Replace("\r", "");
219 ErrMsg = ErrMsg.Replace("\n", "");
220 return false;
221 }
222
223 }
224 /// <summary>
225 /// QuerySingleStrValue 查询出一个单值。
226 /// </summary>
227 /// <param name="SqlStr">
228 /// 传入参数:Sql语句。
229 /// </param>
230 /// <paramref name="Errmg">
231 /// 引用参数：错误信息。
232 /// </paramref>
233 public bool QuerySingleStrValue(string SqlStr,ref string ErrMsg)
234 {
235
236 try
237 {
238 if (ConnectToDb(ref ErrMsg) == true)
239 {
240 SqlCmd=new SqlCommand(SqlStr,CurrentConnection);
241 MySqlDataReader=SqlCmd.ExecuteReader();
242 while(MySqlDataReader.Read())
243 {
244 StrQuryValue = Convert.ToString(MySqlDataReader[0]);
245 }
246 MySqlDataReader.Close();
247 SqlCmd.Dispose();
248 CloseDb(ref ErrMsg);
249 return true;
250 }
251 return false;
252 }
253 catch (SqlException e)
254 {
255 ErrMsg = e.ToString();
256 ErrMsg = ErrMsg.Replace("\r", "");
257 ErrMsg = ErrMsg.Replace("\n", "");
258 return false;
259 }
260
261 }
262 /// <summary>
263 /// MoreValueQuery 查询出多值。
264 /// </summary>
265 /// <param name="SqlStr">
266 /// 查询语句
267 /// </param>
268 /// <param name="FieldCount">
269 /// 读取字段数
270 /// </param>
271 ///<paramref name="ValueArrlist">
272 ///引用参数：查询结果，保存在动态数组当中。
273 ///</paramref>
274 ///<paramref name="ErrMsg">
275 ///引用参数：错误信息。
276 ///</paramref>
277 public bool MoreValueQuery(string SqlStr,int FieldCount,ref ArrayList ValueArrlist,string ErrMsg)
278 {
279
280 try
281 {
282 if (ConnectToDb(ref ErrMsg) == true)
283 {
284
285 SqlCmd=new SqlCommand(SqlStr,CurrentConnection);
286 MySqlDataReader=SqlCmd.ExecuteReader();
287 while(MySqlDataReader.Read())
288 {
289 for (int i = 0; i <= FieldCount-1; i++)
290
291 {
292 if (!MySqlDataReader.IsDBNull(i))
293 {
294 ValueArrlist.Add("无");
295 }
296 else
297 {
298 ValueArrlist.Add(MySqlDataReader.GetString(i));
299 }
300 }
301
302 }
303 MySqlDataReader.Close();
304 SqlCmd.Dispose();
305 CloseDb(ref ErrMsg);
306 return true;
307 }
308 return false;
309 }
310 catch (SqlException e)
311 {
312 ErrMsg = e.ToString();
313 ErrMsg = ErrMsg.Replace("\r", "");
314 ErrMsg = ErrMsg.Replace("\n", "");
315 return false;
316 }
317 }
318 public DataView GetTableData(int PageShow,string SqlStr,string TableName,ref string ErrMsg)
319 {
320 try
321 {
322 if (ConnectToDb(ref ErrMsg) == true)
323 {
324 int StartIndex=0;
325 MySqlDataAdapter=new SqlDataAdapter(SqlStr,CurrentConnection);
326 if (PageShow==0)
327 {
328 MySqlDataAdapter.Fill(MyDataSet,TableName);
329 }
330 else
331 {
332 StartIndex = CurrentPageCode * PageShow;
333 MySqlDataAdapter.Fill(MyDataSet,StartIndex,PageShow,TableName);
334 }
335 DataView MyDataView;//=new DataView();
336 MyDataView = MyDataSet.Tables[TableName].DefaultView;
337
338 SqlCmd.Dispose();
339 CloseDb(ref ErrMsg);
340 return MyDataView;
341 }
342 else
343 return null;
344 }
345 catch (SqlException e)
346 {
347 ErrMsg = e.ToString();
348 ErrMsg = ErrMsg.Replace("\r", "");
349 ErrMsg = ErrMsg.Replace("\n", "");
350 return null;
351 }
352
353 }
354
355 }
/// <summary>
2
/// SqlDbAccessVCsharp 的摘要说明。3
/// </summary>4
public class SqlDbAccessVCsharp5
{
6
7
/// <summary>
8
/// _ConectionString 局部变量：连接字符串。9
/// </summary>10
private string _ConectionString;11
/// <summary>12
/// _QuryValue 局部变量：查询结果，数据类型32位整型数。13
/// </summary>14
private Int32 _IntQuryValue;15
/// <summary>16
/// SqlConnection局部变量：查询结果，数据类型字符串。17
/// </summary>18
private String _StrQuryValue;19
/// <summary>20
/// SqlConnection 局部变量：数据库连接。21
/// </summary>22
private SqlConnection CurrentConnection=new SqlConnection();23
/// <summary>24
/// SqlCmd 局部变量：SqlCommand。25
/// </summary>26
private SqlCommand SqlCmd ;27
/// <summary>28
/// MyDataSet 局部变量：DataSet。29
/// </summary>30
private DataSet MyDataSet=new DataSet();31
/// <summary>32
/// MySqlDataReader 局部变量：SqlDataReader。33
/// </summary>34
private SqlDataReader MySqlDataReader;35
/// <summary>36
/// MySqlDataAdapter 局部变量：SqlDataAdapter。37
/// </summary>38
private SqlDataAdapter MySqlDataAdapter;39
/// <summary>40
/// _CurrentPageCode 局部变量：String。41
/// </summary>42
private int _CurrentPageCode;43
/// <summary>44
/// ConectionString 属性：连接字符串。45
/// </summary> 46
public string ConectionString47
{48
get{49
return _ConectionString;50
}51
set{52
_ConectionString=value;53
}54
}55
/// <summary>56
/// IntQuryValue 属性：整型查询结果。57
/// </summary> 58
public Int32 IntQuryValue59
{60
get61
{62
return _IntQuryValue;63
}64
set65
{66
_IntQuryValue=value;67
} 68
}69
/// <summary>70
/// StrQuryValue 属性：字符串查询结果。71
/// </summary> 72
public string StrQuryValue73
{74
get75
{76
return _StrQuryValue;77
}78
set79
{80
_StrQuryValue=value;81
} 82
}83
/// <summary>84
/// CurrentPageCode 属性：当前页。85
/// </summary> 86
public int CurrentPageCode87
{88
get89
{90
return _CurrentPageCode;91
}92
set93
{94
_CurrentPageCode=value;95
} 96
}97
/// <summary>98
/// ConnectToDb 打开数据库连接。99
/// </summary> 100
/// <paramref name="Errmsg">101
/// 返回值：错误信息。102
/// </paramref> 103
public bool ConnectToDb(ref string ErrMsg)104
{105
try106
107
{108
109
if ( ( CurrentConnection.State== ConnectionState.Broken)||( CurrentConnection.State== ConnectionState.Closed))110
{111
CurrentConnection.ConnectionString =ConectionString;112
CurrentConnection.Open();113
}114
return true;115
}116
catch (SqlException e)117
{118
ErrMsg = e.ToString();119
ErrMsg = ErrMsg.Replace("\r", "");120
ErrMsg = ErrMsg.Replace("\n", "");121
return false;122
}123
}124
/// <summary>125
/// CloseDb 关闭数据库连接。126
/// </summary> 127
/// <paramref name="Errmsg">128
/// 返回值：错误信息。129
/// </paramref> 130
public bool CloseDb(ref string ErrMsg)131
{132
try133
134
{ 135
if ( ( CurrentConnection.State!= ConnectionState.Broken) &&( CurrentConnection.State!= ConnectionState.Closed))136
{ 137
CurrentConnection.Close();138
CurrentConnection.Dispose();139
}140
return true;141
}142
catch (SqlException e)143
{144
ErrMsg = e.ToString();145
ErrMsg = ErrMsg.Replace("\r", "");146
ErrMsg = ErrMsg.Replace("\n", "");147
return false;148
}149
}150
/// <summary>151
/// ExecuteSql 执行Sql命令。152
/// </summary>153
/// <param name="SqlStr">154
/// 传入参数:Sql语句155
/// </param>156
/// <paramref name="Errmg">157
/// 引用参数：错误信息158
/// </paramref>159
public bool ExecuteSql(string SqlStr,ref string ErrMsg)160
{161
162
163
try164
{ 165
if (ConnectToDb(ref ErrMsg) == true)166
{ 167
SqlCmd=new SqlCommand(SqlStr,CurrentConnection);168
SqlCmd.ExecuteNonQuery();169
SqlCmd.Dispose();170
CloseDb(ref ErrMsg);171
return true;172
}173
else174
return false;175
}176
catch (SqlException e)177
{178
ErrMsg = e.ToString();179
ErrMsg = ErrMsg.Replace("\r", "");180
ErrMsg = ErrMsg.Replace("\n", "");181
return false;182
}183
184
185
}186
/// <summary>187
/// QuerySingleIntValue 查询出一个单值。188
/// </summary>189
/// <param name="SqlStr">190
/// 传入参数:Sql语句。191
/// </param>192
/// <paramref name="Errmg">193
/// 引用参数：错误信息。194
/// </paramref>195
public bool QuerySingleIntValue(string SqlStr,ref string ErrMsg)196
{197
198
try199
{ 200
if (ConnectToDb(ref ErrMsg) == true)201
{202
SqlCmd=new SqlCommand(SqlStr,CurrentConnection);203
MySqlDataReader=SqlCmd.ExecuteReader(); 204
while(MySqlDataReader.Read())205
{206
IntQuryValue =Convert.ToInt32(MySqlDataReader[0]);207
}208
MySqlDataReader.Close(); 209
SqlCmd.Dispose();210
CloseDb(ref ErrMsg);211
return true;212
}213
return false;214
}215
catch (SqlException e)216
{217
ErrMsg = e.ToString();218
ErrMsg = ErrMsg.Replace("\r", "");219
ErrMsg = ErrMsg.Replace("\n", "");220
return false;221
}222
223
}224
/// <summary>225
/// QuerySingleStrValue 查询出一个单值。226
/// </summary>227
/// <param name="SqlStr">228
/// 传入参数:Sql语句。229
/// </param>230
/// <paramref name="Errmg">231
/// 引用参数：错误信息。232
/// </paramref>233
public bool QuerySingleStrValue(string SqlStr,ref string ErrMsg)234
{235
236
try237
{ 238
if (ConnectToDb(ref ErrMsg) == true)239
{240
SqlCmd=new SqlCommand(SqlStr,CurrentConnection);241
MySqlDataReader=SqlCmd.ExecuteReader(); 242
while(MySqlDataReader.Read())243
{244
StrQuryValue = Convert.ToString(MySqlDataReader[0]);245
}246
MySqlDataReader.Close(); 247
SqlCmd.Dispose();248
CloseDb(ref ErrMsg);249
return true;250
}251
return false;252
}253
catch (SqlException e)254
{255
ErrMsg = e.ToString();256
ErrMsg = ErrMsg.Replace("\r", "");257
ErrMsg = ErrMsg.Replace("\n", "");258
return false;259
}260
261
}262
/// <summary>263
/// MoreValueQuery 查询出多值。264
/// </summary>265
/// <param name="SqlStr">266
/// 查询语句267
/// </param>268
/// <param name="FieldCount">269
/// 读取字段数270
/// </param>271
///<paramref name="ValueArrlist">272
///引用参数：查询结果，保存在动态数组当中。273
///</paramref>274
///<paramref name="ErrMsg">275
///引用参数：错误信息。276
///</paramref> 277
public bool MoreValueQuery(string SqlStr,int FieldCount,ref ArrayList ValueArrlist,string ErrMsg)278
{279
280
try281
{282
if (ConnectToDb(ref ErrMsg) == true)283
{284
285
SqlCmd=new SqlCommand(SqlStr,CurrentConnection);286
MySqlDataReader=SqlCmd.ExecuteReader(); 287
while(MySqlDataReader.Read())288
{289
for (int i = 0; i <= FieldCount-1; i++)290
291
{292
if (!MySqlDataReader.IsDBNull(i)) 293
{294
ValueArrlist.Add("无");295
}296
else297
{ 298
ValueArrlist.Add(MySqlDataReader.GetString(i));299
}300
}301
302
}303
MySqlDataReader.Close(); 304
SqlCmd.Dispose();305
CloseDb(ref ErrMsg);306
return true;307
}308
return false;309
}310
catch (SqlException e)311
{312
ErrMsg = e.ToString();313
ErrMsg = ErrMsg.Replace("\r", "");314
ErrMsg = ErrMsg.Replace("\n", "");315
return false;316
}317
}318
public DataView GetTableData(int PageShow,string SqlStr,string TableName,ref string ErrMsg)319
{320
try321
{322
if (ConnectToDb(ref ErrMsg) == true)323
{ 324
int StartIndex=0;325
MySqlDataAdapter=new SqlDataAdapter(SqlStr,CurrentConnection);326
if (PageShow==0)327
{328
MySqlDataAdapter.Fill(MyDataSet,TableName);329
}330
else331
{332
StartIndex = CurrentPageCode * PageShow;333
MySqlDataAdapter.Fill(MyDataSet,StartIndex,PageShow,TableName);334
}335
DataView MyDataView;//=new DataView();336
MyDataView = MyDataSet.Tables[TableName].DefaultView;337
338
SqlCmd.Dispose();339
CloseDb(ref ErrMsg);340
return MyDataView;341
}342
else343
return null;344
}345
catch (SqlException e)346
{347
ErrMsg = e.ToString();348
ErrMsg = ErrMsg.Replace("\r", "");349
ErrMsg = ErrMsg.Replace("\n", "");350
return null;351
}352
353
}354
355
}
浙公网安备 33010602011771号