C# Wrapper class for SQL Server

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Configuration;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

/// <summary>
/// New interface for database connectivity.
/// </summary>
public class Database
{
    #region Object properties

    private SqlConnection connection;
    public DataTable resultsTable;
    public List<SqlParameter> queryParameters;

    #endregion

    #region Constructs
    /// <summary>
    /// Default construct
    /// </summary>
    public Database( )
    {
        connection = new SqlConnection(
            ConfigurationManager.ConnectionStrings[
                ConfigurationManager.AppSettings["ConnectionString"].ToString()
            ].ToString()
        );

        resultsTable = new DataTable();
        queryParameters = new List<SqlParameter>();

    }



    /// <summary>
    /// Construct for specific connection strings
    /// </summary>
    /// <param name="connectionName">Name of the connection string in web.config</param>
    public Database( string connectionName )
    {
        try
        {
            connection = new SqlConnection(
                ConfigurationManager.ConnectionStrings[connectionName].ToString()
            );

            resultsTable = new DataTable();
            queryParameters = new List<SqlParameter>();
        }
        catch
        {
            throw new Exception( "A connection string with that name could not be found in web.config" );
        }

    }



    /// <summary>
    /// Construct for manual connection strings (No Validation)
    /// </summary>
    /// <param name="connectionString">The custom connection string</param>
    /// <param name="manual">UNUSED</param>
    public Database( string connectionString, bool manual )
    {
        connection = new SqlConnection(
            connectionString
        );

        resultsTable = new DataTable();
        queryParameters = new List<SqlParameter>();
    }
    #endregion

    #region commandExecutors
    /// <summary>
    /// Executes a Scalar query and returns the int result
    /// </summary>
    /// <param name="commandName">Name of the stored procedure to execute</param>
    /// <param name="commandType">SQL command Type (Usally CommandType.StoredProcedure)</param>
    /// <param name="hasParameters">True if query requires parameters, else false if not.</param>
    /// <returns>The integer result of the scalar query</returns>
    /// REF: http://msdn.microsoft.com/en-gb/library/system.data.sqlclient.sqlcommand.executescalar%28v=vs.110%29.aspx
    public object runScalar( string commandName, bool hasParameters, TypeCode returnType )
    {
        //Verify Parameters have been supplied if required.
        if ( ( hasParameters && this.queryParameters.Count > 0 ) || ( !hasParameters ) )
        {
            SqlCommand command = new SqlCommand( commandName, this.connection )
            {
                CommandType = CommandType.StoredProcedure
            };

            if ( queryParameters != null && hasParameters )
            {
                //Add each parameter from the parameter collection to the command object.
                foreach ( SqlParameter param in queryParameters )
                    command.Parameters.Add( param );
            }

            try
            {
                this.connection.Open();
                if ( returnType == TypeCode.Int32 )
                    return (int)command.ExecuteScalar();
                else if ( returnType == TypeCode.String )
                    return command.ExecuteScalar().ToString();
                else if ( returnType == TypeCode.DateTime )
                    return Convert.ToDateTime( command.ExecuteScalar() );
                else
                    return -1;
            }
            catch
            {
                return -1;
            }
            finally
            {
                this.connection.Close();
                this.cleanUp();
            }
        }
        else
            throw new Exception( "You attempted to execute a select query with an incorrect number of required parameters. If the query doesn't need parameters, set hasParameters to false in method call." );
    }


