C#基础学习21（封装SQLHelper）

优点：代码重用率高，便于维护易修改，代码更简洁、条理

在App.config中添加

<connectionStrings>
  <add name="connStr" connectionString="server=localhost;user id=root;password=123;database=pqxit;charset=utf8;port=3306"/>
</connectionStrings>

SQLHelper类中调用时首先要添加引用System.configuration，类中using System.configuration; 

SQLHelper类：

public class SQLHelper
{
    //连接字符串，从App.config中获取
    private static readonly string connStr=ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

    public static int ExecuteNonQuery(string sql,MySqlParameter[] msp)
    {
        using (MySqlConnection conn = new MySqlConnection(connStr))
        {
            using (MySqlCommand mcmd = new MySqlCommand(sql,conn))
            {
                if (msp!=null)
                {
                    //如果传过来了Parameter则赋值
                    mcmd.Parameters.AddRange(msp);
                }
                conn.Open();
                return mcmd.ExecuteNonQuery();
            }
        }
    }

    public static object ExecuteScalar(string sql, MySqlParameter[] msp)
    {
        using (MySqlConnection conn=new MySqlConnection(connStr))
        {
            using (MySqlCommand mcmd=new MySqlCommand(sql,conn))
            {
                if (msp!=null)
                {
                    mcmd.Parameters.AddRange(msp);
                }
                conn.Open();
                return mcmd.ExecuteScalar();
            }
        }
    }

    public static MySqlDataReader ExecuteReader(string sql, MySqlParameter[] msp)
    {
        MySqlConnection conn = new MySqlConnection(connStr);
        using (MySqlCommand mcmd = new MySqlCommand(sql,conn))
        {
            if (msp!=null)
            {
                mcmd.Parameters.AddRange(msp);
            }
            try
            {
                conn.Open();
                return mcmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//当ExecuteReader对象用完关闭时，也把它关联的Connection对象一块关闭
            }
            catch (Exception ex)
            {
                conn.Close();
                conn.Dispose();
                throw ex;
            }
        }
    }
}

在用到的时候直接类目.方法（SQLHelper.ExecuteNonQuery(sql,null)）就可以