C# How to insert a data table into SQL Server database table?

C# How to insert a data table into SQL Server database table?

In SQL 2008/2012/2014/2016/2017, user-defined table type is a user-defined type that describes the definition of a table structure.

You can use a user-defined table type to declare table-valued parameters for SP or functions or to declare table variables that you require to utilization in a batch or the body of a Stored Procedure or function.
To create a user-defined table type, use the CREATE TYPE declaration. To secure that the data in a user-defined table type matches particular requirements, you can create unique constraints and primary keys on the user-defined table type.

Create a User-Defined TableType in your database:

CREATE TYPE [dbo].[MyTableType] AS TABLE(
    [Id] int NOT NULL,
    [Name] [nvarchar](128) NULL
)

also, define a parameter in your SP:

CREATE PROCEDURE [dbo].[InsertTable] 
@myTableType MyTableType readonly
AS
BEGIN
    insert into [dbo].Records select * from @myTableType
END

--and send your DataTable directly to sql server:

using (var command = new SqlCommand("InsertTable") {CommandType = CommandType.StoredProcedure}){
    var dt = new DataTable(); //create your own data table
    command.Parameters.Add(new SqlParameter("@myTableType", dt));
    SqlHelper.Exec(command);

}

C# example that uses DataTable:

    public DataTable GetTable()
    {
        // Here we create a DataTable with 2 columns.
        DataTable table = new DataTable();
        table.Columns.Add("id", typeof(int));
        table.Columns.Add("Name", typeof(string));
 

        // Here we add five DataRows.
        table.Rows.Add(25, "Indocin");
        table.Rows.Add(50, "Enebrel");
        table.Rows.Add(10, "Hydralazine");
        table.Rows.Add(21, "Janet");
        table.Rows.Add(100, "Melanie");
        return table;
    }

To edit the values inside SP, you can declare a local variable with the same type and insert input table into it:

DECLARE @modifiableTableType MyTableType
INSERT INTO @modifiableTableType SELECT * FROM @myTableType
Then, you can edit @modifiableTableType:
UPDATE @modifiableTableType SET [Name] = 'new value'

Limitations and Restrictions

Table value constructors can be used in one of two ways: directly in the VALUES list of an INSERT … VALUES statement, or as a derived table anywhere that derived tables are allowed. Error 10738 is returned if the number of rows exceeds the maximum. To insert more rows than the limit allows, use one of the following methods:

• Create multiple INSERT statements.
• Use a derived table.
• Bulk import the data by using the bcp utility or the BULK INSERT statement.

Limitations of Table-Valued Parameters

There are several limitations to table-valued parameters:

• You cannot pass table-valued parameters to CLR user-defined functions.
• Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server does not maintain statistics on table-valued parameters.
• Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter, and you cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.
• You cannot use ALTER TABLE statements to modify the design of table-valued parameters.