中小系统.net DataAccess数据访问类

2022-07-15 80酷酷网 80kuku.com

　　access|访问|数据

#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>scwuchen263.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
}