DatenbankClient ADO.NET - Providerunabhängig

Beschreibung:

Da immer mal wieder Fragen auftauchen bzgl. einem DataLayer der für die verschiedensten Datenbanksysteme funktioniert poste ich hier mal den Quelltext meines DbClients.

Der zu verwendende Datenbank-Provider wird entweder über den Namen des ConnectionStrings aus der Anwendungskonfiguration oder durch Übergabe an den Konstruktor definiert.

Ein einfaches Verwendungsbeispiel wäre:

C#-Code:

DbClient client = new DbClient("dbconnection"); int result = (Int32)client.ExecuteScalar(CommandType.Text, "SELECT COUNT(*) FROM myTable", null); Console.WriteLine("Item Count {0}", result);

Zur Datenermittlung stehen die Funktionalitäten "ExecuteScalar", "ExecuteNonQuery", "ExecuteDataReader" aus der IDbConnection zur Verfügung und zusätzlich eine Methode "ExecuteDataSet" welche die Ergebnismenge mit Hilfe des DataAdapters als DataSet zurück liefert.

Sind Parameter an die Datenbankabfrage / Prozedur zu übergeben, kann mit Hilfe der Methode "CreateParameter" ein Parameter erzeugt werden.

C#-Code:

IDataParameter parameter = client.CreateParameter("@MyParam", DbType.String, 255, ParameterDirection.Input, "Dies ist ein Test Parameter");

Der Einfachheithalber kann man die Parameter in einer Liste oder direkt in einem Array von "IDataParameter"-Objekten sammeln und anschließend übergeben.

Hinweis: Die Klasse hat derzeit keine Transaktionsunterstützung. Sollte die benötigt werden, können die ja relativ einfach hinzugefügt werden. Nur die Open- und Close Methode sollten dann angepasst werden.

Hier nun die Klasse.

C#-Code:

