asp.net 数据库连接类代码(SQL)

public class sqloperation
{
#region 属性
/// <summary>
/// 保存在web.config中的连接字符串
/// </summary>
protected static string connectionstring = system.configuration.configurationmanager.connectionstrings["hao"].connectionstring;
/// <summary>
/// sqlconnection对象
/// </summary>
protected static sqlconnection conn = new sqlconnection();
/// <summary>
/// sqlcommand对象
/// </summary>
protected static sqlcommand comm = new sqlcommand();
#endregion

#region 内部函数
/// <summary>
/// 打开数据库连接
/// </summary>
private static void connectionopen()
{
if (conn.state != connectionstate.open)
{
conn.close();
conn.connectionstring = connectionstring;
comm.connection = conn;
try
{
conn.open();
}
catch (exception ex)
{
throw new exception(ex.message);
}
}
}

/// <summary>
/// 关闭数据库连接
/// </summary>
private static void connectionclose()
{
conn.close();
conn.dispose();
comm.dispose();
}

#endregion

/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="sqlstring">要执行的sql语句</param>
public static void executesql(string sqlstring)
{
try
{
connectionopen();
comm.commandtype = commandtype.text;
comm.commandtext = sqlstring;
comm.executenonquery();
}
catch (exception ex)
{
try
{
connectionclose();
}
catch (exception e)
{
throw new exception(e.message);
}
throw new exception(ex.message);
}
finally
{
connectionclose();
}
}

/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procedurename">存储过程名称</param>
/// <param name="coll">存储过程需要的参数集合</param>
public static void executeprocedure(string procedurename, params sqlparameter[] coll)
{
try
{
connectionopen();
comm.commandtype = commandtype.storedprocedure;
comm.commandtext = procedurename;
comm.parameters.clear();
for (int i = 0; i < coll.length; i++)
{
comm.parameters.add(coll[i]);
}
comm.executenonquery();
}
catch (exception ex)
{
try
{
connectionclose();
}
catch (exception e)
{
throw new exception(e.message);
}
throw new exception(ex.message);
}
finally
{
connectionclose();
}
}

/// <summary>
/// 执行sql查询并返回第一行的第一条记录，返回object，使用时需要拆箱 -> unbox
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
/// <returns>返回object类型的第一行第一条记录</returns>
public static object executescalar(string sqlstring)
{
object obj = new object();
try
{
connectionopen();
comm.commandtype = commandtype.text;
comm.commandtext = sqlstring;
obj = comm.executescalar();
}
catch (exception ex)
{
try
{
connectionclose();
}
catch (exception e)
{
throw new exception(e.message);
}
throw new exception(ex.message);
}
finally
{
connectionclose();
}
return obj;
}

/// <summary>
/// 执行sql语句,同时进行事务处理
/// </summary>
/// <param name="sqlstr">要执行的sql语句</param>
public static void executetransactionsql(string sqlstring)
{
sqltransaction trans;
trans = conn.begintransaction();
comm.transaction = trans;
try
{
connectionopen();
comm.commandtype = commandtype.text;
comm.commandtext = sqlstring;
comm.executenonquery();
trans.commit();
}
catch (exception ex)
{
try
{
connectionclose();
}
catch (exception e)
{
throw new exception(e.message);
}
throw new exception(ex.message);
}
finally
{
connectionclose();
}
}

/// <summary>
/// 执行指定sql查询，返回dataset
/// </summary>
/// <param name="sqlstr">要执行的sql语句</param>
/// <returns>dataset</returns>
public static dataset getdatasetbysql(string sqlstring)
{
sqldataadapter da = new sqldataadapter();
dataset ds = new dataset();
try
{
connectionopen();
comm.commandtype = commandtype.text;
comm.commandtext = sqlstring;
da.selectcommand = comm;
da.fill(ds);
}
catch (exception ex)
{
try
{
connectionclose();
}
catch (exception e)
{
throw new exception(e.message);
}
throw new exception(ex.message);
}
finally
{
connectionclose();
}
return ds;
}

/// <summary>
/// 通过存储过程返回dataset
/// </summary>
/// <param name="procedurename">存储过程名称</param>
/// <param name="coll">sqlparameter集合</param>
/// <returns>dataset</returns>
public static dataset getdatasetbyprocedure(string procedurename, params sqlparameter[] coll)
{
sqldataadapter da = new sqldataadapter();
dataset ds = new dataset();
try
{
connectionopen();
comm.commandtype = commandtype.storedprocedure;
comm.parameters.clear();
for (int i = 0; i < coll.length; i++)
{
comm.parameters.add(coll[i]);
}
comm.commandtext = procedurename;
da.selectcommand = comm;
da.fill(ds);
}
catch (exception ex)
{
try
{
connectionclose();
}
catch (exception e)
{
throw new exception(e.message);
}
throw new exception(ex.message);
}
finally
{
connectionclose();
}
return ds;
}


/// <summary>
/// 通过存储过程返回dataset
/// </summary>
/// <param name="procedurename">存储过程名称</param>
/// <returns>dataset</returns>
public static dataset getdatasetbyprocedure(string procedurename)
{
sqldataadapter da = new sqldataadapter();
dataset ds = new dataset();
try
{
connectionopen();
comm.commandtype = commandtype.storedprocedure;
comm.commandtext = procedurename;
comm.parameters.clear();
da.selectcommand = comm;
da.fill(ds);
}
catch (exception ex)
{
try
{
connectionclose();
}
catch (exception e)
{
throw new exception(e.message);
}
throw new exception(ex.message);
}
finally
{
connectionclose();
}
return ds;
}

/// <summary>
/// 返回指定sql语句的datatable
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
/// <returns>datatable</returns>
public static datatable getdatatablebysql(string sqlstring)
{
sqldataadapter da = new sqldataadapter();
datatable dt = new datatable();
try
{
connectionopen();
comm.commandtype = commandtype.text;
comm.commandtext = sqlstring;
da.selectcommand = comm;
da.fill(dt);
}
catch (exception ex)
{
try
{
connectionclose();
}
catch (exception e)
{
throw new exception(e.message);
}
throw new exception(ex.message);
}
finally
{
connectionclose();
}
return dt;
}

/// <summary>
/// 根据存储过程返回datatable
/// </summary>
/// <param name="procedurename">存储过程名</param>
/// <param name="coll">sqlparameter集合</param>
/// <returns>datatable</returns>
public static datatable getdatatablebyprocedure(string procedurename, params sqlparameter[] coll)
{
sqldataadapter da = new sqldataadapter();
datatable dt = new datatable();
try
{
connectionopen();
comm.parameters.clear();
comm.commandtype = commandtype.storedprocedure;
comm.commandtext = procedurename;
for (int i = 0; i < coll.length; i++)
{
comm.parameters.add(coll[i]);
}
da.selectcommand = comm;
da.fill(dt);
}
catch (exception ex)
{
try
{
connectionclose();
}
catch (exception e)
{
throw new exception(e.message);
}
throw new exception(ex.message);
}
finally
{
connectionclose();
}
return dt;
}

/// <summary>
/// 根据存储过程返回datatable
/// </summary>
/// <param name="procedurename">存储过程名称</param>
/// <returns>datatable</returns>
public static datatable getdatatablebyprocedure(string procedurename)
{
sqldataadapter da = new sqldataadapter();
datatable dt = new datatable();
try
{
connectionopen();
comm.parameters.clear();
comm.commandtype = commandtype.storedprocedure;
comm.commandtext = procedurename;
da.selectcommand = comm;
da.fill(dt);
}
catch (exception ex)
{
try
{
connectionclose();
}
catch (exception e)
{
throw new exception(e.message);
}
throw new exception(ex.message);
}
finally
{
connectionclose();
}
return dt;
}
}