Web.Config
<connectionStrings>
<add name="TestConn" connectionString="Data Source=Andy-NB;Initial Catalog=testDB;Persist Security Info=True;User ID=testID;Password=testPWD" providerName="System.Data.SqlClient"/>
</connectionStrings>
类一:
Code
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>
/// DataBase 提供数据库操作的基类,不使用存储过程的操作数据库,使用只需稍微修改
/// </summary>
public class DataBase : IDisposable
{
#region 构造函数
public DataBase()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
#endregion
private SqlConnection con = null; //创建连接对象
#endregion
/// <summary>
/// 打开数据库连接.
/// </summary>
private void Open()
{
// 打开数据库连接
if (con == null)
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConn"].ConnectionString);//我这web.config里连接字符串配置如此,使用时请按照自己的来配置
}
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
if (con != null && con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
}
#endregion
/// <summary>
/// 释放资源
/// </summary>
public void Dispose()
{
// 确认连接是否已经关闭
if (con != null)
{
con.Dispose();
con = null;
}
}
#endregion
/// <summary>
/// 转换参数
/// </summary>
/// <param name="ParamName">存储过程名称或命令文本</param>
/// <param name="DbType">参数类型</param></param>
/// <param name="Size">参数大小</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
/// 初始化参数值
/// </summary>
/// <param name="ParamName">存储过程名称或命令文本</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <param name="Direction">参数方向</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param;
param = new SqlParameter(ParamName, DbType, Size);
else //未指定大小
param = new SqlParameter(ParamName, DbType);
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
return param;
}
/// <summary>
/// 执行数据库操作命令,有参数
/// </summary>
/// <param name="procName">命令文本</param>
/// <param name="prams">参数对象</param>
/// <returns></returns>
public int RunProc(string procName, SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
this.Close();
//得到执行成功返回值
return (int)cmd.Parameters["ReturnValue"].Value;
}
/// 直接执行SQL语句,没有参数
/// </summary>
/// <param name="procName">命令文本</param>
/// <returns></returns>
public int RunProc(string procName)
{
this.Open();
SqlCommand cmd = new SqlCommand(procName, con);
cmd.ExecuteNonQuery();
this.Close();
return 1;
}
/// <summary>
/// 执行查询命令文本,并且返回DataSet数据集,有参数
/// </summary>
/// <param name="procName">命令文本</param>
/// <param name="prams">参数对象</param>
/// <param name="tbName">数据表名称</param>
/// <returns></returns>
public DataSet RunProcReturn(string procName, SqlParameter[] prams, string tbName)
{
SqlDataAdapter dap = CreateDataAdaper(procName, prams);
DataSet ds = new DataSet();
dap.Fill(ds, tbName);
this.Close();
//得到执行成功返回值
return ds;
}
/// 执行命令文本,并且返回DataSet数据集,无参数
/// </summary>
/// <param name="procName">命令文本</param>
/// <param name="tbName">数据表名称</param>
/// <returns>DataSet</returns>
public DataSet RunProcReturn(string procName, string tbName)
{
SqlDataAdapter dap = CreateDataAdaper(procName, null);
DataSet ds = new DataSet();
dap.Fill(ds, tbName);
this.Close();
//得到执行成功返回值
return ds;
}
/// 执行命令文本,并且返回SqlDataReader数据集,无参数
/// </summary>
/// <param name="procName">命令文本</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader RunProcReturn(string procName)
{
///创建SqlCommand
SqlCommand cmd = CreateCommand(procName, null);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
/// 执行命令文本,并且返回SqlDataReader数据集,有参数
/// </summary>
/// <param name="procName">命令文本</param>
/// <param name="prams">参数</param>
/// <returns></returns>
public SqlDataReader RunProcReturn(string procName, SqlParameter[] prams)
{
///创建SqlCommand
SqlCommand cmd = CreateCommand(procName, prams);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
/// <summary>
/// 创建一个SqlDataAdapter对象以此来执行命令文本
/// </summary>
/// <param name="procName">命令文本</param>
/// <param name="prams">参数对象</param>
/// <returns></returns>
private SqlDataAdapter CreateDataAdaper(string procName, SqlParameter[] prams)
{
this.Open();
SqlDataAdapter dap = new SqlDataAdapter(procName, con);
dap.SelectCommand.CommandType = CommandType.Text; //执行类型:命令文本
if (prams != null)
{
foreach (SqlParameter parameter in prams)
dap.SelectCommand.Parameters.Add(parameter);
}
//加入返回参数
dap.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
}
#endregion
/// <summary>
/// 创建一个SqlCommand对象以此来执行命令文本
/// </summary>
/// <param name="procName">命令文本</param>
/// <param name="prams"命令文本所需参数</param>
/// <returns>返回SqlCommand对象</returns>
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
// 确认打开连接
this.Open();
SqlCommand cmd = new SqlCommand(procName, con);
cmd.CommandType = CommandType.Text; //执行类型:命令文本
if (prams != null)
{
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
// 加入返回参数
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
}
#endregion
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>
/// DataBase 提供数据库操作的基类,不使用存储过程的操作数据库,使用只需稍微修改
/// </summary>
public class DataBase : IDisposable
{
#region 构造函数
public DataBase()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
#endregion
#region 创建连接对象
private SqlConnection con = null; //创建连接对象
#endregion
#region 打开数据库连接
/// <summary>
/// 打开数据库连接.
/// </summary>
private void Open()
{
// 打开数据库连接
if (con == null)
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConn"].ConnectionString);//我这web.config里连接字符串配置如此,使用时请按照自己的来配置
}
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
}
#region 关闭连接
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
if (con != null && con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
}
#endregion
#region 释放数据库连接资源
/// <summary>
/// 释放资源
/// </summary>
public void Dispose()
{
// 确认连接是否已经关闭
if (con != null)
{
con.Dispose();
con = null;
}
}
#endregion
#region 传入参数并且转换为SqlParameter类型
/// <summary>
/// 转换参数
/// </summary>
/// <param name="ParamName">存储过程名称或命令文本</param>
/// <param name="DbType">参数类型</param></param>
/// <param name="Size">参数大小</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
/// <summary>
/// 初始化参数值
/// </summary>
/// <param name="ParamName">存储过程名称或命令文本</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <param name="Direction">参数方向</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param;
if (Size > 0) //指定参数大小
param = new SqlParameter(ParamName, DbType, Size);
else //未指定大小
param = new SqlParameter(ParamName, DbType);
param.Direction
= Direction;if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
return param;
}
#endregion
#region 执行参数命令文本(无数据库中数据返回)
/// <summary>
/// 执行数据库操作命令,有参数
/// </summary>
/// <param name="procName">命令文本</param>
/// <param name="prams">参数对象</param>
/// <returns></returns>
public int RunProc(string procName, SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
this.Close();
//得到执行成功返回值
return (int)cmd.Parameters["ReturnValue"].Value;
}
/// <summary>
/// 直接执行SQL语句,没有参数
/// </summary>
/// <param name="procName">命令文本</param>
/// <returns></returns>
public int RunProc(string procName)
{
this.Open();
SqlCommand cmd = new SqlCommand(procName, con);
cmd.ExecuteNonQuery();
this.Close();
return 1;
}
#endregion
#region 执行参数命令文本(有返回值)
/// <summary>
/// 执行查询命令文本,并且返回DataSet数据集,有参数
/// </summary>
/// <param name="procName">命令文本</param>
/// <param name="prams">参数对象</param>
/// <param name="tbName">数据表名称</param>
/// <returns></returns>
public DataSet RunProcReturn(string procName, SqlParameter[] prams, string tbName)
{
SqlDataAdapter dap = CreateDataAdaper(procName, prams);
DataSet ds = new DataSet();
dap.Fill(ds, tbName);
this.Close();
//得到执行成功返回值
return ds;
}
/// <summary>
/// 执行命令文本,并且返回DataSet数据集,无参数
/// </summary>
/// <param name="procName">命令文本</param>
/// <param name="tbName">数据表名称</param>
/// <returns>DataSet</returns>
public DataSet RunProcReturn(string procName, string tbName)
{
SqlDataAdapter dap = CreateDataAdaper(procName, null);
DataSet ds = new DataSet();
dap.Fill(ds, tbName);
this.Close();
//得到执行成功返回值
return ds;
}
/// <summary>
/// 执行命令文本,并且返回SqlDataReader数据集,无参数
/// </summary>
/// <param name="procName">命令文本</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader RunProcReturn(string procName)
{
///创建SqlCommand
SqlCommand cmd = CreateCommand(procName, null);
///读取数据
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
/// <summary>
/// 执行命令文本,并且返回SqlDataReader数据集,有参数
/// </summary>
/// <param name="procName">命令文本</param>
/// <param name="prams">参数</param>
/// <returns></returns>
public SqlDataReader RunProcReturn(string procName, SqlParameter[] prams)
{
///创建SqlCommand
SqlCommand cmd = CreateCommand(procName, prams);
///读取数据
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
#endregion
#region 将命令文本添加到SqlDataAdapter
/// <summary>
/// 创建一个SqlDataAdapter对象以此来执行命令文本
/// </summary>
/// <param name="procName">命令文本</param>
/// <param name="prams">参数对象</param>
/// <returns></returns>
private SqlDataAdapter CreateDataAdaper(string procName, SqlParameter[] prams)
{
this.Open();
SqlDataAdapter dap = new SqlDataAdapter(procName, con);
dap.SelectCommand.CommandType = CommandType.Text; //执行类型:命令文本
if (prams != null)
{
foreach (SqlParameter parameter in prams)
dap.SelectCommand.Parameters.Add(parameter);
}
//加入返回参数
dap.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
return dap;
}
#endregion
#region 将命令文本添加到SqlCommand
/// <summary>
/// 创建一个SqlCommand对象以此来执行命令文本
/// </summary>
/// <param name="procName">命令文本</param>
/// <param name="prams"命令文本所需参数</param>
/// <returns>返回SqlCommand对象</returns>
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
// 确认打开连接
this.Open();
SqlCommand cmd = new SqlCommand(procName, con);
cmd.CommandType = CommandType.Text; //执行类型:命令文本
// 依次把参数传入命令文本
if (prams != null)
{
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
// 加入返回参数
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
return cmd;
}
#endregion
}
类二:
Code
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Xml; namespace TestWeb
{
public class SQLTrans : IDisposable
{
#region ** 构造函数 **
/// <summary>
/// 构造函数
/// </summary>
public SQLTrans()
{
string strConnect = ConfigurationManager.ConnectionStrings["TestConn"].ConnectionString;
transConnection = new SqlConnection(strConnect);
transCommand = new SqlCommand();
transCommand.Connection = transConnection;
}
/// <summary>
/// 构造函数
/// </summary>
public SQLTrans(string strConnection)
{
transConnection = new SqlConnection(strConnection.Trim());
transCommand = new SqlCommand();
transCommand.Connection = transConnection;
}
#endregion ** 构造函数 **
private SqlConnection transConnection;
private SqlCommand transCommand;
private SqlTransaction SQLTransaction;
/// SQL连接字符串
/// </summary>
public string ConnectionString
{
get
{
return transConnection.ConnectionString;
}
set
{
transConnection.Close();
transConnection.ConnectionString = value.Trim();
}
}
/// <summary>
/// SQL连接
/// </summary>
public SqlConnection Connection
{
get
{
return transConnection;
}
}
/// <summary>
/// SQL命令
/// </summary>
public SqlCommand Command
{
get
{
return transCommand;
}
}
/// <summary>
/// 事务
/// </summary>
public SqlTransaction Transaction
{
get
{
return SQLTransaction;
}
}
/// <summary>
/// 是否正在进行事务
/// </summary>
public bool HaveTrans
{
get
{
return SQLTransaction != null;
}
}
#endregion ** 属性 **
/// <summary>
/// 打开连接, 当连接打开时,不执行操作,当关闭时,打开,当断开时,先关闭,再打开
/// </summary>
private void Open()
{
if (transConnection.State == ConnectionState.Closed)
{
transConnection.Open();
}
else if (transConnection.State == ConnectionState.Broken)
{
transConnection.Close();
transConnection.Open();
}
}
#endregion ** 内部方法 **
/// <summary>
/// 释放时, 关于连接
/// </summary>
public void Dispose()
{
Commit();
transConnection.Close();
}
/// <summary>
/// 开始事务
/// </summary>
public void BeginTrans()
{
Open();
SQLTransaction = transConnection.BeginTransaction();
}
/// <summary>
/// 提交事务
/// </summary>
public void Commit()
{
if (SQLTransaction != null)
{
SQLTransaction.Commit();
SQLTransaction = null;
}
}
/// <summary>
/// 回滚事务
/// </summary>
public void Rollback()
{
if (SQLTransaction != null)
{
SQLTransaction.Rollback();
SQLTransaction = null;
}
}
#endregion ** 对外接口 **
}
public class SQLHelper : IDisposable
{
#region ** 构造函数 **
/// <summary>
/// 构造函数
/// </summary>
public SQLHelper()
{
string strConnect = ConfigurationManager.ConnectionStrings["TestConn"].ConnectionString;
sqlConnection = new SqlConnection(strConnect);
sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
}
/// <summary>
/// 构造函数
/// </summary>
public SQLHelper(string strConnection)
{
sqlConnection = new SqlConnection(strConnection.Trim());
sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
}
/// 构造函数
/// </summary>
public SQLHelper(SQLTrans sqlTrans)
{
sqlConnection = null;
sqlCommand = sqlTrans.Command;
sqlTransaction = sqlTrans;
}
#endregion ** 构造函数 **
/// <summary>
/// 保存参数的Hashtable
/// </summary>
private static Hashtable paramCache = Hashtable
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Xml; namespace TestWeb
{
public class SQLTrans : IDisposable
{
#region ** 构造函数 **
/// <summary>
/// 构造函数
/// </summary>
public SQLTrans()
{
string strConnect = ConfigurationManager.ConnectionStrings["TestConn"].ConnectionString;
transConnection = new SqlConnection(strConnect);
transCommand = new SqlCommand();
transCommand.Connection = transConnection;
}
/// <summary>
/// 构造函数
/// </summary>
public SQLTrans(string strConnection)
{
transConnection = new SqlConnection(strConnection.Trim());
transCommand = new SqlCommand();
transCommand.Connection = transConnection;
}
#endregion ** 构造函数 **
#region ** 属性 ConnectionString, Connection, Command, Transaction, HaveTrans**
private SqlConnection transConnection;
private SqlCommand transCommand;
private SqlTransaction SQLTransaction;
/// <summary>
/// SQL连接字符串
/// </summary>
public string ConnectionString
{
get
{
return transConnection.ConnectionString;
}
set
{
transConnection.Close();
transConnection.ConnectionString = value.Trim();
}
}
/// <summary>
/// SQL连接
/// </summary>
public SqlConnection Connection
{
get
{
return transConnection;
}
}
/// <summary>
/// SQL命令
/// </summary>
public SqlCommand Command
{
get
{
return transCommand;
}
}
/// <summary>
/// 事务
/// </summary>
public SqlTransaction Transaction
{
get
{
return SQLTransaction;
}
}
/// <summary>
/// 是否正在进行事务
/// </summary>
public bool HaveTrans
{
get
{
return SQLTransaction != null;
}
}
#endregion ** 属性 **
#region ** 内部方法 **
/// <summary>
/// 打开连接, 当连接打开时,不执行操作,当关闭时,打开,当断开时,先关闭,再打开
/// </summary>
private void Open()
{
if (transConnection.State == ConnectionState.Closed)
{
transConnection.Open();
}
else if (transConnection.State == ConnectionState.Broken)
{
transConnection.Close();
transConnection.Open();
}
}
#endregion ** 内部方法 **
#region ** 对外接口 BeginTrans, Commit, Rollback**
/// <summary>
/// 释放时, 关于连接
/// </summary>
public void Dispose()
{
Commit();
transConnection.Close();
}
/// <summary>
/// 开始事务
/// </summary>
public void BeginTrans()
{
Open();
SQLTransaction = transConnection.BeginTransaction();
}
/// <summary>
/// 提交事务
/// </summary>
public void Commit()
{
if (SQLTransaction != null)
{
SQLTransaction.Commit();
SQLTransaction = null;
}
}
/// <summary>
/// 回滚事务
/// </summary>
public void Rollback()
{
if (SQLTransaction != null)
{
SQLTransaction.Rollback();
SQLTransaction = null;
}
}
#endregion ** 对外接口 **
}
public class SQLHelper : IDisposable
{
#region ** 构造函数 **
/// <summary>
/// 构造函数
/// </summary>
public SQLHelper()
{
string strConnect = ConfigurationManager.ConnectionStrings["TestConn"].ConnectionString;
sqlConnection = new SqlConnection(strConnect);
sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
}
/// <summary>
/// 构造函数
/// </summary>
public SQLHelper(string strConnection)
{
sqlConnection = new SqlConnection(strConnection.Trim());
sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
}
/// <summary>
/// 构造函数
/// </summary>
public SQLHelper(SQLTrans sqlTrans)
{
sqlConnection = null;
sqlCommand = sqlTrans.Command;
sqlTransaction = sqlTrans;
}
#endregion ** 构造函数 **
#region ** 属性 ConnectionString, Connection, Command **
/// <summary>
/// 保存参数的Hashtable
/// </summary>
private static Hashtable paramCache = Hashtable
作者: huhi1019
- 该日志由 huhi1019 于12年前发表在综合分类下,最后更新于 2012年10月08日.
- 转载请注明: 基本的数据库操作类 | 学步园 +复制链接
抱歉!评论已关闭.