Send DataTable as Stored Procedure Paramater

We come across many situations where we need to insert all the records of a DataTable(dt) in a table having same structure as that of the dt. In my case, I was to read excel file and create in-memory dt. This dt resembles a table (dbo.tbl) present in my DB. Now After my dt is ready I was suppose to dump my all the records in dbo.tbl. Though you can do it using loop followed by insert statement but the is another way which is very fast, reliable and efficient too.

I will create my own type in sql using this query:


CREATE TYPE myTableType AS TABLE
(
-- Here you are suppose to mention all of your column names of dbo.tbl (mentioned above)
 -- Please note that you need not to declare any column which has identity 'yes', If you do so, will throw error
   -- do not include such columns having identity [QuestionColumnTableJoinID] [int] IDENTITY(1,1) NOT NULL 
-- include these columns to create your type
    [QuestionId] [varchar](10) NOT NULL,
    [QuestionDesc] [varchar](2000) NOT NULL,
    [TableName] [varchar](50) NULL,
    [ColumnName] [varchar](50) NULL,
    [ColumnDataType] [varchar](20) NULL,
    [IsRequired] [bit] NULL,
    [IsMandatory] [bit] NULL,
    [CreatedDate] [datetime] NULL,
    [IsDeleted] [bit] NOT NULL
)


After Creating the type you need to write a Stored Procedure which has parameter of type : myTableType which we created above.

CREATE PROCEDURE [dbo].[spInsertQuestionTable]
(
    @varTableQuestion dbo.myTableType READONLY
)

AS
BEGIN
    BEGIN TRAN
        INSERT INTO dbo.QuestionTable

        SELECT * FROM @varTableQuestion     
    COMMIT
END


Now your are done with your DB side.
Lets move on to code page, from where we want to call this SP.

                SqlCommand cmd = new SqlCommand("spInsertQuestionTable",con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@varTableQuestion", SqlDbType.Structured);
                cmd.Parameters["@varTableQuestion"].Value = dtQuestions;
                con.open();
                cmd.ExecuteNonQuery();
                con.close();

Note that you have to define your parameter type as SqlDbType.Structured and you are done. All the data inserted in one short.