StoredProcedureHandler

SELECT MAX(ID) FROM Service where ID<(SELECT MAX(ID) FROM Service
)

DECLARE @Results  VARCHAR(8000)
set @Results = ''

SELECT @Results = FirstName +','+Title +','+ @Results FROM
EmployeeMaster

SELECT @Results 

Stored Procedure Handler
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;


///

/// Summary description for StoredProcedureHandler
///

public class StoredProcedureHandler
{
    // private  string str = System.Configuration.ConfigurationManager.AppSettings["connstring"];
    // private static SqlConnection conn;

    public StoredProcedureHandler()
    {
    }

    #region ConnectDB
    public static SqlConnection ConnectToDB()
    {
        string str = System.Configuration.ConfigurationManager.AppSettings["connstring"];
        SqlConnection conn = new SqlConnection(str);
        try
        {

            HttpContext.Current.Trace.Write("ConnectToDB called");

            conn = new SqlConnection(str);
            if (conn.State == ConnectionState.Closed || conn == null || conn.State == ConnectionState.Broken)
            {
                conn.Open();
            }
            return conn;

        }
        catch (Exception ex)
        {
            HttpContext.Current.Trace.Write(ex.Message);
            // closeConnection();

            conn.Close();
            throw;
        }



    }
    #endregion
    //public static void closeConnection()
    //{
    //    conn.Close();
    //}

    public static DataTable GetData(string SP)
    {

        SqlDataAdapter adapter = new SqlDataAdapter();
        string str = System.Configuration.ConfigurationManager.AppSettings["connstring"];
        SqlConnection conn = new SqlConnection(str);
        try
        {
            conn.Open();


            HttpContext.Current.Trace.Write("GetData called");

            adapter.SelectCommand = new SqlCommand();
            adapter.SelectCommand.Connection = conn;//StoredProcedureHandler.ConnectToDB();
            adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            adapter.SelectCommand.CommandText = SP;

            DataSet ds = new DataSet();
            adapter.Fill(ds);


            return ds.Tables[0];


        }
        catch (Exception ex)
        {
            HttpContext.Current.Trace.Write(ex.Message);
            conn.Close();
            //closeConnection();
            throw;
        }
        finally
        {

            adapter.Dispose();
            //closeConnection();
            conn.Close();
            //MyCommand.Dispose()
            //conn.Dispose();
        }



    }
    public static DataTable GetDataLimitBySitemap(string spname, Dictionary parameter)
    {
        SqlDataAdapter adapter = new SqlDataAdapter();
        string str = System.Configuration.ConfigurationManager.AppSettings["connstring"];
        SqlConnection conn = new SqlConnection(str);
        try
        {
            conn.Open();
            HttpContext.Current.Trace.Write("GetDataLimitBySitemap called");

            adapter.SelectCommand = new SqlCommand();
            adapter.SelectCommand.Connection = conn; //StoredProcedureHandler.ConnectToDB();
            adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            adapter.SelectCommand.CommandText = spname;

            foreach (KeyValuePair item in parameter)
            {
                SqlParameter p = new SqlParameter(item.Key, item.Value);
                adapter.SelectCommand.Parameters.Add(p);

            }

            DataSet ds = new DataSet();
            adapter.Fill(ds);
            //conn.Close();
            return ds.Tables[0];

        }
        catch (Exception ex)
        {
            HttpContext.Current.Trace.Write(ex.Message);
            conn.Close();
            throw;
        }
        finally
        {
            conn.Close();
            adapter.Dispose();
        }


    }

    public static DataTable GetDataLimit(string spname, Dictionary parameter, int start, int end)
    {
        SqlDataAdapter adapter = new SqlDataAdapter();
        string str = System.Configuration.ConfigurationManager.AppSettings["connstring"];
        SqlConnection conn = new SqlConnection(str);
        try
        {
            conn.Open();
            HttpContext.Current.Trace.Write("GetDataLimit function called");

            adapter.SelectCommand = new SqlCommand();
            adapter.SelectCommand.Connection = conn;// StoredProcedureHandler.ConnectToDB();
            adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            adapter.SelectCommand.CommandText = spname;

            foreach (KeyValuePair item in parameter)
            {
                SqlParameter p = new SqlParameter(item.Key, item.Value);
                adapter.SelectCommand.Parameters.Add(p);

            }

            DataSet ds = new DataSet();
            adapter.Fill(ds, start, end, "rec");
            return ds.Tables[0];

        }
        catch (Exception ex)
        {
            HttpContext.Current.Trace.Write(ex.Message);
            //closeConnection();
            conn.Close();
            adapter.Dispose();


            throw;
        }
        finally
        {
            adapter.Dispose();
            conn.Close();
        }

    }


