操作SQL Server的帮助类

可作为以后开发的参考代码，也可以再整理下，代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

using System.Windows.Forms;

namespace test1
{
    class DataBase 
    {
        private static string connString;
        private  static SqlConnection Conn;
        //获取连接数据库字符串
        public static String GetConnString()
        {
            string connString = "chaiqianD2.Properties.Settings.testConnectionString";
            String s = ConfigurationManager.ConnectionStrings[connString].ConnectionString;
            return s;
        }

        /**////<summary>
        ///创建connnection并打开
        /// </summary>
        public static void Open()
        {
            GetConnString();
            connString = GetConnString();
            Conn = new SqlConnection();
            Conn.ConnectionString = connString;
            try
            {
                Conn.Open();
            }
            catch (SqlException ee)
            {
                MessageBox.Show(ee.Message.ToString() + ee.ToString());
            }
        }

        /**////<summary>
        ///获取connnection
        /// </summary>
        public static SqlConnection getConnection()
        {
            Open();
            return Conn;
        }

        //执行查询,返回受影响的行数
        public static int ExecuteSQL(string cmdString)
        {
            Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = cmdString;
            cmd.Connection = Conn;
            cmd.CommandType = System.Data.CommandType.Text;
            //返回数据库操作影响的行数
            int nAffected = -1;
            try
            {
                nAffected = cmd.ExecuteNonQuery();
            }
            catch (SqlException sqlEx)
            {
                MessageBox.Show(sqlEx.Message.ToString());
                throw sqlEx;
            }
            finally
            {
                Conn.Close();
            }
            return nAffected;
        }

        //返回第一行第一列的数据
        public static int ExecuteScalar(string cmdString)
        {
            Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = cmdString;
            cmd.Connection = Conn;
            cmd.CommandType = System.Data.CommandType.Text;
            //返回数据库操作影响的行数
            int count = 0;
            try
            {
                count = Int32.Parse(cmd.ExecuteScalar().ToString().Trim());
            }
            catch (SqlException ee)
            {
                Conn.Close();
                MessageBox.Show(ee.Message.ToString());
                count = -1;
                
            }
            finally
            {
                Conn.Close();
            }
            return count;
        }

        //关闭连接
        public static void Close()
        {
            if (Conn.State == ConnectionState.Open)
            Conn.Close();
        }

        //根据查询语句和在数据集中表的名字，返回DataSet
        public static DataSet GetDataSet(String cmdString, String strTableName)
        {
            Open();
            SqlCommand cmd = new SqlCommand(cmdString, Conn);
            SqlDataAdapter myAd = new SqlDataAdapter();
            myAd.SelectCommand = new SqlCommand(cmdString, Conn);

            DataSet myDs = new DataSet();
            //填充数据
            try
            {
                myAd.Fill(myDs, strTableName);
                return myDs;
            }
            catch (SqlException sqlEx)
            {
               
                MessageBox.Show(sqlEx.Message.ToString());
                throw sqlEx;
            }
            catch (Exception ex)   
            {
                MessageBox.Show(ex.Message.ToString());
                throw ex;
            }
            finally
            {
                Close();
            }

        }

        //返回datareader
        public static SqlDataReader GetDataReader(string CmdStr)
        {
            Open();
            SqlCommand myCmd = new SqlCommand();
            myCmd.Connection = Conn;
            myCmd.CommandType = CommandType.Text;
            myCmd.CommandText = CmdStr;
            SqlDataReader myDr = null;
            try
            {
                //数据读取器关闭时，连接对象自动关闭
                myDr = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException sqlEx)
            {
                Close();
                if (myDr != null)
                    myDr.Close();
                throw sqlEx;
            }
             return myDr;
        }
        
        //执行存储过程的函数
        public static int ExecuteStoredProcedure(string StoredProcedureStr, SqlParameter[] parameters)
        { 
            Open();
             
            using (SqlCommand cmd = new SqlCommand(StoredProcedureStr, Conn))
            {
                try
                {
                    if (Conn.State != ConnectionState.Open)
                    {
                        Conn.Open();
                    }
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    cmd.CommandType = CommandType.StoredProcedure;
                    int rows = cmd.ExecuteNonQuery();
                    Close();
                    return rows;
                }
                catch (SqlException E)
                {
                    MessageBox.Show(E.Message.ToString());
                    throw E;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                    return -1;

                }
                finally
                {
                    Close();
                }
                 
            }
            

        }

        //
        public static int ExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        //
        private static void PrepareCommand(SqlCommand cmd, string cmdText, SqlParameter[] cmdParms)
        {
            Open();
            cmd.Connection = Conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.Text;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }


        public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch (SqlException ee)
            {
                Close();
                MessageBox.Show(ee.Message.ToString());
                return null;
            }
        }


         /**//// <summary>
        /// 执行存储过程，返回DataSet对象
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string StoredProcedureStr, SqlParameter[] parameters, string tableName){
            Open();
            DataSet ds = new DataSet();
            try
            {
                if (Conn.State != ConnectionState.Open)
                    Conn.Open();
                SqlDataAdapter command = new SqlDataAdapter(StoredProcedureStr, Conn);
                command.SelectCommand.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parameter in parameters)
                {
                    command.SelectCommand.Parameters.Add(parameter);
                }
                command.Fill(ds, tableName);
                Close();

            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                MessageBox.Show(ex.Message.ToString() + ex.Number);
                throw ex;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                Close();
            }
            return ds;
        }


        //执行存储过程，返回多个表的结果集
        public static DataSet Query(string StoredProcedureStr, SqlParameter[] parameters)
        {
            Open();
            DataSet ds = new DataSet();
            try
            {
                if (Conn.State != ConnectionState.Open)
                    Conn.Open();
                SqlDataAdapter command = new SqlDataAdapter(StoredProcedureStr, Conn);
                command.SelectCommand.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parameter in parameters)
                {
                    command.SelectCommand.Parameters.Add(parameter);
                }
                command.Fill(ds);
                Close();

            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                MessageBox.Show(ex.Message.ToString() + ex.Number);
                throw ex;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                Close();
            }
            return ds;
        }

        //
        public static void ShowSqlException(SqlException ex)
        {
            if (ex == null)
                return;
            // uses SQLServer 2000 ErrorCodes 
            switch (ex.Number)
            {
                case 17:
                //     SQL Server does not exist or access denied. 
                case 4060:
                // Invalid Database 
                case 18456:
                // Login Failed 
                break;
                case 547:
                MessageBox.Show("外键约束!");
                // ForeignKey Violation 
                break;
                case 1205:
                // DeadLock Victim
                break;
                case 2627:
                MessageBox.Show("违反约束，插入重复值!");
                break;
                case 2601:
                MessageBox.Show("违反唯一约束，插入重复值!");
                // Unique Index/Constriant Violation 
                break;
                default:
                // throw a general DAL Exception 
                break;
            }
        } 
    }
}