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;            }        }     }}