Asaf Gilad Asaf Gilad - 2 months ago 17
C# Question

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

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.

Answer

Create a User-Defined TableType in your database:

CREATE TYPE [dbo].[MyTableType] AS TABLE(
    [Id] int NOT NULL,
    [Name] [nvarchar](128) NULL
)

and define a parameter in your Stored Ptocedure:

CREATE PROCEDURE [dbo].[InsertTable]
    @myTableType MyTableType readonly
AS
BEGIN
    insert into [dbo].Records select * from @myTableType 
END

and send your DataTable directly to sql server:

using (var command = new SqlCommand("InsertTable") {CommandType = CommandType.StoredProcedure})
{
    var dt = new DataTable(); //create your own data table
    command.Parameters.Add(new SqlParameter("@myTableType", dt));
    SqlHelper.Exec(command);
}