|
using System;
using System.Data;
using System.Data.OleDb;
using System.Web;
namespace SysClassLibrary
{
/// <summary>
/// DataAccess 數(shù)據(jù)訪問類
/// <description>數(shù)據(jù)處理基類,調(diào)用方式：DataAccess.DataSet((string)sqlstr);或者DataAccess.DataSet((string)sqlstr,ref DataSet ds); </description>
/// </summary>
public class DataAccess
{
#region 屬性
protected static OleDbConnection conn = new OleDbConnection();
protected static OleDbCommand comm = new OleDbCommand();
#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)配置類中連接字符串
string strDbName = HttpContext.Current.Server.MapPath(@"~/App_Data/BookShop.mdb");
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDbName;
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 ex)
{
throw new Exception(ex.Message);
}
finally
{
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 ex)
{
throw new Exception(ex.Message);
}
finally
{
closeConnection();
}
return obj;
}
/// <summary>
/// 執(zhí)行Sql查詢語句,同時進(jìn)行事務(wù)處理
/// </summary>
/// <param name="sqlstr">傳入的Sql語句</param>
public static void ExecuteSqlWithTransaction(string sqlstr)
{
closeConnection();
openConnection();
OleDbTransaction 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語句的OleDbDataReader，請注意，在使用后請關(guān)閉本對象，同時將自動調(diào)用closeConnection()來關(guān)閉數(shù)據(jù)庫連接
/// 方法關(guān)閉數(shù)據(jù)庫連接
/// </summary>
/// <param name="sqlstr">傳入的Sql語句</param>
/// <returns>OleDbDataReader對象</returns>
public static OleDbDataReader DataReader(string sqlstr)
{
OleDbDataReader 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語句的OleDbDataReader，請注意，在使用后請關(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 OleDbDataReader 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();
OleDbDataAdapter da = new OleDbDataAdapter();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception ex)
{
throw new Exception(ex.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)
{
OleDbDataAdapter da = new OleDbDataAdapter();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
closeConnection();
}
}
/// <summary>
/// 返回指定Sql語句的DataTable
/// </summary>
/// <param name="sqlstr">傳入的Sql語句</param>
/// <returns>DataTable</returns>
public static DataTable DataTable(string sqlstr)
{
OleDbDataAdapter da = new OleDbDataAdapter();
DataTable Datatable = new DataTable();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(Datatable);
}
catch (Exception ex)
{
throw new Exception(ex.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)
{
OleDbDataAdapter da = new OleDbDataAdapter();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
closeConnection();
}
}
/// <summary>
/// 返回指定Sql語句的DataView
/// </summary>
/// <param name="sqlstr">傳入的Sql語句</param>
/// <returns>DataView</returns>
public static DataView DataView(string sqlstr)
{
OleDbDataAdapter da = new OleDbDataAdapter();
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 ex)
{
throw new Exception(ex.Message);
}
finally
{
closeConnection();
}
return dv;
}
}
}
|
