sqlHelper.cs

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Data.Common;

/// <summary>
///sqlHelper 的摘要说明
/// </summary>
public class sqlHelper
{
 public sqlHelper()
 {
  //
  //TODO: 在此处添加构造函数逻辑
  //
 }
    public string costConnectionstr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

//public string costConnectionstr = ConfigurationManager.AppSettings["GetCon"].ToString();
    //public string costConnectionstr = "server=.;database=ListView;uid=sa;pwd=sa";
    #region  普通執行方法
    /// <summary>
    /// 操作数据库方法
    /// </summary>
    /// <param name="str"></param>
    /// <param name="sql"></param>
    /// <returns></returns>
    public int ExecuteNonQuery(string str, SqlParameter[] sql)
    {
        try
        {
            using (SqlConnection sqlConnection = new SqlConnection())
            {
                sqlConnection.ConnectionString = costConnectionstr;
                sqlConnection.Open();
                SqlCommand SqlCommand = new SqlCommand(str, sqlConnection);
                SqlCommand.Parameters.AddRange(sql);
                return SqlCommand.ExecuteNonQuery();

            }
        }
        catch (Exception E)
        {

            throw E;
        }
    }

    /// <summary>
    /// 查询方法 数组参数
    /// </summary>
    /// <param name="strSql"></param>
    /// <returns></returns>
    public DataSet ExecuteDataSet(string strSql, SqlParameter[] sqlParameter)
    {
        using (SqlConnection sqlConnection = new SqlConnection())
        {
            sqlConnection.ConnectionString = costConnectionstr;
            sqlConnection.Open();
            try
            {
                SqlCommand sqlCommand = new SqlCommand(strSql, sqlConnection);
                sqlCommand.Parameters.AddRange(sqlParameter);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = sqlCommand;
                DataSet dataSet = new DataSet();
                sqlDataAdapter.Fill(dataSet, "s");
                return dataSet;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }

    /// <summary>
    /// 查询方法 无数组参数
    /// </summary>
    /// <param name="strSql"></param>
    /// <returns></returns>
    public DataSet ExecuteDataSetSql(string strSql)
    {
        using (SqlConnection sqlConnection = new SqlConnection())
        {
            sqlConnection.ConnectionString = costConnectionstr;
            sqlConnection.Open();
            try
            {
                SqlCommand sqlCommand = new SqlCommand(strSql, sqlConnection);

                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = sqlCommand;
                DataSet dataSet = new DataSet();
                sqlDataAdapter.Fill(dataSet, "cost");
                return dataSet;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

    /// <summary>
    /// 返回受影响的首行首列
    /// </summary>
    /// <param name="sql">sql执行语句</param>
    /// <param name="sps">参数</param>
    /// <returns>返回sql执行结果</returns>
    public int ExcuteSql(string sql, SqlParameter[] sps)
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = costConnectionstr;
            conn.Open();
            //新建执行sql对象
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            //添加参数
            if (sps != null)
            {
                foreach (SqlParameter p in sps)
                {
                    cmd.Parameters.Add(p);
                }
            }
            //执行sql返回影响行数
            int num = Convert.ToInt32(cmd.ExecuteScalar());
            return num;
        }
    }
    public int ExcuteSql(string sql)
    {
        return ExcuteSql(sql, null);
    }
    #endregion

    #region  存儲過程執行方法
    /// <summary>
    /// 執行數據庫操作語句  存儲過程
    /// </summary>
    /// <param name="procname"></param>
    /// <param name="pars"></param>
    /// <returns></returns>
    public int ExcuteProcNonQuery(string procname, DbParameter[] pars)
    {
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString = costConnectionstr;
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procname;
            cmd.Connection = con;
            if (pars != null)
            {
                foreach (SqlParameter p in pars)
                {
                    cmd.Parameters.Add(p);
                }
            }
            int count = cmd.ExecuteNonQuery();
            return count;
        }
    }
    //重載  無慘執行
    public int ExcuteProcNonQuery(string procname)
    {
        return ExcuteProcNonQuery(procname, null);
    }
    /// <summary>
    /// 存儲過程查詢 帶參方法
    /// </summary>
    /// <param name="procname"></param>
    /// <param name="pars"></param>
    /// <returns></returns>
    public DataTable ExcuteProcDT(string procname, DbParameter[] pars)
    {
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString = costConnectionstr;
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procname;
            cmd.Connection = con;
            //添加参数
            if (pars != null)
            {
                foreach (SqlParameter p in pars)
                {
                    cmd.Parameters.Add(p);
                }
            }
            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            ada.Fill(ds);
            DataTable dt = ds.Tables[0];
            return dt;
        }
    }
    //重載  無慘查詢
    public DataTable ExcuteProcDT(string procname)
    {
        return ExcuteProcDT(procname, null);
    }
    #endregion
}