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;
}
}



Bulk Editing


Attachments

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: