分享一哥们的SQLHelper类~~

  using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.WebControls;

/// <summary>
/// sqlhelp 的摘要说明
/// =========调用时直接看这里吧，没写错的=========
/// 本人QQ：644817117 小浩专用SqlHelper类
/// 如需要增加新功能可以方便使用下面方法
/// ExcuteNonQuery:执行不返回结果的sql语句或存储过程
/// ExcuteScalar：返回某字段
/// ExcuteReader：返回数据集
/// GetRecord：是否查询到记录
/// GetStrDataSet：返回一个DataSet
/// eccom：只传一个sql语句执行不返回操作
/// ecadabind：根据SQL语句绑定GridView数据源操作
/// ecadabindinfostring：根据sql语句和主键绑定gridview
/// </summary>
public class sqlhelper
{
// 私有变量，数据库连接
protected SqlConnection Connection;
protected string ConnectionString;
// 构造函数，获取默认的数据库设置
public sqlhelper()
{
try
{
if (System.Configuration.ConfigurationManager.ConnectionStrings[ " connstring "] != null)
{
ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[ " connstring "].ConnectionString.ToString();
}
else
ConnectionString = " Data Source=(local);DataBase=db_Examination;Integrated Security=True ";
}
catch (SqlException e)
{
ConnectionString = " Data Source=(local);DataBase=db_Examination;Integrated Security=True ";
}
}
// 打开数据库连接
private void Open()
{
// 判断数据库连接是否存在
if (Connection == null)
{
// 不存在，新建并打开
Connection = new SqlConnection(ConnectionString);
Connection.Open();
}
else
{
// 存在，判断是否处于关闭状态
if (Connection.State.Equals(ConnectionState.Closed))
Connection.Open(); // 连接处于关闭状态，重新打开
}
}
// 公有方法，关闭数据库连接
public void Close()
{
if (Connection.State.Equals(ConnectionState.Open))
{
Connection.Close(); // 连接处于打开状态，关闭连接
}
}
/// <summary>
/// 析构函数，释放非托管资源
/// </summary>
~sqlhelper()
{
try
{
if (Connection != null)
Connection.Close();
}
catch{}
try
{
Dispose();
}
catch{}
}

// 公有方法，释放资源
public void Dispose()
{
if (Connection != null) // 确保连接被关闭
{
Connection.Dispose();
Connection = null;
}
}
// command参数,不需要理会
private void PrepareCommand(SqlCommand cmd, SqlTransaction trans, CommandType cmdtype, string cmdtext, SqlParameter[] cmdparms)
{

Open();
cmd.Connection = Connection;
cmd.CommandText = cmdtext;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdtype;
if (cmdparms != null)
{
foreach (SqlParameter parm in cmdparms)
cmd.Parameters.Add(parm);
}
}

// }执行不返回结果的sql语句或存储过程ExecuteNonQuery
public int ExecuteNonQuery(CommandType cmdtype, string cmdtext, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, null, cmdtype, cmdtext, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
Close();
return val;
}
// 返回一个字段ExecuteScalar
public object ExecuteScalar( CommandType cmdtype, string cmdtext, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, null, cmdtype, cmdtext, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
Close();
return val;
}
// 使用方法：SqlDataReader r = ExecuteReader(connectionString,CommandType.StoredProcedure,"存储过程名",new SqlParameter("@pp",24));
// 返回一个数据集
public SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
try
{

PrepareCommand(cmd, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
Close();
return rdr;
}
catch
{
Close();
throw;
}
}
// 公有方法，根据Sql语句，返回是否查询到记录
public bool GetRecord( string XSqlString)
{
DataSet dataset = GetStrDataSet(XSqlString);
if (dataset.Tables[ 0].Rows.Count > 0)
{
return true;
}
else
{
return false;
}
}

// ecadabindinfostring：根据sql语句和主键绑定gridview
public bool ecadabindinfostring(GridView gv, string sqlstr5, string DNK)
{
DataSet mydataset = GetStrDataSet(sqlstr5);
gv.DataSource = mydataset;
gv.DataKeyNames = new string[] { DNK };
try
{
gv.DataBind();
return true;
}
catch
{
return false;
}
finally
{
Close();
}
}
// ecadabind：根据sql语句绑定GridView数据源操作
public bool ecadabind(GridView gv, string sqlstr4)
{
Open();
SqlDataAdapter mydataadapter = new SqlDataAdapter(sqlstr4,Connection);
DataSet mydataset = new DataSet();
mydataadapter.Fill(mydataset);
gv.DataSource = mydataset;
try
{
gv.DataBind();
return true;
}
catch
{
return false;
}
finally
{
Close();
}
}
// eccom：只传一个sql语句执行不返回操作
public bool eccom( string sqlstr1)
{
Open();
SqlCommand mycommand = new SqlCommand(sqlstr1,Connection);
try
{
mycommand.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
finally
{
Close();
}
}
// 从数据库里获取记录填充下拉列表
public bool ecDropDownList(DropDownList DDL, string sqlstr3, string DTF, string DVF)
{

DataSet mydataset = GetStrDataSet(sqlstr3);
DDL.DataSource = mydataset;
DDL.DataTextField = DTF;
DDL.DataValueField = DVF;
try
{
DDL.DataBind();
return true;
}
catch
{
return false;
}
finally
{
Close();
}
}
// 返回一个DataSet
public DataSet GetStrDataSet( string StrName)
{
Open();
SqlDataAdapter adapter = new SqlDataAdapter(StrName, Connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
Close();
return dataset;
}

}