Bulk Insert into multiple tables - C#,Sql

Question

I’ve 2 tables ClassMaterial and ClassSubMatRelation.

ClassMaterial           
[ClassMaterialID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[Description] [nvarchar](1000) NULL,
[EbookLink] [nvarchar](500) NULL,
[Status_Info] [smallint] NULL DEFAULT ('1'),
[SEOTitle] [nvarchar](100) NULL,
[SEOKeyword] [nvarchar](50) NULL,
[SEODesc] [nvarchar](500) NULL

ClassSubMatRelation
[ClassSubMatRelationID] [int] IDENTITY(1,1) NOT NULL,
[BoardFK] [int] NULL,
[ClassFK] [int] NULL,
[ClassSubjectFK] [int] NOT NULL,
[MaterialTypeFK] [int] NOT NULL,
[ClassMaterialFK] [int] NOT NULL,
[Status_Info] [smallint] NOT NULL

I’m saving multiple records in ClassMaterial c# as :-

foreach (var eachMaterial in value.materialModel)
{
 DataRow dr = dt.NewRow();
 dr[Constants.Name] = eachMaterial.Heading;
 dr[Constants.Description] = eachMaterial.Descr;
 dr[Constants.EbookLink] = eachMaterial.Ebook;
 dr[Constants.SEODesc] = eachMaterial.SEODesc;
 dr[Constants.SEOTitle] = eachMaterial.SEOTitle;
 dr[Constants.SEOKeyword] = eachMaterial.SEOKeyword;

 dt.Rows.Add(dr);
}


using (SqlBulkCopy SQLbulk = new SqlBulkCopy(sqlcon))
 {
    SQLbulk.ColumnMappings.Add(Constants.Name, Constants.Name);
       SQLbulk.ColumnMappings.Add(Constants.Description, Constants.Description);
       SQLbulk.ColumnMappings.Add(Constants.EbookLink, Constants.EbookLink);
       SQLbulk.ColumnMappings.Add(Constants.SEODesc, Constants.SEODesc);
       SQLbulk.ColumnMappings.Add(Constants.SEOTitle, Constants.SEOTitle);
       SQLbulk.ColumnMappings.Add(Constants.SEOKeyword, Constants.SEOKeyword);
       SQLbulk.DestinationTableName = value.aBundleInsert.MainTblName;
       SQLbulk.WriteToServer(dt);
 }

My problem is that in ClassSubMatRelation I’ve column ClassMaterialFK which is a foreign Key for ClassMaterialID column in ClassMaterial. So I want to save ClassMaterialIDs of all new records inserted in ClassMaterial with other records into ClassSubMatRelation. I’ve gone through this ques. It suggest to use the OUTPUT clause but it will require a lot of code change. Also I can't understand this ques. Is there any other way of doing this? Thanks in advance.

EDIT

So with help of @Zohar I achieved this what I want:-

CREATE TYPE udt_classMaterial_base As Table
(
    classMaterial_id int,
    base_classMaterial_temp_id int,  
    [Name] [nvarchar](100) NOT NULL,
    [Description] [nvarchar](1000) NULL,
    [EbookLink] [nvarchar](500) NULL,
    [Status_Info] [smallint] NULL DEFAULT ('1'),
    [SEOTitle] [nvarchar](100) NULL,
    [SEOKeyword] [nvarchar](50) NULL,
    [SEODesc] [nvarchar](500) NULL
);

CREATE TYPE udt_classSubMatRel_related As Table
(
    related_classSubMatRel_base_id int,
    [BoardFK] [int] NULL,
    [ClassFK] [int] NULL,
    [ClassSubjectFK] [int] NOT NULL,
    [MaterialTypeFK] [int] NOT NULL,
    [ClassMaterialFK] [int] NOT NULL,
    [Status_Info] [smallint] NOT NULL
);

CREATE TYPE udt_classMaterial_idMap as table
(
    temp_id int,
    id int
)
GO

and my procedure goes like:-

ALter PROCEDURE stp_InsertMultipleRecordsToMultipleTables
(
    @base as dbo.udt_classMaterial_base readonly,
    @related as dbo.udt_classSubMatRel_related readonly,
    @ReturnBool int out
)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

BEGIN TRY
BEGIN TRANSACTION;

DECLARE @idMap as dbo.udt_classMaterial_idMap
MERGE INTO ClassMaterial USING @base AS temp ON 1 = 0 -- Always not matched
WHEN NOT MATCHED THEN
    INSERT (Name,Description,EbookLink,SEOTitle,SEOKeyword,SEODesc)
    VALUES (temp.Name,temp.Description,temp.EbookLink,temp.SEOTitle,temp.SEOKeyword,temp.SEODesc)
    OUTPUT temp.base_classMaterial_temp_id, inserted.ClassMaterialID -- Here we use the base_temp_id to map to the correct id
    INTO @idMap (temp_id, id);

INSERT INTO ClassSubMatRelation(BoardFK, ClassFK,ClassMaterialFK,ClassSubjectFK,MaterialTypeFK)
SELECT BoardFK, ClassFK, id, ClassSubjectFK,MaterialTypeFK
FROM @related r
INNER JOIN @idMap m ON(r.related_classSubMatRel_base_id = m.temp_id) -- here we use the map to insert the related records with the correct base ids
SET @ReturnBool = 1;

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF (XACT_STATE()) = -1  
    BEGIN  
        SET @ReturnBool = 0;
        ROLLBACK TRANSACTION;  
    END;  

    IF (XACT_STATE()) = 1  
    BEGIN  
        COMMIT TRANSACTION;     
    END;  
END CATCH

Return @ReturnBool

END GO

And The C# Method:-

public int BulkInsertBoard(RequestData value)
    {
        int ReturnBool = 0;
        try
        {
            DataTable dtRel = new DataTable();
            dtRel.Columns.Add("related_classSubMatRel_base_id");
            dtRel.Columns.Add("BoardFK");
            dtRel.Columns.Add("ClassFK");
            dtRel.Columns.Add("ClassSubjectFK");
            dtRel.Columns.Add("MaterialTypeFK");
            dtRel.Columns.Add("ClassMaterialFK");
            dtRel.Columns.Add(Constants.StatusInfo);

            DataTable dt = new DataTable();
            dt.Columns.Add("classMaterial_id");
            dt.Columns.Add("base_classMaterial_temp_id");
            dt.Columns.Add(Constants.Name);
            dt.Columns.Add(Constants.Description);
            dt.Columns.Add(Constants.EbookLink);
            dt.Columns.Add(Constants.StatusInfo);
            dt.Columns.Add(Constants.SEOTitle);
            dt.Columns.Add(Constants.SEOKeyword);
            dt.Columns.Add(Constants.SEODesc);


            using (var sqlcon = new SqlConnection(Constants.Connection))
            {
                SqlDataAdapter adapter = new SqlDataAdapter("DECLARE @udt_classMaterial_base dbo.udt_classMaterial_base SELECT * FROM @udt_classMaterial_base", sqlcon);
                adapter.FillSchema(dt, SchemaType.Source);

                foreach (var eachMaterial in value.materialModel)
                {
                    DataRow dr = dt.NewRow();
                    if (dt.Columns.Contains("classMaterial_id") && dt.Columns["classMaterial_id"].DataType == typeof(int))
                        dr["classMaterial_id"] = 0;
                    if (dt.Columns.Contains("base_classMaterial_temp_id"))
                        dr["base_classMaterial_temp_id"] = 0;
                    dr[Constants.Name] = eachMaterial.Heading;
                    dr[Constants.Description] = eachMaterial.Descr;
                    if (dt.Columns.Contains(Constants.EbookLink) && dt.Columns[Constants.EbookLink].DataType == typeof(string))
                        dr[Constants.EbookLink] = eachMaterial.Ebook;
                    dr[Constants.StatusInfo] = Convert.ToInt16(1);
                    if (!string.IsNullOrEmpty(eachMaterial.SEODesc))
                        dr[Constants.SEODesc] = eachMaterial.SEODesc;
                    if (!string.IsNullOrEmpty(eachMaterial.SEOTitle))
                        dr[Constants.SEOTitle] = eachMaterial.SEOTitle;
                    if (!string.IsNullOrEmpty(eachMaterial.SEOKeyword))
                        dr[Constants.SEOKeyword] = eachMaterial.SEOKeyword;

                    dt.Rows.Add(dr);
                }

                SqlDataAdapter adapterRel = new SqlDataAdapter("DECLARE @udt_classSubMatRel_related dbo.udt_classSubMatRel_related SELECT * FROM @udt_classSubMatRel_related", sqlcon);
                adapterRel.FillSchema(dtRel, SchemaType.Source);


                DataRow drRel = dtRel.NewRow();
                if (dtRel.Columns.Contains("related_classSubMatRel_base_id"))
                    drRel["related_classSubMatRel_base_id"] = 0;
                drRel["BoardFK"] = value.aBundleInsert.BoardFK;
                drRel["ClassFK"] = value.aBundleInsert.ClassesFK;
                drRel["ClassSubjectFK"] = value.aBundleInsert.ClassSubjectFK;
                drRel["MaterialTypeFK"] = value.aBundleInsert.MaterialIndicator;
                if (dtRel.Columns.Contains("ClassMaterialFK"))
                    drRel["ClassMaterialFK"] = value.aBundleInsert.MaterialFetchID;
                drRel[Constants.StatusInfo] = Convert.ToInt16(1);

                dtRel.Rows.Add(drRel);

                using (SqlCommand cmd = new SqlCommand(Constants.ProcNameBulkInsertBoard))
                {
                    cmd.Connection = sqlcon;
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (sqlcon.State == System.Data.ConnectionState.Closed)
                    {
                        sqlcon.Open();
                    }
                    cmd.Parameters.AddWithValue("@base", dt);
                    cmd.Parameters.AddWithValue("@related", dtRel);
                    SqlParameter outputParam = new SqlParameter();
                    outputParam.ParameterName = "@ReturnBool";
                    outputParam.SqlDbType = System.Data.SqlDbType.Int;
                    outputParam.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(outputParam);

                    cmd.ExecuteNonQuery();

                    if (!cmd.Parameters["@ReturnBool"].Value.Equals(DBNull.Value))
                    {
                        ReturnBool = Convert.ToInt32(cmd.Parameters["@ReturnBool"].Value);
                    }

                }
            }
            //using (SqlBulkCopy SQLbulk = new SqlBulkCopy(sqlcon))
            //{
            //    SQLbulk.ColumnMappings.Add(Constants.Name, Constants.Name);
            //    SQLbulk.ColumnMappings.Add(Constants.Description, Constants.Description);
            //    SQLbulk.ColumnMappings.Add(Constants.EbookLink, Constants.EbookLink);
            //    SQLbulk.ColumnMappings.Add(Constants.SEODesc, Constants.SEODesc);
            //    SQLbulk.ColumnMappings.Add(Constants.SEOTitle, Constants.SEOTitle);
            //    SQLbulk.ColumnMappings.Add(Constants.SEOKeyword, Constants.SEOKeyword);
            //    SQLbulk.DestinationTableName = value.aBundleInsert.MainTblName;
            //    SQLbulk.WriteToServer(dt);
            //}
        }
        catch (Exception ex)
        {
            string error = ex.Message;
            return ReturnBool;
            //return new JsonResult { Data = ex.Message, JsonRequestBehavior=JsonRequestBehavior.AllowGet };
        }

        return ReturnBool;
    }

Hope this will help someone in need.

The answer in the dupe does exactly that - insert multiple parent records and their multiple child records. — Zohar Peled, Apr 15 '18 at 04:25
Thanks @ZoharPeled for the guidance. I'm still not able to solve this completely. :( — Deepak, Apr 15 '18 at 13:14
I've added another link to the dupe list, to a simpler version - perhaps it will help — Zohar Peled, Apr 16 '18 at 04:34
Actually data is saved in 1st table (ClassMaterial). But not in 2nd table (ClassSubMatRelation). Please See my modified question. — Deepak, Apr 16 '18 at 06:09
Instead of `SELECT BoardFK, ClassFK,temp_id, ClassSubjectFK,MaterialTypeFK` you should do `SELECT BoardFK, ClassFK, id, ClassSubjectFK,MaterialTypeFK` — Zohar Peled, Apr 16 '18 at 06:48
[Glad to help :-)](http://meta.stackoverflow.com/questions/291325/how-to-show-appreciation-to-a-user-on-stackoverflow/291327#291327) — Zohar Peled, Apr 16 '18 at 07:37

Bulk Insert into multiple tables - C#,Sql

0 Answers0