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.
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.