Asp.Net: create dynamic datatable and Save to database using C#

Aman Sharma
In this article I will explain how to create Datatable dynamically in using C# and Save that datatable to database.
Steps to implement:
1.      Create Datatable dynamically and Bind that datatable to gridview to show data on page Load.
2.      Now Save this Datatable to database using foreach.
3.      Now bind another gridview from tdatabase table to check result.


Design WebPage:

<fieldset style="width:340px;"><legend>Create Datatable Dynamically and Save to database</legend>
            <br />
            Data From Dynamic datatable<br />
            </strong>&nbsp;<asp:GridView ID="GridView1" Width="300px" runat="server"></asp:GridView>
        <br /><br />
        <asp:Button ID="Button1" runat="server" Text="Submit to Database" OnClick="Button1_Click" />
        <br /><br />
              <strong>Data From Database after Submission<br />
&nbsp;</strong><asp:GridView ID="GridView2" Width="300px" runat="server"></asp:GridView>
            <br />
        </fieldset> Code to Create Datatable And Save to Database using C#:

//add following napespaces

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

Now Write following Code:

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
//Create Dynamic datatable

       DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                            new DataColumn("Name", typeof(string)),
                            new DataColumn("Age",typeof(string)) });
        dt.Rows.Add(1, "Ankit", "21");
        dt.Rows.Add(2, "Anuj", "22");
        dt.Rows.Add(3, "Anish", "24");
        dt.Rows.Add(4, "Ankush", "22");

        ViewState["dt"] = dt;
        //Before submission fill gridview from datatable

        GridView1.DataSource = dt;
  protected void Button1_Click(object sender, EventArgs e)
      DataTable dt = (DataTable)ViewState["dt"];
        string sql = "INSERT INTO Student_detail (Name, Age) VALUES (@A, @B)";
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString))
            foreach (DataRow r in dt.Rows)
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddWithValue("@A", r["Name"]);
                cmd.Parameters.AddWithValue("@B", r["Age"]);            
            Response.Write("<script> alert('Saved Successfully');</script>");

       //After submission fill gridview from database

   public void Fill_grid()
       if (con.State == ConnectionState.Closed)
       SqlCommand cmd = new SqlCommand("select * from student_detail", con);     
       SqlDataAdapter adpData = new SqlDataAdapter(cmd);
       DataSet ds = new DataSet();
       GridView2.DataSource = ds;

Post a Comment

Post a Comment (0)

#buttons=(Accept !) #days=(20)

Our website uses cookies to enhance your experience. Learn More
Accept !