    public static DataTable GetDataWithPara(string spname, Dictionary parameter)
    {
        string str = System.Configuration.ConfigurationManager.AppSettings["connstring"];
        SqlConnection conn = new SqlConnection(str);
        try
        {
            conn.Open();
            HttpContext.Current.Trace.Write("GetDataWithPara called");
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand();
            adapter.SelectCommand.Connection = conn;//StoredProcedureHandler.ConnectToDB();
            adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            adapter.SelectCommand.CommandText = spname;

            foreach (KeyValuePair item in parameter)
            {
                SqlParameter p = new SqlParameter(item.Key, item.Value);
                adapter.SelectCommand.Parameters.Add(p);

            }

            DataSet ds = new DataSet();
            adapter.Fill(ds);

            return ds.Tables[0];

        }
        catch (Exception ex)
        {
            HttpContext.Current.Trace.Write(ex.Message);
            conn.Close();
            //closeConnection();
            throw;
        }
        finally
        {
            // closeConnection();
            conn.Close();
        }

    }


    #region DataBase Data handler

    public static Boolean InsertData(string spname, Dictionary parameter)
    {
        SqlCommand cmd = new SqlCommand();
        string str = System.Configuration.ConfigurationManager.AppSettings["connstring"];
        SqlConnection conn = new SqlConnection(str);
        conn.Open();
        SqlTransaction sqlTransaction = conn.BeginTransaction();
        try
        {

            HttpContext.Current.Trace.Write("InsertData called");

            cmd.Transaction = sqlTransaction;
            cmd.Connection = conn;//StoredProcedureHandler.ConnectToDB();
            cmd.CommandText = spname;
            cmd.CommandType = CommandType.StoredProcedure;

            foreach (KeyValuePair item in parameter)
            {
                SqlParameter p = new SqlParameter(item.Key, item.Value);
                cmd.Parameters.Add(p);

            }
            cmd.ExecuteNonQuery();
            sqlTransaction.Commit();

            return true;
        }
        catch (Exception ex)
        {
            sqlTransaction.Rollback();
            HttpContext.Current.Trace.Write(ex.Message);
            return false;
        }
        finally
        {
            cmd.Dispose();
            conn.Close();

        }

    }
    public static Int32 InsertDataOutPutValue(string spname, Dictionary parameter, string OutputParamater)
    {
        SqlCommand cmd = new SqlCommand();
        string str = System.Configuration.ConfigurationManager.AppSettings["connstring"];
        SqlConnection conn = new SqlConnection(str);
        int outputValue = 0;
        try
        {
            conn.Open();
            HttpContext.Current.Trace.Write("InsertData called");
            cmd.Connection = conn;//StoredProcedureHandler.ConnectToDB();
            cmd.CommandText = spname;
            cmd.CommandType = CommandType.StoredProcedure;

            foreach (KeyValuePair item in parameter)
            {
                if (item.Key == OutputParamater)
                {
                    SqlParameter p = new SqlParameter(item.Key, item.Value);
                    p.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(p);
                }
                else
                {
                    SqlParameter p = new SqlParameter(item.Key, item.Value);
                    p.Direction = ParameterDirection.Input;
                    cmd.Parameters.Add(p);
                }

            }
            cmd.ExecuteNonQuery();
            outputValue = Convert.ToInt32(cmd.Parameters[OutputParamater].Value);
            conn.Close();
            return outputValue;
        }
        catch (Exception ex)
        {
            HttpContext.Current.Trace.Write(ex.Message);
            return outputValue;
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
        }
    }
    public static void Update(string spname, Dictionary parameter)
    {
        try
        {
            HttpContext.Current.Trace.Write("Update called");
            StoredProcedureHandler.InsertData(spname, parameter);

        }
        catch (Exception ex)
        {
            HttpContext.Current.Trace.Write(ex.Message);
            throw;
        }

    }

    public static void Delete(string spname, Dictionary parameter)
    {

        StoredProcedureHandler.InsertData(spname, parameter);
    }
    #endregion


}