一个针对SQLServer操作的封装类

1using System;
  2using System.Data.SqlClient;
  3using System.Data;
  4using System.IO;
  5using System.Windows.Forms;
  6using System.Collections;
  7namespace SQL
  8...{
  9    /**//// <summary>
10    /// 作    者：刘决决
11    /// 时    间：2006-7-21
12    /// 用    途：封装针对SQL Server的所有操作
13    /// 修 改 人：
14    /// 修改日期：
15    /// </summary>
16    public class SQL
17    ...{
18        变量和结构#region 变量和结构
19        public static SqlConnection Connection  = null; //数据库连接对象
20        public static SqlTransaction Transaction = null; //数据库事务
21        public static bool IsInTransaction = false; //是否在事务中
22
23        //ParameterEx结构
24        public struct ParameterEx
25        ...{
26            /**//// <summary>
27            /// 参数名称
28            /// </summary>
29            public string Text;
30            /**//// <summary>
31            /// 参数值
32            /// </summary>
33            public object Value;
34            /**//// <summary>
35            /// 参数数据类型
36            /// </summary>
37            public object DataType;
38            /**//// <summary>
39            /// 参数传入传出方向
40            /// </summary>
41            public object ParaDirection;
42            /**//// <summary>
43            /// 参数大小
44            /// </summary>
45            public object ParaSize;
46        }
47
48
49        #endregion
50
51        构造函数#region 构造函数
52        public  SQL()
53        ...{
54            string ConnectionText; //Store The Connection Sentence for Database
55            if (Connection == null)
56            ...{
57                ConnectionText = ReadConn();
58                Connection = new SqlConnection( ConnectionText );
59                try
60                ...{
61                    Connection.Open(); //Open Database
62                }
63                catch(Exception e)
64                ...{
65                    Connection = null;
66                    MessageBox.Show("连接数据库失败！/n错误信息：" + e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
67                }
68            }
69        }
70        #endregion
71
72        读写数据库连接字符串#region 读写数据库连接字符串
73
74        //写连接字符串
75        public static void WriteConn(string conn, string conn2, string conn3, string conn4)
76        ...{
77            conn = Crypt.EncryptAndDecrypt.Encryption( conn );
78            conn2 = Crypt.EncryptAndDecrypt.Encryption( conn2 );
79            conn3 = Crypt.EncryptAndDecrypt.Encryption( conn3 );
80            conn4 = Crypt.EncryptAndDecrypt.Encryption( conn4 );
81            Crypt.EncryptAndDecrypt.WriteIniString("ConnectionCofig", "Server", conn, System.IO.Directory.GetCurrentDirectory()+ "//DatabaseConnection.ini");
82            Crypt.EncryptAndDecrypt.WriteIniString("ConnectionCofig", "Database", conn2, System.IO.Directory.GetCurrentDirectory()+ "//DatabaseConnection.ini");
83            Crypt.EncryptAndDecrypt.WriteIniString("ConnectionCofig", "User ID", conn3, System.IO.Directory.GetCurrentDirectory()+ "//DatabaseConnection.ini");
84            Crypt.EncryptAndDecrypt.WriteIniString("ConnectionCofig", "Password", conn4, System.IO.Directory.GetCurrentDirectory()+ "//DatabaseConnection.ini");
85        }
86        //读连接字符串
87        public static string ReadConn()
88        ...{
89            string conns = Crypt.EncryptAndDecrypt.Decryption(Crypt.EncryptAndDecrypt.GetIniString("ConnectionCofig", "Server", Directory.GetCurrentDirectory ()+ "//DatabaseConnection.ini"));
90            string conns2 = Crypt.EncryptAndDecrypt.Decryption(Crypt.EncryptAndDecrypt.GetIniString("ConnectionCofig", "Database", Directory.GetCurrentDirectory ()+ "//DatabaseConnection.ini"));
91            string conns3 = Crypt.EncryptAndDecrypt.Decryption(Crypt.EncryptAndDecrypt.GetIniString("ConnectionCofig", "User ID", Directory.GetCurrentDirectory ()+ "//DatabaseConnection.ini"));
92            string conns4 = Crypt.EncryptAndDecrypt.Decryption(Crypt.EncryptAndDecrypt.GetIniString("ConnectionCofig", "Password", Directory.GetCurrentDirectory ()+ "//DatabaseConnection.ini"));
93            string str = "Server = " + conns + ";Database = " + conns2 + ";User ID = " + conns3 + ";Password = " + conns4 + ";";
94            return str;
95        }
96        #endregion
97
98        设置和回传参数#region 设置和回传参数
99        //设置参数到SqlCommand
100        public static  void SetParameters(SqlCommand cmd, ParameterEx[] parameters)
101        ...{
102            if(parameters != null)
103            ...{
104                for(int i = 0;i < parameters.GetLength(0); i++)
105                ...{
106                    if ((ParameterDirection)parameters[i].ParaDirection == ParameterDirection.Output)
107                    ...{
108                        if (parameters[i].ParaSize == null)
109                        ...{
110                            cmd.Parameters.Add(new SqlParameter((string)parameters[i].Text, (SqlDbType)parameters[i].DataType)).Direction=(ParameterDirection)parameters[i].ParaDirection;
111                        }
112                        else
113                        ...{
114                            cmd.Parameters.Add(new SqlParameter((string)parameters[i].Text, (SqlDbType)parameters[i].DataType,(int)parameters[i].ParaSize)).Direction=(ParameterDirection)parameters[i].ParaDirection;
115                        }
116                         
117                    }
118                    else
119                    ...{
120                        if (parameters[i].ParaSize == null)
121                        ...{
122                            cmd.Parameters.Add(new SqlParameter((string)parameters[i].Text, parameters[i].Value )).Direction = ParameterDirection.Input ;
123                        }
124                        else
125                        ...{
126                            SqlParameter op0 = new SqlParameter((string)parameters[i].Text,(SqlDbType)parameters[i].DataType,(int)parameters[i].ParaSize);
127                            op0.Value = parameters[i].Value;
128                            cmd.Parameters.Add(op0);
129                        }
130                    }
131                }
132            }
133        }
134
135        //回传参数到SqlCommand
136        public static  void ReturnParameters(SqlCommand cmd, ParameterEx[] parameters)
137        ...{
138            if(parameters != null)
139            ...{
140                for(int i = 0;i < parameters.GetLength(0); i++)        //回传参数值
141                ...{
142                    if ((ParameterDirection)parameters[i].ParaDirection == ParameterDirection.Output)
143                    ...{
144                        if ((SqlDbType)parameters[i].DataType != SqlDbType.UniqueIdentifier )
145                        ...{
146                            parameters[i].Value = cmd.Parameters[i].Value;
147                        }
148                    }
149                }
150            }
151        }
152
153
154        #endregion
155
156        //打开数据库连接
157        public static  int Open() 
158        ...{
159            if (Connection == null)
160            ...{
161                return -2;
162            }
163            if (Connection.State == ConnectionState.Closed)
164            ...{
165                try
166                ...{
167                    Connection.Open();
168                    return 1;
169                }
170                catch
171                ...{
172                    return -1;
173                }
174            }
175            else
176            ...{
177                return 1;
178            }
179        }
180
181
182        //关闭数据库连接
183        public static  void Close()
184        ...{
185            if ( Connection!=null )
186            ...{
187                Connection.Close();
188                Connection.Dispose();
189                Connection = null;
190            }
191        }
192
193
194        //开始一个事务
195        public static  void BeginTransaction()
196        ...{
197            if ((Connection == null)||(Connection.State == ConnectionState.Closed))
198            ...{
199                MessageBox.Show("数据库尚未打开或者初始化，请重试！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
200                return;
201            }
202            else
203            ...{
204                Transaction = Connection.BeginTransaction();
205                IsInTransaction = true;
206            }
207        }
208
209
210        //提交一个事务
211        public static  void CommitTransaction()
212        ...{
213            if (Transaction != null)
214            ...{
215                Transaction.Commit();
216                IsInTransaction = false;
217                Transaction = null;
218            }
219            else
220            ...{
221                MessageBox.Show("无可用事务，请重试！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
222                return;
223            }
224        }
225
226        //回滚一个事务
227        public static  void RollbackTransaction()
228        ...{
229            if (Transaction != null)
230            ...{
231                Transaction.Rollback();
232                IsInTransaction = false;
233                Transaction = null;
234            }
235            else
236            ...{
237                MessageBox.Show("无可用事务，请重试！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
238                return;
239            }
240        }
241
242
243        //GetDataTable（1）由指定的SQL语句，返回一个数据表
244        public static   DataTable GetDataTable(string  commandText)
245        ...{
246            SqlCommand cmd = new SqlCommand(commandText, Connection); //创建并初始化SqlCommand对象
247            if(IsInTransaction)  cmd.Transaction = Transaction;
248
249            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
250            try
251            ...{
252                DataTable dt = new DataTable();
253                adapter.Fill(dt);
254                return dt;
255            }
256            catch(Exception err)
257            ...{
258                throw new Exception("/n错误：" + err.Message);
259            }
260            finally
261            ...{
262                cmd.Dispose();
263                cmd = null;
264                adapter.Dispose();
265                adapter = null;
266            }
267        }
268
269       
270        //GetDataTable（2）由指定的存储过程和相关参数，返回一个数据表
271        public static DataTable GetDataTable(string storeProcedureName,ParameterEx[] parameters)
272        ...{           
273            try
274            ...{
275                SqlCommand cmd = new SqlCommand( storeProcedureName, Connection);
276                if(IsInTransaction) cmd.Transaction = Transaction;
277                cmd.CommandType = CommandType.StoredProcedure;
278                //参数赋值
279                SetParameters(cmd, parameters);
280
281                SqlDataAdapter adapter=new SqlDataAdapter(cmd);
282                DataTable dt = new DataTable();
283                adapter.Fill(dt);
284                cmd.Dispose();
285                cmd = null;                                           
286                return dt;
287            }
288            catch(Exception err)
289            ...{
290                throw new Exception("/nError:"+err.Message);
291            }
292        }
293
294       
295        //DoCommand （1）执行指定的Sql语句
296        public static  int DoCommand(string commandText)
297        ...{
298            int result = 0;
299            SqlCommand cmd = new SqlCommand(commandText);
300            if(IsInTransaction) cmd.Transaction= Transaction;
301            cmd.Connection = Connection;
302            try
303            ...{
304                result = cmd.ExecuteNonQuery();
305                return result;
306            }
307            catch(Exception err)
308            ...{
309                throw new Exception("Error:" + err.Message);
310            }
311            finally
312            ...{
313                cmd.Dispose();
314                cmd=null;
315            }
316        }
317
318
319        //DoCommand （2）执行带参数的Sql语句
320        public static  int DoCommand(string commandText,ParameterEx[] parameters, string NULL)
321        ...{
322            int result = 0;
323            SqlCommand cmd = new SqlCommand(commandText);
324            if(IsInTransaction) cmd.Transaction = Transaction;
325            cmd.Connection = Connection;
326            SetParameters(cmd,parameters);
327            try
328            ...{
329                result = cmd.ExecuteNonQuery();
330                return result;
331            }
332            catch(Exception err)
333            ...{
334                throw new Exception("Error:" + err.Message);
335            }
336            finally
337            ...{
338                cmd.Dispose();
339                cmd = null;
340            }
341        }
342
343
344        //DoCommand（3）执行相应的存储过程
345        public static   int DoCommand(string storeProcedureName, ParameterEx[] parameters)
346        ...{
347            int result = 0;
348            try
349            ...{
350                SqlCommand cmd = Connection.CreateCommand();
351                if(IsInTransaction) cmd.Transaction = Transaction;
352                cmd.CommandText = storeProcedureName;
353                cmd.CommandType = CommandType.StoredProcedure;
354                //参数赋值
355                SetParameters(cmd, parameters);
356                result = cmd.ExecuteNonQuery();
357                //回传数据
358                ReturnParameters(cmd, parameters);
359                cmd.Dispose();
360                cmd=null;
361                return result;
362            }
363            catch(Exception err)
364            ...{
365                throw new Exception("Error:" + err.Message);
366            }
367        }
368
369
370        //DoCommand （4）执行带有返回参数的Sql语句
371        public static   int DoCommand(string commandText,ParameterEx[] parameters, out object ID)
372        ...{
373            int result = 0;
374            try
375            ...{
376                SqlCommand cmd = new SqlCommand(commandText);
377                if(IsInTransaction) cmd.Transaction = Transaction;
378                cmd.Connection = Connection;
379                //参数赋值
380                SetParameters(cmd,parameters);
381                result = cmd.ExecuteNonQuery();
382           
383                //result=DoCommand(cmd);
384                //回传数据
385                ReturnParameters(cmd,parameters);
386                ID = parameters[0].Value;  //对ID赋值
387                cmd.Dispose();
388                cmd=null;
389                return result;
390            }
391            catch(Exception err)
392            ...{
393                throw new Exception("DoCommand//DatabaseAccess//"+err.Message);
394            }   
395        }
396
397
398        //GetDataRow（1）由指定的Sql语句，返回数据集
399        public static  DataRow GetDataRow(string commandText)
400        ...{
401            SqlCommand cmd = new SqlCommand(commandText);
402            cmd.Connection = Connection;                //添加连接
403            DataRow r ;
404
405            SqlDataAdapter adapter = new SqlDataAdapter();
406            adapter.SelectCommand = cmd;
407            if(IsInTransaction) cmd.Transaction = Transaction;
408            try
409            ...{
410                DataTable dt = new DataTable();
411                adapter.Fill(dt);
412           
413                if (dt.Rows.Count>0)
414                ...{
415                    r = dt.Rows[0];
416                }
417                else
418                ...{
419                    r = null;
420                }
421                return r;
422            }
423            catch(Exception err)
424            ...{
425                throw new Exception("Error:" + err.Message);
426            }
427            finally
428            ...{
429                cmd.Dispose();
430                cmd = null;
431                adapter.Dispose();
432                adapter = null;
433            }
434        }
435
436
437        //GetDataRow（2）由指定的存储过程，返回数据集
438        public static DataRow GetDataRow(string storeProcedureName,ParameterEx[] parameters)
439        ...{
440            DataRow dr=null;
441           
442            try
443            ...{
444                SqlCommand cmd = Connection.CreateCommand();
445                if(IsInTransaction) cmd.Transaction = Transaction;
446                cmd.CommandText = storeProcedureName;
447                cmd.CommandType = CommandType.StoredProcedure;
448                //参数赋值
449                SetParameters(cmd,parameters);
450                SqlDataAdapter adapter=new SqlDataAdapter(cmd);
451                DataTable dt = new DataTable();
452                adapter.Fill(dt);
453           
454                if (dt.Rows.Count > 0)
455                ...{
456                    dr = dt.Rows[0];
457                    //回传数据
458                    ReturnParameters(cmd,parameters);
459                }
460                else
461                ...{
462                    dr = null;
463                }           
464                cmd.Dispose();
465                cmd=null;
466                return dr;
467            }
468            catch(Exception err)
469            ...{
470                throw new Exception("Error"+err.Message);
471            }   
472        }
473
474
475        //GetDataResult（1）执行相应的Sql语句，返回执行情况
476        public static  object GetDataResult(string commandText)
477        ...{
478            object Result = null;
479            SqlCommand cmd = new SqlCommand(commandText);
480            if(IsInTransaction) cmd.Transaction = Transaction;
481            cmd.Connection = Connection;
482            try
483            ...{
484                Result = cmd.ExecuteScalar();
485                return Result;
486            }
487            catch(Exception err)
488            ...{
489                throw new Exception("Error:" + err.Message);
490            }
491            finally
492            ...{
493                cmd.Dispose();
494                cmd=null;
495            }
496        }
497
498
499        //GetDataResult（2）执行指定的存储过程，返回执行情况
500        public static  object GetDataResult(string storeProcedureName, ParameterEx[] parameters)
501        ...{
502            object Result = null;
503            SqlCommand cmd = new SqlCommand();
504            if(IsInTransaction) cmd.Transaction = Transaction;
505            cmd.Connection = Connection;
506            try
507            ...{
508                cmd.CommandText = storeProcedureName;
509                cmd.CommandType = CommandType.StoredProcedure;
510                //参数赋值
511                SetParameters(cmd, parameters);
512                Result = cmd.ExecuteScalar();
513                //回传数据
514                ReturnParameters(cmd,parameters);
515               
516                return Result;
517            }
518            catch(Exception err)
519            ...{
520                throw new Exception("Error:" + err.Message);
521            }
522            finally
523            ...{
524                cmd.Dispose();
525                cmd=null;
526            }
527        }
528
529
530        //GetAdapter（1）由指定的Sql语句，返回一个数据适配器
531        public static SqlDataAdapter GetAdapter(string commandText)
532        ...{
533            SqlDataAdapter adapter = new SqlDataAdapter();
534            SqlCommand cmd = new SqlCommand(commandText);
535            adapter.SelectCommand = cmd;
536            cmd.Connection = Connection;
537            if(IsInTransaction) cmd.Transaction = Transaction;
538            return adapter;
539        }
540
541
542        //GetAdapter（2）由指定存储过程，返回一个数据适配器
543        public static SqlDataAdapter GetAdapter(string storeProcedureName,ParameterEx[] parameters)
544        ...{
545            SqlDataAdapter adapter = new SqlDataAdapter();
546            SqlCommand cmd = Connection.CreateCommand();
547            cmd.CommandText = storeProcedureName;
548            cmd.CommandType = CommandType.StoredProcedure;
549            cmd.Connection = Connection;
550            SetParameters(cmd, parameters);
551            adapter.SelectCommand = cmd;
552            if(IsInTransaction) cmd.Transaction = Transaction;
553            ReturnParameters(cmd,parameters);
554            return adapter;
555        }
556
557
558        //AdapterFillDataSet（1）执行指定的Sql语句，填充数据集和数据表
559        public static void AdapterFillDataSet(string commandText,DataSet ds,string tableName)
560        ...{
561            try
562            ...{
563                SqlDataAdapter adapter = new SqlDataAdapter();
564                SqlCommand cmd=new SqlCommand(commandText);
565                adapter.SelectCommand = cmd;
566                cmd.Connection = Connection;
567                adapter.Fill(ds,tableName);
568                adapter.Dispose();
569            }
570            catch(Exception err)
571            ...{
572                throw new Exception("Error:" + err.Message);
573            }
574        }
575
576
577        //AdapterFillDataSet（2）执行指定的存储过程，填充数据集和数据表
578        public static void AdapterFillDataSetP(string storeProcedureName,ParameterEx[] parameters,DataSet ds,string tableName)
579        ...{
580            try
581            ...{
582                SqlDataAdapter adapter = new SqlDataAdapter();
583                SqlCommand cmd = Connection.CreateCommand();
584                cmd.CommandText = storeProcedureName;
585                cmd.CommandType = CommandType.StoredProcedure;
586                cmd.Connection = Connection;
587                SetParameters(cmd,parameters);
588                adapter.SelectCommand = cmd;
589                if(IsInTransaction) cmd.Transaction = Transaction;
590                adapter.Fill(ds,tableName);
591                ReturnParameters(cmd, parameters);
592                adapter.Dispose();
593            }
594            catch(Exception err)
595            ...{
596                throw new Exception("Error:"+err.Message);
597            }       
598        }
599
600
601        //DoCommands（1）执行一组Sql语句
602        public static void DoCommands(ArrayList Commands)
603        ...{
604            try
605            ...{
606                BeginTransaction();
607                foreach(string sql in Commands)
608                ...{
609                    DoCommand(sql);   
610                }
611                CommitTransaction();
612            }
613            catch(Exception err)
614            ...{
615                RollbackTransaction();
616                throw new Exception("Error:" + err.Message);
617            }   
618        }
619
620
621        //DoCommands（2）执行一组存储过程
622        public static void DoCommands(string[] storeProcedureNameArray,ParameterEx[][] parametersArray,string[] commandTexts)
623        ...{
624            BeginTransaction();
625            try
626            ...{
627                int i;
628                if(storeProcedureNameArray!=null)
629                ...{
630                    SqlCommand cmd = Connection.CreateCommand();
631                    for(i=0;i<storeProcedureNameArray.GetLength(0);i++)
632                    ...{
633                                                   
634                        if(storeProcedureNameArray[i] != "")
635                        ...{
636                            cmd.CommandText = storeProcedureNameArray[i];
637                            cmd.CommandType = CommandType.StoredProcedure;
638                            SetParameters(cmd,parametersArray[i]);
639                            cmd.ExecuteNonQuery();
640                            ReturnParameters(cmd,parametersArray[i]);                   
641                        }
642                    }
643                    cmd.Dispose();
644                    cmd = null;
645                }
646                if(commandTexts != null)
647                ...{
648                    for(i = 0;i < commandTexts.GetLength(0); i ++)
649                    ...{
650                        if(commandTexts[i] != null)
651                        ...{
652                            if(commandTexts[i] != "")
653                            ...{
654                                DoCommand(commandTexts[i]);
655                            }
656                        }
657                    }
658                }
659                //提交事务
660                CommitTransaction();
661            }
662            catch(Exception err)
663            ...{
664                //回滚事务
665                RollbackTransaction();
666                throw new Exception("Error:"+err.Message);
667            }
668        }
669       
670
671    }
672}
673