SQL Helper (MS SQL Server)

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

namespace SalesBook
{

    public class SqlHelper : IDisposable
    {
        private string connectionString;
        private SqlConnection connection;
        private SqlCommand command; //command.Connection = connection;
        private SqlTransaction transaction = null;
        private bool useTransaction = false;

        /// <summary>
        /// Default constructor.
        /// It has overloads.
        /// </summary>
        public SqlHelper()
        {

        }

        /// <summary>
        /// Overload constructor.
        /// Creates an of SqlConnection using supplied connection string.
        /// </summary>
        /// <param name="ConnectionString"></param>
        public SqlHelper(string ConnectionString)
        {
            connectionString = ConnectionString;
            connection = new SqlConnection(ConnectionString);
            command = new SqlCommand(); command.Connection = connection;
        }

        public void UseTransaction()
        {
            useTransaction = true;

            OpenConnection();
            transaction = connection.BeginTransaction();
            command.Transaction = transaction;
        }

        public int ExecuteNonQuery(string CommandText, SqlParameter[] CommandParameters)
        {
            command.Parameters.Clear();
            command.CommandText = CommandText;

            if (CommandParameters != null)
            {
                if (CommandParameters.Length > 0)
                {
                    AttachParameters(CommandParameters);
                }
            }

            try
            {
                OpenConnection();
                int result = command.ExecuteNonQuery();
                command.Parameters.Clear();

                return result;
            }
            catch (Exception Ex)
            {
                ManageException(Ex);
                throw Ex;
            }
        }

        /// <summary>
        /// Insert record and retrieve Auto Increment Id
        /// </summary>
        /// <param name="CommandText"></param>
        /// <param name="CommandParameters"></param>
        /// <returns></returns>
        public int ExecuteNonQueryAndRetrieveAutoId(string CommandText, SqlParameter[] CommandParameters)
        {

            command.CommandText = CommandText;

            if (CommandParameters != null)
            {
                if (CommandParameters.Length > 0)
                {
                    AttachParameters(CommandParameters);
                }
            }

            try
            {
                OpenConnection();
                int result = command.ExecuteNonQuery();
                command.Parameters.Clear();

                command.CommandText = "SELECT @@IDENTITY";
                result = Convert.ToInt32(command.ExecuteScalar());
                return result;
            }
            catch (Exception Ex)
            {
                ManageException(Ex);
                throw Ex;
            }
        }
        public SqlDataReader ExecuteReader(string CommandText, SqlParameter[] CommandParameters)
        {
            command.CommandText = CommandText;
            if (CommandParameters != null)
            {
                if (CommandParameters.Length > 0)
                {
                    AttachParameters(CommandParameters);
                }
            }

            try
            {
                OpenConnection();
                var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Parameters.Clear();

                return reader;
            }
            catch (Exception Ex)
            {
                ManageException(Ex);
                throw Ex;
            }
        }

        /// <summary>
        /// Returns DataTable. Don't use any command parameter.
        /// </summary>
        /// <param name="CommandText"></param>
        /// <returns></returns>
        public DataTable ExecuteDatatable(string CommandText)
        {
            try
            {
                using (SqlDataAdapter da = new SqlDataAdapter(command))
                {
                    DataTable table = new DataTable();
                    command.CommandText = CommandText;
                    OpenConnection();
                    da.Fill(table);
                    return table;
                }
            }
            catch (Exception Ex)
            {
                ManageException(Ex); throw Ex;
            }
        }

        public DataTable ExecuteDatatable(string CommandText, SqlParameter[] CommandParameters)
        {
            try
            {
                using (SqlDataAdapter da = new SqlDataAdapter(command))
                {
                    command.CommandText = CommandText;

                    if (CommandParameters != null)
                    {
                        if (CommandParameters.Length > 0)
                        {
                            AttachParameters(CommandParameters);
                        }
                    }

                    DataTable table = new DataTable();
                    OpenConnection();
                    da.Fill(table);

                    command.Parameters.Clear();

                    return table;
                }
            }
            catch (Exception Ex)
            {
                ManageException(Ex); throw Ex;
            }
        }

