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:
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.
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 Procedure
:
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);
}
To edit the values inside stored-procedure, you can declare a local variable with the same type and insert input table into it:
DECLARE @modifiableTableType MyTableType
INSERT INTO @modifiableTableType SELECT * FROM @myTableType
Then, you can edit @modifiableTableType
:
UPDATE @modifiableTableType SET [Name] = 'new value'