Saturday, 31 August 2013

Gridview Bulk Edit, Bulk Update, Bulk Insert in ASP.net

 

GridView Bulk Edit, Update, Insert


                In my previous thread GridView Edit/ Update /Cancle 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 purpose 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


Stored Procedures:



Prepare Stored Procedures, to overcome the compilation process and increase the performance of the page.


CREATE PROCEDURE Get_Material_QuantityAS
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'   
 
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 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;
 }
}


OutPut: