The Provider Design Pattern and Data Access Component - II

SQL implementation of DataProviderBase: SqlDataProviderBase

The SQLDataProviderBase class inherits from DataProviderBase class. It greatly simplifies program code required by a Microsoft SQL Server opeation.

Show/hide code: public abstract class SqlDataProviderBase : DataProviderBase

• /// <summary>
• /// Provides a base implementation for the data providers that using SQL SERVER databases as the data store.
• /// </summary>
• public abstract class SqlDataProviderBase : DataProviderBase
• {
• /// <summary>
• /// Initializes an instance of <see cref="SqlDataProviderBase"/> class.
• /// The instance cannot be used for starting a transaction.
• /// </summary>
• protected SqlDataProviderBase()
• {
• _allowTrans = false;
• }
•  
• /// <summary>
• /// Initializes an instance of <see cref="SqlDataProviderBase"/> class
• /// to continue the active transaction.
• /// </summary>
• protected SqlDataProviderBase(SqlDataProviderBase instance)
• : base(instance)
• {
• _allowTrans = true;
• _underTrans = true;
•  
• _connectionString = instance._connectionString;
• _sqlConnection = instance._sqlConnection;
• _sqlTransaction = instance._sqlTransaction;
• }
•  
• /// <summary>
• /// Initializes an instance of <see cref="SqlDataProviderBase"/> class.
• /// </summary>
• /// <param name="allowPerformTransaction">
• /// Indicates whether the new instance allows to perform a transaction.
• /// </param>
• /// <param name="connectionString">
• /// The connection string used for connecting the specified database server.
• /// </param>
• protected SqlDataProviderBase(bool allowPerformTransaction, string connectionString)
• {
• _allowTrans = allowPerformTransaction;
• _connectionString = connectionString;
• }
• /// <summary>
• /// Begins to perform transaction.
• /// </summary>
• public override void BeginTransaction()
• {
• if( !_allowTrans ) {
• throw new InvalidOperationException(
• "You are not allowed to begin a transaction.");
• }
•  
• if( _underTrans ) {
• throw new InvalidOperationException(
• "A transaction has already been started.");
• }
•  
• _sqlConnection = new SqlConnection(_connectionString);
• _sqlConnection.Open();
•  
• _sqlTransaction = _sqlConnection.BeginTransaction();
• _underTrans = true;
• }
•  
• /// <summary>
• /// Commits the database transaction.
• /// </summary>
• public override void Commit()
• {
• if( !_underTrans ) {
• throw new InvalidOperationException("No transaction has already been started.");
• }
• _sqlTransaction.Commit();
• _sqlTransaction.Dispose();
• _sqlTransaction = null;
•  
• Dispose();
• }
•  
• /// <summary>
• /// Rolls back the database transaction.
• /// </summary>
• public override void Rollback()
• {
• if( !_underTrans ) {
• throw new InvalidOperationException("No transaction has already been started.");
• }
• _sqlTransaction.Rollback();
• _sqlTransaction.Dispose();
• _sqlTransaction = null;
•  
• Dispose();
• }
•  
• /// <summary>
• /// Indicates whether a transaction has been started.
• /// </summary>
• public override bool UnderTransaction
• {
• get { return _underTrans; }
• }
•  
• /// <summary>
• /// Closes the database connection.
• /// </summary>
• protected override void Dispose(bool disposing)
• {
• if( disposing ) {
• lock( this ) {
• if( _sqlConnection != null ) {
• _sqlConnection.Dispose();
• _sqlConnection = null;
• }
• }
• }
• }
•  
• /// <summary>
• /// Excecutes a stored procedure, and returns the first column of the
• /// first row in the result set.
• /// </summary>
• /// <param name="procName">
• /// The name of the stored procedure to execute.
• /// </param>
• /// <param name="parameters">
• /// The parameters required by the stored procedure.
• /// </param>
• protected object RunScalar(string procName, SqlParameter[] parameters)
• {
• SqlConnectionHolder connectionHolder;
• SqlCommand sqlCommand;
•  
• using( connectionHolder = GetConnectionHolder() ) {
• using( sqlCommand = BuildSQLCommand(connectionHolder, procName, parameters) ) {
• return sqlCommand.ExecuteScalar();
• }
• }
• }
•  
• /// <summary>
• /// Excecutes a stored procedure, and returns the first column of the
• /// first row in the result set, as well as the return value from it.
• /// </summary>
• /// <param name="procName">
• /// The name of the stored procedure to execute.
• /// </param>
• /// <param name="parameters">
• /// The parameters required by the stored procedure.
• /// </param>
• /// <param name="returnValue">
• /// The return value from the specified stored procedure.
• /// </param>
• protected object RunScalar(string procName, SqlParameter[] parameters, out int returnValue)
• {
• SqlConnectionHolder connectionHolder;
• SqlCommand sqlCommand;
• SqlParameter param;
• object firstObject;
•  
• using( connectionHolder = GetConnectionHolder() ) {
• using( sqlCommand = BuildSQLCommand(connectionHolder, procName, parameters, true) ) {
• firstObject = sqlCommand.ExecuteScalar();
•  
• //Gets the return value
• param = sqlCommand.Parameters[s_returnValueParamName];
• returnValue = (param.Value == null) ? -1 : (int)param.Value;
•  
• return firstObject;
• }
• }
• }
•  
• /// <summary>
• /// Executes a stored procedure, and builds a <see cref="SqlDataReader"/> object.
• /// </summary>
• /// <param name="procName">
• /// The name of the stored procedure to execute.
• /// </param>
• /// <param name="parameters">
• /// The parameters required by the stored procedure.
• /// </param>
• protected SqlDataReader RunDataReader(string procName, SqlParameter[] parameters)
• {
• SqlConnectionHolder connectionHolder;
• SqlCommand sqlCommand;
•  
• using( connectionHolder = GetConnectionHolder() ) {
• connectionHolder.AutoClose = false;
•  
• using( sqlCommand = BuildSQLCommand(connectionHolder, procName, parameters) ) {
• if( _underTrans ) {
• return sqlCommand.ExecuteReader(CommandBehavior.Default);
• }
• else {
• return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
• }
• }
• }
• }
•  
• /// <summary>
• /// Executes a stored procedure, builds a <see cref="SqlDataReader"/> object,
• /// and also returns the return value from the stored procedure.
• /// </summary>
• /// <param name="procName">
• /// The name of the stored procedure to execute.
• /// </param>
• /// <param name="parameters">
• /// The parameters required by the stored procedure.
• /// </param>
• /// <param name="returnValue">
• /// The return value from the stored procedure.
• /// </param>
• /* protected SqlDataReader RunDataReader(string procName, SqlParameter[] parameters, out int returnValue)
• {
• SqlConnectionHolder connectionHolder;
• SqlCommand sqlCommand;
• SqlDataReader dr;
• SqlParameter param;
•  
• using( connectionHolder = GetConnectionHolder() ) {
• connectionHolder.AutoClose = false;
•  
• using( sqlCommand = BuildSQLCommand(connectionHolder, procName, parameters, true) ) {
• if( _underTrans ) {
• dr = sqlCommand.ExecuteReader(CommandBehavior.Default);
• }
• else {
• dr = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
• }
•  
• //Gets the return value
• param = sqlCommand.Parameters[s_returnValueParamName];
• returnValue = (param.Value == null) ? -1 : (int)param.Value;
•  
• return dr;
• }
• }
• } */
•  
• /// <summary>
• /// Excecutes a stored procedure, returns the return value from the store
• /// procedure, and the number of rows affected.
• /// </summary>
• /// <param name="procName">
• /// The name of the stored procedure to execute.
• /// </param>
• /// <param name="parameters">
• /// Parameters required by the stored procedure.
• /// </param>
• /// <param name="returnValue">
• /// The return value from the stored procedure.
• /// </param>
• /// <returns>
• /// The number of affected rows in the database.
• /// </returns>
• protected int RunSQLCommand(string procName, SqlParameter[] parameters, out int returnValue)
• {
• SqlConnectionHolder connectionHolder;
• SqlCommand sqlCommand;
• SqlParameter param;
• int rowsAffected;
•  
• using( connectionHolder = GetConnectionHolder() ) {
• using( sqlCommand = BuildSQLCommand(connectionHolder, procName, parameters, true) ) {
• rowsAffected = sqlCommand.ExecuteNonQuery();
•  
• //Gets the return value of the stored procedure.
• param = sqlCommand.Parameters[s_returnValueParamName];
• returnValue = (param.Value != null) ? (int)param.Value : -1;
• }
• }
•  
• return rowsAffected;
• }
•  
• /// <summary>
• /// Excecutes a stored procedure, and returns the number of rows affected in the database.
• /// </summary>
• /// <param name="procName">
• /// The name of the stored procedure.
• /// </param>
• /// <param name="parameters">
• /// Parameters required by the stored procedure.
• /// </param>
• /// <returns>
• /// The number of affected rows in the database.
• /// </returns>
• protected int RunSQLCommand(string procName, SqlParameter[] parameters)
• {
• SqlConnectionHolder connectionHolder;
• SqlCommand sqlCommand;
•  
• using( connectionHolder = GetConnectionHolder() ) {
• using( sqlCommand = BuildSQLCommand(connectionHolder, procName, parameters) ) {
• return sqlCommand.ExecuteNonQuery();
• }
• }
• }
•  
• /// <summary>
• /// Creates a <see cref="SqlParameter"/>.
• /// </summary>
• /// <param name="paramName">
• /// The name of the parameter.
• /// </param>
• /// <param name="dbType">
• /// Specifies SQL Server-specific data type of the parameter.
• /// </param>
• /// <param name="value">
• /// The value of the parameter.
• /// </param>
• protected SqlParameter CreateInputParam(string paramName, SqlDbType dbType, object value)
• {
• SqlParameter param = new SqlParameter(paramName, dbType);
• if( value == null ) {
• param.IsNullable = true;
• param.Value = DBNull.Value;
• }
• else {
• param.Value = value;
• }
•  
• return param;
• }
•  
• /// <summary>
• /// Creates a <see cref="SqlParameter"/>.
• /// </summary>
• /// <param name="paramName">
• /// The name of the parameter.
• /// </param>
• /// <param name="dbType">
• /// Specifies SQL Server-specific data type of the parameter.
• /// </param>
• /// <param name="value">
• /// The value of the parameter.
• /// </param>
• /// <param name="paramSize">
• /// The maximum size, in bytes, of the value of the parameter.
• /// </param>
• protected SqlParameter CreateInputParam(string paramName, SqlDbType dbType, int paramSize, object value)
• {
• SqlParameter param = new SqlParameter(paramName, dbType, paramSize);
• if( value == null ) {
• param.IsNullable = true;
• param.Value = DBNull.Value;
• }
• else {
• param.Value = value;
• }
•  
• return param;
• }
•  
• /// <summary>
• /// Creates an output parameter.
• /// </summary>
• /// <param name="paramName">
• /// The name of the parameter
• /// </param>
• /// <param name="dbType">
• /// Specifies SQL Server-specified data type of the parameter.
• /// </param>
• protected SqlParameter CreateOutputParam(string paramName, SqlDbType dbType)
• {
• SqlParameter param = new SqlParameter(paramName, dbType);
• param.Direction = ParameterDirection.Output;
•  
• return param;
• }
•  
• /// <summary>
• /// Creates an output parameter.
• /// </summary>
• /// <param name="paramName">
• /// The name of the parameter
• /// </param>
• /// <param name="dbType">
• /// Specifies SQL Server-specified data type of the parameter.
• /// </param>
• /// <param name="paramSize">
• /// The maximum size, in bytes, of the value of the parameter.
• /// </param>
• protected SqlParameter CreateOutputParam(string paramName, SqlDbType dbType, int paramSize)
• {
• SqlParameter param = new SqlParameter(paramName, dbType, paramSize);
• param.Direction = ParameterDirection.Output;
•  
• return param;
• }
•  
• /// <summary>
• /// Creates an output parameter.
• /// </summary>
• /// <param name="paramName">
• /// The name of the parameter
• /// </param>
• /// <param name="dbType">
• /// Specifies SQL Server-specified data type of the parameter.
• /// </param>
• /// <param name="paramSize">
• /// The maximum size, in bytes, of the value of the parameter.
• /// </param>
• /// <param name="value">
• /// The value of the parameter.
• /// </param>
• protected SqlParameter CreateOutputParam(string paramName, SqlDbType dbType, int paramSize, object value)
• {
• SqlParameter param = new SqlParameter(paramName, dbType, paramSize);
• param.Direction = ParameterDirection.Output;
•  
• if( value == null ) {
• param.IsNullable = true;
• param.Value = DBNull.Value;
• }
• else {
• param.Value = value;
• }
•  
• return param;
• }
•  
• private SqlConnectionHolder GetConnectionHolder()
• {
• if( _allowTrans ) {
• if( !_underTrans ) {
• throw new InvalidOperationException(
• "To perform a transaction, you should invoke 'BeginTransaction' method first.");
• }
•  
• return new SqlConnectionHolder(_sqlConnection, _sqlTransaction);
• }
• else {
• return new SqlConnectionHolder(_connectionString);
• }
• }
•  
• /// <summary>
• /// Creates an <see cref="SqlCommand"/> object for executing a stored procedure.
• /// </summary>
• /// <param name="sqlConnection">
• /// A <see cref="SqlConnectionHolder"/> object used by the new <see cref="SqlCommand"/> object.
• /// </param>
• /// <param name="procName">
• /// The name of the stored procedure to execute.
• /// </param>
• /// <param name="parameters">
• /// Parameters required by the stored procedure.
• /// </param>
• private SqlCommand BuildSQLCommand(SqlConnectionHolder sqlConnection, string procName, SqlParameter[] parameters)
• {
• return BuildSQLCommand(sqlConnection, procName, parameters, false);
• }
•  
• /// <summary>
• /// Creates an <see cref="SqlCommand"/> object for executing a stored procedure.
• /// </summary>
• /// <param name="sqlConnection">
• /// A <see cref="SqlConnectionHolder"/> object used by the new <see cref="SqlCommand"/> object.
• /// </param>
• /// <param name="procName">
• /// The name of the stored procedure to execute.
• /// </param>
• /// <param name="parameters">
• /// Parameters required by the stored procedure.
• /// </param>
• private SqlCommand BuildSQLCommand(SqlConnectionHolder sqlConnection, string procName, SqlParameter[] parameters, bool getReturnValue)
• {
• SqlCommand sqlCommand;
•  
• //Creates a SqlCommand object, and marks it as a stored procedure.
• sqlCommand = new SqlCommand(procName, sqlConnection.Connection);
• sqlCommand.CommandType = CommandType.StoredProcedure;
• if( _underTrans ) {
• sqlCommand.Transaction = _sqlTransaction;
• }
•  
• //Adds parameters
• if( parameters != null && parameters.Length > 0 ) {
• foreach( SqlParameter param in parameters ) {
• sqlCommand.Parameters.Add(param);
• }
• }
•  
• if( getReturnValue ) {
• SqlParameter param;
•  
• // Creates a SqlParameter object for fetching the return value
• // from the stored procedure.
• param = new SqlParameter(s_returnValueParamName, SqlDbType.Int);
• param.Direction = ParameterDirection.ReturnValue;
•  
• sqlCommand.Parameters.Add(param);
• }
•  
• return sqlCommand;
• }
•  
• /// <summary>
• /// Indicates whether the instance is allowed to perform a transaction.
• /// </summary>
• public bool AllowTransaction
• {
• get { return _allowTrans; }
• }
•  
• /// <summary>
• /// Gets or sets the connection string used for connecting the specified database server.
• /// </summary>
• protected string ConnectionString
• {
• get { return _connectionString; }
• set { _connectionString = value; }
• }
•  
• private SqlConnection _sqlConnection;
• private SqlTransaction _sqlTransaction;
• private bool _underTrans;
• private bool _allowTrans;
•  
• private string _connectionString;
•  
• private const string s_returnValueParamName = "@ReturnValue";
• }

