自己写的数据访问层

技术2022-05-11 77

using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;

/// <summary>/// CNetWorks数据访问层///**************************************/// DesignBy:兴百放/// CreateDate:2006-12-09 4:41/// *************************************/// </summary>public class DataAccessLayer{ private SqlCommand cmd; //建立Command对象 private static string errorMeg; //错误信息包括Sql语句及存储过程 private static bool isShowErrorMeg; //是否显示错误信息

/// <summary> /// 返回值参数类型 /// </summary> public enum ParameterKind { Int, NVarChar, Bit, VarChar //如果变化，可以在加 } /// <summary> /// 获得错误信息 /// </summary> public string Error { get { return errorMeg; } }

/// <summary> /// 构造函数，初始化值 /// </summary> public DataAccessLayer() { cmd = new SqlCommand(); //建立Command 连接 //只要修该这里的参数就行，把你的Web.Config中的连接字符串名改成你的就行

cmd.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NicNetWorkConnectionString"].ConnectionString); isShowErrorMeg = true; //本地运行为True;服务器运行为False; }

/// <summary> /// 初始化cm.CommandType和cm.CommandType /// </summary> /// <param name="commandText">执行存储过程或Sql语句</param> /// <param name="commandType">1--Sql语句；2--存储过程</param> public void SetCommand(string commandText,int commandType) { errorMeg = "0"; //清空错误信息 cmd.CommandText = commandText; if (commandType == 1) { cmd.CommandType = CommandType.Text; //执行Sql语句 } else { cmd.CommandType = CommandType.StoredProcedure; //执行存储过程 } }

//---------------------------------------设置错误信息---------------------------------------------// /// <summary> /// 设置显示错误信息 /// </summary> /// <param name="FunctionName">出现错误的函数名</param> /// <param name="cmdtext">错误Sql语句或存储过程</param> /// <param name="message">错误信息</param> private void SetErrorMeg(string FunctionName,string cmdtext,string message) { errorMeg = "<br>函数名为：" + FunctionName + "出现错误.<br>错误名为：" + message; if(isShowErrorMeg) { errorMeg += "<br>查询语句或存储过程是：" + cmdtext;

} addLogErr(cmdtext,errorMeg); cmd.Connection.Close(); //关闭连接 }

记录日志文件/// <summary>/// 把错误日志记录到log文件下/// </summary>/// <param name="SPName">查询语句或者存储过程名</param>/// <param name="ErrDescribe">错误描述</param> public void addLogErr(string SPName, string ErrDescribe) { //记录到错误日志 string FilePath = System.Web.HttpContext.Current.Server.MapPath("~/Log/" + DateTime.Now.ToString("yyyyMMdd") + ".txt"); System.Text.StringBuilder str = new System.Text.StringBuilder(); str.Append(DateTime.Now.ToString()); str.Append(" "); str.Append(SPName); str.Append(" "); str.Append(ErrDescribe.Replace("<br>", "")); str.Append(" ");

System.IO.StreamWriter sw = null; try { sw = new System.IO.StreamWriter(FilePath, true, System.Text.Encoding.Unicode); sw.Write(str.ToString()); } catch (Exception ex) { System.Web.HttpContext.Current.Response.Write("没有访问日志文件的权限！或日志文件只读!"); } finally { if (sw != null) sw.Close(); } } 记录日志文件 //---------------------------------------设置错误信息---------------------------------------------//