/// <summary>     /// class for db access     /// </summary>     public class DbClient : IDisposable     {         #region Fields         IDbConnection _connection;         DbProviderFactory _factory;         #endregion         #region Properties         /// <summary>         /// Gets the connection string         /// </summary>         public string ConnectionString         {             get             {                 return this._connection.ConnectionString;             }         }         /// <summary>         /// Gets or sets the command timeout         /// </summary>         public int CommandTimeout { get; set; }         #endregion         #region Construction / Destruction         /// <summary>         ///         /// </summary>         /// <param name="connectionStringName">the name of the connection string defined in application configuration</param>         public DbClient(string connectionStringName)         {             ConnectionStringSettings connectionString = null;             if (string.IsNullOrEmpty(connectionStringName))                 throw new ArgumentException("There must be given a connection string name!");             if (ConfigurationManager.ConnectionStrings[connectionStringName] != null)                 connectionString = ConfigurationManager.ConnectionStrings[connectionStringName];             else                 throw new InvalidOperationException(String.Format("The connection string settings for {0} could not be found!", connectionStringName));             this.InitializeConnection(connectionString.ProviderName, connectionString.ConnectionString);         }         /// <summary>         ///         /// </summary>         /// <param name="dbProvider">the database provider name</param>         /// <param name="connectionString">the connection string</param>         public DbClient(string dbProvider, string connectionString)         {             this.InitializeConnection(dbProvider, connectionString);         }         #endregion         #region Methods         /// <summary>         /// Method to initialize the db connection         /// </summary>         /// <param name="providerName">the db provider name</param>         /// <param name="connectionString">the connection string</param>         /// <param name="connectTimeout">the connect timeout</param>         private void InitializeConnection(string providerName, string connectionString)         {             this._factory = DbProviderFactories.GetFactory(providerName);             if (this._factory == null)                 throw new InvalidOperationException(String.Format("The factory for data provider {0} could not be found!", providerName));             this._connection = this._factory.CreateConnection();             this._connection.ConnectionString = connectionString;         }         /// <summary>         /// Method to open the connection         /// </summary>         private void OpenConnection()         {             if (this._connection != null && this._connection.State != ConnectionState.Open)             {                 this._connection.Open();             }         }         /// <summary>         /// Method to close the connection         /// </summary>         private void CloseConnection()         {             if (this._connection != null && this._connection.State != ConnectionState.Closed)             {                 this._connection.Close();             }         }         /// <summary>         /// Method to dispose the connection         /// </summary>         public void Dispose()         {             if (this._connection != null)             {                 this.CloseConnection();                 this._connection.Dispose();             }         }         /// <summary>         /// Method to call a stored procedure and retrieve the result         /// </summary>         /// <param name="procedureName">name of the stored procedure</param>         /// <param name="parameters">parameters for calling the stored procedure</param>         /// <returns>the dataset with the execution results</returns>         public DataSet ExecuteDataSet(string procedureName, IDataParameter[] parameters)         {             return this.ExecuteDataSet(CommandType.StoredProcedure, procedureName, parameters);         }         /// <summary>         /// Method to call a stored procedure and retrieve the result         /// </summary>         /// <param name="commandType">the command type</param>         /// <param name="procedureName">the command to execute</param>         /// <param name="parameters">parameters for calling the stored procedure</param>         /// <returns>the dataset with the execution results</returns>         public DataSet ExecuteDataSet(CommandType commandType, string commandText, IDataParameter[] parameters)         {             return this.ExecuteDataSet(commandType, commandText, parameters, 60000);         }         /// <summary>         /// Method to call a stored procedure and retrieve the result         /// </summary>         /// <param name="commandType">the command type</param>         /// <param name="procedureName">the command to execute</param>         /// <param name="parameters">parameters for calling the stored procedure</param>         /// <returns>the dataset with the execution results</returns>         public DataSet ExecuteDataSet(CommandType commandType, string commandText, IDataParameter[] parameters, int commandTimeout)         {             DataSet dsResult = new DataSet();             // open the connection             this.OpenConnection();             DbCommand command = (DbCommand)this.CreateCommand(commandType, commandText, parameters, commandTimeout);             DbDataAdapter adapter = this._factory.CreateDataAdapter();             adapter.SelectCommand = command;             adapter.Fill(dsResult);             // close the connection             this.CloseConnection();             return dsResult;         }         /// <summary>         /// Method to execute a datareader         /// </summary>         /// <param name="procedureName">the procedurename</param>         /// <param name="parameters">the parameters</param>         /// <returns>the data reader</returns>         public IDataReader ExecuteDataReader(string procedureName, IDataParameter[] parameters)         {             return this.ExecuteDataReader(CommandType.StoredProcedure, procedureName, parameters);         }         /// <summary>         /// Method to execute a datareader         /// </summary>         /// <param name="commandType">the command type</param>         /// <param name="procedureName">the procedurename</param>         /// <param name="parameters">the parameters</param>         /// <returns>the data reader</returns>         public IDataReader ExecuteDataReader(CommandType commandType, string commandText, IDataParameter[] parameters)         {             return this.ExecuteDataReader(commandType, commandText, parameters, 60000);         }         /// <summary>         /// Method to execute a datareader         /// </summary>         /// <param name="commandType">the command type</param>         /// <param name="procedureName">the procedurename</param>         /// <param name="parameters">the parameters</param>         /// <param name="commandTimeout">the command timeout</param>         /// <returns>the data reader</returns>         public IDataReader ExecuteDataReader(CommandType commandType, string commandText, IDataParameter[] parameters, int commandTimeout)         {             this.OpenConnection();             IDbCommand command = this.CreateCommand(commandType, commandText, parameters, commandTimeout);             this.CloseConnection();             return command.ExecuteReader();         }         public object ExecuteScalar(string procedureName, IDataParameter[] parameters)         {             return this.ExecuteScalar(CommandType.StoredProcedure, procedureName, parameters);         }         public object ExecuteScalar(CommandType commandType, string commandText, IDataParameter[] parameters)         {             return this.ExecuteScalar(commandType, commandText, parameters, 60000);         }         public object ExecuteScalar(CommandType commandType, string commandText, IDataParameter[] parameters, int commandTimeout)         {             this.OpenConnection();             IDbCommand command = this.CreateCommand(commandType, commandText, parameters, commandTimeout);             Object result = command.ExecuteScalar();             this.CloseConnection();             return result;         }         public int ExecuteNonQuery(string procedureName, IDataParameter[] parameters)         {             return this.ExecuteNonQuery(CommandType.StoredProcedure, procedureName, parameters);         }         public int ExecuteNonQuery(CommandType commandType, string commandText, IDataParameter[] parameters)         {             return this.ExecuteNonQuery(commandType, commandText, parameters, 60000);         }         public int ExecuteNonQuery(CommandType commandType, string commandText, IDataParameter[] parameters, int commandTimeout)         {             int result = 0;             this.OpenConnection();             IDbCommand command = this.CreateCommand(commandType, commandText, parameters, commandTimeout);             result = command.ExecuteNonQuery();             this.CloseConnection();             return result;         }         /// <summary>         /// Method to create a command         /// </summary>         /// <param name="commandType">the command type</param>         /// <param name="commandText">the command text</param>         /// <param name="parameters">the parameters</param>         /// <param name="commandTimeout">the command timeout</param>         /// <returns>the command</returns>         private IDbCommand CreateCommand(CommandType commandType, string commandText, IDataParameter[] parameters, int commandTimeout)         {             IDbCommand command = this._connection.CreateCommand();             command.CommandTimeout = commandTimeout;             command.CommandText = commandText;             command.CommandType = commandType;             if(parameters != null)                 foreach (IDataParameter parameter in parameters)                     command.Parameters.Add(parameter);             return command;         }         /// <summary>         /// Method to create a parameter         /// </summary>         /// <param name="parameterName">the parameter name</param>         /// <param name="dbType">the database type</param>         /// <param name="size">size of the parameter</param>         /// <param name="value">the value of the parameter</param>         /// <returns>the new parameter</returns>         public IDataParameter CreateParameter(string parameterName, DbType dbType, int size, object value)         {             return this.CreateParameter(parameterName, dbType, size, ParameterDirection.Input, value);         }         /// <summary>         /// Method to create a parameter         /// </summary>         /// <param name="parameterName">the parameter name</param>         /// <param name="dbType">the database type</param>         /// <param name="size">size of the parameter</param>         /// <param name="parameterDirection">the parameter direction</param>         /// <returns></returns>         public IDataParameter CreateParameter(string parameterName, DbType dbType, int size, ParameterDirection parameterDirection)         {             return this.CreateParameter(parameterName, dbType, size, parameterDirection, null);         }         /// <summary>         /// Method to create a parameter         /// </summary>         /// <param name="parameterName">the parameter name</param>         /// <param name="dbType">the database type</param>         /// <param name="parameterDirection">the parameter direction</param>         /// <param name="size">size of the parameter</param>         /// <param name="value">the value of the parameter</param>         /// <returns>the new parameter</returns>         public IDataParameter CreateParameter(string parameterName, DbType dbType, int size, ParameterDirection parameterDirection, object value)         {             DbParameter parameter = this._factory.CreateParameter();             parameter.ParameterName = parameterName;             parameter.Direction = parameterDirection;             parameter.DbType = dbType;             parameter.Value = value;             parameter.Size = size;             return parameter;         }         #endregion     }

