Click here to Skip to main content
14,242,790 members

Creating Dynamic Grid-view Worked Like Excel Sheet

Rate this:
4.38 (5 votes)
Please Sign up or sign in to vote.
4.38 (5 votes)
19 May 2015CPOL
Creating dynamic grid-view worked like an Excel sheet

Introduction

This tip is about how to create a dynamic grid view (like Excel sheet) with template field and save input data into database. Sometimes, it felt that Excel sheet is better to input data whenever we select multiple data stores than a form.

Using the Code

Let’s start with database. Create a table named as user.

Creating Stored Procedure for the Table

Create proc SP_USER
@name nvarchar(200) ,
@name_desc nvarchar(500)
as 
begin
insert into user (name,name_desc) values(@name,@name_desc)
end

Designer Source Code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="index.aspx.cs" Inherits="Excelgrid.index" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title></title>
</head>
<body>
   <form id="form1" runat="server">
   <table>
   <tr><td>
     <asp:GridView ID="excelgrd" runat="server" CellPadding="4" 

       ForeColor="#333333" GridLines="None" AutoGenerateColumns="false" ShowFooter="true">
      <AlternatingRowStyle BackColor="White" />
      <EditRowStyle BackColor="#7C6F57" />
      <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
      <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
      <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
      <RowStyle BackColor="#E3EAEB" />
      <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
      <SortedAscendingCellStyle BackColor="#F8FAFA" />
      <SortedAscendingHeaderStyle BackColor="#246B61" />
      <SortedDescendingCellStyle BackColor="#D4DFE1" />
      <SortedDescendingHeaderStyle BackColor="#15524A" />
      <Columns>
          <asp:BoundField DataField="Slno" HeaderText="SL No" />
          <asp:TemplateField HeaderText="Name">
          <ItemTemplate>
              <asp:TextBox ID="txnm" runat="server"></asp:TextBox>
          </ItemTemplate>
          </asp:TemplateField>
          <asp:TemplateField HeaderText="Description">
          <ItemTemplate>
              <asp:TextBox ID="txdesc" runat="server"></asp:TextBox>
          </ItemTemplate>
          <FooterStyle HorizontalAlign="Right" />
          <FooterTemplate>
              <asp:Button ID="ADDBTN" runat="server" Text="Add New Row" OnClick="ADDBTN_Click" />
          </FooterTemplate>
          </asp:TemplateField>
      </Columns>
      </asp:GridView></td>
      </tr>
      <tr>
      <td>
         <asp:Button ID="btnsave" runat="server" Text="Save" OnClick="svbtn_Click" /></td></tr>
      </table>
  </form>
</body>
</html>

C# Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
 
namespace Excelgrid
{
public partial class index : System.Web.UI.Page
{
   SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CONN"].ToString());
   protected void Page_Load(object sender, EventArgs e)
   {
      if (!IsPostBack)
       {
         bindgrd();//bind your grid
       }
   }
   private void bindgrd()
   {
      DataTable dt = new DataTable();
      DataRow dr = null;
      dt.Columns.Add(new DataColumn("Slno", typeof(string)));
      dt.Columns.Add(new DataColumn("Name", typeof(string)));
      dt.Columns.Add(new DataColumn("Desc", typeof(string)));
      dr = dt.NewRow();
      dr["Slno"] = 1;
      dr["Name"] = string.Empty;
      dr["Desc"] = string.Empty;
      dt.Rows.Add(dr);
      //Store the DataTable in ViewState
      ViewState["CurrentTable"] = dt;
      excelgrd.DataSource = dt;
      excelgrd.DataBind();
   }
   protected void addnewrow()
   {
       int rowIndex = 0;

       if (ViewState["CurrentTable"] != null)
       {
          DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
          DataRow drCurrentRow = null;
          if (dtCurrentTable.Rows.Count > 0)
          {
            for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
              {
                //extract the TextBox values
                TextBox tx1 = (TextBox)excelgrd.Rows[rowIndex].Cells[1].FindControl("txnm");
                TextBox tx2 = (TextBox)excelgrd.Rows[rowIndex].Cells[2].FindControl("txdesc");

                drCurrentRow = dtCurrentTable.NewRow();
                drCurrentRow["Slno"] = i + 1;

                dtCurrentTable.Rows[i - 1]["Name"] = tx1.Text;
                dtCurrentTable.Rows[i - 1]["Desc"] = tx2.Text;

               rowIndex++;
             }
           dtCurrentTable.Rows.Add(drCurrentRow);
           ViewState["CurrentTable"] = dtCurrentTable;

           excelgrd.DataSource = dtCurrentTable;
           excelgrd.DataBind();
         }
      }
      else
      {
        Response.Write("ViewState is null");
      }

      //Set Previous Data on Postbacks
      SetPreviousData();
   }
   private void SetPreviousData()
   {
     int rowIndex = 0;
     if (ViewState["CurrentTable"] != null)
     {
         DataTable dt = (DataTable)ViewState["CurrentTable"];
         if (dt.Rows.Count > 0)
         {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                TextBox tx1 = (TextBox)excelgrd.Rows[rowIndex].Cells[1].FindControl("txnm");
                TextBox tx2 = (TextBox)excelgrd.Rows[rowIndex].Cells[2].FindControl("txdesc");
                tx1.Text = dt.Rows[i]["Name"].ToString();
                tx2.Text = dt.Rows[i]["Desc"].ToString();
                rowIndex++;
             }
         }
     }
   }
   protected void svbtn_Click(object sender, EventArgs e)
   {
         foreach (GridViewRow r in excelgrd.Rows)
         {
             string des = (r.FindControl("txdesc") as TextBox).Text;
             string nm = (r.FindControl("txnm") as TextBox).Text;

             try
             {
                  if(nm !="" || des != "")
                  {
                     con.Open();
                     SqlCommand sql = new SqlCommand("SP_User", con);
                     sql.CommandType = CommandType.StoredProcedure;
                     sql.Parameters.AddWithValue("@name", nm);
                     sql.Parameters.AddWithValue("@name_Desc", des);
                     sql.ExecuteNonQuery();
                     sql.Dispose();
                   }
              }
              catch (Exception e1)
              {
                   string error = e1.ToString();
              }
              finally
              {
                  con.Close();

              }

          }
    }
    protected void ADDBTN_Click(object sender, EventArgs e)
    {
         addnewrow();
    }

   }
}

Result of the above code is as shown below:

Result

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Rojalin Sahoo
Software Developer ComputoIT Solutions Pvt. Ltd.
India India
No Biography provided

Comments and Discussions

 
SuggestionGood Efforts...but.. Pin
MayurDighe27-May-15 4:28
professionalMayurDighe27-May-15 4:28 
QuestionSome comments Pin
gstolarov21-May-15 3:41
membergstolarov21-May-15 3:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 19 May 2015

Stats

16.9K views
454 downloads
3 bookmarked