数据访问基类：
1 using System;
2 using System.IO;
3 using System.Data;
4 using System.Data.SqlClient;
5 using System.Configuration;
6 using System.Collections;
7 using System.Web;
8
9 namespace Salary_System.SQLServerDAL
10 {
11 /// <summary>
12 ///内部类:存储过程的返回值记录类
13 /// </summary>
14 public class SqlResult
15 {
16 public bool Succeed; //存储过程是否执行成功.
17 public Hashtable OutputValues; // 存储过程output值,放在(HashTable)表OutputValues里.
18 public DataTable datatable; //存储过程返回的结果集,放在(DataTable)表datatable里.
19 public DataSet dataSet; //存储过程返回的结果集,放在DataSet表中
20 public string errorMessage; //访问数据库失败
21
22 public int inflecntNum;
23
24 public SqlResult()
25 {
26 Succeed = false;
27 OutputValues = new Hashtable();
28 datatable=new DataTable();
29 dataSet=new DataSet();
30 errorMessage = "";
31 }
32 }
33
34 /// <summary>
35 /// ====================***调用存储过程和SQL的基类***============================
36 /// abstract:该类不能被实例化,只能通过派生子类来使用它
37 /// </summary>
38 public abstract class SqlSP_Base : IDisposable
39 {
40 public SqlSP_Base() : this("","")
41 {
42 }
43 //重载
44 public SqlSP_Base(string sp_name,string sql_name)
45 {
46 this.ProcedureName = sp_name;
47 this.SQLName = sql_name;
48 }
49 //私有成员变量
50 private string sp_name;
51 private string sql_name;
52 private SqlConnection myConnection;
53 private SqlCommand myCommand;
54 private SqlParameter myParameter;//存储过程参数
55
56 //公共属性
57 public string ProcedureName//获取和设置存储过程名
58 {
59 get
60 {
61 return this.sp_name;
62 }
63 set
64 {
65 this.sp_name = value;
66 }
67 }
68 //公共属性
69 public string SQLName//获取和设置存储过程名
70 {
71 get
72 {
73 return this.sql_name;
74 }
75 set
76 {
77 this.sql_name = value;
78 }
79 }
80
81
82 /// <summary>
83 /// 调用存储过程
84 /// </summary>
85 /// <param name="parameters">参数集合</param>
86 /// <returns></returns>
87 public SqlResult Call_SP(params object[] parameters)
88 {
89 string strconn=ConfigurationSettings.AppSettings["ConnectionString"];
90 //存储过程的返回值记录类
91 SqlResult result = new SqlResult();
92 myConnection = new SqlConnection(strconn);
93 myCommand = new SqlCommand(this.ProcedureName, myConnection);
94 myCommand.CommandType = CommandType.StoredProcedure;
95 SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
96
97 myConnection.Open();
98 //将参数添加到存储过程的参数集合
99 GetProcedureParameter(result,parameters);
100 //开始事物
101 using(SqlTransaction trans = myConnection.BeginTransaction())
102 {
103 try
104 {
105 if(trans!=null)
106 {
107 myCommand.Transaction = trans;
108 }
109 //填充数据,将结果填充到SqlResult集中
110 myAdapter.Fill(result.dataSet);
111 if(result.dataSet.Tables.Count>0)
112 result.datatable=result.dataSet.Tables[0].Copy();
113 //将输出参数的值添加到Result的OutputValues
114 GetOutputValue(result);
115 //提交事物
116 trans.Commit();
117 }
118 catch(Exception e)
119 {
120 result.errorMessage = e.Message;
121 //事物回滚
122 trans.Rollback();
123 }
124
125 //如果捕捉了异常，但仍会执行包括在 finally 块中的输出语句
126 finally
127 {
128 myAdapter.Dispose();
129 myCommand.Dispose();
130 myConnection.Close();
131 myConnection.Dispose();
132 }
133 }
134 return result;
135 }
136
137 /// <summary>
138 /// 将参数添加到存储过程的参数集合
139 /// </summary>
140 /// <param name="parameters"></param>
141 private void GetProcedureParameter(SqlResult result,params object[] parameters)
142 {
143 SqlCommand myCommand2 = new SqlCommand();
144 myCommand2.Connection = this.myConnection;
145 myCommand2.CommandText = "select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" +this.ProcedureName+ "' order by ORDINAL_POSITION";
146 SqlDataReader reader = null;
147 try
148 {
149 reader = myCommand2.ExecuteReader();
150 int i = 0;
151 while(reader.Read())
152 {
153 myParameter = new SqlParameter();
154 myParameter.ParameterName = reader["PARAMETER_NAME"].ToString();
155 myParameter.Direction = reader["PARAMETER_MODE"].ToString()=="IN"?ParameterDirection.Input:ParameterDirection.Output;
156
157 switch(reader["DATA_TYPE"].ToString())
158 {
159 case "bit" :
160 if(myParameter.Direction == ParameterDirection.Input)
161 myParameter.Value = Convert.ToBoolean(parameters[i]);
162 myParameter.SqlDbType = SqlDbType.Bit;
163 break;
164
165 case "bigint":
166 if(myParameter.Direction == ParameterDirection.Input)
167 myParameter.Value = Convert.ToInt32(parameters[i]);
168 myParameter.SqlDbType = SqlDbType.BigInt;
169 break;
170
171 case "int" :
172 if(myParameter.Direction == ParameterDirection.Input)
173 myParameter.Value = Convert.ToInt32(parameters[i].ToString());
174 myParameter.SqlDbType = SqlDbType.Int;
175 break;
176
177 case "decimal" :
178 if(myParameter.Direction == ParameterDirection.Input)
179 myParameter.Value = (decimal)parameters[i];
180 myParameter.SqlDbType = SqlDbType.Decimal;
181 myParameter.Precision = (byte)reader["NUMERIC_PRECISION"];
182 myParameter.Scale = byte.Parse(reader["NUMERIC_SCALE"].ToString());
183 break;
184
185 case "nvarchar" :
186 if(myParameter.Direction == ParameterDirection.Input)
187 myParameter.Value = Convert.ToString(parameters[i]);
188 myParameter.Size = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
189 myParameter.SqlDbType = SqlDbType.NVarChar;
190 break;
191
192 case "varchar" :
193 if(myParameter.Direction == ParameterDirection.Input)
194 myParameter.Value = (string)parameters[i];
195 myParameter.Size = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
196 myParameter.SqlDbType = SqlDbType.VarChar;
197 break;
198
199 case "nchar" :
200 if(myParameter.Direction == ParameterDirection.Input)
201 myParameter.Value = (string)parameters[i];
202 myParameter.Size = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
203 myParameter.SqlDbType = SqlDbType.NChar;
204 break;
205
206 case "char" :
207 if(myParameter.Direction == ParameterDirection.Input)
208 myParameter.Value = (string)parameters[i];
209 myParameter.Size = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
210 myParameter.SqlDbType = SqlDbType.Char;
211 break;
212
213 case "ntext" :
214 if(myParameter.Direction == ParameterDirection.Input)
215 myParameter.Value = (string)parameters[i];
216 myParameter.SqlDbType = SqlDbType.NText;
217 break;
218
219 case "text" :
220 if(myParameter.Direction == ParameterDirection.Input)
221 myParameter.Value = (string)parameters[i];
222 myParameter.SqlDbType = SqlDbType.Text;
223 break;
224
225 case "datetime" :
226 if(myParameter.Direction == ParameterDirection.Input)
227 myParameter.Value = Convert.ToDateTime(parameters[i]);
228 myParameter.SqlDbType = SqlDbType.DateTime;
229 break;
230
231 case "smalldatetime" :
232 if(myParameter.Direction == ParameterDirection.Input)
233 myParameter.Value = Convert.ToDateTime(parameters[i]);
234 myParameter.SqlDbType = SqlDbType.DateTime;
235 break;
236
237 case "image" :
238 if(myParameter.Direction == ParameterDirection.Input)
239 {
240 myParameter.Value=(byte[])parameters[i];
241 }
242 myParameter.SqlDbType = SqlDbType.Image;
243 break;
244
245 case "real":
246 if(myParameter.Direction==ParameterDirection.Input)
247 myParameter.Value=Convert.ToSingle(parameters[i]);
248 myParameter.SqlDbType = SqlDbType.Real;
249 break;
250 case "varbinary":
251 if(myParameter.Direction==ParameterDirection.Input)
252 myParameter.Value=(byte[])parameters[i];
253 myParameter.SqlDbType = SqlDbType.VarBinary;
254 break;
255 case "money":
256 if(myParameter.Direction==ParameterDirection.Input)
257 myParameter.Value=Convert.ToDecimal(parameters[i]);
258 myParameter.SqlDbType=SqlDbType.Money;
259 break;
260
261 default :
262 break;
263 }
264 i++;
265 myCommand.Parameters.Add(myParameter);
266 }
267 }
268 catch(Exception e)
269 {
270 result.errorMessage = e.Message;
271 }
272 finally
273 {
274 if(reader!=null)
275 {
276 reader.Close();
277 }
278 myCommand2.Dispose();
279 }
280 }
281
282 /// <summary>
283 /// 将输出的值添加到Result的OutputValues
284 /// </summary>
285 /// <param name="result"></param>
286 private void GetOutputValue(SqlResult result)
287 {
288 if(result.Succeed==false)
289 {
290 result.Succeed=true;
291 }
292 foreach(SqlParameter parameter in myCommand.Parameters)
293 {
294 if(parameter.Direction == ParameterDirection.Output)
295 {
296 //Hashtab表是一个键值对
297 result.OutputValues.Add(parameter.ParameterName, parameter.Value);
298 }
299 }
300 }
301
302 public void Dispose()
303 {
304 Dispose(true);
305 GC.SuppressFinalize(true);
306 }
307
308 protected virtual void Dispose(bool disposing)
309 {
310 if (! disposing)
311 return;
312 if(myConnection != null)
313 {
314 myConnection.Dispose();
315 }
316 }
317 //=======end======
318
319 //=======begin====
320 /// <summary>
321 /// 调用SQL的基类
322 /// </summary>
323 /// <param name="parameters">参数集合</param>
324 /// <returns></returns>
325 public SqlResult Call_SQL()
326 {
327 string strconn=ConfigurationSettings.AppSettings["ConnectionString"];
328
329 //存储过程的返回值记录类
330 SqlResult result = new SqlResult();
331 myConnection = new SqlConnection(strconn);
332 myCommand = new SqlCommand(this.sql_name, myConnection);
333 myCommand.CommandType = CommandType.Text;
334
335 SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
336
337 myConnection.Open();
338 using(SqlTransaction trans = myConnection.BeginTransaction())
339 {
340 try
341 {
342 if(trans!=null)
343 {
344 myCommand.Transaction = trans;
345 }
346
347 //填充数据,将结果填充到SqlResult集中
348 myAdapter.Fill(result.datatable);
349 result.Succeed = true;
350 //提交事物
351 trans.Commit();
352 }
353 catch(Exception e)
354 {
355 result.Succeed = false;
356 result.errorMessage = e.Message;
357 }
358
359 //如果捕捉了异常，但仍会执行包括在 finally 块中的输出语句
360 finally
361 {
362 myAdapter.Dispose();
363 myCommand.Dispose();
364 myConnection.Close();
365 myConnection.Dispose();
366 }
367 }
368 return result;
369 }
370 //=======end=========
371 }
372 }
373
374
1 using System;
2 using System.IO;
3 using System.Data;
4 using System.Data.SqlClient;
5 using System.Configuration;
6 using System.Collections;
7 using System.Web;
8
9 namespace Salary_System.SQLServerDAL
10 {
11 /// <summary>
12 ///内部类:存储过程的返回值记录类
13 /// </summary>
14 public class SqlResult
15 {
16 public bool Succeed; //存储过程是否执行成功.
17 public Hashtable OutputValues; // 存储过程output值,放在(HashTable)表OutputValues里.
18 public DataTable datatable; //存储过程返回的结果集,放在(DataTable)表datatable里.
19 public DataSet dataSet; //存储过程返回的结果集,放在DataSet表中
20 public string errorMessage; //访问数据库失败
21
22 public int inflecntNum;
23
24 public SqlResult()
25 {
26 Succeed = false;
27 OutputValues = new Hashtable();
28 datatable=new DataTable();
29 dataSet=new DataSet();
30 errorMessage = "";
31 }
32 }
33
34 /// <summary>
35 /// ====================***调用存储过程和SQL的基类***============================
36 /// abstract:该类不能被实例化,只能通过派生子类来使用它
37 /// </summary>
38 public abstract class SqlSP_Base : IDisposable
39 {
40 public SqlSP_Base() : this("","")
41 {
42 }
43 //重载
44 public SqlSP_Base(string sp_name,string sql_name)
45 {
46 this.ProcedureName = sp_name;
47 this.SQLName = sql_name;
48 }
49 //私有成员变量
50 private string sp_name;
51 private string sql_name;
52 private SqlConnection myConnection;
53 private SqlCommand myCommand;
54 private SqlParameter myParameter;//存储过程参数
55
56 //公共属性
57 public string ProcedureName//获取和设置存储过程名
58 {
59 get
60 {
61 return this.sp_name;
62 }
63 set
64 {
65 this.sp_name = value;
66 }
67 }
68 //公共属性
69 public string SQLName//获取和设置存储过程名
70 {
71 get
72 {
73 return this.sql_name;
74 }
75 set
76 {
77 this.sql_name = value;
78 }
79 }
80
81
82 /// <summary>
83 /// 调用存储过程
84 /// </summary>
85 /// <param name="parameters">参数集合</param>
86 /// <returns></returns>
87 public SqlResult Call_SP(params object[] parameters)
88 {
89 string strconn=ConfigurationSettings.AppSettings["ConnectionString"];
90 //存储过程的返回值记录类
91 SqlResult result = new SqlResult();
92 myConnection = new SqlConnection(strconn);
93 myCommand = new SqlCommand(this.ProcedureName, myConnection);
94 myCommand.CommandType = CommandType.StoredProcedure;
95 SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
96
97 myConnection.Open();
98 //将参数添加到存储过程的参数集合
99 GetProcedureParameter(result,parameters);
100 //开始事物
101 using(SqlTransaction trans = myConnection.BeginTransaction())
102 {
103 try
104 {
105 if(trans!=null)
106 {
107 myCommand.Transaction = trans;
108 }
109 //填充数据,将结果填充到SqlResult集中
110 myAdapter.Fill(result.dataSet);
111 if(result.dataSet.Tables.Count>0)
112 result.datatable=result.dataSet.Tables[0].Copy();
113 //将输出参数的值添加到Result的OutputValues
114 GetOutputValue(result);
115 //提交事物
116 trans.Commit();
117 }
118 catch(Exception e)
119 {
120 result.errorMessage = e.Message;
121 //事物回滚
122 trans.Rollback();
123 }
124
125 //如果捕捉了异常，但仍会执行包括在 finally 块中的输出语句
126 finally
127 {
128 myAdapter.Dispose();
129 myCommand.Dispose();
130 myConnection.Close();
131 myConnection.Dispose();
132 }
133 }
134 return result;
135 }
136
137 /// <summary>
138 /// 将参数添加到存储过程的参数集合
139 /// </summary>
140 /// <param name="parameters"></param>
141 private void GetProcedureParameter(SqlResult result,params object[] parameters)
142 {
143 SqlCommand myCommand2 = new SqlCommand();
144 myCommand2.Connection = this.myConnection;
145 myCommand2.CommandText = "select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" +this.ProcedureName+ "' order by ORDINAL_POSITION";
146 SqlDataReader reader = null;
147 try
148 {
149 reader = myCommand2.ExecuteReader();
150 int i = 0;
151 while(reader.Read())
152 {
153 myParameter = new SqlParameter();
154 myParameter.ParameterName = reader["PARAMETER_NAME"].ToString();
155 myParameter.Direction = reader["PARAMETER_MODE"].ToString()=="IN"?ParameterDirection.Input:ParameterDirection.Output;
156
157 switch(reader["DATA_TYPE"].ToString())
158 {
159 case "bit" :
160 if(myParameter.Direction == ParameterDirection.Input)
161 myParameter.Value = Convert.ToBoolean(parameters[i]);
162 myParameter.SqlDbType = SqlDbType.Bit;
163 break;
164
165 case "bigint":
166 if(myParameter.Direction == ParameterDirection.Input)
167 myParameter.Value = Convert.ToInt32(parameters[i]);
168 myParameter.SqlDbType = SqlDbType.BigInt;
169 break;
170
171 case "int" :
172 if(myParameter.Direction == ParameterDirection.Input)
173 myParameter.Value = Convert.ToInt32(parameters[i].ToString());
174 myParameter.SqlDbType = SqlDbType.Int;
175 break;
176
177 case "decimal" :
178 if(myParameter.Direction == ParameterDirection.Input)
179 myParameter.Value = (decimal)parameters[i];
180 myParameter.SqlDbType = SqlDbType.Decimal;
181 myParameter.Precision = (byte)reader["NUMERIC_PRECISION"];
182 myParameter.Scale = byte.Parse(reader["NUMERIC_SCALE"].ToString());
183 break;
184
185 case "nvarchar" :
186 if(myParameter.Direction == ParameterDirection.Input)
187 myParameter.Value = Convert.ToString(parameters[i]);
188 myParameter.Size = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
189 myParameter.SqlDbType = SqlDbType.NVarChar;
190 break;
191
192 case "varchar" :
193 if(myParameter.Direction == ParameterDirection.Input)
194 myParameter.Value = (string)parameters[i];
195 myParameter.Size = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
196 myParameter.SqlDbType = SqlDbType.VarChar;
197 break;
198
199 case "nchar" :
200 if(myParameter.Direction == ParameterDirection.Input)
201 myParameter.Value = (string)parameters[i];
202 myParameter.Size = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
203 myParameter.SqlDbType = SqlDbType.NChar;
204 break;
205
206 case "char" :
207 if(myParameter.Direction == ParameterDirection.Input)
208 myParameter.Value = (string)parameters[i];
209 myParameter.Size = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
210 myParameter.SqlDbType = SqlDbType.Char;
211 break;
212
213 case "ntext" :
214 if(myParameter.Direction == ParameterDirection.Input)
215 myParameter.Value = (string)parameters[i];
216 myParameter.SqlDbType = SqlDbType.NText;
217 break;
218
219 case "text" :
220 if(myParameter.Direction == ParameterDirection.Input)
221 myParameter.Value = (string)parameters[i];
222 myParameter.SqlDbType = SqlDbType.Text;
223 break;
224
225 case "datetime" :
226 if(myParameter.Direction == ParameterDirection.Input)
227 myParameter.Value = Convert.ToDateTime(parameters[i]);
228 myParameter.SqlDbType = SqlDbType.DateTime;
229 break;
230
231 case "smalldatetime" :
232 if(myParameter.Direction == ParameterDirection.Input)
233 myParameter.Value = Convert.ToDateTime(parameters[i]);
234 myParameter.SqlDbType = SqlDbType.DateTime;
235 break;
236
237 case "image" :
238 if(myParameter.Direction == ParameterDirection.Input)
239 {
240 myParameter.Value=(byte[])parameters[i];
241 }
242 myParameter.SqlDbType = SqlDbType.Image;
243 break;
244
245 case "real":
246 if(myParameter.Direction==ParameterDirection.Input)
247 myParameter.Value=Convert.ToSingle(parameters[i]);
248 myParameter.SqlDbType = SqlDbType.Real;
249 break;
250 case "varbinary":
251 if(myParameter.Direction==ParameterDirection.Input)
252 myParameter.Value=(byte[])parameters[i];
253 myParameter.SqlDbType = SqlDbType.VarBinary;
254 break;
255 case "money":
256 if(myParameter.Direction==ParameterDirection.Input)
257 myParameter.Value=Convert.ToDecimal(parameters[i]);
258 myParameter.SqlDbType=SqlDbType.Money;
259 break;
260
261 default :
262 break;
263 }
264 i++;
265 myCommand.Parameters.Add(myParameter);
266 }
267 }
268 catch(Exception e)
269 {
270 result.errorMessage = e.Message;
271 }
272 finally
273 {
274 if(reader!=null)
275 {
276 reader.Close();
277 }
278 myCommand2.Dispose();
279 }
280 }
281
282 /// <summary>
283 /// 将输出的值添加到Result的OutputValues
284 /// </summary>
285 /// <param name="result"></param>
286 private void GetOutputValue(SqlResult result)
287 {
288 if(result.Succeed==false)
289 {
290 result.Succeed=true;
291 }
292 foreach(SqlParameter parameter in myCommand.Parameters)
293 {
294 if(parameter.Direction == ParameterDirection.Output)
295 {
296 //Hashtab表是一个键值对
297 result.OutputValues.Add(parameter.ParameterName, parameter.Value);
298 }
299 }
300 }
301
302 public void Dispose()
303 {
304 Dispose(true);
305 GC.SuppressFinalize(true);
306 }
307
308 protected virtual void Dispose(bool disposing)
309 {
310 if (! disposing)
311 return;
312 if(myConnection != null)
313 {
314 myConnection.Dispose();
315 }
316 }
317 //=======end======
318
319 //=======begin====
320 /// <summary>
321 /// 调用SQL的基类
322 /// </summary>
323 /// <param name="parameters">参数集合</param>
324 /// <returns></returns>
325 public SqlResult Call_SQL()
326 {
327 string strconn=ConfigurationSettings.AppSettings["ConnectionString"];
328
329 //存储过程的返回值记录类
330 SqlResult result = new SqlResult();
331 myConnection = new SqlConnection(strconn);
332 myCommand = new SqlCommand(this.sql_name, myConnection);
333 myCommand.CommandType = CommandType.Text;
334
335 SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
336
337 myConnection.Open();
338 using(SqlTransaction trans = myConnection.BeginTransaction())
339 {
340 try
341 {
342 if(trans!=null)
343 {
344 myCommand.Transaction = trans;
345 }
346
347 //填充数据,将结果填充到SqlResult集中
348 myAdapter.Fill(result.datatable);
349 result.Succeed = true;
350 //提交事物
351 trans.Commit();
352 }
353 catch(Exception e)
354 {
355 result.Succeed = false;
356 result.errorMessage = e.Message;
357 }
358
359 //如果捕捉了异常，但仍会执行包括在 finally 块中的输出语句
360 finally
361 {
362 myAdapter.Dispose();
363 myCommand.Dispose();
364 myConnection.Close();
365 myConnection.Dispose();
366 }
367 }
368 return result;
369 }
370 //=======end=========
371 }
372 }
373
374
