Save C# DataTable SQL Server Table

Save C# DataTable SQL Server Table

 

1.    Create User Defined Data Table Type 

 

CREATE TYPE tbl_data AS TABLE
(
    InvoiceDate        NVARCHAR(50),
    InvoiceNo           VARCHAR(20),
    SKUCode           VARCHAR(10),
    ItemName          VARCHAR(50),
    Category            VARCHAR(50),
    SubCategory      VARCHAR(50) NULL,
    Qty                     INT  
) 

 

2. Pass data stored in DataTable to a stored procedure as a parameter of type DataTable.

 

3. In stored procedure 

INSERT INTO INV_DATA (InvoiceDate, InvoiceNo, SKUCode, ItemName, Category, SubCategory, Qty)
SELECT                             InvoiceDate, InvoiceNo, SKUCode, ItemName, Category, SubCategory, Qty
FROM             @data