Customer Main

http://www.c-sharpcorner.com/Blogs/9797/category-sub-category.aspx
http://www.c-sharpcorner.com/Blogs/9796/customer-main.aspx
http://www.c-sharpcorner.com/Blogs/9795/storedprocedure-handler.aspx

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {

            DataTable dtService = new DataTable();
            dtService = StoredProcedureHandler.GetData("usp_getAllService");
            drpService.DataTextField = "ServiceName";
            drpService.DataValueField = "ServiceID";
            drpService.DataSource = dtService;
            drpService.DataBind();
            ListItem i = new ListItem("Select", "0", true);
            i.Selected = true;
            drpService.Items.Add(i);

            DataTable dt = (DataTable)Session["MyData"];
            if (dt == null)
            {
                dt = createDataTable();
            }
            grdCustomerDetail.DataSource = dt;
            Session["MyData"] = dt;
            grdCustomerDetail.DataBind();
        }
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        DataTable dt = (DataTable)Session["MyData"];
        if (dt == null)
        {
            dt = createDataTable();
        }
        DataRow dr;
        dr = dt.NewRow();
        dr["ID"] = dt.Rows.Count + 1;
        dr["CustomerName"] = txtCustomerName.Text;
        dr["ServiceID"] = drpService.SelectedItem.Value;
        dr["ServiceName"] = drpService.SelectedItem.Text;
        dr["Price"] = txtPrice.Text;
        dr["Date"] = DateTime.Now;// txtDate.Text;

        dt.Rows.Add(dr);
        grdCustomerDetail.DataSource = dt;
        Session["MyData"] = dt;
        grdCustomerDetail.DataBind();
    }
    protected void drpService_SelectedIndexChanged(object sender, EventArgs e)
    {
        Dictionary param = new Dictionary();
        param.Add("@ServiceID", drpService.SelectedItem.Value);

        DataTable dtService = new DataTable();
        dtService = StoredProcedureHandler.GetDataWithPara("usp_getServicePricebyServiceID", param);
        txtPrice.Text = dtService.Rows[0]["Price"].ToString();
    }

    public DataTable createDataTable()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("ID", System.Type.GetType("System.Int32"));
        dt.Columns.Add("CustomerName", System.Type.GetType("System.String"));
        dt.Columns.Add("ServiceID", System.Type.GetType("System.Int32"));
        dt.Columns.Add("ServiceName", System.Type.GetType("System.String"));
        dt.Columns.Add("Price", System.Type.GetType("System.Int32"));
        dt.Columns.Add("Date", System.Type.GetType("System.DateTime"));
        return dt;


    }
    protected void grdCustomerDetail_RowEditing(object sender, GridViewEditEventArgs e)
    {
        grdCustomerDetail.EditIndex = e.NewEditIndex;
        grdCustomerDetail.DataSource = (DataTable)Session["MyData"];

        grdCustomerDetail.DataBind();
    }
    protected void grdCustomerDetail_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        DataTable dt = (DataTable)Session["MyData"];
        GridViewRow gr = grdCustomerDetail.Rows[e.RowIndex];
        DropDownList drpService = (DropDownList)grdCustomerDetail.Rows[e.RowIndex].FindControl("drpService");
        dt.Rows[gr.DataItemIndex]["ServiceID"] = drpService.SelectedItem.Value;
        dt.Rows[gr.DataItemIndex]["ServiceName"] = drpService.SelectedItem.Text;

        Dictionary param = new Dictionary();
        param.Add("@ServiceID", drpService.SelectedItem.Value);

        DataTable dtService = new DataTable();
        dtService = StoredProcedureHandler.GetDataWithPara("usp_getServicePricebyServiceID", param);
        dt.Rows[gr.DataItemIndex]["Price"] = dtService.Rows[0]["Price"].ToString();
        dt.AcceptChanges();
        grdCustomerDetail.EditIndex = -1;
        Session["MyData"] = dt;
        grdCustomerDetail.DataSource = (DataTable)Session["MyData"];
        grdCustomerDetail.DataBind();

    }
    protected void grdCustomerDetail_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DropDownList drpService = (DropDownList)e.Row.FindControl("drpService");
            Label lblServiceID = (Label)e.Row.FindControl("lblServiceIDEdit");

            if (drpService != null)
            {
                drpService.DataSource = StoredProcedureHandler.GetData("usp_getAllService");
                drpService.DataBind();
                drpService.SelectedValue = lblServiceID.Text;
            }
        }
    }
    protected void grdCustomerDetail_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        grdCustomerDetail.EditIndex = -1;

        grdCustomerDetail.DataSource = (DataTable)Session["MyData"];
        grdCustomerDetail.DataBind();
    }
    protected void grdCustomerDetail_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        DataTable dt = (DataTable)Session["MyData"];
        GridViewRow gr = grdCustomerDetail.Rows[e.RowIndex];
        dt.Rows[gr.DataItemIndex].Delete();
        dt.AcceptChanges();
        Session["MyData"] = dt;
        grdCustomerDetail.DataSource = (DataTable)Session["MyData"];
        grdCustomerDetail.DataBind();

    }
    protected void btnSaveAll_Click(object sender, EventArgs e)
    {

        Dictionary param = new Dictionary();
        Label lblCustomerName = (Label)grdCustomerDetail.Rows[0].FindControl("lblCustomerName");
        param.Add("@CustomerName", lblCustomerName.Text);
        param.Add("@CustomerID", 0);
        param.Add("@Date", DateTime.Now);
        DataTable dtService = new DataTable();
        int returnvalue = StoredProcedureHandler.InsertDataOutPutValue("isp_InserCustomer", param, "@CustomerID");

        for (int i = 0; i < grdCustomerDetail.Rows.Count; i++)
        {
            //Label lblCustomerName = (Label)grdCustomerDetail.Rows[i].FindControl("lblCustomerName");
            Label lblServiceID = (Label)grdCustomerDetail.Rows[i].FindControl("lblServiceID");
            Label lblPrice = (Label)grdCustomerDetail.Rows[i].FindControl("lblPrice");
            Label lblDate = (Label)grdCustomerDetail.Rows[i].FindControl("lblDate");

            Dictionary param1 = new Dictionary();
            param1.Add("@ServiceID", lblServiceID.Text);
            param1.Add("@CustomerID", returnvalue);


            StoredProcedureHandler.InsertData("isp_InserCustomerServiceMap", param1);
           

        }
    }

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


}