ASP.NET 數(shù)據(jù)訪問類
2024-07-10 12:57:21
供稿：網(wǎng)友
using system;
using system.data;
using system.data.sqlclient;
namespace sysclasslibrary
{
/// <summary>
/// dataaccess 的摘要說明。
/// <description>數(shù)據(jù)處理基類,調(diào)用方式：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 內(nèi)部函數(shù) 靜態(tài)方法中不會執(zhí)行dataaccess()構(gòu)造函數(shù)
/// <summary>
/// 打開數(shù)據(jù)庫連接
/// </summary>
private static void openconnection()
{
if (conn.state == connectionstate.closed)
{
//sysconfig.connectionstring 為系統(tǒng)配置類中連接字符串，如："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>
/// 關(guān)閉當(dāng)前數(shù)據(jù)庫連接
/// </summary>
private static void closeconnection()
{
if(conn.state == connectionstate.open)
conn.close();
conn.dispose();
comm.dispose();
}
#endregion
/// <summary>
/// 執(zhí)行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>
/// 執(zhí)行存儲過程
/// </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>
/// 執(zhí)行存儲過程并返回?cái)?shù)據(jù)集
/// </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>
/// 執(zhí)行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>
/// 執(zhí)行sql查詢語句,同時進(jìn)行事務(wù)處理
/// </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，請注意，在使用后請關(guān)閉本對象，同時將自動調(diào)用closeconnection()來關(guān)閉數(shù)據(jù)庫連接
/// 方法關(guān)閉數(shù)據(jù)庫連接
/// </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，請注意，在使用后請關(guān)閉本對象，同時將自動調(diào)用closeconnection()來關(guān)閉數(shù)據(jù)庫連接
/// 方法關(guān)閉數(shù)據(jù)庫連接
/// </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>
/// 執(zhí)行指定sql語句,同時給傳入datatable進(jìn)行賦值
/// </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>
/// 執(zhí)行帶參數(shù)存儲過程并返回?cái)?shù)據(jù)集合
/// </summary>
/// <param name="procname">存儲過程名稱</param>
/// <param name="parameters">sqlparametercollection 輸入?yún)?shù)</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;
}
}
}
