In
this article I will explain how to create Datatable dynamically in asp.net
using C# and Save that datatable to database.
Description: In previous articles I have explained Error: ASP.NET: How to Get Public IP address in ASP.NET using C# & VB.NET? And Custom Validator: Server Side validation using Custom Validator in ASP.NET (C# & Vb.NET) And ASP.NET: Bind or Populate Form View Data control with data from SQL database using c# & vb.net .
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.
Demo:
Design WebPage:
<fieldset style="width:340px;"><legend>Create Datatable
Dynamically and Save to database</legend>
<strong>
<br />
Data
From Dynamic datatable<br />
</strong> <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 />
</strong><asp:GridView ID="GridView2" Width="300px" runat="server"></asp:GridView>
<br />
</fieldset>
|
Asp.net 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)
{
if(!IsPostBack)
{
//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;
GridView1.DataBind();
}
}
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))
{
conn.Open();
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"]);
cmd.ExecuteNonQuery();
}
Response.Write("<script>
alert('Saved Successfully');</script>");
}
//After submission fill gridview from database
Fill_grid();
}
public void Fill_grid()
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd =
new SqlCommand("select * from student_detail", con);
SqlDataAdapter
adpData = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adpData.Fill(ds);
GridView2.DataSource = ds;
GridView2.DataBind();
con.Close();
}
}
|
