Passing a DataTable to a stored procedure as an argument

Question

I have a data table created in C #.

DataTable dt = new DataTable(); dt.Columns.Add("Name", typeof(string)); dt.Columns.Add("Age", typeof(int)); dt.Rows.Add("James", 23); dt.Rows.Add("Smith", 40); dt.Rows.Add("Paul", 20); 

I want to pass this to the next stored procedure.

 CREATE PROCEDURE SomeName(@data DATATABLE) AS BEGIN INSERT INTO SOMETABLE(Column2,Column3) VALUES(......); END 

My question is: how do we insert these 3 tuples into an SQL table? do we need to access column values ​​using the dot operator? or is there any other way to do this?

Answer 1

You can modify the stored procedure to accept table value parameter as input. However, first you need to create a custom TYPE table that matches the C # DataTable structure:

 CREATE TYPE dbo.PersonType AS TABLE ( Name NVARCHAR(50), -- or whatever the length of the `SOMETABLE` column Age INT ); 

Adjust your SPROC:

 CREATE PROCEDURE dbo.InsertPerson @Person dbo.PersonType READONLY AS BEGIN INSERT INTO SomeTable(Column1, Column2) SELECT p.Name, p.Age FROM @Person p; END 

When you bind a datatable to a PROC parameter, you need to specify the parameter as:

 parameter.SqlDbType = SqlDbType.Structured; parameter.TypeName = "dbo.PersonType"; 

See also an example here. Passing a parameter with a table value to a stored procedure

Answer 2

First you need to create a user-defined type table that resembles your actual table. See the example below.

 CREATE TYPE SomeType AS TABLE ( C1 int, C2 VARCHAR(50) ) After this you need to create a stored procedure that takes this table type as parameter. CREATE PROCEDURE SomeUSP @tabType SomeType READONLY AS BEGIN SET NOCOUNT ON; INSERT INTO Yourtable(C1,C2) SELECT C1,C2 FROM @tabType END 

What is it ... Work done :)