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

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