|
Trouver une ressource
Vous ne trouvez pas de réponse à votre problème ? Alors posez la question dans le forum. Souvenez-vous qu'il n'y a jamais de question bête, mais rester dans l'ignorance parce que l'on n'ose pas poser une question, ça c'est une erreur !
CRÉER UNE APPLICATION INDÉPENDANTE À LA BASE DE DONNÉES
Information sur la source
Description
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
- 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;
- }
-
- }
- }
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.
Historique
- 16 juin 2006 09:12:40 :
- .
- 28 mars 2008 15:49:46 :
- Nouvelle version plus fiable et plus simple à comprendre, à implémenter.
- 28 mars 2008 15:51:12 :
- J'ai fait une nouvelle version plus complétè et très fiable.
Sources de la même categorie
Sources en rapport avec celle ci
Commentaires et avis
|
Téléchargements
Logiciels à télécharger sur le même thème :
|