I am using asp.net with C#. I have a DataTable filled with Columns and Rows. I would like to save my Datatable inside my Database. Is there any one who could help on this?
Don't forget to click "Mark as Answer" on the post that helped you.
You can use ADO.net to save the datatable to database.
Define the connectionstring to sql. Find a sample below.
public static DataTable Save()
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
string sql = "select * from Employee";
using (SqlCommand command = new SqlCommand(sql, connection)) {
command.CommandType = CommandType.Text;
using (SqlDataAdapter adapter = new SqlDataAdapter(command)) {
You can also use SqlBulkCopy to quickly insert the records from the target table to db. You can use the Sql server management objects to create the target table.
StringBuilder sb = new StringBuilder(string.Empty);
DataTable dt = //Set the DataSource
if (dt.Rows.Count > 0)
{
string name = dt.Rows[0]["ColumnName"].ToString();
string address = dt.Rows[0]["ColumnName"].ToString();
sb.AppendFormat("'{0}','{1}'",name, address);
}
string sql = string.Format("INSERT INTO tbl_students(Name, Address) VALUES ({0});", sb.ToString());
// But still i would suggest you to use SP or parameterize queries for security reasons
WhoIsPrime
Member
178 Points
267 Posts
Saving DataTable to database
Jun 20, 2008 12:52 PM|LINK
I am using asp.net with C#. I have a DataTable filled with Columns and Rows. I would like to save my Datatable inside my Database. Is there any one who could help on this?
DotNetXenon
Member
437 Points
134 Posts
Re: Saving DataTable to database
Jun 20, 2008 02:04 PM|LINK
You can use ADO.net to save the datatable to database.
Define the connectionstring to sql. Find a sample below.
public static DataTable Save()
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
string sql = "select * from Employee";
using (SqlCommand command = new SqlCommand(sql, connection)) {
command.CommandType = CommandType.Text;
using (SqlDataAdapter adapter = new SqlDataAdapter(command)) {
SqlParameter idParameter = new SqlParameter();
idParameter.ParameterName = "@EMPLID";
idParameter.Direction = ParameterDirection.Input;
idParameter.SqlDbType = SqlDbType.Int;
idParameter.Size = 4;
idParameter.Value = EmployeeId;
command.Parameters.Add(idParameter);
DataTable dt = new DataTable();
adapter.Fill(dt, "Save");
return dt; }
}
}
You can also use SqlBulkCopy to quickly insert the records from the target table to db. You can use the Sql server management objects to create the target table.
vinz
All-Star
127087 Points
17946 Posts
MVP
Re: Saving DataTable to database
Jun 20, 2008 04:06 PM|LINK
Here's a quick dirty way..
using System.Text;using System.Data;
using System.Data.SqlClient;
private void InsertData()
{
SqlConnection conn = new SqlConnection ("YOUR CONNECTION STRING");
StringBuilder sb = new StringBuilder(string.Empty);
DataTable dt = //Set the DataSource
if (dt.Rows.Count > 0)
{
string name = dt.Rows[0]["ColumnName"].ToString();
string address = dt.Rows[0]["ColumnName"].ToString();
sb.AppendFormat("'{0}','{1}'",name, address);
}
string sql = string.Format("INSERT INTO tbl_students(Name, Address) VALUES ({0});", sb.ToString()); // But still i would suggest you to use SP or parameterize queries for security reasons
conn.Open();
SqlCommand cmd = new SqlbCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
conn.Close();
}
private void Submit_Click(object sender, System.EventArgs e)
{
InsertData();
}
You may also refer here
http://geekswithblogs.net/dotNETvinz/archive/2008/02/01/creating-a-data-access-framework-again.aspx
MessageBox Controls for WebForms | Blog | Twitter | Linkedin