针对下面的类,比如 需要更新数据的时候就会用到 ExecuteNonQuery() 方法,而这个方法中调用了,Open()方法。
我的问题是:1、这个Open方法是 与数据库建立链接 还是在连接池中取链接?
2、 比如一个业务逻辑中 需要在一个事物 中多次 调用ExecuteNonQuery() ,这样就会 多次调用 Open()方法,这样是不是对资源的浪费。如果想在业务逻辑中打开一次链接 完成事物中的操作 在关闭这个数据库的链接会不会更好些?应该如何实现?
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient; public class DbHelper
{
//数据连接接口
private IDbConnection _IConn = null;
//数据命令接口
private IDbCommand _ICmd = null;
//数据适配器接口
private IDbDataAdapter _IDA;
//数据读取器接口
//private IDataReader _IDR = null;
//事务接口
private IDbTransaction _ITran = null;
//数据提供程序
private DbProviderFactory _DbFactory = null;
//数据库类型
private string strDBType = "";
//数据库连接字符串
private string strConnectionString = ""; #region 构造函数
/// <summary>
/// 构造函数
/// 从配置文件中读取数据库类型、连接字符串
/// </summary>
public DbHelper()
{
try
{
if (_IConn == null)
{
//数据库类型
//strDBType = ConfigurationManager.AppSettings["DBType"];
strDBType = DB_Configuration.DbProviderName;
//数据库连接字符串
//strConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
strConnectionString = DB_Configuration.DbConnectionString;
//获取指定提供程序名称的 DbProviderFactory 的一个实例
_DbFactory = DbProviderFactories.GetFactory(strDBType);
//创建连接
_IConn = _DbFactory.CreateConnection();
//连接字符串
_IConn.ConnectionString = strConnectionString;
}
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
} /// <summary>
/// 构造函数
/// </summary>
/// <param name="strDBType">数据库类型:"System.Data.SqlClient" or "System.Data.OracleClient" or "System.Data.Odbc" or "System.Data.OleDb"</param>
/// <param name="strConn">连接字符串</param>
public DbHelper(string strDBType,string strConn)
{
//获取连接
SetConnection(strDBType, strConn);
} /// <summary>
/// 构造函数
/// </summary>
/// <param name="strDBType">数据库类型:"System.Data.SqlClient" or "System.Data.OracleClient" or "System.Data.Odbc" or "System.Data.OleDb"</param>
/// <param name="strConn">连接字符串</param>
/// <param name="cmdText">查询语句</param>
public DbHelper(string strDBType, string strConn, string cmdText)
{
//获取连接
SetConnection(strDBType, strConn);
//获取数据库命令接口
_ICmd = GetCommand(cmdText);
} /// <summary>
/// 设置连接
/// </summary>
/// <param name="strDBType">数据库类型:"System.Data.SqlClient" or "System.Data.OracleClient" or "System.Data.Odbc" or "System.Data.OleDb"</param>
/// <param name="strConn">连接字符串</param>
private void SetConnection(string strDBType, string strConn)
{
try
{
if (_IConn == null)
{
//获取指定提供程序名称的 DbProviderFactory 的一个实例
_DbFactory = DbProviderFactories.GetFactory(strDBType);
//创建连接
_IConn = _DbFactory.CreateConnection();
//设置用于打开数据库的连接字符串
_IConn.ConnectionString = strConn;
}
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion #region 获取数据库连接接口
/// <summary>
/// 获取数据库连接接口
/// </summary>
public IDbConnection IConn
{
get
{
return _IConn;
}
}
#endregion #region 获取事务
/// <summary>
/// 获取事务
/// </summary>
/// <returns></returns>
public IDbTransaction GetTransaction()
{
return _IConn.BeginTransaction();
}
#endregion #region 打开连接
/// <summary>
/// 打开连接
/// </summary>
public void Open()
{
try
{
if (_IConn.State == ConnectionState.Closed)
{
//关闭状态,打开连接
_IConn.Open();
}
else if (_IConn.State == ConnectionState.Broken)
{
//中断状态,先关闭后打开连接
_IConn.Close();
_IConn.Open();
}
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion #region 关闭连接
/// <summary>
/// 关闭连接
/// </summary>
public void Close()
{
if (_IConn == null)
{
return;
}
else if (_IConn.State != ConnectionState.Closed)
{
_IConn.Close();
}
}
#endregion #region 获取数据库命令接口
/// <summary>
/// 获取数据库命令接口
/// </summary>
/// <param name="cmdText">查询语句</param>
/// <returns>返回数据库命令接口</returns>
public IDbCommand GetCommand(string cmdText)
{
//创建数据库命令
IDbCommand cmd = _DbFactory.CreateCommand();
//设置数据源运行的文本命令
cmd.CommandText = cmdText;
//设置连接
cmd.Connection = _IConn;
return cmd;
}
/// <summary>
/// 获取数据库命令接口
/// </summary>
/// <param name="cmdText">查询语句</param>
/// <param name="conn">数据库连接接口</param>
/// <returns>返回数据库命令接口</returns>
public IDbCommand GetCommand(string cmdText, IDbConnection conn)
{
IDbCommand cmd = _DbFactory.CreateCommand();
cmd.CommandText = cmdText;
cmd.Connection = conn;
return cmd;
}
#endregion #region 执行SQL语句返回受影响的行数
/// <summary>
/// 执行SQL语句返回受影响的行数
/// </summary>
/// <param name="cmdText">查询语句</param>
/// <returns>返回受影响的行数(-1:执行失败)</returns>
public int ExecuteNonQuery(string cmdText)
{
int iRows = 0;
try
{
Open();
_ICmd = GetCommand(cmdText);
iRows = _ICmd.ExecuteNonQuery();
}
catch(Exception ex)
{
iRows = -1;
throw new Exception(ex.Message);
}
finally
{
Close();
}
return iRows;
} #endregion #region 执行SQL语句返回数据集
/// <summary>
/// 执行SQL语句返回数据集
/// </summary>
/// <param name="cmdText">查询语句</param>
/// <returns>返回数据集</returns>
public DataSet GetDataSet(string cmdText)
{
DataSet ds = new DataSet();
try
{
Open();
_IDA = _DbFactory.CreateDataAdapter();
_IDA.SelectCommand = GetCommand(cmdText);
_IDA.Fill(ds); }
catch(Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
Close();
}
return ds;
} /// <summary>
/// 执行存储过程返回数据集
/// </summary>
/// <param name="cmdType">命令类型</param>
/// <param name="strProcudureName">存储过程名称</param>
/// <param name="param">存储过程参数</param>
/// <returns>返回数据集</returns>
public DataSet GetDataSet(CommandType cmdType, string strProcudureName, params IDbDataParameter[] param)
{
DataSet ds = new DataSet();
Open();
try
{
_ICmd.Connection = _IConn;
_ICmd.CommandText = strProcudureName;
_ICmd.CommandType = cmdType;
if (param != null)
{
_ICmd.Parameters.Clear();
foreach (DbParameter p in param)
{
_ICmd.Parameters.Add(p);
}
}
_IDA = _DbFactory.CreateDataAdapter();
_IDA.SelectCommand = _ICmd;
_IDA.Fill(ds);
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
Close();
}
return ds;
} #endregion #region 执行SQL语句返回单值对象
/// <summary>
/// 执行SQL语句返回单值对象
/// </summary>
/// <param name="cmdText">查询语句</param>
/// <returns>返回结果集中第一行的第一列数据</returns>
public object ExecuteScalar(string cmdText)
{
try
{
Open();
_ICmd = GetCommand(cmdText);
return _ICmd.ExecuteScalar();
}
catch(Exception ex)
{
throw new Exception(ex.Message);
//return null;
}
finally
{
Close();
}
}
#endregion #region 执行多条SQL语句
/// <summary>
/// 执行多条SQL语句
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns>-1:不成功;1:成功</returns>
public int ExecuteSql(string[] strSql)
{
//是否成功标识
int iSuccess = -1;
Open();
try
{
_ITran = GetTransaction();
int iLength = strSql.Length;
for (int i = 0; i < iLength; i++)
{
_ICmd = GetCommand(strSql[i]);
_ICmd.Transaction = _ITran;
_ICmd.ExecuteNonQuery();
}
_ITran.Commit();
iSuccess = 1;
}
catch
{
_ITran.Rollback();
throw new Exception("");
}
finally
{
Close();
}
return iSuccess;
}
/// <summary>
/// 执行多条SQL语句
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns>-1:不成功;1:成功</returns>
public int ExecuteSql(List<string> strSql)
{
//是否成功标识
int iSuccess = -1;
Open();
try
{
_ITran = GetTransaction();
foreach(string sql in strSql)
{
_ICmd = GetCommand(sql);
_ICmd.Transaction = _ITran;
_ICmd.ExecuteNonQuery();
}
_ITran.Commit();
iSuccess = 1;
}
catch(Exception ex)
{
_ITran.Rollback();
throw ex;
}
finally
{
Close();
}
return iSuccess;
}
#endregion
}
我的问题是:1、这个Open方法是 与数据库建立链接 还是在连接池中取链接?
2、 比如一个业务逻辑中 需要在一个事物 中多次 调用ExecuteNonQuery() ,这样就会 多次调用 Open()方法,这样是不是对资源的浪费。如果想在业务逻辑中打开一次链接 完成事物中的操作 在关闭这个数据库的链接会不会更好些?应该如何实现?
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient; public class DbHelper
{
//数据连接接口
private IDbConnection _IConn = null;
//数据命令接口
private IDbCommand _ICmd = null;
//数据适配器接口
private IDbDataAdapter _IDA;
//数据读取器接口
//private IDataReader _IDR = null;
//事务接口
private IDbTransaction _ITran = null;
//数据提供程序
private DbProviderFactory _DbFactory = null;
//数据库类型
private string strDBType = "";
//数据库连接字符串
private string strConnectionString = ""; #region 构造函数
/// <summary>
/// 构造函数
/// 从配置文件中读取数据库类型、连接字符串
/// </summary>
public DbHelper()
{
try
{
if (_IConn == null)
{
//数据库类型
//strDBType = ConfigurationManager.AppSettings["DBType"];
strDBType = DB_Configuration.DbProviderName;
//数据库连接字符串
//strConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
strConnectionString = DB_Configuration.DbConnectionString;
//获取指定提供程序名称的 DbProviderFactory 的一个实例
_DbFactory = DbProviderFactories.GetFactory(strDBType);
//创建连接
_IConn = _DbFactory.CreateConnection();
//连接字符串
_IConn.ConnectionString = strConnectionString;
}
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
} /// <summary>
/// 构造函数
/// </summary>
/// <param name="strDBType">数据库类型:"System.Data.SqlClient" or "System.Data.OracleClient" or "System.Data.Odbc" or "System.Data.OleDb"</param>
/// <param name="strConn">连接字符串</param>
public DbHelper(string strDBType,string strConn)
{
//获取连接
SetConnection(strDBType, strConn);
} /// <summary>
/// 构造函数
/// </summary>
/// <param name="strDBType">数据库类型:"System.Data.SqlClient" or "System.Data.OracleClient" or "System.Data.Odbc" or "System.Data.OleDb"</param>
/// <param name="strConn">连接字符串</param>
/// <param name="cmdText">查询语句</param>
public DbHelper(string strDBType, string strConn, string cmdText)
{
//获取连接
SetConnection(strDBType, strConn);
//获取数据库命令接口
_ICmd = GetCommand(cmdText);
} /// <summary>
/// 设置连接
/// </summary>
/// <param name="strDBType">数据库类型:"System.Data.SqlClient" or "System.Data.OracleClient" or "System.Data.Odbc" or "System.Data.OleDb"</param>
/// <param name="strConn">连接字符串</param>
private void SetConnection(string strDBType, string strConn)
{
try
{
if (_IConn == null)
{
//获取指定提供程序名称的 DbProviderFactory 的一个实例
_DbFactory = DbProviderFactories.GetFactory(strDBType);
//创建连接
_IConn = _DbFactory.CreateConnection();
//设置用于打开数据库的连接字符串
_IConn.ConnectionString = strConn;
}
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion #region 获取数据库连接接口
/// <summary>
/// 获取数据库连接接口
/// </summary>
public IDbConnection IConn
{
get
{
return _IConn;
}
}
#endregion #region 获取事务
/// <summary>
/// 获取事务
/// </summary>
/// <returns></returns>
public IDbTransaction GetTransaction()
{
return _IConn.BeginTransaction();
}
#endregion #region 打开连接
/// <summary>
/// 打开连接
/// </summary>
public void Open()
{
try
{
if (_IConn.State == ConnectionState.Closed)
{
//关闭状态,打开连接
_IConn.Open();
}
else if (_IConn.State == ConnectionState.Broken)
{
//中断状态,先关闭后打开连接
_IConn.Close();
_IConn.Open();
}
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion #region 关闭连接
/// <summary>
/// 关闭连接
/// </summary>
public void Close()
{
if (_IConn == null)
{
return;
}
else if (_IConn.State != ConnectionState.Closed)
{
_IConn.Close();
}
}
#endregion #region 获取数据库命令接口
/// <summary>
/// 获取数据库命令接口
/// </summary>
/// <param name="cmdText">查询语句</param>
/// <returns>返回数据库命令接口</returns>
public IDbCommand GetCommand(string cmdText)
{
//创建数据库命令
IDbCommand cmd = _DbFactory.CreateCommand();
//设置数据源运行的文本命令
cmd.CommandText = cmdText;
//设置连接
cmd.Connection = _IConn;
return cmd;
}
/// <summary>
/// 获取数据库命令接口
/// </summary>
/// <param name="cmdText">查询语句</param>
/// <param name="conn">数据库连接接口</param>
/// <returns>返回数据库命令接口</returns>
public IDbCommand GetCommand(string cmdText, IDbConnection conn)
{
IDbCommand cmd = _DbFactory.CreateCommand();
cmd.CommandText = cmdText;
cmd.Connection = conn;
return cmd;
}
#endregion #region 执行SQL语句返回受影响的行数
/// <summary>
/// 执行SQL语句返回受影响的行数
/// </summary>
/// <param name="cmdText">查询语句</param>
/// <returns>返回受影响的行数(-1:执行失败)</returns>
public int ExecuteNonQuery(string cmdText)
{
int iRows = 0;
try
{
Open();
_ICmd = GetCommand(cmdText);
iRows = _ICmd.ExecuteNonQuery();
}
catch(Exception ex)
{
iRows = -1;
throw new Exception(ex.Message);
}
finally
{
Close();
}
return iRows;
} #endregion #region 执行SQL语句返回数据集
/// <summary>
/// 执行SQL语句返回数据集
/// </summary>
/// <param name="cmdText">查询语句</param>
/// <returns>返回数据集</returns>
public DataSet GetDataSet(string cmdText)
{
DataSet ds = new DataSet();
try
{
Open();
_IDA = _DbFactory.CreateDataAdapter();
_IDA.SelectCommand = GetCommand(cmdText);
_IDA.Fill(ds); }
catch(Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
Close();
}
return ds;
} /// <summary>
/// 执行存储过程返回数据集
/// </summary>
/// <param name="cmdType">命令类型</param>
/// <param name="strProcudureName">存储过程名称</param>
/// <param name="param">存储过程参数</param>
/// <returns>返回数据集</returns>
public DataSet GetDataSet(CommandType cmdType, string strProcudureName, params IDbDataParameter[] param)
{
DataSet ds = new DataSet();
Open();
try
{
_ICmd.Connection = _IConn;
_ICmd.CommandText = strProcudureName;
_ICmd.CommandType = cmdType;
if (param != null)
{
_ICmd.Parameters.Clear();
foreach (DbParameter p in param)
{
_ICmd.Parameters.Add(p);
}
}
_IDA = _DbFactory.CreateDataAdapter();
_IDA.SelectCommand = _ICmd;
_IDA.Fill(ds);
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
Close();
}
return ds;
} #endregion #region 执行SQL语句返回单值对象
/// <summary>
/// 执行SQL语句返回单值对象
/// </summary>
/// <param name="cmdText">查询语句</param>
/// <returns>返回结果集中第一行的第一列数据</returns>
public object ExecuteScalar(string cmdText)
{
try
{
Open();
_ICmd = GetCommand(cmdText);
return _ICmd.ExecuteScalar();
}
catch(Exception ex)
{
throw new Exception(ex.Message);
//return null;
}
finally
{
Close();
}
}
#endregion #region 执行多条SQL语句
/// <summary>
/// 执行多条SQL语句
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns>-1:不成功;1:成功</returns>
public int ExecuteSql(string[] strSql)
{
//是否成功标识
int iSuccess = -1;
Open();
try
{
_ITran = GetTransaction();
int iLength = strSql.Length;
for (int i = 0; i < iLength; i++)
{
_ICmd = GetCommand(strSql[i]);
_ICmd.Transaction = _ITran;
_ICmd.ExecuteNonQuery();
}
_ITran.Commit();
iSuccess = 1;
}
catch
{
_ITran.Rollback();
throw new Exception("");
}
finally
{
Close();
}
return iSuccess;
}
/// <summary>
/// 执行多条SQL语句
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns>-1:不成功;1:成功</returns>
public int ExecuteSql(List<string> strSql)
{
//是否成功标识
int iSuccess = -1;
Open();
try
{
_ITran = GetTransaction();
foreach(string sql in strSql)
{
_ICmd = GetCommand(sql);
_ICmd.Transaction = _ITran;
_ICmd.ExecuteNonQuery();
}
_ITran.Commit();
iSuccess = 1;
}
catch(Exception ex)
{
_ITran.Rollback();
throw ex;
}
finally
{
Close();
}
return iSuccess;
}
#endregion
}
我跟您的理解是一样的。 但是我看到好多数据访问的类(包括自己公司的)好像都是 针对每个 操作 比如update select调用一次Open函数,而不是针对每个业务逻辑方法 调一次Open函数
1、究竟是在哪里 真正的打开和关闭物理连接的呢?
2、如果Open方法只是改变了一个状态,那么我们的web 和数据库 是不是一直保持连接状态的?比如连接池中有10个连接,是不是就是说 这10个连接一直被占用?
Open的时候如果池中没有可用的连接,且池未满的情况下,就会打开物理连接你可以测试一下,用代码打开个连接,拨掉网线。看看连接的状态,应该还是Open
再。打开个连接,然后用代码关闭,用命令行netstat /an看看是不是与数据库还有个物理连接。
结贴了