ASP.NET 数据访问类
2024-07-10 12:57:21
供稿：网友
using system;
using system.data;
using system.data.sqlclient;
namespace sysclasslibrary
{
/// <summary>
/// dataaccess 的摘要说明。
/// <description>数据处理基类,调用方式：dataaccess.dataset((string)sqlstr);或者dataaccess.dataset((string)sqlstr,ref dataset ds); </description>
/// </summary>
public class dataaccess
{
#region 属性
protected static sqlconnection conn=new sqlconnection();
protected static sqlcommand comm=new sqlcommand();
#endregion
public dataaccess()
{
//init();
}
#region 内部函数 静态方法中不会执行dataaccess()构造函数
/// <summary>
/// 打开数据库连接
/// </summary>
private static void openconnection()
{
if (conn.state == connectionstate.closed)
{
//sysconfig.connectionstring 为系统配置类中连接字符串，如："server=localhost;database=databasename;uid=sa;pwd=;"
conn.connectionstring = sysconfig.connectionstring ;
comm.connection =conn;
try
{
conn.open();
}
catch(exception e)
{
throw new exception(e.message);
}
}
}
/// <summary>
/// 关闭当前数据库连接
/// </summary>
private static void closeconnection()
{
if(conn.state == connectionstate.open)
conn.close();
conn.dispose();
comm.dispose();
}
#endregion
/// <summary>
/// 执行sql查询语句
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
public static void executesql(string sqlstr)
{
try
{
openconnection();
comm.commandtype =commandtype.text ;
comm.commandtext =sqlstr;
comm.executenonquery();
}
catch(exception e)
{
throw new exception(e.message);
}
finally
{
closeconnection();
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procname">存储过程名</param>
/// <param name="coll">sqlparameters 集合</param>
public static void executeporcedure(string procname,sqlparameter[] coll)
{
try
{
openconnection();
for(int i=0;i<coll.length;i++)
{
comm.parameters .add(coll[i]);
}
comm.commandtype=commandtype.storedprocedure ;
comm.commandtext =procname;
comm.executenonquery();
}
catch(exception e)
{
throw new exception(e.message);
}
finally
{
comm.parameters.clear();
closeconnection();
}
}
/// <summary>
/// 执行存储过程并返回数据集
/// </summary>
/// <param name="procname">存储过程名称</param>
/// <param name="coll">sqlparameter集合</param>
/// <param name="ds">dataset </param>
public static void executeporcedure(string procname,sqlparameter[] coll,ref dataset ds)
{
try
{
sqldataadapter da=new sqldataadapter();
openconnection();
for(int i=0;i<coll.length;i++)
{
comm.parameters .add(coll[i]);
}
comm.commandtype=commandtype.storedprocedure ;
comm.commandtext =procname;
da.selectcommand =comm;
da.fill(ds);
}
catch(exception e)
{
throw new exception(e.message);
}
finally
{
comm.parameters.clear();
closeconnection();
}
}
/// <summary>
/// 执行sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> unbox
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
/// <returns>object 返回值 </returns>
public static object executescalar(string sqlstr)
{
object obj=new object();
try
{
openconnection();
comm.commandtype =commandtype.text ;
comm.commandtext =sqlstr;
obj=comm.executescalar();
}
catch(exception e)
{
throw new exception(e.message);
}
finally
{
closeconnection();
}
return obj;
}
/// <summary>
/// 执行sql查询语句,同时进行事务处理
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
public static void executesqlwithtransaction(string sqlstr)
{
sqltransaction trans ;
trans=conn.begintransaction();
comm.transaction =trans;
try
{
openconnection();
comm.commandtype =commandtype.text ;
comm.commandtext =sqlstr;
comm.executenonquery();
trans.commit();
}
catch
{
trans.rollback();
}
finally
{
closeconnection();
}
}
/// <summary>
/// 返回指定sql语句的sqldatareader，请注意，在使用后请关闭本对象，同时将自动调用closeconnection()来关闭数据库连接
/// 方法关闭数据库连接
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
/// <returns>sqldatareader对象</returns>
public static sqldatareader datareader(string sqlstr)
{
sqldatareader dr=null;
try
{
openconnection();
comm.commandtext =sqlstr;
comm.commandtype =commandtype.text ;
dr=comm.executereader(commandbehavior.closeconnection);
}
catch
{
try
{
dr.close();
closeconnection();
}
catch
{
}
}
return dr;
}
/// <summary>
/// 返回指定sql语句的sqldatareader，请注意，在使用后请关闭本对象，同时将自动调用closeconnection()来关闭数据库连接
/// 方法关闭数据库连接
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
/// <param name="dr">传入的ref datareader 对象</param>
public static void datareader(string sqlstr,ref sqldatareader dr)
{
try
{
openconnection();
comm.commandtext =sqlstr;
comm.commandtype =commandtype.text ;
dr=comm.executereader(commandbehavior.closeconnection);
}
catch
{
try
{
if(dr!=null && !dr.isclosed)
dr.close();
}
catch
{
}
finally
{
closeconnection();
}
}
}
/// <summary>
/// 返回指定sql语句的dataset
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
/// <returns>dataset</returns>
public static dataset dataset(string sqlstr)
{
dataset ds= new dataset();
sqldataadapter da=new sqldataadapter();
try
{
openconnection();
comm.commandtype =commandtype.text ;
comm.commandtext =sqlstr;
da.selectcommand =comm;
da.fill(ds);
}
catch(exception e)
{
throw new exception(e.message);
}
finally
{
closeconnection();
}
return ds;
}
/// <summary>
/// 返回指定sql语句的dataset
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
/// <param name="ds">传入的引用dataset对象</param>
public static void dataset(string sqlstr,ref dataset ds)
{
sqldataadapter da=new sqldataadapter();
try
{
openconnection();
comm.commandtype =commandtype.text ;
comm.commandtext =sqlstr;
da.selectcommand =comm;
da.fill(ds);
}
catch(exception e)
{
throw new exception(e.message);
}
finally
{
closeconnection();
}
}
/// <summary>
/// 返回指定sql语句的datatable
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
/// <returns>datatable</returns>
public static datatable datatable(string sqlstr)
{
sqldataadapter da=new sqldataadapter();
datatable datatable=new datatable();
try
{
openconnection();
comm.commandtype =commandtype.text ;
comm.commandtext =sqlstr;
da.selectcommand =comm;
da.fill(datatable);
}
catch(exception e)
{
throw new exception(e.message);
}
finally
{
closeconnection();
}
return datatable;
}
/// <summary>
/// 执行指定sql语句,同时给传入datatable进行赋值
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
/// <param name="dt">ref datatable dt </param>
public static void datatable(string sqlstr,ref datatable dt)
{
sqldataadapter da=new sqldataadapter();
try
{
openconnection();
comm.commandtype =commandtype.text ;
comm.commandtext =sqlstr;
da.selectcommand =comm;
da.fill(dt);
}
catch(exception e)
{
throw new exception(e.message);
}
finally
{
closeconnection();
}
}
/// <summary>
/// 执行带参数存储过程并返回数据集合
/// </summary>
/// <param name="procname">存储过程名称</param>
/// <param name="parameters">sqlparametercollection 输入参数</param>
/// <returns></returns>
public static datatable datatable(string procname,sqlparametercollection parameters)
{
sqldataadapter da=new sqldataadapter();
datatable datatable=new datatable();
try
{
openconnection();
comm.parameters.clear();
comm.commandtype=commandtype.storedprocedure ;
comm.commandtext =procname;
foreach(sqlparameter para in parameters)
{
sqlparameter p=(sqlparameter)para;
comm.parameters.add(p);
}
da.selectcommand =comm;
da.fill(datatable);
}
catch(exception e)
{
throw new exception(e.message);
}
finally
{
closeconnection();
}
return datatable;
}
public static dataview dataview(string sqlstr)
{
sqldataadapter da=new sqldataadapter();
dataview dv=new dataview();
dataset ds=new dataset();
try
{
openconnection();
comm.commandtype=commandtype.text;
comm.commandtext =sqlstr;
da.selectcommand =comm;
da.fill(ds);
dv=ds.tables[0].defaultview;
}
catch(exception e)
{
throw new exception(e.message);
}
finally
{
closeconnection();
}
return dv;
}
}
}