        public object ExecuteScalar(string CommandText)
        {
            object value = null;

            try
            {
                command.CommandText = CommandText;
                OpenConnection();
                value = command.ExecuteScalar();
                return value;
            }
            catch (Exception Ex)
            {
                ManageException(Ex); throw Ex;
            }

        }

        /// <summary>
        /// Command Parameters can be null.
        /// </summary>
        /// <param name="CommandText"></param>
        /// <param name="CommandParameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(string CommandText, SqlParameter[] CommandParameters)
        {
            object value = null;
            try
            {
                command.CommandText = CommandText;

                if (CommandParameters != null)
                {
                    if (CommandParameters.Length > 0)
                    {
                        AttachParameters(CommandParameters);
                    }
                }

                OpenConnection();

                value = command.ExecuteScalar();
                command.Parameters.Clear();
                return value;
            }
            catch (Exception Ex)
            {
                ManageException(Ex); throw Ex;
            }


        }

        public void Commit()
        {
            if (useTransaction)
            {
                transaction.Commit();
            }

            if (connection.State == ConnectionState.Open) { connection.Close(); }
        }

        #region Helper Methods
        private void AttachParameters(SqlParameter[] commandParameters)
        {
            foreach (SqlParameter p in commandParameters)
            {
                //check for derived output value with no value assigned
                if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                {
                    p.Value = DBNull.Value;
                }

                command.Parameters.Add(p);
            }
        }

        private void ManageException(Exception Ex)
        {
            if (useTransaction)
            {
                if (transaction != null) { transaction.Rollback(); }
            }

            if (connection.State == ConnectionState.Open) { connection.Close(); }


        }

        /// <summary>
        /// Optional.
        /// </summary>
        private void OpenConnection()
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
        }
        #endregion



        #region iDisposable_mthods

        private bool disposed = false;

        public void Dispose()
        {
            CleanUp(true);
            GC.SuppressFinalize(this);
        }

        private void CleanUp(bool disposing)
        {
            // Be sure we have not already been disposed!
            if (!this.disposed)
            {
                // If disposing equals true, dispose all
                // managed resources.
                if (disposing)
                {
                    if (connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }
                    // Dispose managed resources.
                    command.Dispose();
                    connection.Dispose();

                    if (useTransaction)
                    {
                        if (transaction != null)
                        {
                            transaction.Dispose(); //da.Dispose();
                        }
                    }
                }
                // Clean up unmanaged resources here.
            }
            disposed = true;
        }

        ~SqlHelper()
        {
            CleanUp(false);
        }

        #endregion
    }
}

Advertisements

SQL – Select Parent and Count Child from same table


//Let's say, you have a table as follows-
--------------------------------------------
 DesignationId | DesignationName| ParentId |
 -------------------------------------------
    1          |     A          |    0     |
 -------------------------------------------
    2          |     B          |    1     |
 -------------------------------------------
    3          |     C          |    1     |
 -------------------------------------------


// You would like to select records as follows
--------------------------------------------
 DesignationId | DesignationName| Childs   |
 -------------------------------------------
    1          |     A          |    2     |
 -------------------------------------------
    2          |     B          |    0     |
 -------------------------------------------
    3          |     C          |    0     |
 -------------------------------------------

Here is the SQL Statement-

SELECT DesignationId AS Id, DesignationName as Name, 
(SELECT COUNT(*)  FROM Designations AS T WHERE T.ParentID = O.DesignationId) 
FROM Designations AS O Where O.DesignationId>0

Get Hourly Data from SQL Server DateTime Field


SELECT DATEPART(dd, SurveyorActivityDateTime) AS myday, 
       DATEPART(hh, SurveyorActivityDateTime) AS myhour, 
       Count(RetailerId) AS Quantity
FROM [RobiPosMapping].[dbo].[Retailer]
WHERE CAST(SurveyorActivityDateTime AS DATE) = '2015-06-04'
GROUP BY DATEPART(dd, SurveyorActivityDateTime), 
         DATEPART(hh, SurveyorActivityDateTime)