本着代码公开的一些精神，今天把我自己封装的ADO数据库操作层公共出来给大家看看
此类是Sqlserver的操作类，同时数据错误处理要主要是前台的样式。
public class Connection
{
/// <summary>
/// 获取连接对象
/// </summary>
/// <param name="ConnectionString">连接字符串</param>
/// <returns>连接对象</returns>
public static SqlConnection getConnection(string ConnectionString)
{
SqlConnection conn = new SqlConnection(ConnectionString);
return conn;
}
/// <summary>
/// 开启数据库链接
/// </summary>
/// <param name="conn">链接对象</param>
public static void openConnection(SqlConnection conn)
{
if (conn.State != System.Data.ConnectionState.Connecting)
{
closeConnection(conn);//如果是打开的 就关闭他
}
conn.Open();
}
/// <summary>
/// 关闭数据库链接
/// </summary>
/// <param name="conn">连接对象</param>
public static void closeConnection(SqlConnection conn)
{
if (conn.State != System.Data.ConnectionState.Connecting)
{
conn.Close();
}
}
}Command类，用于构造command对象
public class Command
{
/// <summary>
/// 构建Command
/// </summary>
/// <param name="cmdType">执行的类型 执行存储过程或者SQL语句</param>
/// <param name="CommandText">执行的SQL或者存储过程的名称</param>
/// <param name="CommandTimeout">等待时间</param>
/// <param name="conn">数据库链接对象</param>
/// <param name="parameters">参数 </param>
/// <returns>SqlCommand</returns>
public static SqlCommand getCommand(CommandType cmdType, string CommandText,int CommandTimeout, SqlConnection conn, params SqlParameter[] parameters)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = cmdType;
cmd.CommandText = CommandText;
if (CommandTimeout > 0)
{
cmd.CommandTimeout = CommandTimeout;
}
if (parameters != null)//如果参数不为空
{
foreach (SqlParameter para in parameters)//循环参数
{
cmd.Parameters.Add(para);//添加参数
}
}
return cmd;
}
/// <summary>
/// 构建Command
/// </summary>
/// <param name="cmdType">执行的类型 执行存储过程或者SQL语句</param>
/// <param name="CommandText">执行的SQL或者存储过程的名称</param>
/// <param name="conn">数据库链接对象</param>
/// <param name="parameters">参数 </param>
/// <returns>SqlCommand</returns>
public static SqlCommand getCommand(CommandType cmdType, string CommandText, SqlConnection conn, params SqlParameter[] parameters)
{
return getCommand(cmdType, CommandText, 5000, conn, parameters);
}
/// <summary>
/// 构建Command(执行Sql语句的)
/// </summary>
/// <param name="CommandText">执行的SQL或者存储过程的名称</param>
/// <param name="conn">数据库链接对象</param>
/// <param name="parameters">参数 </param>
/// <returns>SqlCommand</returns>
public static SqlCommand getCommand( string CommandText, SqlConnection conn, params SqlParameter[] parameters)
{
return getCommand(CommandType.Text, CommandText, 5000, conn, parameters);
}
}
DbHelperSQL类，用于执行方法
public class DbHelperSQL
{
private static readonly string constr = ConfigurationSettings.AppSettings["connstring"];//得到数据库联接（判断是什么联接）
/// <summary>
/// 执行查询类型的SQL或者存储过程
/// </summary>
/// <param name="ProcedureName">SQL语句或者存储过程名称</param>
/// <param name="CmdType">执行的类型SQL或者存储过程</param>
/// <param name="parameters">SQL参数或者存储过程参数</param>
/// <returns>DataSet数据集合</returns>
private static DataSet SqlServerQuery(string ProcedureName, CommandType CmdType,params SqlParameter[] parameters)
{
SqlConnection conn=null;
SqlCommand cmd=null;
DataSet ds = null;
SqlDataAdapter ada=null;
try
{
conn= Connection.getConnection(constr);//得到连接对象
cmd = Command.getCommand(CmdType, ProcedureName, 0, conn, parameters);//得到Command对象
Connection.openConnection(conn);//打开连接
ada = new SqlDataAdapter();
ada.SelectCommand = cmd; //设置参数
ds = new DataSet();
ada.Fill(ds); //填充数据
Connection.closeConnection(conn);//关闭连接
return ds;
}catch(Exception ex)
{
System.Web.HttpContext.Current.Response.Write(DataException.getDataException(ProcedureName,constr,ex.Message,parameters));//抛到前台显示出来
//后期可以添加写入 日志的功能
System.Web.HttpContext.Current.Response.End();
}
return ds;
}
/// <summary>
/// 执行操作类型的SQL或者存储过程
/// </summary>
/// <param name="ProcedureName">SQL语句或者存储过程名称</param>
/// <param name="CmdType">执行的类型SQL或者存储过程</param>
/// <param name="parameters">SQL参数或者存储过程参数</param>
/// <returns>受影响的行数</returns>
private static int SqlServerExecute(string ProcedureName, CommandType CmdType, params SqlParameter[] parameters)
{
SqlConnection conn = null;
SqlCommand cmd = null;
int falge = -1;
try
{
conn = Connection.getConnection(constr);//得到连接对象
cmd = Command.getCommand(CmdType, ProcedureName, 0, conn, parameters);//得到Command对象
Connection.openConnection(conn);//打开连接
falge = cmd.ExecuteNonQuery();
Connection.closeConnection(conn);//关闭连接
return falge;
}
catch (SqlException ex)
{
System.Web.HttpContext.Current.Response.Write(DataException.getDataException(ProcedureName, constr, ex.Message, parameters));//抛到前台显示出来
//后期可以添加写入 日志的功能
System.Web.HttpContext.Current.Response.End();
}
return falge;
}
/// <summary>
/// 执行的SQL或者存储过程返回第一行第一列的值
/// </summary>
/// <param name="ProcedureName">SQL语句或者存储过程名称</param>
/// <param name="CmdType">执行的类型SQL或者存储过程</param>
/// <param name="parameters">SQL参数或者存储过程参数</param>
/// <returns>返回Object类型第一行第一列的值</returns>
private static object SqlserverExecuteScalar(string ProcedureName, CommandType CmdType, params SqlParameter[] parameters)
{
SqlConnection conn = null;
SqlCommand cmd = null;
object returnValue = -1;
try
{
conn = Connection.getConnection(constr);//得到连接对象
cmd = Command.getCommand(CmdType, ProcedureName, 0, conn, parameters);//得到Command对象
Connection.openConnection(conn);//打开连接
returnValue = cmd.ExecuteScalar();
Connection.closeConnection(conn);//关闭连接
return returnValue;
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write(DataException.getDataException(ProcedureName, constr, ex.Message, parameters));//抛到前台显示出来
System.Web.HttpContext.Current.Response.End();
//后期可以添加写入 日志的功能
}
return returnValue;
}
/// <summary>
///执行查询存储过程
/// </summary>
/// <param name="strProcName">存储过程名称</param>
/// <param name="parameters">存储过程的参数</param>
/// <param name="TableName">无用的（存储的表的名称）</param>
/// <returns>DataSet数据集合</returns>
public static DataSet RunProcedure(string strProcName, SqlParameter[] parameters,string TableName)
{
return SqlServerQuery(strProcName, CommandType.StoredProcedure, parameters);
}
/// <summary>
/// 执行SQL 返回第一列第一行数据
/// </summary>
/// <param name="strProcName">SQL语句</param>
/// <param name="parameters">SQL参数</param>
/// <returns>Object对象</returns>
public static object RunSqlScalar(string strProcName, SqlParameter[] parameters)
{
return SqlserverExecuteScalar(strProcName, CommandType.Text, parameters);
}
/// <summary>
/// 执行操作类的存储过程
/// </summary>
/// <param name="strProcName">存储过程名称</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>操作所影响的行数</returns>
public static int RunProcedure(string strProcName, SqlParameter[] parameters)
{
return SqlServerExecute(strProcName, CommandType.StoredProcedure, parameters);
}
public static int RunSqlExecute(string strProcName, SqlParameter[] parameters)
{
return SqlServerExecute(strProcName, CommandType.Text, parameters);
}
/// <summary>
/// 返回第一行 第一列
/// </summary>
/// <param name="strProcName">存储过程名称</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>第一行第一列的值</returns>
///
public static object RunProcedureScalar(string strProcName, SqlParameter[] parameters)
{
return SqlserverExecuteScalar(strProcName, CommandType.StoredProcedure, parameters);
}
/// <summary>
/// 执行查询类SQL语句
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns>DataSet 数据集合</returns>
public static DataSet Query(string strSql)
{
return SqlServerQuery(strSql, CommandType.Text, null);
}
}
还有一个错误处理的类DataException
public class DataException
{
/// <summary>
/// 添加错误信息
/// </summary>
/// <param name="strProcName">执行的SQL或者存储过程名称</param>
/// <param name="strConnstring">连接语句</param>
/// <param name="ExceptionText">错误信息</param>
/// <param name="parameters">SQL或者存储过程参数</param>
/// <returns>组合好的错误信息</returns>
public static string getDataException(string strProcName,string strConnstring,string ExceptionText, params SqlParameter[] parameters)
{
StringBuilder sb = new StringBuilder();
sb.Append("<div style=\"font-size:12px\">");
sb.Append("数据操作错误：<br/>");
sb.AppendFormat("<span style='color:red'> 提示错误信息：{0}<span>", ExceptionText);
sb.AppendFormat("连接字符串为：{0}<br/>",strConnstring);
sb.AppendFormat("执行的SQL/存储过程名称为：{0}<br/>",strProcName);
sb.Append("<table style=\"font-size:12px\">");
if(parameters!=null)
{
foreach (SqlParameter para in parameters)//循环参数
{
sb.AppendFormat("<tr><td<参数名：</td><td>{0}</td><td> 参数类型：</td><td>{1}</td><td> 参数值：</td><td>{2}</td></tr>", para.ParameterName, para.SqlDbType, para.Value);
}
}
sb.Append("</table>");
sb.Append("</div>");
return sb.ToString();
}
}