62,074
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 数据访问类(ADO.NET)
/// </summary>
public class DbProvider
{
//数据提供程序
public static readonly string SqlProvider = "System.Data.SqlClient";
public static readonly string OracleProvider = "System.Data.OracleClient";
public static readonly string OdbcProvider = "System.Data.Odbc";
public static readonly string OleDbProvider = "System.Data.OleDb";
//数据库默认连接字符串
public string ConnString = ConfigurationManager.AppSettings["ConnString"];
/// <summary>
/// 数据工厂
/// </summary>
private DbProviderFactory factory;
//数据库连接对象
private DbConnection conn;
//数据库命令对象
private DbCommand cmd;
//事务对象
private DbTransaction trans;
/// <summary>
/// 是否保持数据库连接打开状态
/// </summary>
public bool isKeepOpen = false;
/// <summary>
/// 构造函数,创建一个数据库连接实例
/// </summary>
public DbProvider()
{
if (!string.IsNullOrEmpty(ConnString))
{
factory = DbProviderFactories.GetFactory(SqlProvider);
conn = factory.CreateConnection();
conn.ConnectionString = ConnString;
}
}
/// <summary>
/// 构造函数,创建一个数据库连接实例
/// <param name="provider">数据库提供程序</param>
/// </summary>
public DbProvider(string provider)
{
if (!string.IsNullOrEmpty(ConnString) && !string.IsNullOrEmpty(provider))
{
factory = DbProviderFactories.GetFactory(provider);
conn = factory.CreateConnection();
conn.ConnectionString = ConnString;
}
}
/// <summary>
/// 构造函数,创建一个数据库连接实例
/// <param name="provider">数据库提供程序</param>
/// <param name="connName">数据库连接在Connfig中的配置节点</param>
/// </summary>
public DbProvider(string provider, string connName)
{
if (string.IsNullOrEmpty(provider))
provider = SqlProvider;
ConnString = ConfigurationManager.AppSettings[connName];
factory = DbProviderFactories.GetFactory(provider);
conn = factory.CreateConnection();
conn.ConnectionString = ConnString;
}
/// <summary>
/// 打开数据库连接
/// </summary>
private void Open()
{
if (conn != null && conn.State != ConnectionState.Open)
conn.Open();
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
if (conn != null && conn.State != ConnectionState.Closed && !isKeepOpen)
conn.Close();
}
/// <summary>
/// 启动事务
/// </summary>
public void BeginTrans()
{
if (conn != null)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd = conn.CreateCommand();
trans = conn.BeginTransaction();
cmd.Transaction = trans;
//事务中保持数据库连接打开状态
isKeepOpen = true;
}
}
/// <summary>
/// 提交事务
/// </summary>
public void CommitTrans()
{
try
{
trans.Commit();
}
catch
{ throw new Exception("事务提交失败"); }
finally
{
//取消数据库打开状态
isKeepOpen = false;
this.Close();
}
}
/// <summary>
/// 回滚事务
/// </summary>
public void RollbackTrans()
{
try
{
trans.Rollback();
}
catch
{ throw new Exception("事务回滚失败"); }
finally
{
//取消数据库打开状态
isKeepOpen = false;
this.Close();
}
}
/// <summary>
/// 得到数据流对象,结束后需关闭数据库连接,建议使用using
/// </summary>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdParms">参数</param>
/// <returns></returns>
public IDataReader GetDataReader(CommandType cmdType, string cmdText, DbParameter[] cmdParms)
{
PrepareCommand(cmdType, cmdText, cmdParms);
return this.cmd.ExecuteReader();
}
/// <summary>
/// 执行命令对象,得到受影响的行数
/// </summary>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdParms">参数</param>
/// <returns>受影响的行数或存储过程的返回值</returns>
public int ExecuteNonQuery(CommandType cmdType, string cmdText, DbParameter[] cmdParms)
{
try
{
PrepareCommand(cmdType, cmdText, cmdParms);
return this.cmd.ExecuteNonQuery();
}
catch
{
//取消数据库打开状态
isKeepOpen = false; throw new Exception("数据库操作错误");
}
finally { this.Close(); }
}
/// <summary>
/// 得到运行结果集中首行首列的值
/// </summary>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdParms">参数</param>
/// <returns>首行首列的值</returns>
public object GetScalar(CommandType cmdType, string cmdText, DbParameter[] cmdParms)
{
try
{
PrepareCommand(cmdType, cmdText, cmdParms);
return this.cmd.ExecuteScalar();
}
catch
{
//取消数据库打开状态
isKeepOpen = false; throw new Exception("数据库操作错误");
}
finally { this.Close(); }
}
/// <summary>
/// 查询得到数据集的方法
/// </summary>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdParms">参数</param>
/// <param name="mTableName">数据集中的表名</param>
/// <returns>对应的DataSet</returns>
public DataSet GetDataSet(CommandType cmdType, string cmdText, DbParameter[] cmdParms, string mTableName)
{
DataSet ds = new DataSet();
this.Fill(ds, cmdType, cmdText, cmdParms);
return ds;
}
/// <summary>
/// 查询得到数据集的方法
/// </summary>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdParms">参数</param>
/// <returns>对应的DataSet</returns>
public DataSet GetDataSet(CommandType cmdType, string cmdText, DbParameter[] cmdParms)
{
return this.GetDataSet(cmdType, cmdText, cmdParms, null);
}
/// <summary>
/// 查询得到数据表的方法
/// </summary>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdParms">参数</param>
/// <param name="mTableName">表名</param>
/// <returns>对应的DataTable</returns>
public DataTable GetDataTable(CommandType cmdType, string cmdText, DbParameter[] cmdParms, string mTableName)
{
DataSet ds = this.GetDataSet(cmdType, cmdText, cmdParms, mTableName);
if (ds.Tables.Count > 0)
return ds.Tables[0];
else
return null;
}
/// <summary>
/// 查询得到数据表的方法
/// </summary>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdParms">参数</param>
/// <returns>对应的DataTable</returns>
public DataTable GetDataTable(CommandType cmdType, string cmdText, DbParameter[] cmdParms)
{
return this.GetDataTable(cmdType, cmdText, cmdParms, null);
}
/// <summary>
/// 填充数据集的方法
/// </summary>
/// <param name="mDataSet">数据集对象</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdParms">参数</param>
public void Fill(DataSet mDataSet, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
{
DbDataAdapter da = this.factory.CreateDataAdapter();
try
{
PrepareCommand(cmdType, cmdText, cmdParms);
da.SelectCommand = this.cmd;
da.Fill(mDataSet);
}
catch
{
//取消数据库打开状态
isKeepOpen = false;
throw new Exception("数据库操作错误");
}
finally { da.Dispose(); this.Close(); }
}
/// <summary>
/// 初始化命令对象
/// </summary>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdParms">参数</param>
private void PrepareCommand(CommandType cmdType, string cmdText, DbParameter[] cmdParms)
{
if (conn != null)
{
if (conn.State != ConnectionState.Open)
conn.Open();
if (cmd == null)
this.cmd = conn.CreateCommand();
}
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (cmdParms != null)
{
for (int i = 0; i < cmdParms.Length; i++)
cmd.Parameters.Add(cmdParms[i]);
}
}
}