DBManager按名称而不是索引Retieve参数

我很久以前就发现了一个DBManager类，并且已经成功地使用了一年多。它缺少一个"功能"，即按名称而不是索引检索参数。目前我使用：

  using (IDBManager dbManager = new DBManager())
  {
    try
    {
      dbManager.Open();
      dbManager.CreateParameters(8);
      dbManager.AddParameters(0, "@Court", 0);
      dbManager.AddParameters(1, "@BookingDate", DateTime.Today);
      dbManager.AddParameters(2, "@BookingNumber", bookingNumber + 0.01M);
      dbManager.AddParameters(3, "@EventID", eventID);
      dbManager.AddParameters(4, "@BookingStartDT", BookingStartDT);
      dbManager.AddParameters(5, "@BookingFinishDT", BookingFinishDT);
      dbManager.AddParameters(6, "@CourseID", courseID);
      dbManager.AddParameters(7, "@ProSessionID", proSessionID);

等等来创建实例和参数，但需要始终使用以下格式：

            dbManager.Parameters[1].Value = requestedDate;

在我的代码中。

我希望能够使用中的参数名称

            dbManager.Parameters["@BookingDate"].Value = requestedDate;

因为我厌倦了滚动代码来检查索引号是什么。

我基本上理解使用接口，但在编写接口时，我肯定是个黑客。

在我的接口IDBManager类中，我有：

IDbDataParameter[] Parameters
{
  get;
}

在我密封的DBManagerFactory类中，我有：

public static IDbDataParameter[] GetParameters(DataProvider providerType, int paramsCount)
{
  IDbDataParameter[] idbParams = new IDbDataParameter[paramsCount];
  switch (providerType)
  {
    case DataProvider.SqlServer:
      for (int i = 0; i < paramsCount; ++i)
      {
        idbParams[i] = new SqlParameter();
      }
      break;
    case DataProvider.SqlExpress:
      for (int i = 0; i < paramsCount; ++i)
      {
        idbParams[i] = new SqlParameter();
      }
      break;
    case DataProvider.VistaDB:
      for (int i = 0; i < paramsCount; ++i)
      {
        idbParams[i] = new VistaDBParameter();
      }
      break;
    case DataProvider.SqlCE:
      for (int i = 0; i < paramsCount; ++i)
      {
        idbParams[i] = new SqlCeParameter();
      }
      break;
    case DataProvider.Oracle:
      for (int i = 0; i < paramsCount; ++i)
      {
        idbParams[i] = new OracleParameter();
      }
      break;
    case DataProvider.Odbc:
      for (int i = 0; i < paramsCount; ++i)
      {
        idbParams[i] = new OdbcParameter();
      }
      break;
    default:
      idbParams = null;
      break;
  }
  return idbParams;
 }
}

在我的密封类DBManager中，我有：

public IDbDataParameter[] Parameters
{
  get
  {
    return idbParameters;
  }
}

我需要向类中添加哪些代码才能获得所需的结果？我希望得到一些简单的解释，同时我也希望将其作为编写界面及其工作方式的良好培训示例。

提前感谢。。。

编辑：以下是所涉及的三个课程的完整副本。。

/// <summary>
/// The DataProvider's available.
/// </summary>
public enum DataProvider
{
    /// <summary>
    /// VistaDB
    /// </summary>
    VistaDB,
    /// <summary>
    /// SqlServer
    /// </summary>
    SqlServer,
    /// <summary>
    /// SQL Express
    /// </summary>
    SqlExpress,
    /// <summary>
    /// SQL CE
    /// </summary>
    SqlCE,
    /// <summary>
    /// Oracle
    /// </summary>
    Oracle,
    /// <summary>
    /// ODBC
    /// </summary>
    Odbc
}
/// <summary>
/// IDBManager Interface: Provides access to the DBManagerFactory sealed class.
/// </summary>
public interface IDBManager : IDisposable
{
/// <summary>
/// Gets or sets the type of the provider.
/// </summary>
/// <value>The type of the provider.</value>
DataProvider ProviderType
{
    get;
    set;
}
/// <summary>
/// Gets or sets the connection string.
/// </summary>
/// <value>The connection string.</value>
string ConnectionString
{
    get;
    set;
}
/// <summary>
/// Gets the connection.
/// </summary>
/// <value>The connection.</value>
IDbConnection Connection
{
    get;
}
/// <summary>
/// Gets the transaction.
/// </summary>
/// <value>The transaction.</value>
IDbTransaction Transaction
{
    get;
}
/// <summary>
/// Gets the data reader.
/// </summary>
/// <value>The data reader.</value>
IDataReader DataReader
{
    get;
}
/// <summary>
/// Gets the command.
/// </summary>
/// <value>The command.</value>
IDbCommand Command
{
    get;
}
/// <summary>
/// Gets the parameters.
/// </summary>
/// <value>The parameters.</value>
IDbDataParameter[] Parameters
{
    get;
}

/// <summary>
/// Opens this instance.
/// </summary>
void Open();
/// <summary>
/// Begins the transaction.
/// </summary>
void BeginTransaction();
/// <summary>
/// Commits the transaction.
/// </summary>
void CommitTransaction();

/// <summary>
/// Rolls the back transaction.
/// </summary>
void RollBackTransaction();
/// <summary>
/// Creates the parameters.
/// </summary>
/// <param name="paramsCount">The params count.</param>
void CreateParameters(int paramsCount);
/// <summary>
/// Adds the parameters.
/// </summary>
/// <param name="index">The index.</param>
/// <param name="paramName">Name of the param.</param>
/// <param name="objValue">The obj value.</param>
void AddParameters(int index, string paramName, object objValue);
/// <summary>
/// The command to be utilised by the reader.
/// </summary>
/// <param name="commandType">Type of the command.</param>
/// <param name="commandText">The command text.</param>
/// <returns></returns>
IDataReader ExecuteReader(CommandType commandType, string commandText);
/// <summary>
/// The command to be utilised to fill the dataset.
/// </summary>
/// <param name="commandType">Type of the command.</param>
/// <param name="commandText">The command text.</param>
/// <returns></returns>
DataSet ExecuteDataSet(CommandType commandType, string commandText);

/// <summary>
/// Creates a new datatable containg the columns specified in the command. Replaces any current table data.
/// </summary>
/// <param name="commandType">Type of the command.</param>
/// <param name="commandText">The command text.</param>
/// <returns></returns>
DataTable ExecuteDataTable(CommandType commandType, string commandText);

/// <summary>
/// Executes the command and returns the result as an object.
/// </summary>
/// <param name="CommandType">Type of the command.</param>
/// <param name="CommandText">The command text.</param>
/// <returns>The result of the command as an object.</returns>
object ExecuteScalar(CommandType CommandType, string CommandText);
/// <summary>
/// Executes the command and returns the number of rows affected as an int.
/// </summary>
/// <param name="CommandType">Type of the command.</param>
/// <param name="CommandText">The command text.</param>
/// <returns>Returns the number of rows affected.</returns>
int ExecuteNonQuery(CommandType CommandType, string CommandText);
/// <summary>
/// Closes the reader.
/// </summary>
void CloseReader();
/// <summary>
/// Closes this instance.
/// </summary>
void Close();
/// <summary>
/// Releases unmanaged and - optionally - managed resources
/// </summary>
//void Dispose();
}
/// <summary>
/// DBManagerFactory: Sealed class and cannot be inherited.
/// </summary>
public sealed class DBManagerFactory
{
    /// <summary>
    /// Initializes a new instance of the <see cref="DBManagerFactory"/> class.
    /// </summary>
    private DBManagerFactory() { }

    /// <summary>
    /// Gets the connection.
    /// </summary>
    /// <param name="providerType">Type of the provider.</param>
    /// <returns></returns>
    public static IDbConnection GetConnection(DataProvider providerType)
    {
        IDbConnection iDbConnection = null;
        switch (providerType)
        {
            case DataProvider.SqlServer:
                iDbConnection = new SqlConnection();
                break;
            case DataProvider.SqlExpress:
                iDbConnection = new SqlConnection();
                break;
            case DataProvider.VistaDB:
                iDbConnection = new VistaDBConnection();
                break;
            case DataProvider.SqlCE:
                iDbConnection = new SqlCeConnection();
                break;
            case DataProvider.Oracle:
                iDbConnection = new OracleConnection();
                break;
            case DataProvider.Odbc:
                iDbConnection = new OdbcConnection();
                break;
            default:
                return null;
        }
        return iDbConnection;
    }
    /// <summary>
    /// Gets the command.
    /// </summary>
    /// <param name="providerType">Type of the provider.</param>
    /// <returns></returns>
    public static IDbCommand GetCommand(DataProvider providerType)
    {
        switch (providerType)
        {
            case DataProvider.SqlServer:
                return new SqlCommand();
            case DataProvider.SqlExpress:
                return new SqlCommand();
            case DataProvider.VistaDB:
                return new VistaDBCommand();
            case DataProvider.SqlCE:
                return new SqlCeCommand();
            case DataProvider.Oracle:
                return new OracleCommand();
            case DataProvider.Odbc:
                return new OdbcCommand();
            default:
                return null;
        }
    }
    /// <summary>
    /// Gets the data adapter.
    /// </summary>
    /// <param name="providerType">Type of the provider.</param>
    /// <returns></returns>
    public static IDbDataAdapter GetDataAdapter(DataProvider providerType)
    {
        switch (providerType)
        {
            case DataProvider.SqlServer:
                return new SqlDataAdapter();
            case DataProvider.SqlExpress:
                return new SqlDataAdapter();
            case DataProvider.VistaDB:
                return new VistaDBDataAdapter();
            case DataProvider.SqlCE:
                return new SqlCeDataAdapter();
            case DataProvider.Oracle:
                return new OracleDataAdapter();
            case DataProvider.Odbc:
                return new OdbcDataAdapter();
            default:
                return null;
        }
    }
    /// <summary>
    /// Gets the transaction.
    /// </summary>
    /// <param name="providerType">Type of the provider.</param>
    /// <returns></returns>
    public static IDbTransaction GetTransaction(DataProvider providerType)
    {
        IDbConnection iDbConnection = GetConnection(providerType);
        IDbTransaction iDbTransaction = iDbConnection.BeginTransaction();
        return iDbTransaction;
    }
    /// <summary>
    /// Gets the parameter.
    /// </summary>
    /// <param name="providerType">Type of the provider.</param>
    /// <returns></returns>
    public static IDataParameter GetParameter(DataProvider providerType)
    {
        IDataParameter iDataParameter = null;
        switch (providerType)
        {
            case DataProvider.SqlServer:
                iDataParameter = new SqlParameter();
                break;
            case DataProvider.SqlExpress:
                iDataParameter = new SqlParameter();
                break;
            case DataProvider.VistaDB:
                iDataParameter = new VistaDBParameter();
                break;
            case DataProvider.SqlCE:
                iDataParameter = new SqlCeParameter();
                break;
            case DataProvider.Oracle:
                iDataParameter = new OracleParameter();
                break;
            case DataProvider.Odbc:
                iDataParameter = new OdbcParameter();
                break;
        }
        return iDataParameter;
    }
    /// <summary>
    /// Gets the parameters.
    /// </summary>
    /// <param name="providerType">Type of the provider.</param>
    /// <param name="paramsCount">The params count.</param>
    /// <returns></returns>
    public static IDbDataParameter[] GetParameters(DataProvider providerType, int paramsCount)
    {
        IDbDataParameter[] idbParams = new IDbDataParameter[paramsCount];
        switch (providerType)
        {
            case DataProvider.SqlServer:
                for (int i = 0; i < paramsCount; ++i)
                {
                    idbParams[i] = new SqlParameter();
                }
                break;
            case DataProvider.SqlExpress:
                for (int i = 0; i < paramsCount; ++i)
                {
                    idbParams[i] = new SqlParameter();
                }
                break;
            case DataProvider.VistaDB:
                for (int i = 0; i < paramsCount; ++i)
                {
                    idbParams[i] = new VistaDBParameter();
                }
                break;
            case DataProvider.SqlCE:
                for (int i = 0; i < paramsCount; ++i)
                {
                    idbParams[i] = new SqlCeParameter();
                }
                break;
            case DataProvider.Oracle:
                for (int i = 0; i < paramsCount; ++i)
                {
                    idbParams[i] = new OracleParameter();
                }
                break;
            case DataProvider.Odbc:
                for (int i = 0; i < paramsCount; ++i)
                {
                    idbParams[i] = new OdbcParameter();
                }
                break;
            default:
                idbParams = null;
                break;
        }
        return idbParams;
    }
}

/// <summary>
/// DBManager 
/// </summary>
public sealed class DBManager : IDBManager, IDisposable
{
    private IDbConnection idbConnection;
    private IDataReader idataReader;
    private IDbCommand idbCommand;
    private DataProvider providerType;
    private IDbTransaction idbTransaction = null;
    private IDbDataParameter[] idbParameters = null;
    private string strConnection;
    /// <summary>
    /// Initializes a new instance of the <see cref="DBManager"/> class.
    /// </summary>
    public DBManager()
    {
        //DataProvider defaultDataProvider = DataProvider.VistaDB;
        //if (Config.DALProvider != null)
        //  defaultDataProvider = Config.DALProvider;
        //switch (defaultDataProvider)
        //{
        //  case(DataProvider.VistaDB):
        //    providerType = DataProvider.VistaDB;
        //    break;


        //}
        //Config.DALProvider = DataProvider.VistaDB; // <--------------------- Needs adding to Config.xyz
        this.providerType = Config.DALProvider;
        this.ConnectionString = CommonData.vdbConnectionString; //.sqlExpressConnectionString;
        //.vdbConnectionString; //.sqlExpressConnectionString; // CommonData.vdbConnectionString;
    }
    /// <summary>
    /// Initializes a new instance of the <see cref="DBManager"/> class.
    /// </summary>
    /// <param name="providerType">Type of the provider.</param>
    public DBManager(DataProvider providerType)
    {
        this.providerType = providerType;
    }
    /// <summary>
    /// Initializes a new instance of the <see cref="DBManager"/> class.
    /// </summary>
    /// <param name="providerType">Type of the provider.</param>
    /// <param name="connectionString">The connection string.</param>
    public DBManager(DataProvider providerType, string connectionString)
    {
        this.providerType = providerType;
        this.strConnection = connectionString;
    }
    /// <summary>
    /// Gets the connection.
    /// </summary>
    /// <value>The connection.</value>
    public IDbConnection Connection
    {
        get
        {
            return idbConnection;
        }
    }
    /// <summary>
    /// Gets the data reader.
    /// </summary>
    /// <value>The data reader.</value>
    public IDataReader DataReader
    {
        get
        {
            return idataReader;
        }
        set
        {
            idataReader = value;
        }
    }
    /// <summary>
    /// Gets or sets the type of the provider.
    /// </summary>
    /// <value>The type of the provider.</value>
    public DataProvider ProviderType
    {
        get
        {
            return providerType;
        }
        set
        {
            providerType = value;
        }
    }
    /// <summary>
    /// Gets or sets the connection string.
    /// </summary>
    /// <value>The connection string.</value>
    public string ConnectionString
    {
        get
        {
            return strConnection;
        }
        set
        {
            strConnection = value;
        }
    }
    /// <summary>
    /// Gets the command.
    /// </summary>
    /// <value>The command.</value>
    public IDbCommand Command
    {
        get
        {
            return idbCommand;
        }
    }
    /// <summary>
    /// Gets the transaction.
    /// </summary>
    /// <value>The transaction.</value>
    public IDbTransaction Transaction
    {
        get
        {
            return idbTransaction;
        }
    }
    /// <summary>
    /// Gets the parameters.
    /// </summary>
    /// <value>The parameters.</value>
    public IDbDataParameter[] Parameters
    {
        get
        {
            return idbParameters;
        }
    }
    /// <summary>
    /// Opens this instance.
    /// </summary>
    public void Open()
    {
        idbConnection = DBManagerFactory.GetConnection(this.providerType);
        idbConnection.ConnectionString = this.ConnectionString;
        if (idbConnection.State != ConnectionState.Open)
            idbConnection.Open();
        this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
    }
    /// <summary>
    /// Closes this instance.
    /// </summary>
    public void Close()
    {
        if (idbConnection != null && idbConnection.State != ConnectionState.Closed)
            idbConnection.Close();
    }
    /// <summary>
    /// Releases unmanaged and - optionally - managed resources
    /// </summary>
    public void Dispose()
    {
        GC.SuppressFinalize(this);
        this.Close();
        this.idbCommand = null;
        this.idbTransaction = null;
        this.idbConnection = null;
        if (ProviderType == DataProvider.VistaDB)
            VistaDBConnection.ClearAllPools();
    }
    /// <summary>
    /// Creates the parameters.
    /// </summary>
    /// <param name="paramsCount">The params count.</param>
    public void CreateParameters(int paramsCount)
    {
        idbParameters = new IDbDataParameter[paramsCount];
        idbParameters = DBManagerFactory.GetParameters(this.ProviderType, paramsCount);
    }
    /// <summary>
    /// Adds the parameters.
    /// </summary>
    /// <param name="index">The index.</param>
    /// <param name="paramName">Name of the param.</param>
    /// <param name="objValue">The obj value.</param>
    public void AddParameters(int index, string paramName, object objValue)
    {
        if (index < idbParameters.Length)
        {
            idbParameters[index].ParameterName = paramName;
            idbParameters[index].Value = objValue;
        }
    }
    /// <summary>
    /// Begins the transaction.
    /// </summary>
    public void BeginTransaction()
    {
        if (this.idbTransaction == null)
            idbTransaction = DBManagerFactory.GetTransaction(this.ProviderType);
        this.idbCommand.Transaction = idbTransaction;
    }
    /// <summary>
    /// Commits the transaction.
    /// </summary>
    public void CommitTransaction()
    {
        if (this.idbTransaction != null)
            this.idbTransaction.Commit();
        idbTransaction = null;
    }
    /// <summary>
    /// Rolls back the transaction.
    /// </summary>
    public void RollBackTransaction()
    {
        if (this.idbTransaction != null)
            this.idbTransaction.Rollback();
        idbTransaction = null;
    }

    /// <summary>
    /// Executes the reader.
    /// </summary>
    /// <param name="commandType">Type of the command.</param>
    /// <param name="commandText">The command text.</param>
    /// <returns></returns>
    public IDataReader ExecuteReader(CommandType commandType, string commandText)
    {
        this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
        idbCommand.Connection = this.Connection;
        PrepareCommand(idbCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
        this.DataReader = idbCommand.ExecuteReader();
        idbCommand.Parameters.Clear();
        return this.DataReader;
    }
    /// <summary>
    /// Closes the reader.
    /// </summary>
    public void CloseReader()
    {
        if (this.DataReader != null)
            this.DataReader.Close();
    }
    private void AttachParameters(IDbCommand command, IDbDataParameter[] commandParameters)
    {
        foreach (IDbDataParameter idbParameter in commandParameters)
        {
            if ((idbParameter.Direction == ParameterDirection.InputOutput) && (idbParameter.Value == null))
            {
                idbParameter.Value = DBNull.Value;
            }
            command.Parameters.Add(idbParameter);
        }
    }
    private void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, IDbDataParameter[] commandParameters)
    {
        command.Connection = connection;
        command.CommandText = commandText;
        command.CommandType = commandType;
        if (transaction != null)
        {
            command.Transaction = transaction;
        }
        if (commandParameters != null)
        {
            AttachParameters(command, commandParameters);
        }
    }
    /// <summary>
    /// Executes the command and returns the number of rows affected as an int.
    /// </summary>
    /// <param name="CommandType">Type of the command.</param>
    /// <param name="CommandText">The command text.</param>
    /// <returns>Returns the number of rows affected.</returns>
    public int ExecuteNonQuery(CommandType CommandType, string CommandText)
    {
        this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
        PrepareCommand(idbCommand, this.Connection, this.Transaction, CommandType, CommandText, this.Parameters);
        int returnValue = idbCommand.ExecuteNonQuery();
        idbCommand.Parameters.Clear();
        return returnValue;
    }
    /// <summary>
    /// Executes the command and returns the results as an object.
    /// </summary>
    /// <param name="CommandType">Type of the command.</param>
    /// <param name="CommandText">The command text.</param>
    /// <returns>An object as returned by the command.</returns>
    public object ExecuteScalar(CommandType CommandType, string CommandText)
    {
        this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
        PrepareCommand(idbCommand, this.Connection, this.Transaction, CommandType, CommandText, this.Parameters);
        object returnValue = idbCommand.ExecuteScalar();
        idbCommand.Parameters.Clear();
        return returnValue;
    }
    /// <summary>
    /// Executes the data set.
    /// </summary>
    /// <param name="commandType">Type of the command.</param>
    /// <param name="commandText">The command text.</param>
    /// <returns></returns>
    public DataSet ExecuteDataSet(CommandType commandType, string commandText)
    {
        this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
        PrepareCommand(idbCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
        IDbDataAdapter dataAdapter = DBManagerFactory.GetDataAdapter(this.ProviderType);
        dataAdapter.SelectCommand = idbCommand;
        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet);
        idbCommand.Parameters.Clear();
        return dataSet;
    }
    public DataTable ExecuteDataTable(CommandType commandType, string commandText)
    {
        this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
        PrepareCommand(idbCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
        //IDbDataAdapter dataAdapter = DBManagerFactory.GetDataAdapter(this.ProviderType);
        //dataAdapter.SelectCommand = idbCommand;
        DataTable dataTable = new DataTable();
        using (IDataReader idr = ExecuteReader(commandType, commandText))
        {
            dataTable.Load(idr);
        }

        idbCommand.Parameters.Clear();
        return dataTable;
    }
}