一个简单的数据访问类,没有使用接口,使用的是DbProviderFactory请各位指点一下有些什么问题
/// <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>
/// 数据访问类(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]);
}
}
}
刚刚看了PetShop里的SqlHelper.cs,感觉封装的不是很够
以前用的都是别人的库,感觉还是自己写一个用用好些,能够了解一下基本的东西,功能简单些不要紧主要是满足自己的开发需要就行.
可能是功能比较简单,呵呵,主要是担心数据库连接的关闭和扩展是否方便(用的都是DbConnection这样的抽象类而不是SqlConnection)
主要还是想通过写访问类再了解一下ado.net,学了这么久还有很多基本的东西都不清楚,真去写才觉得有很多不明白的,特别是OO的应用
继承接口实现多种数据库操作
楼主可以下载一个类似的框架看看:
http://www.pwmis.com/sqlmap/
自己的东西就算有问题也比较好解决,主要还是想自己动手,一步步来吧,不能总是COPY别人的