SQL Insert and Update Stored Procedure

ALTER PROCEDURE [dbo].[sp_common_department_setup]
(
	@DepartmentCode int,
	@DepartmentId varchar(20),
	@DepartmentName varchar(100),
	@InsertUpdate varchar(10)
)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	if(@InsertUpdate = 'Insert')
	begin
		insert into T_Common_Department_Setup(departmentCode,departmentId,departmentName)
		values (@DepartmentCode,@DepartmentId,@DepartmentName)
	end
	else if(@InsertUpdate = 'Update')
	begin
		update T_Common_Department_Setup
		set departmentCode = @DepartmentCode,
		departmentId = @DepartmentId,
		departmentName = @DepartmentName
		WHERE departmentCode = @departmentCode
	end
END
C#/ ASP .NET Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using RegistrationandExamination.Model;
using RegistrationandExamination.BLL;
using RegistrationandExamination.CommonLayer;
using System.Data;


namespace Registration_and_Examination
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        String GlobalString = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                binddatagrid();
            }
        }

        protected void insert_Click(object sender, EventArgs e)
        {
            model_common_department_setup obj = new model_common_department_setup();

            try
            {
                obj.DepartmentCode = int.Parse(departmentCodes.Text.Trim());
                obj.DepartmentId = departmentIds.Text;
                obj.DepartmentName = departmentNames.Text;
                obj.InserUpdate = "Insert";

                bll_common_department_setup objProcess = new bll_common_department_setup();
                objProcess.Model_common_department_setup = obj;
                objProcess.AddDepartmentData();

                lblMessage.Text = CommonVariable.InsertSuccess;
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message.ToString();
            }

            binddatagrid();
        }

        protected void edit_Click(object sender, EventArgs e)
        {
            model_common_department_setup obj = new model_common_department_setup();

            try
            {
                obj.DepartmentCode = int.Parse(departmentCodes.Text.Trim());
                obj.DepartmentId = departmentIds.Text;
                obj.DepartmentName = departmentNames.Text;
                obj.InserUpdate = "Update";

                bll_common_department_setup objProcess = new bll_common_department_setup();
                objProcess.Model_common_department_setup = obj;
                objProcess.AddDepartmentData();

                lblMessage.Text = CommonVariable.InsertSuccess;
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message.ToString();
            }

            binddatagrid();
        }

        public void binddatagrid()
        {
            try
            {
                DataSet ds = new DataSet();
                String sqlString = "select * from T_Common_Department_Setup";
                ds = ClsCommon.GetAdhocResult(sqlString);

                gvPreview.DataSource = ds.Tables[0];
                gvPreview.DataBind();
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message.ToString();
            }
        }

        private void CommonDelete()
        {
            GlobalString = "";

            try
            {
                foreach (GridViewRow rowItem in gvPreview.Rows)
                {
                    CheckBox chkDelete = (CheckBox)rowItem.FindControl("chkDelete");
                    if (chkDelete.Checked)
                    {
                        Label DepartmentCode = (Label)rowItem.FindControl("DepartmentCode");
                        GlobalString = GlobalString + "," + DepartmentCode.Text.Trim();
                    }
                }

                bll_CommonInfoDelete objbll_CommonInfoDelete = new bll_CommonInfoDelete();
                objbll_CommonInfoDelete.CommonInfoDelete(GlobalString, ",", "T_Common_Department_Setup", "departmentCode");

                if (CommonVariable.CommonId == 1)
                {
                    binddatagrid();
                    CommonMethods.SuccessMessageColor(lblMessage, "Ok");
                }
                else
                    CommonMethods.FailuerMessageColor(lblMessage, "Failed");
            }
            catch (Exception ex)
            {
                CommonMethods.FailuerMessageColor(lblMessage, ex.Message.ToString());
            }
        }

        protected void btnDelete_Click(object sender, EventArgs e)
        {
            //String sqlString = "DELETE from T_Common_Department_Setup WHERE departmentCode='" + departmentCodes.Text.Trim() + "'";
            //ClsCommon.DeleteData(sqlString);
            //binddatagrid();

            CommonDelete();
        }

        protected void chkEdit_CheckedChanged(object sender, EventArgs e)
        {
            Int32 CountValue = 0;
            try
            {
                foreach (GridViewRow rowItem in gvPreview.Rows)
                {
                    CheckBox chkEdit = (CheckBox)rowItem.FindControl("chkEdit");
                    if (chkEdit.Checked)
                    {
                        Label DepartmentCode = ((Label)rowItem.FindControl("DepartmentCode"));
                        Label DepartmentId = ((Label)rowItem.FindControl("DepartmentId"));
                        Label DepartmentName = ((Label)rowItem.FindControl("DepartmentName"));

                        departmentCodes.Text = DepartmentCode.Text;
                        departmentIds.Text = DepartmentId.Text;
                        departmentNames.Text = DepartmentName.Text;

                        btnDelete.Enabled = false;
                        insert.Enabled = true;
                        CountValue = CountValue + 1;
                    }
                }
                if (CountValue == 0)
                {
                    departmentCodes.Text = "";
                    departmentIds.Text = "";
                    departmentNames.Text = "";
                }


            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message.ToString();
            }
        }

        protected void chkDelete_CheckedChanged(object sender, EventArgs e)
        {
            Int32 CountValue = 0;
            try
            {
                foreach (GridViewRow rowItem in gvPreview.Rows)
                {
                    CheckBox chkDelete = (CheckBox)rowItem.FindControl("chkDelete");
                    if (chkDelete.Checked)
                    {
                        CountValue = CountValue + 1;
                    }
                }
                if (CountValue > 0)
                {
                    btnDelete.Enabled = true;
                    insert.Enabled = false;
                }
                else
                {
                    btnDelete.Enabled = false;
                    insert.Enabled = true;
                }
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message.ToString();
            }
        }

        protected void gvPreview_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                CommonMethods.GridRowDataBound(e, hidEditCheckedIDS);
            }
        }
    }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s