Créer une application indépendante à la base de données

Commenter

Contenu du snippet

Voilà une classe avec une méthode qui vous permet de créer les objets nécessaire pour exploiter une base de données quelconque.

C'est une classe que j'ai utilisé pour un projet. elle est simple à utiliser et à adapter.
si vous ne comprenez pas quelque chose, n'hésitez pas à me demander.

Source / Exemple :

using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.Common;
using System.Data;




namespace CrmDbClient
{
    public class CrmDbClientException:Exception 
    {
        public CrmDbClientException(string Message):base(Message){}
        public CrmDbClientException(string Message, Exception inerException) : base(Message, inerException) { }
    }
    
    public class CrmDbClient : IDisposable 
    {
       
        private IDbConnection  _DbConnection;
        private IDbCommand _DbCommand;
        private ConnectionStringSettings _DbConnectionString;
        private DbProviderFactory _DbFactory = null;
        private IDbTransaction _DbTransaction = null;
        private IDbDataAdapter _DbDataAdapter = null;
        
        
        /// <summary>
        /// Creates a new instance of CRMDbClient
        /// </summary>
        /// <param name="CurrentConnectionString"></param>
        public CrmDbClient(ConnectionStringSettings CurrentConnectionString)
        {
            _DbConnectionString = CurrentConnectionString;


                                 
                            _DbFactory = DbProviderFactories.GetFactory(CurrentConnectionString.ProviderName);
                            _DbConnection = _DbFactory.CreateConnection();
                            _DbConnection.ConnectionString = CurrentConnectionString.ConnectionString;
                            _DbCommand = _DbConnection.CreateCommand();
                        
                            _DbDataAdapter = _DbFactory.CreateDataAdapter();
                            _DbDataAdapter.SelectCommand = _DbCommand;
                           
                        
                       

             

           OpenConnection();

        }
        /// <summary>
        /// Destroy the current instance
        /// </summary>
        public void Dispose()
        {
            CloseConnection();
            
            _DbCommand.Dispose();
            _DbConnection.Dispose();
            _DbConnectionString = null;
            _DbDataAdapter = null;
            _DbFactory = null;
            _DbTransaction = null;
            
        }

        ~CrmDbClient()
        {
            GC.Collect();
        }
        
        #region Properties
        /// <summary>
        /// Gets or sets the command timeout in seconds
        /// </summary>
        public int CommandTimeout
        {
            get { return _DbCommand.CommandTimeout; }
            set { _DbCommand.CommandTimeout = value; }
        }
        /// <summary>
        /// Gets the database provider
        /// </summary>
        public string DataBaseProvider
        {
            get{return _DbConnectionString.ProviderName;}
        }
        /// <summary>
        /// Gets or set the 
        /// </summary>
        public int ConnectionTimeout
        {
            get { return _DbConnection.ConnectionTimeout; }
        }

        #endregion
        /// <summary>
        /// Begins transaction
        /// </summary>
        public void BeginTransaction()
        {
            _DbCommand.Transaction = _DbTransaction = _DbConnection.BeginTransaction();
        }
        /// <summary>
        /// Commit transaction
        /// </summary>
        public void CommitTransaction()
        {
            if (_DbTransaction != null)
            {
                _DbTransaction.Commit();
                _DbTransaction.Dispose();
                _DbTransaction = null;
                _DbCommand.Transaction = null;
                
            }
            else
            {
                throw new CrmDbClientException("BeginTransaction must be called before commit or rollback. No open transactions found");
            }
        }
        /// <summary>
        /// Rollbacks transaction
        /// </summary>
        public void RollbackTransaction()
        {
            try
            {
                _DbTransaction.Rollback();
            }
            catch (InvalidOperationException invexcept)
            {
                throw new CrmDbClientException(invexcept.Message, invexcept);
            }
            catch (Exception e)
            {
                throw new CrmDbClientException(e.Message, e);
            }
            finally
            {
                //dispose _dbTransaction
                if (_DbTransaction != null)
                    _DbTransaction.Dispose();

                _DbTransaction = null;
            }
        }
        /// <summary>
        /// Creates a generic parameter
        /// </summary>
        /// <param name="ParameterName">Represents the prameter's name</param>
        /// <param name="ParameterValue">Represents the parameter's value</param>
        /// <returns>IDbDataParameter</returns>
        private IDbDataParameter CreateParameter(string ParameterName,object ParameterValue)
        {
                IDbDataParameter p = _DbCommand.CreateParameter(); //create the command using the DBCommand
                p.Value = ParameterValue;
                p.ParameterName = ParameterName ;
                return p;       
        }
        /// <summary>
        /// Open the connection with de server
        /// </summary>
        private void OpenConnection()
        {
            if (_DbConnection.State != ConnectionState.Open)
                _DbConnection.Open();
        }
        /// <summary>
        /// Closes the connection with the server
        /// </summary>
        private void CloseConnection()
        {
            if (_DbConnection.State != ConnectionState.Closed)
                _DbConnection.Close();
        }
        /// <summary>
        /// Executes a scalar query
        /// </summary>
        /// <param name="sql">Represents the query text</param>
        /// <param name="CmdType">Represents the command type</param>
        /// <returns>Object</returns>
        public object ExecuteScalarQuery(string QueryText,CommandType CmdType)
        {
            
