#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>[email protected]</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 } 
|