C# Inserting A Dataset Into Sql Database

I'm trying to insert a dataset into an SQL database but I am having difficulties passing my dataset as an argument to my DB class. I am not sure if it is allowed to pass as an argu

Solution 1:

you can loop through datatables in a dataset and can pass a datatable as a stored procedure paramater, found an example here

Solution 2:

1.- Go to SQL Server, under your DB name go to "programmability\Types\User-Defined Table Types, right click and create a new one:

USE DBNAME
GO

-- Create the data typeCREATE TYPE ValuesToInsert ASTABLE 
(
    Value1 INTNOTNULL,
    Value2 INTNOTNULL,
    Value3 VARCHAR(20)
)
GO

2.- Create a SP to receive the table as parameter, parameter must be the new User-Defined table type created in step 1

USE [DBNAME]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- CREATEPROCEDURE [dbo].[spImportData]
    @DataImported dbo.ValuesToInsert READONLY
ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;

    -- Insert statements for procedure hereINSERTINTO [dbo].[TableName] (Value1, Value2, Value3)
    SELECT Value1, Value2, Value3
    FROM@DataImported

3.- Pass a datatable from your code to DB, in this case using Dapper.net as following:

DataTabledtExcelData=newDataTable();
 //Fill dtExcelData and pass as parameterParametersCollectionparam=newParametersCollection();
 param.Add(CreateParameter("@DataImported", dtExcelData));
 ExecuteDataSet("spImportData", CommandType.StoredProcedure, param);