Accèder à une base Access via DbProvider

Avec DbProvider aucun changement au niveau du code de la présentation, de même pour le fichier pour de configuration

public class ClientDAO : DbBase          

{          

             public List<Client> GetClients()          

             {

                 List<Client> clients = new List<Client>();

            using (DbConnection connection = Sql.Instance.GetConnection())

            {

                using (DbCommand command = connection.CreateCommand())

                {

                    command.CommandType = CommandType.Text;

                    command.CommandText = "SELECT [Id],[Name],[Email],[CategoryId] FROM [Client]";

                    using (DbDataReader reader = command.ExecuteReader())

                    {

                        while (reader.Read())

                        {

                            Client client = new Client();

                            client.Id = reader["Id"] == DBNull.Value ? default(int) : int.Parse(reader["Id"].ToString());

                            client.Name = reader["Name"] == DBNull.Value ? default(string) : reader["Name"].ToString();

                            client.Email = reader["Email"] == DBNull.Value ? default(string) : reader["Email"].ToString();

                            client.CategoryId = reader["CategoryId"] == DBNull.Value ? default(int) : int.Parse(reader["CategoryId"].ToString());

                            clients.Add(client);

                        }

                    }

                }

            }

                    return clients;

              }

              public Client GetClient(int id)

              {

                   Client client = new Client();

                   using (DbConnection connection = Sql.Instance.GetConnection())

              {

                using (DbCommand command = connection.CreateCommand())

                {

                    command.CommandType = CommandType.Text;

                    command.CommandText = "SELECT [Id],[Name],[Email],[CategoryId] FROM [Client] WHERE [Id]=parid";

                    command.Parameters.Add(CreateParameter(command, "@id", id));

                    using (DbDataReader reader = command.ExecuteReader())

                    {

                        while (reader.Read())

                        {

                            client.Id = reader["Id"] == DBNull.Value ? default(int) : int.Parse(reader["Id"].ToString());

                            client.Name = reader["Name"] == DBNull.Value ? default(string) : reader["Name"].ToString();

                            client.Email = reader["Email"] == DBNull.Value ? default(string) : reader["Email"].ToString();

                            client.CategoryId = reader["CategoryId"] == DBNull.Value ? default(int) : int.Parse(reader["CategoryId"].ToString());

                        }

                    }

                }

            }

                    return client;

               }

              public int AddClient(Client client)

              {

                  int result = 0;

            using (DbConnection connection = Sql.Instance.GetConnection())

            {

                using (DbCommand command = connection.CreateCommand())

                {

                    command.CommandType = CommandType.Text;

                    command.CommandText = "INSERT INTO [Client]([Name],[Email],[CategoryId]) VALUES(?,?,?);";

                    command.Parameters.Add(CreateParameter(command, "@name", client.Name));

                    command.Parameters.Add(CreateParameter(command, "@email", client.Email));

                    command.Parameters.Add(CreateParameter(command, "@categoryId", client.CategoryId));

                    command.ExecuteNonQuery();

                    command.CommandText = "SELECT @@IDENTITY";

                    result = Convert.ToInt32(command.ExecuteScalar());

                }

            }

                    return result;

               }

               public int UpdateClient(Client client)

               {

                 int result = 0;

            using (DbConnection connection = Sql.Instance.GetConnection())

            {

                using (DbCommand command = connection.CreateCommand())

                {

                    command.CommandType = CommandType.Text;

                    command.CommandText = "UPDATE [Client] SET [Name]=?,[Email]=?,[CategoryId]=? WHERE [Id]=?";

                    command.Parameters.Add(CreateParameter(command, "@name", client.Name));

                    command.Parameters.Add(CreateParameter(command, "@email", client.Email));

                    command.Parameters.Add(CreateParameter(command, "@categoryId", client.CategoryId));

                    command.Parameters.Add(CreateParameter(command, "@categoryId", client.Id));

                    result = command.ExecuteNonQuery();

                }

            }

                    return result;

             }

             public int DeleteClient(Client client)

             {

                 int result = 0;

            using (DbConnection connection = Sql.Instance.GetConnection())

            {

                using (DbCommand command = connection.CreateCommand())

                {

                    command.CommandType = CommandType.Text;

                    command.CommandText = "DELETE FROM [Client] WHERE [Id]=?";

                    command.Parameters.Add(CreateParameter(command, "@id", client.Id));

                    result = command.ExecuteNonQuery();

                }

            }

                    return result;

                }

       }

Je fais hériter d’une classe DbBase au lieu de réécrire la méthode dans toutes les classes DAO

public class DbBase

{

           protected DbParameter CreateParameter(DbCommand command, string parameterName, object value)

            {

                    DbParameter parameter = command.CreateParameter();

                    parameter.ParameterName = parameterName;

                    parameter.Value = value;

                    return parameter;

             }          

}          

Le singleton

public class Sql

       {

             private static Sql _instance;

             private DbConnection connection;

             private static object locker = new object();

             public ConnectionStringSettings ConnectionStringSettings { get; set; }

             private Sql() { }

             public static Sql Instance

             {

                    get

                    {

                           lock (locker)

                           {

                                  if (_instance == null)

                                  _instance = new Sql();

                           }

                           return _instance;

                    }

             }

             public DbConnection GetConnection()

             {

                    if (connection == null)

                    {

                           ConnectionStringSettings = ConfigurationManager.ConnectionStrings["dbConnectionString"];

                           DbProviderFactory factory = DbProviderFactories.GetFactory(ConnectionStringSettings.ProviderName);

                           connection = factory.CreateConnection();                    

                    }

            connection.ConnectionString = ConnectionStringSettings.ConnectionString;

                    if (connection.State == ConnectionState.Closed)

                           connection.Open();

                    return connection;

             }                        

       }