CRÉER UNE APPLICATION INDÉPENDANTE À LA BASE DE DONNÉES

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;
        }

    }
}