On a connu plusieurs version du DAAB de l'équipe Architect de Microsoft. Cette librairie est une des plus utilisée dans le développement .net, lorsqu'il s'agit de mettre en place une application à architecture N'Tier cohérente en .net.
Dans cette article je vous propose une version générique ( generic ) prenant en compte les nouvelles spécificités de ADO.NET 2.0.
Les fichiers de configurations de .net 2.0 maintenant lors de la définition d'une chaine de connexion mettent en relation la chaine permettant de se connecter à la base et le provider utilisé.
<connectionStrings>
<add name="ConnectionString" connectionString="Password=sa;
User ID=sa;
Data Source=127.0.0.1;
Initial Catalog=DB_PORTAL_V1D"providerName ="System.Data.SqlClient"/></connectionStrings>On peut mettre en place une classe de base pour notre DAL ( Data Access Logic ) afin de récupérer les informations de connexions.
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Web.Configuration;
namespace Net.Gavarin.Portal.Data
{
public class BaseData
{
public static ConnectionStringSettings Settings
{
get
{
return ConfigurationManager.ConnectionStrings["ConnectionString"];
}
}
}
}
De maniere à pouvoir mettre en place notre DAL, incorporant nos méthodes de type CRUD ( Create, Read, Update, Delete ).
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Net.Gavarin.Data;
namespace Net.Gavarin.Portal.Data
{
public class DBCatalog : BaseData
{
public static DataSet Get()
{
return DbHelper.ExecuteDataset(
Settings,
CommandType.StoredProcedure,
"PORTAL_GetCatalog",
null);
}
public static DataSet Get(int p_ID)
{
SqlParameter[] v_params = new SqlParameter[1];
v_params[0] = new SqlParameter();
v_params[0].ParameterName = "@CTL_ID";
v_params[0].DbType = DbType.Int32;
v_params[0].Value = p_ID;
return DbHelper.ExecuteDataset(
Settings,
CommandType.StoredProcedure,
"PORTAL_GetCatalogByID",
v_params);
}
}
}
Exemple de DAAB Générique ADO.NET 2.0
using System;
using System.Data;
using System.Data.Common;
using System.Collections;
using System.Configuration;
namespace Net.Gavarin.Data
{
[CLSCompliant(true)]
public static class DbHelper
{
#region constructors
#endregion
#region Private utility methods
private static void AttachParameters(
DbCommand p_command,
DbParameter[] p_commandParameters)
{
if( p_command == null )
throw new ArgumentNullException("p_command");
if( p_commandParameters != null )
{
foreach (DbParameter v_p in p_commandParameters)
{
if( v_p != null )
{
if ( ( v_p.Direction == ParameterDirection.InputOutput
v_p.Direction == ParameterDirection.Input ) &&
(v_p.Value == null))
{
v_p.Value = DBNull.Value;
}
p_command.Parameters.Add(v_p);
}
}
}
}
private static void AssignParameterValues(
DbParameter[] p_commandParameters,
DataRow p_dataRow)
{
if ((p_commandParameters == null) (p_dataRow == null))
{
return;
}
int v_i = 0;
foreach(DbParameter v_commandParameter in p_commandParameters)
{
if( v_commandParameter.ParameterName == null
v_commandParameter.ParameterName.Length <= 1 )
throw new ArgumentException(
string.Format(
"Please provide a valid
parameter name on the parameter #{0},
the ParameterName property has
the following value: '{1}'.",
v_i, v_commandParameter.ParameterName ) );
if (p_dataRow.Table.Columns.IndexOf(v_commandParameter.ParameterName.Substring(1)) != -1)
v_commandParameter.Value = p_dataRow[v_commandParameter.ParameterName.Substring(1)];
v_i++;
}
}
private static void AssignParameterValues(
DbParameter[] p_commandParameters,
object[] p_parameterValues)
{
if ((p_commandParameters == null) (p_parameterValues == null))
{
return;
}
if (p_commandParameters.Length != p_parameterValues.Length)
{
throw new ArgumentException("Parameter count
does not match Parameter Value count.");
}
for (int v_i = 0, v_j = p_commandParameters.Length; v_i < v_j; v_i++)
{
if (p_parameterValues[v_i] is IDbDataParameter)
{
IDbDataParameter paramInstance = (IDbDataParameter)p_parameterValues[v_i];
if( paramInstance.Value == null )
{
p_commandParameters[v_i].Value = DBNull.Value;
}
else
{
p_commandParameters[v_i].Value = paramInstance.Value;
}
}
else if (p_parameterValues[v_i] == null)
{
p_commandParameters[v_i].Value = DBNull.Value;
}
else
{
p_commandParameters[v_i].Value = p_parameterValues[v_i];
}
}
}
private static void PrepareCommand(
DbCommand p_command,
DbConnection p_connection,
DbTransaction p_transaction,
CommandType p_commandType,
string p_commandText,
DbParameter[] p_commandParameters,
out bool p_mustCloseConnection )
{
if( p_command == null )
throw new ArgumentNullException("p_command");
if( p_commandText == null p_commandText.Length == 0 )
throw new ArgumentNullException( "p_commandText" );
if (p_connection.State != ConnectionState.Open)
{
p_mustCloseConnection = true;
p_connection.Open();
}
else
{
p_mustCloseConnection = false;
}
p_command.Connection = p_connection;
p_command.CommandText = p_commandText;
if (p_transaction != null)
{
if( p_transaction.Connection == null )
throw new ArgumentException( "The transaction was rollbacked or
commited, please provide an open transaction.", "p_transaction" );
p_command.Transaction = p_transaction;
}
p_command.CommandType = p_commandType;
if (p_commandParameters != null)
{
AttachParameters(p_command, p_commandParameters);
}
return;
}
#endregion private utility methods & constructors
#region Basics methods
public static DbProviderFactory GetFactoryAlias(ConnectionStringSettings p_connectionSettings)
{
if ( string.IsNullOrEmpty(p_connectionSettings.ProviderName) )
{
return DbProviderFactories.GetFactory("System.Data.SqlClient");
}
else
return DbProviderFactories.GetFactory(p_connectionSettings.ProviderName);
}
public static DbConnection CreateConnection(ConnectionStringSettings p_connectionSettings)
{
DbConnection v_conn = GetFactoryAlias(p_connectionSettings).CreateConnection();
v_conn.ConnectionString = p_connectionSettings.ConnectionString;
return v_conn;
}
public static DbParameter CreateParameter(ConnectionStringSettings p_connectionSettings)
{
return GetFactoryAlias(p_connectionSettings).CreateParameter();
}
public static DbParameter CreateParameter(
ConnectionStringSettings p_connectionSettings,
string p_parameterName,
DbType p_dbType)
{
DbParameter v_param = CreateParameter(p_connectionSettings);
v_param.ParameterName = p_parameterName;
v_param.DbType = p_dbType;
return v_param;
}
public static DbParameter CreateParameter(
ConnectionStringSettings p_connectionSettings,
string p_parameterName,
DbType p_dbType,
int p_size)
{
DbParameter v_param = CreateParameter(p_connectionSettings, p_parameterName, p_dbType);
v_param.Size = p_size;
return v_param;
}
public static DbParameter CreateParameter(
ConnectionStringSettings p_connectionSettings,
string p_parameterName,
object p_value)
{
DbParameter v_param = CreateParameter(p_connectionSettings);
v_param.ParameterName = p_parameterName;
v_param.Value = p_value;
return v_param;
}
public static DbCommand CreateCommand(ConnectionStringSettings p_connectionSettings)
{
return GetFactoryAlias(p_connectionSettings).CreateCommand();
}
public static DbCommand CreateCommand(
ConnectionStringSettings p_connectionSettings,
string p_cmdText)
{
DbCommand v_cmd = CreateCommand(p_connectionSettings);
v_cmd.CommandText = p_cmdText;
return v_cmd;
}
public static DbCommand CreateCommand(
ConnectionStringSettings p_connectionSettings,
string p_cmdText,
DbConnection p_connection)
{
DbCommand v_cmd = CreateCommand(p_connectionSettings, p_cmdText);
v_cmd.Connection = p_connection;
return v_cmd;
}
public static DbDataAdapter CreateDataAdapter(ConnectionStringSettings p_connectionSettings)
{
return GetFactoryAlias(p_connectionSettings).CreateDataAdapter();
}
public static DbDataAdapter CreateDataAdapter(
ConnectionStringSettings p_connectionSettings,
DbCommand p_selectCommand)
{
DbDataAdapter v_ada = CreateDataAdapter(p_connectionSettings);
v_ada.SelectCommand = p_selectCommand;
return v_ada;
}
#endregion
#region ExecuteNonQuery
public static int ExecuteNonQuery(
ConnectionStringSettings p_connectionSettings,
CommandType p_commandType,
string p_commandText,
params DbParameter[] p_commandParameters)
{
if ( p_connectionSettings == null )
throw new ArgumentNullException("p_connectionSettings");
using ( DbConnection v_connection = CreateConnection(p_connectionSettings) )
{
v_connection.Open();
return ExecuteNonQuery(
p_connectionSettings,
v_connection,
p_commandType,
p_commandText,
p_commandParameters);
}
}
public static int ExecuteNonQuery(
ConnectionStringSettings p_connectionSettings,
DbConnection p_connection,
CommandType p_commandType,
string p_commandText,
params DbParameter[] p_commandParameters)
{
if ( p_connection == null )
throw new ArgumentNullException("p_connection");
DbCommand v_cmd = CreateCommand(p_connectionSettings);
bool v_mustCloseConnection = false;
PrepareCommand(
v_cmd,
p_connection,
(DbTransaction)null,
p_commandType,
p_commandText,
p_commandParameters,
out v_mustCloseConnection);
int v_retval = v_cmd.ExecuteNonQuery();
v_cmd.Parameters.Clear();
if ( v_mustCloseConnection )
p_connection.Close();
return v_retval;
}
public static int ExecuteNonQuery(
ConnectionStringSettings p_connectionSettings,
DbTransaction p_transaction,
CommandType p_commandType,
string p_commandText,
params DbParameter[] p_commandParameters)
{
if( p_transaction == null )
throw new ArgumentNullException( "p_transaction" );
if( p_transaction != null && p_transaction.Connection == null )
throw new ArgumentException( "The transaction was rollbacked or
commited, please provide an open transaction.", "p_transaction" );
DbCommand v_cmd = CreateCommand(p_connectionSettings);
bool v_mustCloseConnection = false;
PrepareCommand(
v_cmd,
p_transaction.Connection,
p_transaction,
p_commandType,
p_commandText,
p_commandParameters,
out v_mustCloseConnection );
int v_retval = v_cmd.ExecuteNonQuery();
v_cmd.Parameters.Clear();
return v_retval;
}
#endregion ExecuteNonQuery
#region ExecuteDataset
public static DataSet ExecuteDataset(
ConnectionStringSettings p_connectionSettings,
CommandType p_commandType,
string p_commandText,
params DbParameter[] p_commandParameters)
{
if ( p_connectionSettings == null)
throw new ArgumentNullException("p_connectionAlias");
using ( DbConnection v_connection = CreateConnection(p_connectionSettings) )
{
v_connection.Open();
return ExecuteDataset(
p_connectionSettings,
v_connection,
p_commandType,
p_commandText,
p_commandParameters);
}
}
public static DataSet ExecuteDataset(
ConnectionStringSettings p_connectionSettings,
DbConnection p_connection,
CommandType p_commandType,
string p_commandText,
params DbParameter[] p_commandParameters)
{
if( p_connection == null )
throw new ArgumentNullException( "p_connection" );
DbCommand v_cmd = CreateCommand(p_connectionSettings);
bool v_mustCloseConnection = false;
PrepareCommand(
v_cmd,
p_connection,
(DbTransaction)null,
p_commandType,
p_commandText,
p_commandParameters,
out v_mustCloseConnection );
using ( DbDataAdapter v_da = CreateDataAdapter(p_connectionSettings, v_cmd) )
{
DataSet v_ds = new DataSet();
v_da.Fill(v_ds);
v_cmd.Parameters.Clear();
if( v_mustCloseConnection )
p_connection.Close();
return v_ds;
}
}
public static DataSet ExecuteDataset(
ConnectionStringSettings p_connectionSettings,
DbTransaction p_transaction,
CommandType p_commandType,
string p_commandText,
params DbParameter[] p_commandParameters)
{
if( p_transaction == null )
throw new ArgumentNullException( "p_transaction" );
if( p_transaction != null && p_transaction.Connection == null )
throw new ArgumentException( "The transaction was rollbacked or
commited,please provide an open transaction.", "p_transaction" );
DbCommand v_cmd = CreateCommand(p_connectionSettings);
bool v_mustCloseConnection = false;
PrepareCommand(
v_cmd,
p_transaction.Connection,
p_transaction,
p_commandType,
p_commandText,
p_commandParameters,
out v_mustCloseConnection );
using ( DbDataAdapter v_da = CreateDataAdapter(p_connectionSettings, v_cmd) )
{
DataSet v_ds = new DataSet();
v_da.Fill(v_ds);
v_cmd.Parameters.Clear();
return v_ds;
}
}
#endregion ExecuteDataset
#region ExecuteReader
private enum DbConnectionOwnership
{
/// <summary>Connection is owned and managed by DbHelper</summary>
Internal,
/// <summary>Connection is owned and managed by the caller</summary>
External
}
private static DbDataReader ExecuteReader(
ConnectionStringSettings p_connectionSettings,
DbConnection p_connection,
DbTransaction p_transaction,
CommandType p_commandType,
string p_commandText,
DbParameter[] p_commandParameters,
DbConnectionOwnership p_connectionOwnership)
{
if( p_connection == null )
throw new ArgumentNullException( "p_connection" );
bool v_mustCloseConnection = false;
DbCommand v_cmd = CreateCommand(p_connectionSettings);
try
{
PrepareCommand(
v_cmd,
p_connection,
p_transaction,
p_commandType,
p_commandText,
p_commandParameters,
out v_mustCloseConnection );
DbDataReader v_dataReader;
if (p_connectionOwnership == DbConnectionOwnership.External)
{
v_dataReader = v_cmd.ExecuteReader();
}
else
{
v_dataReader = v_cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// Detach the DbParameters from the command object, so they can be used again.
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SqlReader can´t set its values.
// When this happen, the parameters can´t be used again in other command.
bool v_canClear = true;
foreach(DbParameter commandParameter in v_cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
v_canClear = false;
}
if (v_canClear)
{
v_cmd.Parameters.Clear();
}
return v_dataReader;
}
catch
{
if( v_mustCloseConnection )
p_connection.Close();
throw;
}
}
public static DbDataReader ExecuteReader(
ConnectionStringSettings p_connectionSettings,
CommandType p_commandType,
string p_commandText,
params DbParameter[] p_commandParameters)
{
if ( p_connectionSettings == null)
throw new ArgumentNullException("p_connectionSettings");
DbConnection v_connection = null;
try
{
v_connection = CreateConnection(p_connectionSettings);
v_connection.Open();
return ExecuteReader(
p_connectionSettings,
v_connection,
null,
p_commandType,
p_commandText,
p_commandParameters,
DbConnectionOwnership.Internal);
}
catch
{
if( v_connection != null )
v_connection.Close();
throw;
}
}
public static DbDataReader ExecuteReader(
ConnectionStringSettings p_connectionSettings,
DbConnection p_connection,
CommandType p_commandType,
string p_commandText,
params DbParameter[] p_commandParameters)
{
return ExecuteReader(
p_connectionSettings,
p_connection,
(DbTransaction)null,
p_commandType,
p_commandText,
p_commandParameters,
DbConnectionOwnership.External);
}
public static DbDataReader ExecuteReader(
ConnectionStringSettings p_connectionSettings,
DbTransaction p_transaction,
CommandType p_commandType,
string p_commandText,
params DbParameter[] p_commandParameters)
{
if( p_transaction == null )
throw new ArgumentNullException( "p_transaction" );
if( p_transaction != null && p_transaction.Connection == null )
throw new ArgumentException( "The transaction was rollbacked or
commited, please provide an open transaction.", "p_transaction" );
return ExecuteReader(
p_connectionSettings,
p_transaction.Connection,
p_transaction,
p_commandType,
p_commandText,
p_commandParameters,
DbConnectionOwnership.External);
}
#endregion ExecuteReader
#region ExecuteScalar
public static object ExecuteScalar(
ConnectionStringSettings p_connectionSettings,
CommandType p_commandType,
string p_commandText,
params DbParameter[] p_commandParameters)
{
if ( p_connectionSettings == null)
throw new ArgumentNullException("p_connectionSettings");
using ( DbConnection v_connection = CreateConnection(p_connectionSettings) )
{
v_connection.Open();
return ExecuteScalar(
p_connectionSettings,
v_connection,
p_commandType,
p_commandText,
p_commandParameters);
}
}
public static object ExecuteScalar(
ConnectionStringSettings p_connectionSettings,
DbConnection p_connection,
CommandType p_commandType,
string p_commandText,
params DbParameter[] p_commandParameters)
{
if( p_connection == null )
throw new ArgumentNullException( "p_connection" );
DbCommand v_cmd = CreateCommand(p_connectionSettings);
bool v_mustCloseConnection = false;
PrepareCommand(
v_cmd,
p_connection,
(DbTransaction)null,
p_commandType,
p_commandText,
p_commandParameters,
out v_mustCloseConnection );
object v_retval = v_cmd.ExecuteScalar();
v_cmd.Parameters.Clear();
if( v_mustCloseConnection )
p_connection.Close();
return v_retval;
}
public static object ExecuteScalar(
ConnectionStringSettings p_connectionSettings,
DbTransaction p_transaction,
CommandType p_commandType,
string p_commandText,
params DbParameter[] p_commandParameters)
{
if( p_transaction == null )
throw new ArgumentNullException( "p_transaction" );
if( p_transaction != null && p_transaction.Connection == null )
throw new ArgumentException( "The transaction was rollbacked or
commited, please provide an open transaction.", "p_transaction" );
DbCommand v_cmd = CreateCommand(p_connectionSettings);
bool v_mustCloseConnection = false;
PrepareCommand(
v_cmd,
p_transaction.Connection,
p_transaction,
p_commandType,
p_commandText,
p_commandParameters,
out v_mustCloseConnection );
object retval = v_cmd.ExecuteScalar();
v_cmd.Parameters.Clear();
return retval;
}
#endregion ExecuteScalar
#region FillDataset
public static void FillDataset(
ConnectionStringSettings p_connectionSettings,
CommandType p_commandType,
string p_commandText,
DataSet p_dataSet,
DataTableMapping[] p_tableMappings,
params DbParameter[] p_commandParameters)
{
if ( p_connectionSettings == null )
throw new ArgumentNullException("p_connectionSettings");
if( p_dataSet == null )
throw new ArgumentNullException( "p_dataSet" );
using ( DbConnection v_connection = CreateConnection(p_connectionSettings) )
{
v_connection.Open();
FillDataset(
p_connectionSettings,
v_connection,
p_commandType,
p_commandText,
p_dataSet,
p_tableMappings,
p_commandParameters);
}
}
public static void FillDataset(
ConnectionStringSettings p_connectionSettings,
DbConnection p_connection,
CommandType p_commandType,
string p_commandText,
DataSet p_dataSet,
DataTableMapping[] p_tableMappings,
params DbParameter[] p_commandParameters)
{
FillDataset(
p_connectionSettings,
p_connection,
null,
p_commandType,
p_commandText,
p_dataSet,
p_tableMappings,
p_commandParameters);
}
public static void FillDataset(
ConnectionStringSettings p_connectionSettings,
DbTransaction p_transaction,
CommandType p_commandType,
string p_commandText,
DataSet p_dataSet,
DataTableMapping[] p_tableMappings,
params DbParameter[] p_commandParameters)
{
FillDataset(
p_connectionSettings,
p_transaction.Connection,
p_transaction,
p_commandType,
p_commandText,
p_dataSet,
p_tableMappings,
p_commandParameters);
}
private static void FillDataset(
ConnectionStringSettings p_connectionSettings,
DbConnection p_connection,
DbTransaction p_transaction,
CommandType p_commandType,
string p_commandText,
DataSet p_dataSet,
DataTableMapping[] p_tableMappings,
params DbParameter[] p_commandParameters)
{
if( p_connection == null )
throw new ArgumentNullException( "p_connection" );
if( p_dataSet == null )
throw new ArgumentNullException( "p_dataSet" );
DbCommand v_command = CreateCommand(p_connectionSettings);
bool v_mustCloseConnection = false;
PrepareCommand(
v_command,
p_connection,
p_transaction,
p_commandType,
p_commandText,
p_commandParameters,
out v_mustCloseConnection );
using ( DbDataAdapter v_dataAdapter = CreateDataAdapter(p_connectionSettings, v_command) )
{
if (p_tableMappings != null && p_tableMappings.Length > 0)
v_dataAdapter.TableMappings.AddRange(p_tableMappings);
v_dataAdapter.Fill(p_dataSet);
v_command.Parameters.Clear();
}
if( v_mustCloseConnection )
p_connection.Close();
}
#endregion
#region UpdateDataset
public static void UpdateDataset(
ConnectionStringSettings p_connectionSettings,
DbCommand p_insertCommand,
DbCommand p_deleteCommand,
DbCommand p_updateCommand,
DataSet p_dataSet,
string p_tableName)
{
if( p_insertCommand == null )
throw new ArgumentNullException( "insertCommand" );
if( p_deleteCommand == null )
throw new ArgumentNullException( "deleteCommand" );
if( p_updateCommand == null )
throw new ArgumentNullException( "updateCommand" );
if(string.IsNullOrEmpty(p_tableName) )
throw new ArgumentNullException( "tableName" );
using ( DbDataAdapter v_dataAdapter = CreateDataAdapter(p_connectionSettings) )
{
v_dataAdapter.UpdateCommand = p_updateCommand;
v_dataAdapter.InsertCommand = p_insertCommand;
v_dataAdapter.DeleteCommand = p_deleteCommand;
v_dataAdapter.Update (p_dataSet, p_tableName);
p_dataSet.AcceptChanges();
}
}
#endregion
}
}