//---------------------------------- —— 存储过程部分，包括存储过程的参数----------------------------// /// <summary> /// 清空参数值 /// </summary> public void ClearParamers() { cmd.Parameters.Clear(); //清空参数 } /// <summary> /// 设置返回方向 /// </summary> /// <param name="ParameterName">参数名，如@Return</param> /// <param name="Kind">参数的类型，如nvchar</param> /// <param name="Des">参数的方向,1-输出参数；2-返回值;3-输入参数</param> /// <param name="valueSize">输入参数的值得大小</param> public void AddNewParameter(string ParameterName,ParameterKind Kind,string Des,string valueSize) { switch(Kind) { case ParameterKind.Bit : cmd.Parameters.Add(ParameterName,SqlDbType.Bit,int.Parse(valueSize)); //设置参数 break; case ParameterKind.Int : cmd.Parameters.Add(ParameterName,SqlDbType.Int,int.Parse(valueSize)); //设置参数 break; case ParameterKind.NVarChar : cmd.Parameters.Add(ParameterName,SqlDbType.NVarChar,int.Parse(valueSize)); //设置参数 break; case ParameterKind.VarChar : cmd.Parameters.Add(ParameterName,SqlDbType.VarChar,int.Parse(valueSize)); break; } switch(Des) { case "1" : cmd.Parameters[ParameterName].Direction = ParameterDirection.Output; //设置方向 break; case "2" : cmd.Parameters[ParameterName].Direction = ParameterDirection.ReturnValue; //设置方向 break; case "3" : cmd.Parameters[ParameterName].Direction = ParameterDirection.Input; break; } //if (Des == "1") //{ //} //else if (Des == "2") //{ //} //else //{ //} } /// <summary> /// 根据参数获得存储过程的返回值 /// </summary> /// <param name="ParameterName">参数名，如@UserName</param> /// <returns></returns> public string GetParameter(string ParameterName) { //cmd.Parameters[ParameterName]. return cmd.Parameters[ParameterName].Value.ToString(); } public int GetParameter(int ParameterName) { return int.Parse(cmd.Parameters[ParameterName].Value.ToString()); } /// 添加参数值 /// </summary> /// <param name="ParameterName">参数名如@UserName</param> /// <param name="ParameterValue">参数名的值</param> public void AddNewParameter(string ParameterName,string ParameterValue) { cmd.Parameters.Add(ParameterName,ParameterValue); } public void AddNewParameter(string ParameterName,int ParameterValue) { cmd.Parameters.Add(ParameterName,ParameterValue); } public void AddNewParameter(string ParameterName, bool ParameterValue) { cmd.Parameters.Add(ParameterName, ParameterValue); }

//运行存储过程返回DataSet /// <summary> /// 运行存储过程返回DataSet,DataSet里面可能有好多表 /// </summary> /// <param name="StroreName">存储过程名</param> /// <returns>返回DataSet如果出错侧返回null</returns> public DataSet RunStoreDataSet(string StroreName) { SetCommand(StroreName,2); //执行存储过程 SqlDataAdapter sda = new SqlDataAdapter(cmd); try { DataSet ds = new DataSet(); sda.Fill(ds); return ds; } catch(System.Exception e1) { SetErrorMeg("RunStoreDataSet", StroreName, e1.ToString()); //处理错误 return null; } } /// <summary> /// 执行存储过程返回DataTable，一张表最好用它； /// </summary> /// <param name="StoreName">存储过程名</param> /// <returns>返回DataTable</returns> public DataTable RunStoreDataTable(string StoreName) { SetCommand(StoreName, 2); SqlDataAdapter sda = new SqlDataAdapter(cmd); try { DataTable dt = new DataTable(); sda.Fill(dt); return dt; } catch (System.Exception e1) { SetErrorMeg("RunStoreDataTable", StoreName, e1.ToString()); //处理错误 return null; } finally { sda.Dispose(); //释放资源 } } /// <summary> /// 运行存储过程返回数据阅读器,单行纪录 /// </summary> /// <param name="StoreName">存储过程名</param> /// <returns>DataReader</returns> public SqlDataReader RunStoreNameDataReader(string StoreName) { SqlDataReader dr=null;

