SQLHelper

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// 2013年6月10日 10:27:52
/// </summary>
public class SqlHelperXhf
{

    #region 初始化参数 public SqlConnection myConn 
    public SqlConnection myConn = null; 
    #endregion

    #region 构造函数 SqlHelperXhf()
    public SqlHelperXhf()
    {
        myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
    } 
    #endregion

    #region 重载连接字符串 +SqlHelperXhf(string WebConfigConnectionStringsName)
    /// <summary>
    /// 重载连接字符串
    /// </summary>
    /// <param name="WebConfigConnectionStringsName"></param>
    public SqlHelperXhf(string WebConfigConnectionStringsName)
    {
        if (WebConfigConnectionStringsName.ToLower().Contains("data source="))
        {
            myConn = new SqlConnection(WebConfigConnectionStringsName);
        }
        else
        {
            myConn = new SqlConnection(ConfigurationManager.ConnectionStrings[WebConfigConnectionStringsName].ConnectionString);
        }
    } 
    #endregion

    #region 析构 ~SqlHelperXhf()

    ~SqlHelperXhf()
    {
        myConn.Close();
        myConn.Dispose();
    } 
    #endregion

    #region 返回SqlDataAdapter + SqlDataAdapter Getsda(string str)
    /// <summary>
    /// 返回sda
    /// </summary>
    /// <param name="str">sql语句</param>
    /// <returns>SqlDataAdapter</returns>
    public SqlDataAdapter Getsda(string str)
    {
        return new SqlDataAdapter(str, myConn);
    }
    
    #endregion

    #region 返回第一行的第一列的数据 +object ExecuteScalar(string sql)
    /// <summary>
    /// 返回第一行的第一列
    /// </summary>
    /// <param name="sql">需要查询的SQL语句</param>
    /// <returns>返回Object类型的数据</returns>
    public object ExecuteScalar(string sql)
    {
        SqlCommand sqlCommand = new SqlCommand(sql, this.myConn);
        try
        {
            if (myConn.State == ConnectionState.Closed) myConn.Open();
            object reObj = sqlCommand.ExecuteScalar();
            myConn.Close();
            return reObj;
        }
        catch (Exception ex)
        {
            myConn.Close();
            throw new Exception(ex + "<\r\n>" + sql);
        }
    } 
    #endregion

    #region 返回第一行第一列的数据 +object ExecuteScalar(string sql, params SqlParameter[] sqlParames)
    /// <summary>
    /// 返回第一行第一列的数据
    /// </summary>
    /// <param name="sql">需要查询的SQL语句</param>
    /// <param name="sqlParames">SQL语句中的参数</param>
    /// <returns>object类型的数据</returns>
    public object ExecuteScalar(string sql, params SqlParameter[] sqlParames)
    {
        SqlCommand sqlCommand = new SqlCommand(sql, this.myConn);

        try
        {
            if (myConn.State == ConnectionState.Closed) myConn.Open();
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddRange(sqlParames);
            object reObj = sqlCommand.ExecuteScalar();
            myConn.Close();
            return reObj;
        }
        catch (Exception ex)
        {
            myConn.Close();
            throw new Exception(ex + "<\r\n>" + sql);
        }
    }  
    #endregion

    #region 返回数量 +int Count(string sql)
    public int Count(string sql)
    {
        return Convert.ToInt32(ExecuteScalar(sql));
    } 
    #endregion

    #region 返回数量 +int Count(string sql, params SqlParameter[] sqlParames)
    public int Count(string sql, params SqlParameter[] sqlParames)
    {
        return Convert.ToInt32(ExecuteScalar(sql, sqlParames));
    } 
    #endregion

    #region 执行SQL语句。返回受影响行数 +int ExecuteNonQuery(string sql)
    public int ExecuteNonQuery(string sql)
    {
        SqlCommand sqlCommand = new SqlCommand(sql, this.myConn);
        try
        {
            if (myConn.State == ConnectionState.Closed) myConn.Open();
            int reInt = sqlCommand.ExecuteNonQuery();
            myConn.Close();
            return reInt;
        }
        catch (Exception ex)
        {
            myConn.Close();
            throw new Exception(ex + "<\r\n>" + sql);
        }
    } 
    #endregion

    #region 执行SQL语句。返回 DataSet +DataSet ExecuteDataSet(string sql)
    public DataSet ExecuteDataSet(string sql)
    {
        SqlCommand sqlCommand = new SqlCommand(sql, this.myConn);
        DataSet retrunDataSet = new DataSet();
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
        try
        {
            sqlDataAdapter.Fill(retrunDataSet);
            return retrunDataSet;
        }
        catch (Exception ex)
        {
            throw new Exception(ex + "<\r\n>" + sql);
        }
    } 
    #endregion

