Ent Lib 2.0 DAAB添加MySql扩展 MySql5.0.27+MySql .Net Connector 5.0.2beta
1. 准备
注释掉Data项目GlobalAssemblyInfo.cs文件里[assembly: CLSCompliant(true)]这一句。因为引用MySql.Data.dll中有不符合CLS规范的地方。
确保安装了MySql .Net Connector,为Data项目添加MySql.Data.dll的引用。
其它的一些预先的测试参考:DAAB MySql支持测试(Proc+DataSet更新) 5.0.27+.Net Connector 5.0.2beta。
2. 扩展
经过前期的测试,MySql.Data基本完全跟System.Data.SqlClient操作方式一样,因此DAAB中MySql的扩展也只需要针对MySql,仿照SqlDatabase进行扩展就可以了。
在Data项目中添加MySql目录,并在目录下添加下面的两个文件,编译,或者另外建立一个工程来做(为了简短起见,注视都去掉了)。
3. 测试
没有使用NUnit,也没有使用MS的Test,直接建立一个web项目大致测试了一下。测试用的表、存储过程在DAAB MySql支持测试(Proc+DataSet更新) 5.0.27+.Net Connector 5.0.2beta中。
在web.config文件里面加上下面的配置,注意将MySql连接字符串相关参数配置正确。
为web项目添加Microsoft.Practices.EnterpriseLibrary.Data.dll的引用,web项目引用的命名空间:
4. 评论
DAAB对数据库操作封装一下之后,用起来是简单了一些。
使用DAAB,如果你写的SQL,或者是生成的SQL符合SQL92、99等标准,大部分情况下都能满足多数据库支持要求。从上面的测试代码可以看到,一定程度上你不需要关注使用的什么数据库类型。
如果你想多数据库支持方案里面支持更多的一些特性,如更丰富的语法特性、分页、系统函数等,你还得在DAAB的基础上对数据库再次封装扩展。
注释掉Data项目GlobalAssemblyInfo.cs文件里[assembly: CLSCompliant(true)]这一句。因为引用MySql.Data.dll中有不符合CLS规范的地方。
确保安装了MySql .Net Connector,为Data项目添加MySql.Data.dll的引用。
其它的一些预先的测试参考:DAAB MySql支持测试(Proc+DataSet更新) 5.0.27+.Net Connector 5.0.2beta。
2. 扩展
经过前期的测试,MySql.Data基本完全跟System.Data.SqlClient操作方式一样,因此DAAB中MySql的扩展也只需要针对MySql,仿照SqlDatabase进行扩展就可以了。
在Data项目中添加MySql目录,并在目录下添加下面的两个文件,编译,或者另外建立一个工程来做(为了简短起见,注视都去掉了)。
namespace Microsoft.Practices.EnterpriseLibrary.Data.MySql
{
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
public class MySqlDatabaseAssembler : IDatabaseAssembler
{
public Database Assemble(string name, ConnectionStringSettings connectionStringSettings, IConfigurationSource configurationSource)
{
return new MySqlDatabase(connectionStringSettings.ConnectionString);
}
}
}
{
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
public class MySqlDatabaseAssembler : IDatabaseAssembler
{
public Database Assemble(string name, ConnectionStringSettings connectionStringSettings, IConfigurationSource configurationSource)
{
return new MySqlDatabase(connectionStringSettings.ConnectionString);
}
}
}
namespace Microsoft.Practices.EnterpriseLibrary.Data.MySql
{
using System;
using System.Data;
using System.Data.Common;
using MySql.Data.MySqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
[DatabaseAssembler(typeof(MySqlDatabaseAssembler))]
public class MySqlDatabase : Database
{
private const char _parameterToken = '?';
public MySqlDatabase(string connectionString)
: base(connectionString, MySqlClientFactory.Instance)
{
}
protected override void DeriveParameters(DbCommand discoveryCommand)
{
MySqlCommandBuilder.DeriveParameters((MySqlCommand)discoveryCommand);
}
public override string BuildParameterName(string name)
{
if (name[0] != _parameterToken)
{
return name.Insert(0, new string(_parameterToken, 1));
}
return name;
}
protected override void SetUpRowUpdatedEvent(DbDataAdapter adapter)
{
((MySqlDataAdapter)adapter).RowUpdated += new MySqlRowUpdatedEventHandler(OnMySqlRowUpdated);
}
private void OnMySqlRowUpdated(object sender, MySqlRowUpdatedEventArgs args)
{
if (args.RecordsAffected == 0)
{
if (args.Errors != null)
{
args.Row.RowError = Resources.ExceptionMessageUpdateDataSetRowFailure;
args.Status = UpdateStatus.SkipCurrentRow;
}
}
}
public virtual void AddParameter(DbCommand command, string name, MySqlDbType dbType, int size, ParameterDirection direction
, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
{
DbParameter parameter = CreateParameter(name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value);
command.Parameters.Add(parameter);
}
public void AddParameter(DbCommand command, string name, MySqlDbType dbType
, ParameterDirection direction, string sourceColumn, DataRowVersion sourceVersion, object value)
{
AddParameter(command, name, dbType, 0, direction, false, 0, 0, sourceColumn, sourceVersion, value);
}
public void AddOutParameter(DbCommand command, string name, MySqlDbType dbType, int size)
{
AddParameter(command, name, dbType, size, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, DBNull.Value);
}
public void AddInParameter(DbCommand command, string name, MySqlDbType dbType)
{
AddParameter(command, name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, null);
}
public void AddInParameter(DbCommand command, string name, MySqlDbType dbType, object value)
{
AddParameter(command, name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, value);
}
public void AddInParameter(DbCommand command, string name, MySqlDbType dbType, string sourceColumn, DataRowVersion sourceVersion)
{
AddParameter(command, name, dbType, 0, ParameterDirection.Input, true, 0, 0, sourceColumn, sourceVersion, null);
}
protected DbParameter CreateParameter(string name, MySqlDbType dbType, int size, ParameterDirection direction
, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
{
MySqlParameter param = base.CreateParameter(name) as MySqlParameter;
ConfigureParameter(param, name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value);
return param;
}
protected virtual void ConfigureParameter(MySqlParameter param, string name, MySqlDbType dbType, int size, ParameterDirection direction
, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
{
param.MySqlDbType = dbType;
param.Size = size;
param.Value = (value == null) ? DBNull.Value : value;
param.Direction = direction;
param.IsNullable = nullable;
param.SourceColumn = sourceColumn;
param.SourceVersion = sourceVersion;
}
}
}
{
using System;
using System.Data;
using System.Data.Common;
using MySql.Data.MySqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
[DatabaseAssembler(typeof(MySqlDatabaseAssembler))]
public class MySqlDatabase : Database
{
private const char _parameterToken = '?';
public MySqlDatabase(string connectionString)
: base(connectionString, MySqlClientFactory.Instance)
{
}
protected override void DeriveParameters(DbCommand discoveryCommand)
{
MySqlCommandBuilder.DeriveParameters((MySqlCommand)discoveryCommand);
}
public override string BuildParameterName(string name)
{
if (name[0] != _parameterToken)
{
return name.Insert(0, new string(_parameterToken, 1));
}
return name;
}
protected override void SetUpRowUpdatedEvent(DbDataAdapter adapter)
{
((MySqlDataAdapter)adapter).RowUpdated += new MySqlRowUpdatedEventHandler(OnMySqlRowUpdated);
}
private void OnMySqlRowUpdated(object sender, MySqlRowUpdatedEventArgs args)
{
if (args.RecordsAffected == 0)
{
if (args.Errors != null)
{
args.Row.RowError = Resources.ExceptionMessageUpdateDataSetRowFailure;
args.Status = UpdateStatus.SkipCurrentRow;
}
}
}
public virtual void AddParameter(DbCommand command, string name, MySqlDbType dbType, int size, ParameterDirection direction
, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
{
DbParameter parameter = CreateParameter(name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value);
command.Parameters.Add(parameter);
}
public void AddParameter(DbCommand command, string name, MySqlDbType dbType
, ParameterDirection direction, string sourceColumn, DataRowVersion sourceVersion, object value)
{
AddParameter(command, name, dbType, 0, direction, false, 0, 0, sourceColumn, sourceVersion, value);
}
public void AddOutParameter(DbCommand command, string name, MySqlDbType dbType, int size)
{
AddParameter(command, name, dbType, size, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, DBNull.Value);
}
public void AddInParameter(DbCommand command, string name, MySqlDbType dbType)
{
AddParameter(command, name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, null);
}
public void AddInParameter(DbCommand command, string name, MySqlDbType dbType, object value)
{
AddParameter(command, name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, value);
}
public void AddInParameter(DbCommand command, string name, MySqlDbType dbType, string sourceColumn, DataRowVersion sourceVersion)
{
AddParameter(command, name, dbType, 0, ParameterDirection.Input, true, 0, 0, sourceColumn, sourceVersion, null);
}
protected DbParameter CreateParameter(string name, MySqlDbType dbType, int size, ParameterDirection direction
, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
{
MySqlParameter param = base.CreateParameter(name) as MySqlParameter;
ConfigureParameter(param, name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value);
return param;
}
protected virtual void ConfigureParameter(MySqlParameter param, string name, MySqlDbType dbType, int size, ParameterDirection direction
, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
{
param.MySqlDbType = dbType;
param.Size = size;
param.Value = (value == null) ? DBNull.Value : value;
param.Direction = direction;
param.IsNullable = nullable;
param.SourceColumn = sourceColumn;
param.SourceVersion = sourceVersion;
}
}
}
3. 测试
没有使用NUnit,也没有使用MS的Test,直接建立一个web项目大致测试了一下。测试用的表、存储过程在DAAB MySql支持测试(Proc+DataSet更新) 5.0.27+.Net Connector 5.0.2beta中。
在web.config文件里面加上下面的配置,注意将MySql连接字符串相关参数配置正确。
<configSections>
<section name="dataConfiguration"
type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data
, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null" />
</configSections>
<dataConfiguration defaultDatabase="MySqlTest">
<providerMappings>
<add databaseType="Microsoft.Practices.EnterpriseLibrary.Data.MySql.MySqlDatabase, Microsoft.Practices.EnterpriseLibrary.Data
, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null"
name="MySql.Data.MySqlClient" />
</providerMappings>
</dataConfiguration>
<connectionStrings>
<!--add name="MySqlSystem" connectionString="Database=mysql;Data Source=localhost;User Id=root;Password=123;"
providerName="MySql.Data.MySqlClient" /-->
<add name="MySqlTest" connectionString="Database=test;Data Source=localhost;User Id=root;Password=123;"
providerName="MySql.Data.MySqlClient" />
</connectionStrings>
<section name="dataConfiguration"
type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data
, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null" />
</configSections>
<dataConfiguration defaultDatabase="MySqlTest">
<providerMappings>
<add databaseType="Microsoft.Practices.EnterpriseLibrary.Data.MySql.MySqlDatabase, Microsoft.Practices.EnterpriseLibrary.Data
, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null"
name="MySql.Data.MySqlClient" />
</providerMappings>
</dataConfiguration>
<connectionStrings>
<!--add name="MySqlSystem" connectionString="Database=mysql;Data Source=localhost;User Id=root;Password=123;"
providerName="MySql.Data.MySqlClient" /-->
<add name="MySqlTest" connectionString="Database=test;Data Source=localhost;User Id=root;Password=123;"
providerName="MySql.Data.MySqlClient" />
</connectionStrings>
为web项目添加Microsoft.Practices.EnterpriseLibrary.Data.dll的引用,web项目引用的命名空间:
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.MySql;
using MySql.Data.MySqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.MySql;
using MySql.Data.MySqlClient;
部分测试代码如下:
private void displayBySqlText()
{
Database database=DatabaseFactory.CreateDatabase("MySqlTest");
string sql = "select * from test.tbluser where tbluser.UserCode like " +
database.BuildParameterName("UserCode") + " and tbluser.UserName like " +
database.BuildParameterName("UserName");
DbCommand command = database.GetSqlStringCommand(sql);
//database.AddParameter()方法会自动调用database.BuildParameterName()进行处理
database.AddParameter(command, "UserCode", DbType.String, ParameterDirection.Input, "",
DataRowVersion.Default, this.TextBox1.Text + "%");
database.AddParameter(command, "UserName", DbType.String, ParameterDirection.Input, "",
DataRowVersion.Default, "%" + this.TextBox2.Text + "%");
DataSet ds = database.ExecuteDataSet(command);
this.GridView1.DataSource = ds;
this.GridView1.DataBind();
}
{
Database database=DatabaseFactory.CreateDatabase("MySqlTest");
string sql = "select * from test.tbluser where tbluser.UserCode like " +
database.BuildParameterName("UserCode") + " and tbluser.UserName like " +
database.BuildParameterName("UserName");
DbCommand command = database.GetSqlStringCommand(sql);
//database.AddParameter()方法会自动调用database.BuildParameterName()进行处理
database.AddParameter(command, "UserCode", DbType.String, ParameterDirection.Input, "",
DataRowVersion.Default, this.TextBox1.Text + "%");
database.AddParameter(command, "UserName", DbType.String, ParameterDirection.Input, "",
DataRowVersion.Default, "%" + this.TextBox2.Text + "%");
DataSet ds = database.ExecuteDataSet(command);
this.GridView1.DataSource = ds;
this.GridView1.DataBind();
}
private void displayByProcedure()
{
Database db = DatabaseFactory.CreateDatabase("MySqlTest");
DataSet ds = db.ExecuteDataSet("SP_QueryUser"
, new object[] { this.TextBox1.Text.Trim() + "%", "%" + this.TextBox2.Text.Trim() + "%" });
this.GridView1.DataSource = ds;
this.GridView1.DataBind();
}
{
Database db = DatabaseFactory.CreateDatabase("MySqlTest");
DataSet ds = db.ExecuteDataSet("SP_QueryUser"
, new object[] { this.TextBox1.Text.Trim() + "%", "%" + this.TextBox2.Text.Trim() + "%" });
this.GridView1.DataSource = ds;
this.GridView1.DataBind();
}
4. 评论
DAAB对数据库操作封装一下之后,用起来是简单了一些。
使用DAAB,如果你写的SQL,或者是生成的SQL符合SQL92、99等标准,大部分情况下都能满足多数据库支持要求。从上面的测试代码可以看到,一定程度上你不需要关注使用的什么数据库类型。
如果你想多数据库支持方案里面支持更多的一些特性,如更丰富的语法特性、分页、系统函数等,你还得在DAAB的基础上对数据库再次封装扩展。