    /// <summary>
    /// Executes a SELECT statement against the database
    /// </summary>
    /// <param name="commandName">Name of the stored proceedure to execute</param>
    /// <param name="commandType">SQL command Type (Usually CommandType.StoredProceedure)</param>
    /// <param name="hasParameters">True if query requires parameters, else false if not.</param>
    /// <returns></returns>
    public DataTable runSelect( string commandName, bool hasParameters )
    {
        //Verify Parameters have been supplied if required.
        if ( ( hasParameters && this.queryParameters.Count > 0 ) || ( !hasParameters ) )
        {
            //Create a new command instance.
            SqlCommand command = new SqlCommand( commandName, this.connection )
            {
                CommandType = CommandType.StoredProcedure,
                CommandTimeout = 600
            };

            if ( queryParameters != null && hasParameters )
            {
                //Add each parameter from the parameter collection to the command object.
                foreach ( SqlParameter param in queryParameters )
                    command.Parameters.Add( param );
            }

            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter( command );

                //Flush resultsTable
                resultsTable = new DataTable();
                //Open the connection
                connection.Open();
                //Create an SqlDataAdapter instance, and use it to fill the 'resultSet' datatable with the returned results.
                //Command is executed here.
                adapter.Fill( resultsTable );

                return resultsTable;
            }
            //TODO: Improve this error handling.
            //Handle SQL Specific exceptions
            catch ( SqlException sqlEx )
            {
                return null;
            }
            //Handle Generic exceptions
            catch ( Exception ex )
            {
                return null;
            }
            //Close the database connection.
            finally
            {
                this.connection.Close();
                this.cleanUp();
            }
        }
        else
            throw new Exception( "You attempted to execute a select query with an incorrect number of required parameters. If the query doesn't need parameters, set hasParameters to false in method call." );
    }

    /// <summary>
    /// Executes and INSERT statement against the database.
    /// </summary>
    /// <param name="commandName">Name of the stored proceedure to execute</param>
    /// <param name="commandType">SQL command Type (Usually CommandType.StoredProceedure)</param>
    /// <returns></returns>
    public int runInsert( string commandName )
    {
        if ( this.getParamCount() > 0 )
        {
            //Create a new command instance.
            SqlCommand command = new SqlCommand( commandName, this.connection )
            {
                CommandType = CommandType.StoredProcedure
            };

            if ( this.queryParameters != null )
            {
                //Add each parameter from the parameter collection to the command object.
                foreach ( SqlParameter param in this.queryParameters )
                    command.Parameters.Add( param );
            }

            try
            {
                this.connection.Open();

                int rowsAffected = command.ExecuteNonQuery();

                return rowsAffected;
            }
            //TODO: Improve this error handling.
            //Handle SQL Specific exceptions
            catch ( SqlException sqlEx )
            {
                return -1;
            }
            //Handle Generic exceptions
            catch ( Exception ex )
            {
                return -1;
            }
            //Close the database connection.
            finally
            {
                this.connection.Close();
                this.cleanUp();
            }
        }
        else
            throw new Exception( "Query is missing required parameters!" );
    }

    /// <summary>
    /// Execures an UPDATE command against the database
    /// </summary>
    /// <param name="commandName">Name of the stored proceedure to execute</param>
    /// <param name="commandType">SQL command Type (Usually CommandType.StoredProceedure)</param>
    /// <returns></returns>
    public int runUpdate( string commandName )
    {
        if ( this.getParamCount() > 0 )
        {
            //Create a new command instance.
            SqlCommand command = new SqlCommand( commandName, this.connection )
            {
                CommandType = CommandType.StoredProcedure
            };

            //Add each parameter from the parameter collection to the command object.
            foreach ( SqlParameter param in this.queryParameters )
                command.Parameters.Add( param );

            try
            {
                this.connection.Open();

                int rowsAffected = command.ExecuteNonQuery();

                return rowsAffected;
            }
            //TODO: Improve this error handling.
            //Handle SQL Specific exceptions
            catch ( SqlException sqlEx )
            {
                return 0;
            }
            //Handle Generic exceptions
            catch ( Exception ex )
            {
                return 0;
            }
            //Close the database connection.
            finally
            {
                this.connection.Close();
                this.cleanUp();
            }
        }
        else
            throw new Exception( "Query is missing required parameters!" );
    }

    /// <summary>
    /// Executes a DELETE command against the database.
    /// </summary>
    /// <param name="commandName">Name of the stored proceedure to execute</param>
    /// <param name="commandType">SQL command Type (Usually CommandType.StoredProceedure)</param>
    /// <returns></returns>
    public int runDelete( string commandName )
    {
        if ( this.getParamCount() > 0 )
        {
            //Create a new command instance.
            SqlCommand command = new SqlCommand( commandName, this.connection )
            {
                CommandType = CommandType.StoredProcedure
            };

            if ( this.queryParameters != null )
            {
                //Add each parameter from the parameter collection to the command object.
                foreach ( SqlParameter param in this.queryParameters )
                    command.Parameters.Add( param );
            }
            else
            {
                //Force an exception, as DELETE commands require parameters.
                throw new Exception( "" );
            }

            try
            {
                this.connection.Open();

                int rowsAffected = command.ExecuteNonQuery();

                return rowsAffected;
            }
            //TODO: Improve this error handling.
            //Handle SQL Specific exceptions
            catch ( SqlException sqlEx )
            {
                return 0;
            }
            //Handle Generic exceptions
            catch ( Exception ex )
            {
                return 0;
            }
            //Close the database connection.
            finally
            {
                this.connection.Close();
                this.cleanUp();
            }
        }
        else
            throw new Exception( "Query is missing required parameters!" );
    }

    #endregion

    #region Utility Methods - INCOMPLETE

    public ConnectionState getConnectionState( )
    {
        return this.connection.State;
    }

    public bool isConnected( )
    {
        ConnectionState connectionState = getConnectionState();

        if ( connectionState == ConnectionState.Open )
            return true;
        else
            return false;
    }

    /// <summary>
    /// Returns the number if records stored in the result datatable
    /// </summary>
    /// <returns>Number of datatable rows. Returns -1 if object is null.</returns>
    public int getResultTableSize( )
    {
        if ( this.resultsTable != null )
            return this.resultsTable.Rows.Count;
        else
            return -1;
    }

    /// <summary>
    /// Returns the number of parameters that have been created for the query
    /// </summary>
    /// <returns>Number of SqlParameter objects present in the List. Returns -1 if object is null.</returns>
    public int getParamCount( )
    {
        if ( this.queryParameters != null )
            return this.queryParameters.Count;
        else
            return -1;
    }

    /// <summary>
    /// Ensures that the SQLParameter List has been cleaned. This allows multiple commands to be
    /// exectued on the same database object instance.
    /// </summary>
    private void cleanUp( )
    {
        //Reinitialise the attribute.
        queryParameters = new List<SqlParameter>();
    }

    #endregion
}