GridView Bulk Edit / Bulk Update/ Bulk Insert in ASP.net

In this artical i'm trying to explain how to do multiple items editing and updating and inserting using asp.net concepts. In my previous thread i'm explaining about how to edit GridView Records, but now i'm trying to explain Bulk Records edit, Update and inserting.

GridView Bulk Edit, Update, Insert

In my previous thread i'm explaining about how to edit,update GridView Records, but now i'm trying to explain Bulk Records edit, Update and inserting. For this puprose i'm introduce one checkbox and based on checkbox selection i want to insert data , edit data, and update data based on checkBox selection. For this we no need to choose GridView EditItem Template why because we are performing multiple rows editing not a single row. EditItemTemplate performs single row Editing. So, for that purpose i'm consider both Label and TextBox controls inside Template field only it is easy to understanding.

Prepare Tables:


CREATE TABLE Material
(
	[Material_Id] INT ,
	[Material_Name] varchar(100),
	[Active_Tag] CHAR(1) NOT NULL,	
	[Created_By] INT  NULL ,
	[Created_On] DATETIME  NULL,
	[Modified_By] INT  NULL ,
	[Modified_On] DATETIME  NULL,

	CONSTRAINT [PK_Material] PRIMARY KEY CLUSTERED
	(		
		[Material_Id] ASC		
	)
)
GO

insert into Material(Material_Id,Material_Name,Active_Tag,Created_By,Created_On)
values(1,'pile','Y',200,getdate()),(2,'pile','Y',200,getdate()),(3,'PCC','Y',200,getdate())

select * from Material

CREATE TABLE Material_Quantity
(
	[Material_Id] INT ,
	[Tender_Quantity] float,
	[Actual_Quantity] float,
	[Active_Tag] CHAR(1) NOT NULL,
	[Remarks] VARCHAR(100),	
	[Created_By] INT  NULL ,
	[Created_On] DATETIME  NULL,
	[Modified_By] INT  NULL ,
	[Modified_On] DATETIME  NULL,

	CONSTRAINT [PK_Material_Quantity] PRIMARY KEY CLUSTERED
	(		
		[Material_Id] ASC		
	)
)
GO

Prepare Stored Procedures

Prepare StoredProcedures, to overcome the compilation process and increase the performence of the page.


CREATE PROCEDURE Get_Material_Quantity
AS
BEGIN
	SELECT Material_Name,Tender_Quantity,Actual_Quantity,Remarks
	FROM  Material M
		  Left JOIN Material_Quantity MQ on M.Material_Id=MQ.Material_Id
END

CREATE PROCEDURE Insert_Material_Quantity
(
	@Material_Id int,
	@Tender_Quantity float=null,
	@Actual_Quantity float=null,
	@Remarks VARCAHR(100)=NULL
	@User_Id int
)
AS

BEGIN
	INSERT INTO Material_Quantity(Material_Id,Tender_Quantity,Actual_Quantity,Remarks ,Active_Tag,Created_By,Created_On)
	VALUES(@Material_Id,@Tender_Quantity,@Actual_Quantity,@Remarks ,'Y',@User_Id,GETDATE())
END

CREATE PROCEDURE Update_Material_Quantity
(
	@Material_Id int,
	@Tender_Quantity float=null,
	@Actual_Quantity float=null,
	@Remarks VARCAHR(100)=NULL
	@User_Id int
)
AS

BEGIN
	UPDATE Material_Quantity SET Tender_Quantity=@Tender_Quantity, Actual_Quantity=@Actual_Quantity,Remarks =@Remarks ,Active_Tag='Y'
	WHERE Material_Id=@Material_Id
END

JavaScript

for Select and DeSelect All CheckBoxes purpose i wrote some javascript function. Use below lines of code.



