using System;
using System.Data;
using System.Data.Common;
using System.Runtime.Serialization;

namespace JRD.Database
{
    /// <summary>
    /// Thrown when a data access request fails.
    /// </summary>
    [Serializable]
    public class DataAccessException : Exception
    {
        /// <summary>
        /// Creates a new instance.
        /// </summary>
        public DataAccessException() { }

        /// <summary>
        /// Creates a new instance initialized with the specified message string.
        /// </summary>
        /// <param name="message">The error message that explains the reason for the exception.</param>
        public DataAccessException(string message) : base(message) { }

        /// <summary>
        /// Creates a new instance initialized with the specified message string and inner exception.
        /// </summary>
        /// <param name="message">The error message that explains the reason for the exception.</param>
        /// <param name="inner">The exception that is the cause of the current exception.</param>
        public DataAccessException(string message, Exception inner) : base(message, inner) { }

        /// <summary>
        /// Creates a new instance initialized with serialization data.
        /// </summary>
        /// <param name="info">The object that holds the serialized object data.</param>
        /// <param name="context">The contextual information about the source or destination.</param>
        protected DataAccessException(SerializationInfo info, StreamingContext context) : base(info, context) { }
    }

    /// <summary>
    /// Represents parameter information for a command into the DataAccessManager.
    /// </summary>
    public class DatabaseParameter
    {
        /// <summary>
        /// Gets or sets the SQL command parameter name of the parameter.
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// Gets or sets the value of the parameter.
        /// </summary>
        public object Value { get; set; }

        /// <summary>
        /// Gets or sets the expected type of the parameter value.
        /// </summary>
        public DbType Type { get; set; }

        /// <summary>
        /// Creates and initializes a new instance.
        /// </summary>
        /// <param name="name">The name of the database parameter.</param>
        /// <param name="value">The value of the parameter.</param>
        /// <remarks>
        /// The expected type is defaulted, which may create performance problems with implict typing in the database.
        /// </remarks>
        public DatabaseParameter(string name, object value)
        {
            Name = name;
            Value = value;
        }

        /// <summary>
        /// Creates and initializes a new instance.
        /// </summary>
        /// <param name="name">The name of the database parameter (eg. @MyParameter or :MyParameter)</param>
        /// <param name="value">The value of the parameter.</param>
        /// <param name="type">The expected type of the parameter.</param>
        public DatabaseParameter(string name, object value, DbType type)
        {
            Name = name;
            Value = value;
            Type = type;
        }
    }

    /// <summary>
    /// Provides data provider agnostic queries and commands.
    /// </summary>
    public class DataAccessManager : IDisposable
    {
        #region IDisposable Implementation
        public void Dispose()
        {
            // Does nothing; included for future using statement support.
        }
        #endregion

        private DbProviderFactory ProviderFactory { get; set; }

        /// <summary>
        /// Gets or sets the external data store provider name (ex. System.Data.SqlClient).
        /// </summary>
        public string ProviderName { get; set; }

        /// <summary>
        /// Gets or sets the external data store connection string.
        /// </summary>
        public string ConnectionString { get; set; }

        /// <summary>
        /// Creates and initializes a new instance.
        /// </summary>
        /// <param name="providerName">The data provider name (ex. System.Data.SqlClient).</param>
        /// <param name="connectionString">An appropriate connection string for the data provider.</param>
        public DataAccessManager(string providerName, string connectionString)
        {
            ProviderName = providerName;
            ConnectionString = connectionString;
            ProviderFactory = DbProviderFactories.GetFactory(ProviderName);
        }

