Alex Rivera | Logout

How to insert a data table into SQL Server database table?

Asked 2012-01-31T06:07:07.087
64

I have imported data from some Excel file and I have saved it into a datatable. Now I'd like to save this information in my SQL Server database.

I saw a lot of information on the web but I cannot understand it:

  1. Someone said insert line by line another suggested bulk update... etc: what it better?
  2. Should I use OLE or SQL Server objects (like dataAdapter or connection)?

My need is to read the employee weekly hours report, from his Excel file and save it to a database table where all the reports are saved (updating the db with new records every week).

The Excel file contains reports only for the current week.

c# sql-server datatable
Edit
Report

1 Answer

11

I am giving a very simple code, which i used in my solution (I have the same problem statement as yours)

    SqlConnection con = connection string ;
//new SqlConnection("Data Source=.;uid=sa;pwd=sa123;database=Example1");
con.Open();
string sql = "Create Table abcd (";
foreach (DataColumn column in dt.Columns)
{
    sql += "[" + column.ColumnName + "] " + "nvarchar(50)" + ",";
}
sql = sql.TrimEnd(new char[] { ',' }) + ")";
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
using (var adapter = new SqlDataAdapter("SELECT * FROM abcd", con)) 
using(var builder = new SqlCommandBuilder(adapter))
{
adapter.InsertCommand = builder.GetInsertCommand();
adapter.Update(dt);
// adapter.Update(ds.Tables[0]); (Incase u have a data-set)
}
con.Close();

I have given a predefined table-name as "abcd" (you must take care that a table by this name doesn't exist in your database). Please vote my answer if it works for you!!!! :)

answered 2012-11-29T11:37:10.230

Your Answer