.net教程：中小系统.net dataaccess数据访问类
[size=3]#region 引用对象
using system;
using system.xml ;
using system.data;
using system.data.sqlclient;
using system.web;
#endregion
namespace sysclasslibrary
{
/// <summary>
/// dataaccess 的摘要说明。
/// <author>wuchen</author>
/// <date>2004-4-12</date>
/// <email>scwuchen@263.net</email>
/// <description>数据处理基类,调用方式：dataaccess.dataset((string)sqlstr);或者dataaccess.dataset((string)sqlstr,ref dataset ds); </description>
/// </summary>
public class dataaccess
{
#region 属性
/// <summary>
/// 是否必须关闭数据库连接
/// </summary>
public static bool mustcloseconnection
{
get
{
return _mustcloseconnection;
}
set
{
_mustcloseconnection=value;
}
}
/// <summary>
/// 连接字符串
/// </summary>
public static string connectionstring
{
get
{
if(_connectionstring ==string.empty)
return sysconfig.connectionstring ;
else
return _connectionstring;
}
set
{
_connectionstring =value;
}
}
/// <summary>
/// 是否关闭数据库连接
/// </summary>
private static bool _mustcloseconnection = true;
private static string _connectionstring =string.empty ;
#endregion
#region 类构造函数
/// <summary>
/// 构造函数
/// </summary>
public dataaccess()
{
}
/// <summary>
/// 析构函数，释放相应的对象
/// </summary>
~dataaccess()
{
}
#endregion
#region method
/// <summary>
/// 执行sql查询语句
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
///<returns >i </returns>
public static int executesql(string sqlstr){
int i=0;
using (sqlconnection conn =new sqlconnection(connectionstring))
{
sqlcommand comm=new sqlcommand();
comm.connection =conn;
comm.commandtype =commandtype.text ;
comm.commandtext =sqlstr;
try
{
conn.open();
i=comm.executenonquery();
}
catch(sqlexception e)
{
new errorlog().savedataaccesserror(e);
}
finally
{
conn.close();
comm.dispose();
}
}
return i;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procname">存储过程名</param>
/// <param name="coll">sqlparameters 集合</param>
public static void executeporcedure(string procname,sqlparameter[] coll)
{
using (sqlconnection conn =new sqlconnection(connectionstring))
{
sqlcommand comm=new sqlcommand();
comm.connection =conn;
comm.commandtype =commandtype.storedprocedure ;
executeporcedure(procname,coll,conn,comm);
}
}
public static void executeporcedure(string procname,sqlparameter[] coll,ref dataset ds)
{
using (sqlconnection conn =new sqlconnection(connectionstring))
{
sqlcommand comm=new sqlcommand();
comm.connection =conn;
comm.commandtype =commandtype.storedprocedure ;
executeporcedure(procname,coll,conn,comm,ref ds);
}
}
/// <summary>
/// 执行存储过程类
/// </summary>
/// <param name="procname"></param>
/// <param name="coll"></param>
/// <param name="conn"></param>
/// <param name="comm"></param>
public static void executeporcedure(string procname,sqlparameter[] coll,sqlconnection conn,sqlcommand comm)
{
if(procname ==null || procname=="")
throw new sqlnullexception();
try
{
conn.open();
for(int i=0;i<coll.length;i++)
{
comm.parameters .add(coll[i]);
}
comm.commandtype=commandtype.storedprocedure ;
comm.commandtext =procname;
comm.executenonquery();
}
catch(sqlexception e)
{
new errorlog().savedataaccesserror(e);
}
finally
{
comm.parameters.clear();
conn.close();
comm.dispose();
}
}
public static void executeporcedure(string procname,sqlparameter[] coll,sqlconnection conn,sqlcommand comm,ref dataset ds)
{
if(procname ==null || procname=="")
throw new sqlnullexception();
try
{
sqldataadapter da =new sqldataadapter();
conn.open();
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(sqlexception e)
{
new errorlog().savedataaccesserror(e);
}
finally
{
comm.parameters.clear();
conn.close();
comm.dispose();
}
}
/// <summary>
/// 执行sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> unbox
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
/// <returns>object 返回值 </returns>
public static object executescalar(string sqlstr)
{
if(sqlstr ==null || sqlstr =="")
throw new sqlnullexception();
object obj=new object();
using (sqlconnection conn =new sqlconnection(connectionstring))
{
sqlcommand comm=new sqlcommand();
comm.connection =conn;
comm.commandtype =commandtype.text;
try
{
conn.open();
comm.commandtext =sqlstr;
obj=comm.executescalar();
}
catch(sqlexception e)
{
new errorlog().savedataaccesserror(e);
}
finally
{
conn.close();
comm.dispose();
}
}
return obj;
}
/// <summary>
/// 执行sql查询语句,同时进行事务处理
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
public static void executesqlwithtransaction(string sqlstr)
{
if(sqlstr ==null || sqlstr =="")
throw new sqlnullexception();
using(sqlconnection conn=new sqlconnection(connectionstring))
{
//可以在事务中创建一个保存点，同时回滚到保存点
sqltransaction trans ;
trans=conn.begintransaction();
sqlcommand comm=new sqlcommand();
comm.connection =conn;
comm.transaction =trans;
try
{
conn.open();
comm.commandtype =commandtype.text ;
comm.commandtext =sqlstr;
comm.executenonquery();
trans.commit();
}
catch
{
trans.rollback();
}
finally
{
trans.dispose();
conn.close();
comm.dispose();
}
}
}
/// <summary>
/// 返回指定sql语句的sqldatareader，请注意，在使用后请关闭本对象，同时将自动调用closeconnection()来关闭数据库连接
/// 方法关闭数据库连接
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
/// <returns>sqldatareader对象</returns>
public static sqldatareader datareader(string sqlstr)
{
sqldatareader _datareader =null ;
datareader(sqlstr,ref _datareader);
return _datareader;
}
/// <summary>
/// 返回指定sql语句的sqldatareader，请注意，在使用后请关闭本对象，同时将自动调用closeconnection()来关闭数据库连接
/// 方法关闭数据库连接
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
/// <param name="dr">传入的ref datareader 对象</param>
public static void datareader(string sqlstr,ref sqldatareader _datareader)
{
if(sqlstr ==null || sqlstr =="")
throw new sqlnullexception();
try
{
sqlconnection conn=new sqlconnection(connectionstring);
sqlcommand comm=new sqlcommand();
comm.connection =conn;
comm.commandtext =sqlstr;
comm.commandtype =commandtype.text ;
conn.open();
if(_mustcloseconnection)
{
_datareader=comm.executereader(commandbehavior.closeconnection);
}
else
{
_datareader= comm.executereader();
}
}
catch(sqlexception e)
{
_datareader =null;
//输出错误原因
throw e;
}
}
/// <summary>
/// 返回指定sql语句的dataset
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
/// <returns>dataset</returns>
public static dataset dataset(string sqlstr)
{
if(sqlstr ==null || sqlstr =="")
throw new sqlnullexception();
dataset ds= new dataset();
sqldataadapter da=new sqldataadapter();
using (sqlconnection conn=new sqlconnection(connectionstring))
{
sqlcommand comm=new sqlcommand();
comm.connection =conn;
try
{
conn.open();
comm.commandtype =commandtype.text ;
comm.commandtext =sqlstr;
da.selectcommand =comm;
da.fill(ds);
}
catch(sqlexception e)
{
new errorlog().savedataaccesserror(e);
}
finally
{
conn.close();
}
}
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)
{
if(sqlstr ==null || sqlstr =="")
throw new sqlnullexception();
using (sqlconnection conn=new sqlconnection(connectionstring))
{
sqldataadapter da=new sqldataadapter();
sqlcommand comm=new sqlcommand();
comm.connection =conn;
try
{
conn.open();
comm.commandtype =commandtype.text ;
comm.commandtext =sqlstr;
da.selectcommand =comm;
da.fill(ds);
}
catch(sqlexception e)
{
new errorlog().savedataaccesserror(e);
}
finally
{
conn.close();
}
}
}
/// <summary>
/// 返回指定sql语句的datatable
/// </summary>
/// <param name="sqlstr">传入的sql语句</param>
/// <returns>datatable</returns>
public static datatable datatable(string sqlstr)
{
if(sqlstr ==null || sqlstr =="")
throw new sqlnullexception();
sqldataadapter da=new sqldataadapter();
datatable datatable=new datatable();
using (sqlconnection conn=new sqlconnection(connectionstring))
{
sqlcommand comm=new sqlcommand();
comm.connection =conn;
try
{
conn.open();
comm.commandtype =commandtype.text ;
comm.commandtext =sqlstr;
da.selectcommand =comm;
da.fill(datatable);
}
catch(sqlexception e)
{
new errorlog().savedataaccesserror(e);
}
finally
{
conn.close();
}
}
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)
{
if(sqlstr ==null || sqlstr =="")
throw new sqlnullexception();
if(dt ==null)
dt=new datatable();
sqldataadapter da=new sqldataadapter();
using (sqlconnection conn=new sqlconnection(connectionstring))
{
sqlcommand comm=new sqlcommand();
comm.connection =conn;
try
{
conn.open();
comm.commandtype =commandtype.text ;
comm.commandtext =sqlstr;
da.selectcommand =comm;
da.fill(dt);
}
catch(sqlexception e)
{
new errorlog().savedataaccesserror(e);
}
finally
{
conn.close();
}
}
}
/// <summary>
/// 执行带参数存储过程并返回数据集合
/// </summary>
/// <param name="procname">存储过程名称</param>
/// <param name="parameters">sqlparametercollection 输入参数</param>
/// <returns></returns>
public static datatable datatable(string procname,sqlparametercollection parameters)
{
if(procname ==null || procname =="")
throw new sqlnullexception();
sqldataadapter da=new sqldataadapter();
datatable datatable=new datatable();
using (sqlconnection conn=new sqlconnection(connectionstring))
{
sqlcommand comm=new sqlcommand();
comm.connection =conn;
try
{
comm.parameters.clear();
comm.commandtype=commandtype.storedprocedure ;
comm.commandtext =procname;
foreach(sqlparameter para in parameters)
{
sqlparameter p=(sqlparameter)para;
comm.parameters.add(p);
}
conn.open();
da.selectcommand =comm;
da.fill(datatable);
}
catch(sqlexception e)
{
new errorlog().savedataaccesserror(e);
}
finally
{
conn.close();
}
}
return datatable;
}
/// <summary>
/// dataview
/// </summary>
/// <param name="sqlstr"></param>
/// <returns></returns>
public static dataview dataview(string sqlstr)
{
if(sqlstr ==null || sqlstr =="")
throw new sqlnullexception();
sqldataadapter da=new sqldataadapter();
dataview dv=new dataview();
dataset ds=new dataset();
dataset(sqlstr,ref ds);
dv=ds.tables[0].defaultview;
return dv;
}
#endregion
}
#region 异常类，记录出错信息
/// <summary>
/// 异常类
/// </summary>
public class sqlnullexception:applicationexception
{
/// <summary>
/// 构造函数
/// </summary>
public sqlnullexception(){
new sqlnullexception("dataaccess类中静态成员 参数不能为空。可能是sqlstr =null");
}
/// <summary>
/// 重载出错信息
/// </summary>
/// <param name="message"></param>
public sqlnullexception(string message)
{
//保存出错信息
try
{
//err.savedataaccesserror(message);
httpcontext.current.response.write(message);
}
catch
{
throw;
}
}
/// <summary>
/// 重载出错信息
/// </summary>
/// <param name="e"></param>
public sqlnullexception(sqlexception e)
{
//保存出错信息
try
{
httpcontext.current.response.write(e.message);
//err.savedataaccesserror(e);
}
catch
{
throw;
}
}
/// <summary>
/// 析构函数
/// </summary>
~ sqlnullexception()
{
}
private errorlog err=new errorlog();
}
#endregion
#region errorlog 错误日志捕获
/// <summary>
/// errorlog 的摘要说明。
/// </summary>
public class errorlog
{
/// <summary>
/// ctr
/// </summary>
public errorlog()
{
//
// todo: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 数据库访问出错日志
/// </summary>
/// <param name="e">错误信息 </param>
public void savedataaccesserror(sqlexception e)
{
//生成的错误行号
// int linenumber = e.linenumber ;
// string message= e.message;
// int number =e.number;
// string procedure=e.procedure ;
// string source=e.source ;
//
// string errmessage ="linenumber："+linenumber.tostring() + " ---- procedure："+ procedure.tostring() ;
// string errsource =source ;
// string errtargetsite ="错误号："+number ;
// string url =httpcontext.current.request.urlreferrer.absolutepath ;
// string ip = httpcontext.current.request.userhostaddress ;
// try
// {
// sysclasslibrary.dataaccess.mustcloseconnection =true;
// sysclasslibrary.dataaccess.executesql(string.format("insert into sys_errorlog(errmessage,errsource,errtargetsite,url,ip)values('{0}','{1}','{2}','{3}','{4}') ",errmessage,errsource,errtargetsite,url,ip));
// }
// catch
// {
// }
}
/// <summary>
/// 数据库访问出错日志
/// </summary>
/// <param name="message">出错信息</param>
public void savedataaccesserror(string message)
{
//生成的错误行号
// string errmessage =message;
// string errsource ="" ;
// string errtargetsite ="";
// string url =httpcontext.current.request.urlreferrer.absolutepath ;
// string ip = httpcontext.current.request.userhostaddress ;
// try
// {
// sysclasslibrary.dataaccess.mustcloseconnection =true;
// sysclasslibrary.dataaccess.executesql(string.format("insert into sys_errorlog(errmessage,errsource,errtargetsite,url,ip)values('{0}','{1}','{2}','{3}','{4}') ",errmessage,errsource,errtargetsite,url,ip));
// }
// catch
// {
// }
}
}
#endregion
}
[/size]
页:
[1]