Show/hide code: internal sealed class SqlConnectionHolder

• internal sealed class SqlConnectionHolder : IDisposable
• {
• internal SqlConnectionHolder(string connectionString)
• {
• try {
• _connection = new SqlConnection(connectionString);
• }
• catch( ArgumentException innerException ) {
• throw new ArgumentException("Invalid connection string.", innerException);
• }
•  
• // Opens the database connection here.
• Open();
• }
•  
• internal SqlConnectionHolder(SqlConnection sqlConnection, SqlTransaction sqlTransaction)
• {
• if( sqlConnection == null ) {
• throw new ArgumentNullException("sqlConnection");
• }
• if( sqlTransaction == null ) {
• throw new ArgumentNullException("sqlTransaction");
• }
• if( !object.ReferenceEquals(sqlTransaction.Connection, sqlConnection) ) {
• throw new ArgumentException(
• "The Connection of the 'sqlTransaction' must be equal to 'sqlConnection'",
• "sqlTransaction");
• }
•  
• _connection = sqlConnection;
• _transaction = sqlTransaction;
• _isOpened = true;
• _underTrans = true;
• }
•  
• /// <summary>
• /// Closes the database connection
• /// </summary>
• public void Dispose()
• {
• Close();
• }
•  
• /// <summary>
• /// Opens the database connection.
• /// </summary>
• public void Open()
• {
• if( !_isOpened ) {
• _connection.Open();
• _isOpened = true;
• }
• }
•  
• /// <summary>
• /// Closes the database connection
• /// </summary>
• public void Close()
• {
• if( _isOpened && _autoClose && !_underTrans ) {
• _connection.Dispose();
• _connection = null;
•  
• _isOpened = false;
• }
• }
•  
• /// <summary>
• /// Gets or sets a value indicating whether the database connection will be automatically closed.
• /// </summary>
• public bool AutoClose
• {
• get { return _autoClose; }
• set { _autoClose = value; }
• }
•  
• internal SqlConnection Connection
• {
• get { return _connection; }
• }
•  
• internal SqlTransaction Transaction
• {
• get { return _transaction; }
• }
•  
• private SqlConnection _connection;
• private SqlTransaction _transaction;
• private bool _isOpened;
• private bool _underTrans;
• private bool _autoClose = true;
•  
• }

The UML diagram illustrates inheritance relationship.