    #region 执行SQL语句。返回 DataTable +DataTable ExecuteDataTable(string sql)
    public DataTable ExecuteDataTable(string sql)
    {
        SqlCommand sqlCommand = new SqlCommand(sql, this.myConn);
        DataTable retrunDataTable = new DataTable();
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
        try
        {
            sqlDataAdapter.Fill(retrunDataTable);
            return retrunDataTable;
        }
        catch (Exception ex)
        {
            throw new Exception(ex + "<\r\n>" + sql);
        }
    } 
    #endregion

    #region 执行带参数的SQL语句。返回受影响行数 +int ExecuteNonQuery(string sql， params SqlParameter []sqlParames)
    public int ExecuteNonQuery(string sql, params SqlParameter [] sqlParames)
    {
        SqlCommand sqlCommand = new SqlCommand(sql, this.myConn);
        if (sqlParames.Length>0)
        {
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddRange(sqlParames);
        }
        try
        {
            if (myConn.State == ConnectionState.Closed)
            {
                myConn.Open();
            }
            int reInt = sqlCommand.ExecuteNonQuery();
            myConn.Close();
            return reInt;
        }
        catch (Exception ex)
        {
            myConn.Close();
            throw new Exception(ex + "<\r\n>" + sql);
        }
    }
    #endregion

    #region 执行带参数的sql + DataSet ExecuteDataSet(string sql, params SqlParameter[] sqlParames)
    /// <summary>
    /// 执行带参数的sql
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="sqlParames"></param>
    /// <returns></returns>
    public DataSet ExecuteDataSet(string sql, params SqlParameter[] sqlParames)
    {
        SqlCommand cmd = new SqlCommand(sql, myConn);
        if (sqlParames.Length > 0)
        {
            cmd.Parameters.Clear();
            cmd.Parameters.AddRange(sqlParames);
        }
        DataSet ds = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        try
        {
            sda.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            throw new Exception(ex + "<\r\n>" + sql);
        }
        return ds;
    }

    #endregion

    #region 执行带参数的sql + DataTable ExecuteDataTable(string sql, params SqlParameter[] sqlParames)
    /// <summary>
    /// 执行带参数的sql
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="sqlParames"></param>
    /// <returns></returns>
    public DataTable ExecuteDataTable(string sql, params SqlParameter[] sqlParames)
    {
        SqlCommand cmd = new SqlCommand(sql, myConn);
        if (sqlParames.Length > 0)
        {
            cmd.Parameters.Clear();
            cmd.Parameters.AddRange(sqlParames);
        }
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        try
        {
            sda.Fill(dt);
            return dt;
        }
        catch (Exception ex)
        {
            throw new Exception(ex + "<\r\n>" + sql);
        }
        return dt;
    }

    #endregion

    #region 执行一个带参数的存储过程，返回 DataSet +DataSet RunProcedure(string commandText, SqlParameter [] commandParameters)
    /// <summary>
    /// 执行一个带参数的存储过程，返回 DataSet
    /// 格式 DataSet ds = ExecuteDataset("GetOrders", new SqlParameter("@prodid", 24));
    /// </summary>
    /// <param name="commandText"></param>
    /// <param name="commandParameters"></param>
    /// <returns></returns>
    public DataSet RunProcedure(string commandText, SqlParameter []commandParameters)
    {
        //创建一个命令
        SqlCommand sqlCommand = new SqlCommand();

        sqlCommand.CommandText = commandText;
        sqlCommand.CommandType = CommandType.StoredProcedure;
        sqlCommand.Connection = myConn;
        sqlCommand.Parameters.Clear();
        sqlCommand.Parameters.AddRange(commandParameters);

        //创建 DataAdapter 和 DataSet
        SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
        DataSet ds = new DataSet();
        try
        {
            da.Fill(ds);
            sqlCommand.Parameters.Clear();
            //返回结果集
            return ds;
        }
        catch (Exception ex)
        {
            string parasList = "";
            foreach (SqlParameter para in commandParameters)
            {
                parasList += "<\r\n>@" + para.ParameterName + ":" + para.Value;
            }
            throw new Exception(ex + "<\r\n>" + commandText + parasList);
        }

    } 
    #endregion

    #region 执行一个存储过程，不返回结果 +void RunProcedureNoReturn(string commandText, SqlParameter commandParameters)
    public void RunProcedureNoReturn(string commandText, SqlParameter []commandParameters)
    {
        //创建一个命令

        SqlCommand sqlCommand = new SqlCommand();
        sqlCommand.CommandText = commandText;
        sqlCommand.CommandType = CommandType.StoredProcedure;
        sqlCommand.Connection = myConn;
        sqlCommand.Parameters.AddRange(commandParameters);
        try
        {
            if (myConn.State == ConnectionState.Closed) myConn.Open();
            sqlCommand.ExecuteNonQuery();
            myConn.Close();
        }
        catch (Exception ex)
        {
            myConn.Close();
            string parasList = "";
            foreach (SqlParameter para in commandParameters)
            {
                parasList += "<\r\n>@" + para.ParameterName + ":" + para.Value;
            }
            throw new Exception(ex + "<\r\n>" + commandText + parasList);
        }

    } 
    #endregion

}