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
}