Hinweis: Die Klasse an sich ist nicht Threadsicher. In meinen Privaten projekten löse ich das so, dass ich den Client noch einmal mit einer übergeordneten Klasse kapsle die dann nur die notwendigen Methoden bereitstellt.

Ein Beispiel für die Kapselnde Klasse ist:

C#-Code:

public class Database {       #region Fields       private Int32 _commandTimeout = 10;       private String _connectionStringName;       #endregion       #region Construction / Destruction       public Database(string connectionStringName, Int32 commandTimeout)       {             this._connectionStringName = connectionStringName;             this._commandTimeout = commandTimeout;       }       public DataSet GetData(string procedureName, List<IDataParameter> parameters)       {             using(DbClient dbClient = new DbClient(connectionStringName))             {                 dbClient.CommandTimeout = commandTimeout;                 return dbClient.ExecuteDataSet(procedureName, parameter.ToArray());             }       }       #endregion }

Damit bin ich auf der sicheren Seite und es wird immer eine neue Instanz der DbClient-Klasse erstellt. In Anwendungsszenarien, wo Crossthreaded-Zugriffe ausgeschlossen sind, lasse ich diese zusätzliche Klasse jedoch weg.

Schlagwörter: Datenbankabfrage, unterschiedliche Datenbanken unterstützen , Datenbankzugriffe