            _DbCommand.CommandText = QueryText; //set que querytext
            _DbCommand.CommandType = CmdType;

            OpenConnection();

            object val = _DbCommand.ExecuteScalar(); //runs que query

            _DbCommand.Parameters.Clear();
            //CloseConnection();

            return val;
        }
        /// <summary>
        /// Executes a parametred scalar query
        /// </summary>
        /// <param name="QueryText">Query text</param>
        /// <param name="Parameters">Parameters list</param>
        /// <param name="CmdType">Command type</param>
        /// <returns></returns>
        public object ExecuteScalarQuery(string QueryText, Dictionary<string, object> Parameters, CommandType CmdType)
        {
           
            _DbCommand.CommandText = QueryText;
            
            _DbCommand.CommandType = CommandType.Text;

            _DbCommand.Parameters.Clear(); //clear parameters
            foreach (KeyValuePair<string, object> k in Parameters)
            {
                _DbCommand.Parameters.Add(CreateParameter(k.Key, k.Value));
            }

            OpenConnection();
            object val = _DbCommand.ExecuteScalar();

            _DbCommand.Parameters.Clear(); //clear parameters
            
            
            return val;
        }
       
        /// <summary>
        /// Executes a query and retourns the result into a datatable.
        /// </summary>
        /// <param name="QueryText">Query text</param>
        /// <param name="CmdType">Command type</param>
        /// <returns>Datatable containing the result</returns>
        public DataTable ExecuteReaderQuery(string QueryText, CommandType CmdType)
        {
            DataSet myDs = new DataSet();
           
            _DbCommand.CommandText = QueryText;
            _DbCommand.CommandType = CmdType;

            OpenConnection();
            
            _DbDataAdapter.Fill(myDs);

            

            return myDs.Tables[0];
        }
        
        /// <summary>
        /// Executes a parametred query and retourns the result into a datatable
        /// </summary>
        /// <param name="QueryText">Query text</param>
        /// <param name="QueryParameters">Command type</param>
        /// <param name="CmdType">Parameters list</param>
        /// <returns></returns>
        public DataTable ExecuteReaderQuery(string QueryText, Dictionary<string, object> QueryParameters, CommandType CmdType)
        {
            _DbDataAdapter.SelectCommand.Parameters.Clear();

            foreach (KeyValuePair<string, object> k in QueryParameters)
            {
                 _DbDataAdapter.SelectCommand.Parameters.Add(CreateParameter(k.Key, k.Value));
            }

            DataTable val = this.ExecuteReaderQuery(QueryText,CmdType);
            _DbCommand.Parameters.Clear(); //clear parameters

            return val;
        }
        /// <summary>
        /// Executes a query and retourns affected rows number
        /// </summary>
        /// <param name="QueryText">Query text</param>
        /// <param name="CmdType">Command Type</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string QueryText, CommandType CmdType)
        {
            _DbCommand.CommandText = QueryText;
            _DbCommand.CommandType = CmdType;
            OpenConnection();
            return _DbCommand.ExecuteNonQuery();
        }
        /// <summary>
        /// Executes a parametred query and retourns affected rows number
        /// </summary>
        /// <param name="QueryText">Query text</param>
        /// <param name="QueryParameters">Query parameters</param>
        /// <param name="CmdType">Command type</param>
        /// <returns>Integer value representing affected rows</returns>
        public int ExecuteNonQuery(string QueryText, Dictionary<string, object> QueryParameters, CommandType CmdType)
        {
            _DbCommand.Parameters.Clear();

            foreach (KeyValuePair<string, object> k in QueryParameters)
            {
                _DbCommand.Parameters.Add(this.CreateParameter(k.Key, k.Value));
            }
            int val = this.ExecuteNonQuery(QueryText, CmdType);

            _DbCommand.Parameters.Clear();

            return val;
        }

    }
}

Conclusion :

Il faut imperativement passer au contructeur un type ConnectionstringSetting (voir la msdn pour détails)

Ce type va derminer la chaine de connexion et le type de base à utiliser et en fonction de cela, le système va créer les composants nécessaires à cela.

Je l'utilise avec du Oracle, Sybase, MsSql Server, CSV, Access, ODBC et ça fonctionne super bien.. de plus ça prend en charge les transactions.

A voir également

S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Du même auteur (bossun)