SQL - User-Defined Type (DataTable)

How to create a User-Defined in your database and use it in C# like DataTable

Create the data type and refresh the DB

[type_name] - The name for the new data type
[parameter] - The name of your paramenter
int - The data type
NULL - Declare if the parameter has to be null or not

 CREATE TYPE [relProjectDS] AS TABLE(
 
    [idProject] int NOT NULL ,
    [idCatDomain] int NULL,
    [catSubdomain] [nvarchar]  (50) NULL
)

Create the Store procedure

CREATE PROCEDURE  [dbo].[insertProject]
    @myDataType relProjectDS readonly
AS
BEGIN
      insert into [dbo].[Table] select * from @myDataType
END

Create and fill the DataTable in C#

idProject- Name of the parameter according with data type from the DB
typeof(int) - The type definition according with the DB
Rows.Add(param1, param2) - The method to add each row to the DataTable

DataTable projectsTable = new DataTable();
projectsTable.Columns.Add("idProject", typeof(int));
projectsTable.Columns.Add("idCatDomain", typeof(int));
projectsTable.Columns.Add("idCatSubdomain", typeof(int));

projectsTable.Rows.Add(1,2,3,4,5);

Pass dataTable as parameter

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

Update dataTable in Store Procedure

In the store procedure our dataType is readonly so to modify it we can pass to a temporally table and then update the data.

In the following steps after insert in another table we get the ID and the update the ID in the temporally table.

Finally we insert the teporally table in the DB. 

1.  SET @idProject = (SELECT SCOPE_IDENTITY()) 
2.  INSERT INTO #temp_table SELECT * FROM @relProjectDS
3. UPDATE #temp_table SET idProject = @idProject 
4. INSERT INTO [dbo].[relProjectDomainSubdomain] (idProject, idCatDomain, idCatSubdomain) SELECT idProject, idCatDomain, idCatSubdomain FROM  #temp_table

 

Tips:

How to create a temporal table

create table #temp_table
    (
        [idProjectDomainSubdomain] [int] IDENTITY(1,1) NOT NULL,
        [idProject] [int] NULL,
        [idCatDomain] [int] NOT NULL,
        [idCatSubdomain] [int] NULL,
    )