DbProviderFactories使用记录
由于需要在工作需要使用ADO.NET实现通用的多数据库操作,通过资料发现可以使用DbProviderFactories类实现,碰到了一些坑,下面和大家说一下。
- ProviderFactories只有四类数据库工厂对象,分别是Odbc、OleDb、Oracle、SqlServer。没有Mysql的,正好我项目需要用到的是Mysql,那么就只能自己去注册工厂了。
- 在微软官网的文档中看到的DbProviderFactories类是有注册工厂的静态方法的,可是我发现根本就没有,原来高一点版本的.net就不再使用这个功能了。要通过App.config配置文件去注册。
- 在配置文件中是这样配置的:
<system.data>
<DbProviderFactories>
<remove invariant="MySql.Data.MySqlClient" />
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.12.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>
- 然而其实我发现,在NuGet里面下载引用了Mysql.Data后,他会自动往App.config里面去添加这个配置文件的。哈哈哈。
- 下面我给一些常用方法类的使用方法吧:
//获取Mysql的工厂实例化对象
this.DataBaseProvider = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
//获取其他数据库工厂跟Mysql一样,就是参数不同:
this.DataBaseProvider = DbProviderFactories.GetFactory("System.Data.Odbc");
this.DataBaseProvider = DbProviderFactories.GetFactory("System.Data.OleDb");
this.DataBaseProvider = DbProviderFactories.GetFactory("System.Data.OracleClient");
this.DataBaseProvider = DbProviderFactories.GetFactory("System.Data.SqlClient");
//连接数据库
using (DbConnection conn = this.DataBaseProvider.CreateConnection())
{
conn.ConnectionString = "链接字符串,这个自己拼";
conn.Open();
}
- 下面是封装的一些常用方法:
/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="sqlString">SQL字符串</param>
/// <returns></returns>
public int ExecuteSql(string sqlString)
{
int resultCount = 0;
using (DbConnection conn = this.DataBaseProvider.CreateConnection())
{
conn.ConnectionString = this.ConnectionString;
conn.Open();
using (DbCommand cmd = conn.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = sqlString;
try
{
resultCount = cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
return resultCount;
}
/// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="sqlStringList">SQL字符串列表</param>
/// <returns></returns>
public bool ExecuteSqlTran(List<string> sqlStringList)
{
using (DbConnection conn = this.DataBaseProvider.CreateConnection())
{
conn.ConnectionString = this.ConnectionString;
conn.Open();
using (DbTransaction transaction = conn.BeginTransaction())
{
using (DbCommand cmd = conn.CreateCommand())
{
cmd.Transaction = transaction;
cmd.Connection = conn;
try
{
foreach (string sql in sqlStringList)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
transaction.Commit();
conn.Close();
}
catch (Exception e)
{
transaction.Rollback();
throw new Exception(e.Message);
}
}
}
return true;
}
}
/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="sqlString">SQL字符串</param>
/// <returns></returns>
public DataSet Query(string sqlString)
{
DataSet dataSet = new DataSet();
using (DbConnection conn = this.DataBaseProvider.CreateConnection())
{
conn.ConnectionString = this.ConnectionString;
conn.Open();
using (DbCommand cmd = conn.CreateCommand())
{
using (DbDataAdapter dataAdapter = this.DataBaseProvider.CreateDataAdapter())
{
cmd.Connection = conn;
cmd.CommandText = sqlString;
dataAdapter.SelectCommand = cmd;
try
{
dataAdapter.Fill(dataSet);
conn.Close();
return dataSet;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
}