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

本文分享了一个用于简化ADO.NET数据库操作的辅助类，包括连接管理、执行SQL语句、获取数据等功能，适用于多种数据库操作场景。

---------------------- Windows Phone 7手机开发、.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];
        }
    }