SQLMANAGER : FACILITE L'ACCÈS À UNE BASE DE DONNÉE MS SQL

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using System.Runtime.Remoting.Contexts;

namespace zAlbumManager
{
public class SqlManager : IDisposable
{
    //SqlManager est une classe Singleton
    private static SqlManager sqlmanager = new SqlManager();
    private string connectionString = AppSettings.ConnectionString;
    private SqlConnection sqlConnection;
    private bool disposed = false;
    //contient la dernière erreur survenue
    private string lastError;
    public string LastError { get { return lastError; } }

    #region Constructeur
    private SqlManager()
    {
        sqlConnection = new SqlConnection(connectionString);
        OpenConnection();
    }
    public static SqlManager GetInstance()
    {
        return sqlmanager;
    }
    #endregion

    #region ExecuteQuery
    public SqlQueryResult ExecuteQuery(string query, SqlParameter[] parameters)
    {
        lock (this)
        {
            SqlQueryResult sqr = new SqlQueryResult();
            SqlCommand sqlCommand = new SqlCommand(query, sqlConnection);
            if (parameters != null) sqlCommand.Parameters.AddRange(parameters);
            SqlDataReader sqlDataReader = null;
            try
            {
                sqlDataReader = sqlCommand.ExecuteReader();
            }
            catch (Exception e)
            {
                lastError = sqr.ErrorMessage =  e.Message;
                sqr.IsSuccess = false;
                sqr.Error = Error.DataBaseAccessError;
            }
            if (sqr.IsSuccess)
            {
                sqr.Table = new DataReaderAdapter().FillFromReader(sqlDataReader);
                sqlDataReader.Close();
            }
            return sqr;
        }
    }
    public SqlQueryResult ExecuteQuery(string query)
    {
        return this.ExecuteQuery(query, null);
    }
    #endregion
    #region ExecuteNonQuery
    public SqlNonQueryResult ExecuteNonQuery(string query, SqlParameter[] parameters)
    {
        lock (this)
        {
            SqlNonQueryResult snqr = new SqlNonQueryResult();
            SqlCommand sqlCommand = new SqlCommand(query, sqlConnection);
            if (parameters != null) sqlCommand.Parameters.AddRange(parameters);
            try
            {
                //sqlConnection.Open();
                snqr.NumberOfRowsAffected = sqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                lastError = snqr.ErrorMessage = e.Message;
                snqr.IsSuccess = false;
                snqr.Error = Error.DataBaseAccessError;
            }
            return snqr;
        }
    }
    public SqlNonQueryResult ExecuteNonQuery(string query)
    {
        return this.ExecuteNonQuery(query, null);
    }
    #endregion


    #region OpenConnection
    public void OpenConnection()
    {
        try
        {
            sqlConnection.Open();
        }
        catch (Exception e)
        {
            lastError = e.Message;
        }
    }
    #endregion
    #region CloseConnection
    public void CloseConnection()
    {
        if (sqlConnection.State != ConnectionState.Closed) sqlConnection.Close();
    }
    #endregion

    #region IDisposable Membres
    public void Dispose()
    {
        if (!this.disposed)
        {
            CloseConnection();
            sqlConnection.Dispose();
            disposed = true;
        }
        GC.SuppressFinalize(this);
        
    }
    #endregion
}
#region QueryResult
public class QueryResult
{
    private bool isSuccess = true;
    private string errorMessage;
    private Error error;
    public Error Error { get { return error; } set { error = value; } }
    public bool IsSuccess { get { return isSuccess; } set { isSuccess = value; } }
    public string ErrorMessage { get { return errorMessage; } set { errorMessage = value; } }

    public override string ToString()
    {
        return " - IsSuccess : " + isSuccess + "\n" + " - Error : " + error + "\n" + " - ErrorMessage : " + errorMessage;
    }
}
#endregion 
#region SqlQueryResult
    public class SqlQueryResult : QueryResult
    {
        private DataTable table;
        public DataTable Table { get { return table; } set { table = value; } }
    }
    #endregion
#region SqlNonQueryResult
public class SqlNonQueryResult : QueryResult
{
    private int numberOfRowsAffected;
    public int NumberOfRowsAffected { get { return numberOfRowsAffected; } set { numberOfRowsAffected = value; } }
}
#endregion

#region DataReaderAdapter
class DataReaderAdapter : DbDataAdapter
{
    public DataTable FillFromReader(IDataReader dataReader)
    {
        DataTable dt = new DataTable();
        this.Fill(dt, dataReader);
        return dt;
    }
}
#endregion
}