Factory Abstract Data Layer

Question

I am new to developing the Abstract Factory template and would like to create an abstract Factory layer in a data layer that will help me link this layer to any other databases like sql and oracle. Can you help me in developing this task, please. Note that the database connection string will not be found in this layer in the presentation.

thanks

edited

public abstract class Database { public string connectionString; #region Abstract Functions public abstract IDbConnection CreateConnection(); public abstract IDbCommand CreateCommand(); public abstract IDbConnection CreateOpenConnection(); public abstract IDbCommand CreateCommand(string commandText, IDbConnection connection); public abstract IDbCommand CreateStoredProcCommand(string procName, IDbConnection connection); public abstract IDataParameter CreateParameter(string parameterName, object parameterValue); #endregion } 

---

 public class SQLDatabase : Database { public override IDbConnection CreateConnection() { return new SqlConnection(connectionString); } public override IDbCommand CreateCommand() { return new SqlCommand(); } public override IDbConnection CreateOpenConnection() { SqlConnection connection = (SqlConnection)CreateConnection(); connection.Open(); return connection; } public override IDbCommand CreateCommand(string commandText, IDbConnection connection) { SqlCommand command = (SqlCommand)CreateCommand(); command.CommandText = commandText; command.Connection = (SqlConnection)connection; command.CommandType = CommandType.Text; return command; } public override IDbCommand CreateStoredProcCommand(string procName, IDbConnection connection) { SqlCommand command = (SqlCommand)CreateCommand(); command.CommandText = procName; command.Connection = (SqlConnection)connection; command.CommandType = CommandType.StoredProcedure; return command; } public override IDataParameter CreateParameter(string parameterName, object parameterValue) { return new SqlParameter(parameterName, parameterValue); } } 

These are two classes that I created.

Answer 1

Functionality already exists.

Add the connection string to app / webb.config:

 <connectionStrings> <add name="TheDatabase" providerName="System.Data.OleDb" connectionString="Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User Id=xxx;Password=yyy;Data Source=zzzz;Extended Properties="/> </connectionStrings> 

Create a connection using factory:

 var connectionString = ConfigurationManager.ConnectionStrings["TheDatabase"]; var providerName = connectionString.ProviderName; var factory = DbProviderFactories.GetFactory(providerName); 

Get connection:

 var connection = factory.CreateConnection(); 

Get the command:

 var command == connection.CreateCommand(); 

The only thing you need to do is switch the driver to the /web.config application. No other changes are required.

Update

 public class Database { public static IDbConnection CreateOpenConnection() { var connectionString = ConfigurationManager.ConnectionStrings["TheDatabase"]; var providerName = connectionString.ProviderName; var factory = DbProviderFactories.GetFactory(providerName); var connection = factory.CreateConnection(); connection.Open(); return connection; } } class FlowerManager : DataWorker { public static void GetFlowers() { using (IDbConnection connection = Database.CreateOpenConnection()) { using (IDbCommand command = connection.CreateCommand("SELECT * FROM FLOWERS", connection)) { using (IDataReader reader = command.ExecuteReader()) { // ... } } } } } 

Answer 2

Most of the required functionality can be obtained from

  System.Data.Common.DbProviderFactories 

where you can get System.Data.Common.DbProviderFactory elements that are implemented by most pronet-databaseproviders.

Update

havig your own factory is ok. if you look at examples of working databases, look at the source code

• queryexpress running gui database query,
• queryexplus spinon queryexpress
• mygeneration code generator that connects to many different databases
• NHibernate with a sophisticated driver for many databases

Answer 3

I would not have the "createcommand" or "createconnection" methods.

It is much better to approach each of the access methods (for example, "GetAccounts") to process their own instance of the connection / command.

Connection and Command objects implement IDisposable. Therefore, it is better to use statements in which they are created and deleted as necessary. Thus, this can lead to serious memory problems.

In addition, the CreateParameter method does not seem to provide any real benefit from simply invoking the “new SqlParameter” in the code that should create these parameters.

I would do the following:

 public interface IDbAccess { String ConnectionString; Collection<Account> GetAccountsById(Int32 id); Boolean StoreAccount(Account acct); } public class SqlDatabase : IDbAccess { public String ConnectionString {get; set;} public SqlDatabase(String connection) { ConnectionString = connection; } public Collection<Account> GetAccountsById(Int32 id) { using (SqlConnection connect = new SqlConnection(ConnectionString)) { using (SqlCommand cmd = new SqlCommand(connect)) { /// etc. } } } } 

Thus, your datalayer is specific to the functions you provide. There are already excellent wrappers for accessing db, such as the Enterprise Library. The approach you take adds nothing and introduces errors.

In addition, this approach means that you can implement vendors without a database, such as XML, web services, etc. with zero code changes.

Answer 4

Hello People I know is an old post, but I would like to share something with you.

The Enterprise Library and OleDb have some problem, when you want to insert an image larger than 32k, it will throw an Exception, so solve this, I did:

Create a project you can call CustomProvider

Create a cluster that you name in the database

  public abstract class Database { public string ConnectionString { get; set; } // Preciso uma variavel para guardar o ConnectionString public IDbConnection Connection { get; set; } //public abstract string ProviderName { get; } // Preciso uma variavel para guardar o ConnectionString //public abstract IDbConnection CreateConnection(string ConnectionString); public abstract IDbConnection CreateConnection(); // Preciso um Metodo Abstract para CreateConnection Para Tratar da Connection public abstract IDbCommand CreateCommand(); } } 

2. Create Seccond Class OracleDatabase.cs

3. Create third class SQLDatabase.cs

     public class OracleDatabase : Database { public override IDbConnection CreateConnection() { return new OracleConnection(ConnectionString); } public override IDbCommand CreateCommand() { return new OracleCommand(); } public override IDbConnection CreateOpenConnection() { OracleConnection connection = (OracleConnection)CreateConnection(); connection.Open(); return connection; } public override IDbCommand CreateCommand(string commandText, IDbConnection connection) { OracleCommand command = (OracleCommand)CreateCommand(); command.CommandText = commandText; command.Connection = (OracleConnection)connection; command.CommandType = CommandType.Text; return command; } } 

   public class SQLDatabase: database {

     public override IDbConnection CreateConnection() { return new SqlConnection(ConnectionString); } public override IDbCommand CreateCommand() { return new SqlCommand(); } public override IDbConnection CreateOpenConnection() { SqlConnection connection = (SqlConnection)CreateConnection(); connection.Open(); return connection; } public override IDbCommand CreateCommand(string commandText, IDbConnection connection) { SqlCommand command = (SqlCommand)CreateCommand(); command.CommandText = commandText; command.Connection = (SqlConnection)connection; command.CommandType = CommandType.Text; return command; } public override IDbCommand CreateStoredProcCommand(string procName, IDbConnection connection) { SqlCommand command = (SqlCommand)CreateCommand(); command.CommandText = procName; command.Connection = (SqlConnection)connection; command.CommandType = CommandType.StoredProcedure; return command; } } 

4. and then in the program

     Database db = Factory.CreateDatabase("ConnectionString"); try { using (IDbConnection w_connection = db.Connection) { w_connection.Open(); IDbTransaction transation = w_connection.BeginTransaction(); IDbCommand dbcomand = db.CreateStoredProcCommand("INSERTTEST"); db.AddInParameter(dbcomand, "@ATTCH", DbType.Binary, bytes); db.ExecuteNonQuery(dbcomand, transation); transation.Commit(); } } catch (Exception) { } } 

You must override all defined methods in the master class.

5. Create Factory.cs

    public static Database CreateDatabase(string ConnectionString) { //var Conn = ConfigurationManager.ConnectionStrings[ConnectionString].ToString(); if (string.IsNullOrEmpty(ConnectionString)) throw new Exception("Connectionstring Not Found" + ConnectionString); Database db = null; if (ConfigurationManager.ConnectionStrings[ConnectionString].ProviderName.Contains("Oracle")) { db = new OracleDatabase(); db.ConnectionString = GetConnectionString(ConnectionString); db.Connection = db.CreateConnection(); } else { db = new SQLDatabase(); db.ConnectionString = GetConnectionString(ConnectionString); db.Connection = db.CreateConnection(); } return db; }