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