        #region Commands
        /// <summary>
        /// Selects a DataTable from the DbProvider.
        /// </summary>
        /// <param name="commandText">The select command text to execute.</param>
        /// <param name="args">Parameter definitions for the command.</param>
        /// <returns>A DataTable containing records selected from the DbProvider.</returns>
        public DataTable Select(string commandText, params DatabaseParameter[] args)
        {
            var result = new DataTable();

            try
            {
                using (var connection = GetConnection())
                {
                    using (var command = ProviderFactory.CreateCommand())
                    {
                        command.Connection = connection;
                        command.CommandType = CommandType.Text;
                        command.CommandText = commandText;

                        foreach (var arg in args)
                        {
                            AddParameter(command, arg);
                        }

                        using (var adapter = ProviderFactory.CreateDataAdapter())
                        {
                            adapter.SelectCommand = command;
                            adapter.Fill(result);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new DataAccessException("Provider: " + ProviderName + Environment.NewLine + "CommandText: " + commandText, ex);
            }

            return result;
        }

        /// <summary>
        /// Executes a non-query command on the DbProvider.
        /// </summary>
        /// <param name="commandText">The non-query command text to execute.</param>
        /// <param name="args">Parameter definitions for the command.</param>
        public void ExecuteCommand(string commandText, params DatabaseParameter[] args)
        {
            try
            {
                using (var connection = GetConnection())
                {
                    using (var command = ProviderFactory.CreateCommand())
                    {
                        command.Connection = connection;
                        command.CommandType = CommandType.Text;
                        command.CommandText = commandText;

                        foreach (var arg in args)
                        {
                            AddParameter(command, arg);
                        }

                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new DataAccessException("Provider: " + ProviderName + Environment.NewLine + "CommandText: " + commandText, ex);
            }
        }
        #endregion

        #region Validation
        /// <summary>
        /// Sanitizes an identifier for the provider by quoting it.
        /// </summary>
        /// <param name="identifier">The identifier to sanitize.</param>
        /// <returns>The sanitized identifier.</returns>
        public string QuotedIdentifier(string identifier)
        {
            using (var builder = ProviderFactory.CreateCommandBuilder())
            {
                return builder.QuoteIdentifier(identifier);
            }
        }

        /// <summary>
        /// Checks a table name to verify that it exists in the DbProvider.
        /// </summary>
        /// <param name="tableName">Name of the table to verify.</param>
        /// <param name="isQuoted">True if the table name is already quoted.</param>
        /// <returns>True if the table exists, false otherwise.</returns>
        public bool TableExists(string tableName, bool isQuoted)
        {
            using (var connection = GetConnection())
            {
                using (var tables = connection.GetSchema("Tables"))
                {
                    string newTable = isQuoted ? tableName : QuotedIdentifier(tableName);

                    foreach (DataRow row in tables.Rows)
                    {
                        string existingTable = QuotedIdentifier(row["TABLE_NAME"].ToString());

                        if (existingTable == newTable)
                        {
                            return true;
                        }
                    }

                    return false;
                }
            }
        }
        #endregion

        #region Miscellaneous Helpers
        /// <summary>
        /// Retrieves an open connection from the provider factory.
        /// </summary>
        /// <returns>An open DbConnection.</returns>
        private DbConnection GetConnection()
        {
            var connection = ProviderFactory.CreateConnection();

            connection.ConnectionString = ConnectionString;
            connection.Open();

            return connection;
        }

        /// <summary>
        /// Adds the provided database parameter to the provided command.
        /// </summary>
        /// <param name="command">The command the parameter will be added to.</param>
        /// <param name="parameter">The parameter settings.</param>
        private void AddParameter(DbCommand command, DatabaseParameter parameter)
        {
            var p = command.CreateParameter();

            p.ParameterName = parameter.Name;
            p.Value = parameter.Value;
            p.DbType = parameter.Type;

            command.Parameters.Add(p);
        }
        #endregion
    }
}

Interesting design. I have actually been working on a few of these myself. After really learning SQL at my job, I have written one for connecting and interacting with like SQL/Oracle/AS400/IBMDB2, as well as one for SQLite.

Have to say, the approach you took, and your design is somewhat different then what I would have thought ... not saying it's a bad thing at all, just interesting.

(Sadly mine aren't up for sharing anytime soon, they have a lot of work still needed to be done to improve them more).

Have to say, the approach you took, and your design is somewhat different then what I would have thought

The key design aspect is simplicity of usage and implementation, so the class is really little more than a wrapper around how I usually do database access manually. I'd love to see how you'd do it when you feel comfortable sharing, as this is probably something everyone would do differently. :)

Yeah mine was about take all the work and simplifying it. I actually am hoping to have a rebuilt version of it soon (Working on it tonight after work). I'll follow up when done with it.

(I do have to admit I am protective of it, partially why it was never release ... okay actually mainly was I wasn't ready to as it wasn't done).

Funny part is, it has shifted in designs partially. Depending on the scenario one layout works better then another and it's hard finding the perfect balance.

Hi deceptikon. I know this is an old thread but could you post some examples of how to use each method in your wrapper. I've figured out some, but not all. I'd really appreciate it. Thanks and awesome job!

The article starter has earned a lot of community kudos, and such articles offer a bounty for quality replies.