SetCommand(StoreName,2); try { if (cmd.Connection.State == ConnectionState.Broken || cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } dr= cmd.ExecuteReader(); } catch (System.Exception e1) { SetErrorMeg("RunStoreNameDataReader", StoreName, e1.ToString()); //处理错误信息 } return dr; }

/// <summary> /// 运行存储过程，用于添加数据的添加，删除，修改,无返回值 /// </summary> /// <param name="StoreName">存储过程名</param> public void RunStore(string StoreName) { SetCommand(StoreName, 2); try { //如果cmd对象的连接关闭侧打开 if (cmd.Connection.State == ConnectionState.Broken || cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } //执行 cmd.ExecuteNonQuery(); } catch (System.Exception e1) { SetErrorMeg("RunStore", StoreName, e1.ToString()); //处理错误信息 } //finally //{ // Dispone(); //} } /// <summary> /// 运行存储过程，返回第一行第一列 /// </summary> /// <param name="StoreName"></param> /// <returns></returns> public string RunStoreFirst(string StoreName) { SetCommand(StoreName, 2); string flag = null; try { if (cmd.Connection.State == ConnectionState.Broken || cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } //执行 SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { flag = dr.GetValue(0).ToString(); } } catch (System.Exception e1) { SetErrorMeg("RunStore", StoreName, e1.ToString()); return null; } Dispone(); return flag; } //---------------------------------- —— 存储过程部分，包括存储过程的参数----------------------------//

//---------------------------------------sql部分--------------------------------------------- /// <summary> /// 运行SQl语句返回第一条记录的第一列的值。 /// </summary> /// <param name="sqlName"></param> public string RunSql(string sqlName) { SetCommand(sqlName,1); string flag = null; SqlDataReader dr=null; try { if (cmd.Connection.State == ConnectionState.Broken || cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } //执行 dr = cmd.ExecuteReader(); if (dr.Read()) { flag = dr.GetValue(0).ToString(); } } catch (System.Exception e1) { SetErrorMeg("RunSql", sqlName, e1.ToString()); return null; } dr.Close(); Dispone(); return flag; } /// <summary> /// 执行sql语句，返回DataSet /// </summary> /// <param name="SqlName">sql语句</param> /// <returns>返回DataSet</returns> public DataSet RunSqlDataSet(string SqlName) { SetCommand(SqlName, 1); //执行sql SqlDataAdapter sda = new SqlDataAdapter(cmd); try { DataSet ds = new DataSet(); sda.Fill(ds); return ds; } catch (System.Exception e1) { SetErrorMeg("RunSqlDataSet", SqlName, e1.ToString()); //处理错误 return null; } //finally //{ // sda.Dispose(); //释放sda的资源 //} } /// <summary> /// 执行sql语句返回DataTable /// </summary> /// <param name="SqlName">sql语句</param> /// <returns>返回DataSet</returns> public DataTable RunSqlDataTable(string SqlName) { SetCommand(SqlName, 1); SqlDataAdapter sda = new SqlDataAdapter(cmd); try { DataTable dt = new DataTable(); sda.Fill(dt); return dt; } catch (System.Exception e1) { SetErrorMeg("RunSqlDataTable", SqlName, e1.ToString()); //处理错误 return null; } finally { sda.Dispose(); //释放资源 } } /// <summary> /// 执行sql语句，返回DataReader /// </summary> /// <param name="SqlName"></param> /// <returns></returns> public SqlDataReader RunSqlNameDataReader(string SqlName) { SqlDataReader dr = null; //if(dr.IsClosed.ToString()=="false") //{ // dr.Close(); //} SetCommand(SqlName, 1); try { if (cmd.Connection.State == ConnectionState.Broken || cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } dr = cmd.ExecuteReader(); } catch (System.Exception e1) { SetErrorMeg("RunSqlNameDataReader", SqlName, e1.ToString()); //处理错误信息 } return dr; } /// <summary> /// 执行sql语句，无返回值，用于数据的添加，删除，修改 /// </summary> /// <param name="SqlName">sql语句</param> public void RunSqlName(string SqlName) { SetCommand(SqlName, 1); try { //如果cmd对象的连接关闭侧打开 if (cmd.Connection.State == ConnectionState.Broken || cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } //执行 cmd.ExecuteNonQuery(); } catch (System.Exception e1) { SetErrorMeg("RunSql", SqlName, e1.ToString()); //处理错误信息 } }

//---------------------------------------sql部分---------------------------------------------

释放资源 public void Dispone() { errorMeg=null; cmd.Parameters.Clear(); cmd.Connection.Close(); cmd.Dispose(); }}

专利

最新回复(0)