< script type="text/Javascript" >

 function Check(parentChk,container) 
        {
            var elements =  document.getElementsByTagName("INPUT"); 
            for(i=0; i            {
                if(parentChk.checked == true) 
                {  
                    if( IsCheckBox(elements[i]) && IsMatch(elements[i].id,container)) 
                    {
                     elements[i].checked = true; 
                    }         
                }
                else 
                {
                 if( IsCheckBox(elements[i]) && IsMatch(elements[i].id,container)) 
                    {
                        elements[i].checked = false; 
                    }
                }       
            }       
        }
< script>

Design Page

Design your page using the following code snippet



<table align="right" cellpadding="2" cellspacing="2" width="100%">
    <tr>
       <td colspan="2" align="right" >
            <asp:Button ID="btnEdit" runat="server" Text="Edit" Width="73px" OnClick="btnEdit_Click" />
                           
            <asp:Button ID="btnInsert" runat="server" Text="Insert" Width="73px" OnClick="btnInsert_Click" />
                         
            <asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click" />           
        </td>
	<td>
		<asp:GridView ID="GV" runat="server" ShowFooter="false" Width="100%"
                    EnableModelValidation="True" ForeColor="#333333" 
                    GridLines="Vertical" AutoGenerateColumns="false" >
                   
                    <Columns>
                       <asp:TemplateField >
                            <HeaderTemplate>
                                <input id="HchkSelect" type="checkbox" onclick="javascript:Check(this,'GV');" />                               
                            </HeaderTemplate>
                            <ItemTemplate>
                                <asp:CheckBox ID="chkSelect" runat="server" />
                            </ItemTemplate>
                            <ItemStyle HorizontalAlign="center" />
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Material"  HeaderStyle-ForeColor="White">
                            <ItemTemplate>
                                        
                                <asp:Label ID="lblMaterial" runat="server" Text='<%# Bind_Material(Convert.ToString(Eval("Material_Name")))%>'/>
                                <asp:Label ID="lblMaterial_Id" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Material_Id") %>' Visible="false"/>
                            </ItemTemplate>
                            
                        <asp:TemplateField HeaderText="Tender Quantity"  HeaderStyle-ForeColor="White">
                            <ItemTemplate>
                                <asp:Label ID="lblTenderQty" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Tender_Quantity") %>'/>
                                <asp:TextBox ID="txtTenderQty" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Tender_Quantity") %>' visible="false"/>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText=" Actual Quantity"  HeaderStyle-ForeColor="White">
                            <ItemTemplate>
                                <asp:Label ID="lblActualQty" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Actual_Quantity") %>'/>
                                <asp:TextBox ID="txtActualQty" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Actual_Quantity") %>' visible="false"/>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Remarks"  HeaderStyle-ForeColor="White">
                            <ItemTemplate>
                                <asp:Label ID="lblRemarks" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Remarks") %>'/>
                                <asp:TextBox ID="txtRemarks" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Remarks") %>' visible="false"/>
                            </ItemTemplate>
                        </asp:TemplateField>                  
                    </Columns>
                </asp:GridView>
	</td>
    </tr>
</table>

Code Behind

In code behind wrote the following lines of code



using System.Data;
using System.Data.SqlClient;
using System.Configuration;


public partial class DMS_QuantityPage : System.Web.UI.Page
{
	SqlConnection con;
	SqlCommand cmd; 
	SqlDataAdapter da;   
	DataSet ds; 



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

	protected void Bind_GV()    
	{ 
       		con = new SqlConnection("DataBase=naveen;User id=sa;Password=P@ssword9");
       		cmd=new SqlCommand("Get_Material_Quantity",con);
       		cmd.CommandType=CommandType.StoredProcedure;
		try
		{
       			con.Open();
       			da=new SqlDataAdapter(cmd);
       			da.Fill(ds);
        
       			GV.DataSource = ds; 
       			GV.DataBind();
		}
		Catch(Exception ex)
		{
		}
		finally
		{
			con.Close();
			con.Dispose();
		}
	}
 	protected string Bind_Material(string sMaterial)
 	{
 		if (Material == sMaterial)
        	{
            		sMaterial = "";
        	}
        	else
        	{
            		Material = sMaterial;
        	}
        	return sMaterial;
	}
	protected void btnInsert_Click(object sender, EventArgs e)
	{
		foreach(GridViewRow row in GV.Rows)
		{
			CheckBox chk = ((CheckBox)row.FindControl("chkSelect"));
			if(chk.Checked==true)
			{
				string Material_Id = ((Label)row.FindControl("lblMaterial_Id")).Text;
				string Tender_Qty = ((TextBox)row.FindControl("txtTenderQty")).Text;
                		string Actual_Qty = ((TextBox)row.FindControl("txtActualQty")).Text;
                		string Remarks = ((TextBox)row.FindControl("txtRemarks")).Text;
				try
        			{
            				con.Open();
            				cmd = new SqlCommand("Insert_Material_Quantity", con);
            				cmd.CommandType = CommandType.StoredProcedure;
            				cmd.Parameters.AddWithValue("@Material_Id",Material_Id );
            				cmd.Parameters.AddWithValue("@Tender_Quantity",Tender_Qty);
					cmd.Parameters.AddWithValue("@Actual_Quantity",Actual_Qty);
					cmd.Parameters.AddWithValue("@Remarks",Remarks);
            				cmd.ExecuteNonQuery();
        			}
        			catch (Exception ex)
        			{
        			}
        			finally
        			{
            				con.Close();
        			}
			}
		}
		Bind_GV();
     	
	}

	protected void btnUpdate_Click(object sender,EventArgs e)
	{
		foreach(GridViewRow row in GV.Rows)
		{
			CheckBox chk = ((CheckBox)row.FindControl("chkSelect"));
			if(chk.Checked==true)
			{
				string Material_Id = ((Label)row.FindControl("lblMaterial_Id")).Text;
				string Tender_Qty = ((TextBox)row.FindControl("txtTenderQty")).Text;
                		string Actual_Qty = ((TextBox)row.FindControl("txtActualQty")).Text;
                		string Remarks = ((TextBox)row.FindControl("txtRemarks")).Text;
				try
        			{
            				con.Open();
            				cmd = new SqlCommand("Update_Material_Quantity", con);
            				cmd.CommandType = CommandType.StoredProcedure;
            				cmd.Parameters.AddWithValue("@Material_Id",Material_Id );
            				cmd.Parameters.AddWithValue("@Tender_Quantity",Tender_Qty);
					cmd.Parameters.AddWithValue("@Actual_Quantity",Actual_Qty);
					cmd.Parameters.AddWithValue("@Remarks",Remarks);
            				cmd.ExecuteNonQuery();
        			}
        			catch (Exception ex)
        			{
        			}
        			finally
        			{
            				con.Close();
        			}
			}
		}
		Bind_GV();
		btnInsert.Enabled=true;
	}
	protected void btnEdit_Click(object sender,EventArgs e)
	{
		foreach (GridViewRow row in GV.Rows)
        	{
            		CheckBox chk = ((CheckBox)row.FindControl("chkSelect"));
            		if (chk.Checked == true)
            		{
                		((TextBox)row.FindControl("txtTenderQty")).Visible = true;
                		((TextBox)row.FindControl("txtActualQty")).Visible = true;
                		((TextBox)row.FindControl("txtRemarks")).Visible = true;
                		((Label)row.FindControl("lblTenderQty")).Visible = false;
                		((Label)row.FindControl("lblActualQty")).Visible = false;
                		((Label)row.FindControl("lblRemarks")).Visible = false;
            		}
            		else
            		{
                		((TextBox)row.FindControl("txtTenderQty")).Visible = false;
                		((TextBox)row.FindControl("txtActualQty")).Visible = false;
                		((TextBox)row.FindControl("txtRemarks")).Visible = false;
                		((Label)row.FindControl("lblTenderQty")).Visible = true;
                		((Label)row.FindControl("lblActualQty")).Visible = true;
                		((Label)row.FindControl("lblRemarks")).Visible = true;     
            		}
        	}
		btnInsert.Enabled=false;
	}
}

GridView Bulk Edit / Bulk Update/ Bulk Insert in ASP.net

GridView Bulk Edit, Update, Insert

Prepare Tables:

Prepare Stored Procedures

JavaScript

Design Page

Code Behind

Attachments

Comments

About dotnetspider.com

Quick Links

General

Services