扩展Enterprise Libary 中DataAceess Block 以支持Access数据库
以下是两个主类
AccessDatabase:
using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Xml;
using Microsoft.Practices.EnterpriseLibrary.Common;
namespace Microsoft.Practices.EnterpriseLibrary.Data.Access
{
/// <summary>
/// Summary description for accessDatabase.
/// </summary>
public class AccessDatabase : Database
{
public AccessDatabase(): base()
{
//
// TODO: Add constructor logic here
//
}
public override DBCommandWrapper GetSqlStringCommandWrapper(string query)
{
ArgumentValidation.CheckForNullReference(query, "query");
ArgumentValidation.CheckForEmptyString(query, "query");
return new AccessCommandWrapper(query);
}
public override DataSet ExecuteDataSet(DBCommandWrapper command)
{
return base.ExecuteDataSet (command);
}
public override void ExecuteNonQuery(DBCommandWrapper command)
{
base.ExecuteNonQuery (command);
}
public override IDataReader ExecuteReader(DBCommandWrapper command)
{
return base.ExecuteReader (command);
}
public override object ExecuteScalar(DBCommandWrapper command)
{
return base.ExecuteScalar (command);
}
public override void LoadDataSet(DBCommandWrapper command, DataSet dataSet, string tableName)
{
base.LoadDataSet (command, dataSet, tableName);
}
public override int UpdateDataSet(DataSet dataSet, string tableName, DBCommandWrapper insertCommand, DBCommandWrapper updateCommand, DBCommandWrapper deleteCommand, UpdateBehavior updateBehavior)
{
return base.UpdateDataSet (dataSet, tableName, insertCommand, updateCommand, deleteCommand, updateBehavior);
}
public override int UpdateDataSet(DataSet dataSet, string tableName, DBCommandWrapper insertCommand, DBCommandWrapper updateCommand, DBCommandWrapper deleteCommand, IDbTransaction transaction)
{
return base.UpdateDataSet (dataSet, tableName, insertCommand, updateCommand, deleteCommand, transaction);
}
public override IDbConnection GetConnection()
{
return new System.Data.OleDb.OleDbConnection(ConnectionString);
}
public override DBCommandWrapper GetStoredProcCommandWrapper(string storedProcedureName)
{
ArgumentValidation.CheckForNullReference(storedProcedureName, "storedProcedureName");
ArgumentValidation.CheckForEmptyString(storedProcedureName, "storedProcedureName");
return new AccessCommandWrapper(storedProcedureName, CommandType.StoredProcedure, ParameterToken);
}
public override DBCommandWrapper GetStoredProcCommandWrapper(string storedProcedureName, params object[] parameterValues)
{
return null;
}
protected override DbDataAdapter GetDataAdapter(UpdateBehavior behavior, IDbConnection connection)
{
string queryStringToBeFilledInLater = String.Empty;
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(queryStringToBeFilledInLater, (System.Data.OleDb.OleDbConnection)connection);
// if (updateBehavior == UpdateBehavior.Continue)
// {
// adapter.RowUpdated += new SqlRowUpdatedEventHandler(OnSqlRowUpdated);
// }
return adapter;
}
protected override char ParameterToken
{
get
{
return '/0';
}
}
}
}
AccessCommandWrapper:
using System;
using System.Data;
using System.Data.OleDb;
namespace Microsoft.Practices.EnterpriseLibrary.Data.Access
{
/// <summary>
/// Summary description for AccessCommandWrapper.
/// </summary>
public class AccessCommandWrapper:DBCommandWrapper
{
private System.Data.OleDb.OleDbCommand command;
private int rowsAffected;
private object[] parameterValues;
private bool needsParameters = false;
private char parameterToken;
internal AccessCommandWrapper()
{
//
// TODO: Add constructor logic here
//
this.command=new OleDbCommand();
this.command.CommandType=CommandType.Text;
}
internal AccessCommandWrapper(string commandText)
{
//
// TODO: Add constructor logic here
//
this.command = CreateCommand(commandText, CommandType.Text);
}
internal AccessCommandWrapper(string commandText, CommandType commandType, char parameterToken)
{
this.parameterToken = parameterToken;
this.command = CreateCommand(commandText, commandType);
}
internal AccessCommandWrapper(string commandText, CommandType commandType, char parameterToken, object[] parameterValues) : this(commandText, commandType, parameterToken)
{
//this.command = CreateCommand(commandText, commandType);
this.parameterValues = parameterValues;
if (commandType == CommandType.StoredProcedure)
{
this.needsParameters = true;
}
}
public override IDbCommand Command
{
get
{
return this.command ;
}
}
public override int RowsAffected
{
get { return this.rowsAffected; }
set { this.rowsAffected = value; }
}
public override int CommandTimeout
{
get { return this.command.CommandTimeout; }
set { this.command.CommandTimeout = value; }
}
public override void AddParameter(string name, DbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
{
this.command.Parameters.Add(CreateParameter(name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value));
}
public override void AddOutParameter(string name, DbType dbType, int size)
{
AddParameter(name, dbType, size, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, DBNull.Value);
}
public override void AddInParameter(string name, DbType dbType, object value)
{
AddParameter(name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, value);
}
public override void AddInParameter(string name, DbType dbType)
{
AddParameter(name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, null);
}
public override void AddInParameter(string name, DbType dbType, string sourceColumn, DataRowVersion sourceVersion)
{
AddParameter(name, dbType, 0, ParameterDirection.Input, true, 0, 0, sourceColumn, sourceVersion, null);
}
public override void AddParameter(string name, DbType dbType, ParameterDirection direction, string sourceColumn, DataRowVersion sourceVersion, object value)
{
OleDbParameter param = CreateParameter(name, dbType, 0, direction, false, 0, 0, sourceColumn, sourceVersion, value);
this.command.Parameters.Add(param);
}
public void AddParameter(string name, OleDbType oledbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
{
OleDbParameter param = CreateParameter(name, DbType.String, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value);
param.OleDbType = oledbType;
this.command.Parameters.Add(param);
}
public override object GetParameterValue(string name)
{
return this.command.Parameters[BuildParameterName(name)].Value;
}
public override void SetParameterValue(string name, object value)
{
this.command.Parameters[BuildParameterName(name)].Value = (value == null) ? DBNull.Value : value;
}
protected override void DoAssignParameterValues()
{
if (SameNumberOfParametersAndValues() == false)
{
throw new InvalidOperationException(SR.ExceptionMessageParameterMatchFailure);
}
int returnParameter = 1;
for (int i = 0; i < this.parameterValues.Length; i++)
{
IDataParameter parameter = this.command.Parameters[i + returnParameter];
// There used to be code here that checked to see if the parameter was input or input/output
// before assigning the value to it. We took it out because of an operational bug with
// deriving parameters for a stored procedure. It turns out that output parameters are set
// to input/output after discovery, so any direction checking was unneeded. Should it ever
// be needed, it should go here, and check that a parameter is input or input/output before
// assigning a value to it.
SetParameterValue(parameter.ParameterName, this.parameterValues[i]);
}
}
protected override bool DoIsFurtherPreparationNeeded()
{
return this.needsParameters;
}
protected override void DoDiscoverParameters(char parameterToken)
{
this.parameterToken = parameterToken;
using (OleDbCommand newCommand = CreateNewCommandAndConnectionForDiscovery())
{
OleDbCommandBuilder.DeriveParameters(newCommand);
foreach (IDataParameter parameter in newCommand.Parameters)
{
IDataParameter cloneParameter = (IDataParameter)((ICloneable)parameter).Clone();
cloneParameter.ParameterName = BuildParameterName(cloneParameter.ParameterName);
this.command.Parameters.Add(cloneParameter);
}
newCommand.Connection.Close();
}
}
public override void Dispose()
{
this.command.Dispose();
}
private static OleDbCommand CreateCommand(string commandText, CommandType commandType)
{
OleDbCommand newCommand = new OleDbCommand();
newCommand.CommandText = commandText;
newCommand.CommandType = commandType;
return newCommand;
}
private OleDbCommand CreateNewCommandAndConnectionForDiscovery()
{
OleDbConnection clonedConnection = (OleDbConnection)((ICloneable)this.command.Connection).Clone();
clonedConnection.Open();
OleDbCommand newCommand = CreateCommand(this.command.CommandText, this.command.CommandType);
newCommand.Connection = clonedConnection;
return newCommand;
}
private string BuildParameterName(string name)
{
//System.Diagnostics.Debug.Assert(parameterToken != 0x0000);
if (name[0] != this.parameterToken)
{
return name.Insert(0, new string(this.parameterToken, 1));
}
return name;
}
private OleDbParameter CreateParameter(string name, DbType type, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
{
OleDbParameter param = this.command.CreateParameter();
param.ParameterName = BuildParameterName(name);
if ((type.Equals(DbType.Object)) && (value is byte[]))
{
//myby there i an error
param.OleDbType = OleDbType.VarBinary ;
}
else
{
param.DbType = type;
}
param.Size = size;
param.Direction = direction;
param.IsNullable = nullable;
param.Precision = precision;
param.Scale = scale;
param.SourceColumn = sourceColumn;
param.SourceVersion = sourceVersion;
param.Value = (value == null) ? DBNull.Value : value;
return param;
}
private bool SameNumberOfParametersAndValues()
{
int returnParameterCount = 1;
int numberOfParametersToStoredProcedure = this.command.Parameters.Count - returnParameterCount;
int numberOfValuesProvidedForStoredProcedure = this.parameterValues.Length;
return numberOfParametersToStoredProcedure == numberOfValuesProvidedForStoredProcedure;
}
}
}
配置:
1、首先在Database types节点添加Database type
将TypeName 设为:Microsoft.Practices.EnterpriseLibrary.Data.Access.AccessDatabase, Microsoft.Practices.EnterpriseLibrary.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
2、在ConnectionStrings节点中设置新的Connectionstring,删除默认的配置,再加上三个新项
1)Parameter
Name:Data Source ; Value:C:/Acc.Mdb(就是Access数据库路径)
2)Parameter
Name:Provider; Value:Microsoft.Jet.OLEDB.4.0
3) Password Parameter
Name:Jet OLEDB:Database Password; MaskValue:XXX