Create User Defined Table Type

/****** Object:  UserDefinedTableType [Entity].[EntityUploadType]    Script Date: 28/02/2017 16:00:11 ******/
CREATE TYPE [Entity].[EntityUploadType] AS TABLE(
	[Reference] [varchar](50) NULL,
	[LetterReference] [varchar](50) NULL,
	[StatusId] [int] NOT NULL,
	[DateReceived] [datetime] NOT NULL,
	[DateClosed] [datetime] NULL,
	[OriginalProductId] [int] NULL,
	[OriginalOutcomeId] [int] NULL,
	[CaseTypeId] [int] NOT NULL,
	[ComplexityId] [int] NULL,
	[StatusUpdatedBy] [int] NOT NULL,
	[StatusUpdatedDate] [datetime] NOT NULL,
	[CreatedBy] [int] NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[LastUpdatedBy] [int] NOT NULL,
	[LastUpdatedDate] [datetime] NOT NULL,
	[IsVisible] [bit] NOT NULL
)
GO

Stored Procedure using the UDT

/****** Object:  StoredProcedure [Entity].[spEntityUpload]    Script Date: 28/02/2017 16:00:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [Entity].[spEntityUpload]
	-- Add the parameters for the stored procedure here
	@datatable [Entity].[EntityUploadType] READONLY
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--insert new data - we validated it on the front end so it's fine to just insert
	INSERT INTO [Entity].[Entity]
           ([StatusId]
           ,[Reference]
           ,[LetterReference]
           ,[DateReceived]
           ,[DateClosed]
           ,[OriginalProductId]
           ,[OriginalOutcomeId]
           ,[CaseTypeId]
           ,[ComplexityId]
           ,[PolicyCount]
           ,[SelectedForQC]
           ,[SelectedForQA]
           ,[StatusUpdatedBy]
           ,[StatusUpdatedDate]
           ,[CreatedBy]
           ,[CreatedDate]
           ,[LastUpdatedBy]
           ,[LastUpdatedDate]
           ,[IsVisible])
     SELECT
           StatusId
           ,Reference
           ,LetterReference
           ,DateReceived
           ,DateClosed
           ,OriginalProductId
           ,OriginalOutcomeId
           ,CaseTypeId
           ,ComplexityId
           ,null
           ,null
           ,null
           ,StatusUpdatedBy
           ,StatusUpdatedDate
           ,CreatedBy
           ,CreatedDate
           ,LastUpdatedBy
           ,LastUpdatedDate
           ,IsVisible
	FROM @datatable
END

C#

The two SQL files are attached. In the C#, the method to actually do the insert is below:

public bool SaveEntityDataTable(DataSet ds)
{
    try
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = this.GetConnectionString();
            conn.Open();

            SqlCommand command = new SqlCommand("[Entity].[spEntityUpload]", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@dataTable", ds.Tables[0]));
            command.ExecuteNonQuery();

            return true;
        }
    }
    catch (Exception ex)
    {
        return false;
    }
}

Which calls another function called GetConnectionString(), which looks like this:

public string GetConnectionString()
{
    //this derives a "classic" sql connection string from the entity framework connection
    return context.Database.Connection.ConnectionString;
}

This all sits within my repository, so if you’ve not already created a data context you’ll need to create one so you can get its connection string. The DataSet input for SaveEntityDataTable was just a new DataSet with one DataTable, containing all the data from the Excel spreadsheet I uploaded into the system and had been validated.

Example of using OUTPUT INSERTED. to return inserted data

BEGIN TRAN

    DECLARE @tblSource AS TABLE 
    (
    tableId   INT,
    [Name]    VARCHAR(255), 
    [Address] VARCHAR(255), 
    [PhoneNo] VARCHAR(255)
    )

    INSERT INTO @tblSource
    (Name, [Address], PhoneNo)
    VALUES 
    ('Yatrix', '1234 Address Stuff', '1')

    INSERT INTO @tblSource
    (Name, [Address], PhoneNo)
    VALUES 
    ('Neil Delaney', '12 Address 1', '2')

    INSERT INTO @tblSource
    (Name, [Address], PhoneNo)
    VALUES 
    ('Jim Delaney', '13 Address 2', '3')


    DECLARE @tblTarget AS TABLE 
    (
    tableId   INT IDENTITY(1,1) PRIMARY KEY,
    [Name]    VARCHAR(255), 
    [Address] VARCHAR(255), 
    [PhoneNo] VARCHAR(255)
    )

    INSERT INTO @tblTarget
    (Name, [Address], PhoneNo)
    OUTPUT 
      INSERTED.tableId, 
      INSERTED.Name, 
      INSERTED.[Address], 
      INSERTED.PhoneNo
    SELECT Name, [Address], PhoneNo 
    FROM   @tblSource

ROLLBACK TRAN