注释掉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项目引用的命名空间:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
部分测试代码如下:
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的基础上对数据库再次封装扩展。