Microsoft.ApplicationBlocks.Data

using System;
using System.Data;
using System.Xml;
using System.Data.Common;
using System.Collections;

namespace Microsoft.ApplicationBlocks.Data
{
    /// <summary>
    /// The SqlHelper class is intended to encapsulate high performance, scalable best practices for 
    /// common uses of SqlClient
    /// </summary>
    public sealed class SqlHelper
    {
        private static DbProviderFactory dbFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");

        private static readonly string DbConnectionString = "Data Source=.;Initial Catalog=School;Persist Security Info=True;User ID=sa;password=123456;Pooling=True;Min Pool Size=10;Max Pool Size=200";

        private static DbConnection CreateConnection()
        {
            DbConnection connection = dbFactory.CreateConnection();
            connection.ConnectionString = DbConnectionString;
            connection.Open();
            return connection;
        }

        public static DbTransaction BeginTransaction(IsolationLevel Iso)
        {
            return (CreateConnection()).BeginTransaction(Iso);
        }

        public static DbTransaction BeginTransaction()
        {
            return (CreateConnection()).BeginTransaction();
        }

        public static void Commit(DbTransaction Transaction)
        {
            DbConnection con = (DbConnection)Transaction.Connection;
            Transaction.Commit();
            con.Close();
        }

        public static void Rollback(DbTransaction Transaction)
        {
            DbConnection con = (DbConnection)Transaction.Connection;
            Transaction.Rollback();
            con.Close();
        }

        #region private utility methods & constructors
        private SqlHelper() { }

        private static void AttachParameters(DbCommand command, DbParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (DbParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }
        private static void PrepareCommand(DbCommand command, DbConnection connection, DbTransaction transaction, CommandType commandType, string commandText, DbParameter[] commandParameters, out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            // If the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }

            // Associate the connection with the command
            command.Connection = connection;

            // Set the command text (stored procedure name or SQL statement)
            command.CommandText = commandText;

            // If we were provided a transaction, assign it
            if (transaction != null)
            {
                if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = transaction;
            }

            // Set the command type
            command.CommandType = commandType;

            // Attach the command parameters if they are provided
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }

        #endregion private utility methods & constructors

        #region ExecuteNonQuery
        public static int ExecuteNonQuery(string commandText, params DbParameter[] commandParameters)
        {
            using (DbConnection connection = CreateConnection())
            {
                CommandType commandType = CommandType.Text;
                if (connection == null) throw new ArgumentNullException("connection");

                // Create a command and prepare it for execution
                DbCommand cmd = connection.CreateCommand();
                bool mustCloseConnection = false;
                PrepareCommand(cmd, connection, (DbTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

                // Finally, execute the command
                int retval = cmd.ExecuteNonQuery();

                // Detach the DbParameters from the command object, so they can be used again
                cmd.Parameters.Clear();
                if (mustCloseConnection)
                    connection.Close();
                return retval;
            }
        }
        public static int ExecuteNonQuery(DbTransaction transaction, string commandText, params DbParameter[] commandParameters)
        {
            CommandType commandType = CommandType.Text;
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

            // Create a command and prepare it for execution
            DbCommand cmd = transaction.Connection.CreateCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // Finally, execute the command
            int retval = cmd.ExecuteNonQuery();

            // Detach the DbParameters from the command object, so they can be used again
            cmd.Parameters.Clear();
            return retval;
        }

        #endregion ExecuteNonQuery
        #region ExecuteDataTable
        public static DataTable ExecuteDataTable(string commandText, params DbParameter[] commandParameters)
        {
            using (DbConnection connection = CreateConnection())
            {
                CommandType commandType = CommandType.Text;
                if (connection == null) throw new ArgumentNullException("connection");

                // Create a command and prepare it for execution
                DbCommand cmd = connection.CreateCommand();
                bool mustCloseConnection = false;
                PrepareCommand(cmd, connection, (DbTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

                // Create the DataAdapter & DataSet
                using (DbDataAdapter dbDataAdapter = dbFactory.CreateDataAdapter())
                {
                    DataTable ds = new DataTable();
                    dbDataAdapter.SelectCommand = cmd;
                    // Fill the DataSet using default values for DataTable names, etc
                    dbDataAdapter.Fill(ds);

                    // Detach the DbParameters from the command object, so they can be used again
                    cmd.Parameters.Clear();

                    if (mustCloseConnection)
                        connection.Close();

                    // Return the dataset
                    return ds;
                }
            }
        }

        public static DataTable ExecuteDataTable(DbTransaction transaction, string commandText, params DbParameter[] commandParameters)
        {
            CommandType commandType = CommandType.Text;
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

            // Create a command and prepare it for execution
            DbCommand cmd = transaction.Connection.CreateCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // Create the DataAdapter & DataSet
            using (DbDataAdapter dbDataAdapter = dbFactory.CreateDataAdapter())
            {
                DataTable ds = new DataTable();
                dbDataAdapter.SelectCommand = cmd;

                // Fill the DataSet using default values for DataTable names, etc
                dbDataAdapter.Fill(ds);

                // Detach the DbParameters from the command object, so they can be used again
                cmd.Parameters.Clear();

                // Return the dataset
                return ds;
            }
        }

        #endregion ExecuteDataset
        #region ExecuteDataset
        public static DataSet ExecuteDataset(string commandText, params DbParameter[] commandParameters)
        {
            using (DbConnection connection = CreateConnection())
            {
                CommandType commandType = CommandType.Text;
                if (connection == null) throw new ArgumentNullException("connection");

                // Create a command and prepare it for execution
                DbCommand cmd = connection.CreateCommand();
                bool mustCloseConnection = false;
                PrepareCommand(cmd, connection, (DbTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

                // Create the DataAdapter & DataSet
                using (DbDataAdapter dbDataAdapter = dbFactory.CreateDataAdapter())
                {
                    DataSet ds = new DataSet();
                    dbDataAdapter.SelectCommand = cmd;
                    // Fill the DataSet using default values for DataTable names, etc
                    dbDataAdapter.Fill(ds);

                    // Detach the DbParameters from the command object, so they can be used again
                    cmd.Parameters.Clear();

                    if (mustCloseConnection)
                        connection.Close();

                    // Return the dataset
                    return ds;
                }
            }
        }

        public static DataSet ExecuteDataset(DbTransaction transaction, string commandText, params DbParameter[] commandParameters)
        {
            CommandType commandType = CommandType.Text;
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

            // Create a command and prepare it for execution
            DbCommand cmd = transaction.Connection.CreateCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // Create the DataAdapter & DataSet
            using (DbDataAdapter dbDataAdapter = dbFactory.CreateDataAdapter())
            {
                DataSet ds = new DataSet();
                dbDataAdapter.SelectCommand = cmd;
                // Fill the DataSet using default values for DataTable names, etc
                dbDataAdapter.Fill(ds);

                // Detach the DbParameters from the command object, so they can be used again
                cmd.Parameters.Clear();

                // Return the dataset
                return ds;
            }
        }

        #endregion ExecuteDataset

        #region ExecuteScalar
        public static object ExecuteScalar(string commandText, params DbParameter[] commandParameters)
        {
            using (DbConnection connection = CreateConnection())
            {
                CommandType commandType = CommandType.Text;
                if (connection == null) throw new ArgumentNullException("connection");

                // Create a command and prepare it for execution
                DbCommand cmd = connection.CreateCommand();

                bool mustCloseConnection = false;
                PrepareCommand(cmd, connection, (DbTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

                // Execute the command & return the results
                object retval = cmd.ExecuteScalar();

                // Detach the DbParameters from the command object, so they can be used again
                cmd.Parameters.Clear();

                if (mustCloseConnection)
                    connection.Close();

                return retval;
            }
        }
        public static object ExecuteScalar(DbTransaction transaction, string commandText, params DbParameter[] commandParameters)
        {
            CommandType commandType = CommandType.Text;
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

            // Create a command and prepare it for execution
            DbCommand cmd = transaction.Connection.CreateCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // Execute the command & return the results
            object retval = cmd.ExecuteScalar();

            // Detach the DbParameters from the command object, so they can be used again
            cmd.Parameters.Clear();
            return retval;
        }
        #endregion ExecuteScalar
    }
}