data access layer for multiple databases c#

Views: 376
Created Date: 01-Oct-2016

Following code shows the design of data access layer with multiple databases.

Design your data access layer with multiple databases.

  • An enum is needed to represent different providers, i.e SQL, Access etc
  • Class perhaps called DataAccess which in the constructor accepts the provider type:
public enum Provider

   { SQL, Access }




   public DataAccess(Provider provider)

   {

        switch (provider)

        {

            case Provider.Access:

                factory = DbProviderFactories.GetFactory("System.Data.OleDb");

                break;

            case Provider.Sql:

                factory = DbProviderFactories.GetFactory("System.Data.SqlClient");

                break;

        }

        this.connection = factory.CreateConnection();

   }




    public DbDataReader GetDataReader(string statement, params DbParameter[] parameters)

    {

        using (DbCommand command = factory.CreateCommand())

        {

            if (connection.State == ConnectionState.Closed)

            {

                connection.Open();

            }

            command.CommandText = statement;

            command.Connection = connection;

            parameters.ToList().ForEach(x => command.Parameters.Add(x));




            return command.ExecuteReader(CommandBehavior.CloseConnection);

        }

    }

}

 

Whenever a command or parameter need to be created, the factory variable can be used.




DbCommand command = Factory.CreateCommand();

command.CommandType = CommandType.StoredProcedure;

command.CommandText = procedureName;

command.Connection = connection;

Similar Tags
4.6 5