黑马程序员---ADO.NET基础之数据库操作辅助类

 

通过ADO.NET对数据库的操作无外乎增删改查，程序里边可能要多次的用到增删改查，每次操作数据库的语句大体都一样，只是SQL语句或者参数不同，这就需要把

这些频繁使用并且相同的代码提取出来，做一个公共类，用到的时候，只需要调用就行了，下面是我最近写出来的一个数据库操作辅助类，拿出来分享一下。

public static class DBHelper
    {
        private static SqlConnection connection;

        public static SqlConnection Connection
        {
            get 
            {
                string connectionString = "Data Source=.;Initial Catalog=MyBookShop; uid=sa;Password=sa";
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return DBHelper.connection; 
            }
        }

        public static int ExecuteCommand(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            int result = cmd.ExecuteNonQuery();
            return result;
        }

        public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = cmd.ExecuteNonQuery();
            return result;
        }

        public static int GetScalar(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            int result =Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }

        public static int GetScalar(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }

        public static int GetScalar(params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection;
            cmd.CommandText = "Pro_InsertOrder";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }

        public static SqlDataReader GetReader(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            SqlDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return dataReader;
        }

        public static SqlDataReader GetReader(string sql,params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return dataReader;
        }

        public static DataTable GetDataSet(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            DataSet ds = new DataSet();
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable GetDataSet(string sql,params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            DataSet ds = new DataSet();
            cmd.Parameters.AddRange(values);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(ds);
            return ds.Tables[0];
        }
    }