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();
}
}