简单的SqlHelper

class SqlHelper
{
    private static string connstr = ConfigurationManager.ConnectionString["DBConnectionStr"].ConnectionString;
    //封装方法原则：把不变的放到方法里，把变化的放在参数中     

    public static int ExecuteNonQuery(string sql)
    {
        using(Sqlconnection conn = new SqlConnetion(connstr))
        {
            conn.Open();
            using(SqlCommmand cmd = new SqlCommand())
            {
                cmd.CommandText = sql;
                //执行返回影响多少行数据
                return cmd.ExecuteNonQuery();
            }
        }
    }

    public static object ExecuteScalar()
    {
        using(SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            using(SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = sql;
                return cmd.ExecuteScalar();
            }
        }
    }
    //只用来执行查询结果比较小的sql，太大使用reader

    //一般只返回一个表，可以用DataTable
    public static DataSet ExecuteDataSet(string sql)
    {
        using(SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            using(SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = sql;
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                ad.Fill(ds);
                return ds;
            }
        }
    }
    
    //第二版
    //可能参数有多个
    public static int ExecuteNonQuery(string sql,SqlParameter[] parameters)
    {
        using(SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            using(SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                foreach(SqlParameter param in parameters)
                {
                    cmd.Parameters.Add(param);
                }
                //cmd.Parameters.AddRange(parameters);    简化循环加载参数
            }
        }
    }

    //第三版，使用长度可变参数
public static int ExecuteNonQuery(string sql,params SqlParameter[] parameters)
    {
        using(SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            using(SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                foreach(SqlParameter param in parameters)
                {
                    cmd.Parameters.Add(param);
                }
                //cmd.Parameters.AddRange(parameters);    简化循环加载参数
            }
        }
    }
}