using System; using System.Data; using System.Data.OleDb; using System.Web;
namespace SysClassLibrary { /// <summary> /// DataAccess 数据访问类 /// <description>数据处理基类,调用方式: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 内部函数 静态方法中不会执行DataAccess()构造函数
/// <summary> /// 打开数据库连接 /// </summary> private static void openConnection() { if (conn.State == ConnectionState.Closed) { //SysConfig.ConnectionString 为系统配置类中连接字符串
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> /// 关闭当前数据库连接 /// </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 ex) { throw new Exception(ex.Message); } finally { 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 ex) { throw new Exception(ex.Message); } finally { closeConnection(); } return obj; }
/// <summary> /// 执行Sql查询语句,同时进行事务处理 /// </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,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接 /// 方法关闭数据库连接 /// </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,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接 /// 方法关闭数据库连接 /// </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> /// 执行指定Sql语句,同时给传入DataTable进行赋值 /// </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; } }
}
|