SqlBulkCopy WriteToServer example. What am I doing wrong?

Question

This might be long, but I want to explain my example

I got this Code:

        #region [parent table]
        DataTable dtParent = new DataTable();
        DataColumn dc;

        dc = new DataColumn();
        dc.DataType = System.Type.GetType("System.Int32");
        dc.ColumnName = "Id";
        dc.Unique = true;
        dc.AutoIncrement = true;
        dtParent.Columns.Add(dc);

        dc = new DataColumn();
        dc.DataType = System.Type.GetType("System.String");
        dc.ColumnName = "Title";
        dc.Unique = false;
        dtParent.Columns.Add(dc);

        dtParent.TableName = "aTestSw";
        dtParent.PrimaryKey = new DataColumn[] { dtParent.Columns[0] };
        #endregion

        #region [child table]
        DataTable dtChild = new DataTable();

        dc = new DataColumn();
        dc.DataType = System.Type.GetType("System.Int32");
        dc.ColumnName = "Id";
        dc.Unique = true;
        dc.AutoIncrement = true;
        dtChild.Columns.Add(dc);

        dc = new DataColumn();
        dc.DataType = System.Type.GetType("System.Int32");
        dc.ColumnName = "ParentId";
        dc.Unique = false;
        dtChild.Columns.Add(dc);

        dc = new DataColumn();
        dc.DataType = System.Type.GetType("System.String");
        dc.ColumnName = "Description";
        dc.Unique = false;
        dtChild.Columns.Add(dc);

        dtChild.TableName = "aTestSwChild";
        dtChild.PrimaryKey = new DataColumn[] { dtChild.Columns[0] };
        #endregion

        DataSet DataSet1 = new DataSet();
        DataSet1.Tables.Add(dtParent);
        DataSet1.Tables.Add(dtChild);

        #region [fk]
        DataColumn parentColumn = dtParent.Columns["Id"];
        DataColumn childColumn = dtChild.Columns["ParentId"];
        DataRelation relParentChild = new DataRelation("ParentChild", parentColumn, childColumn);
        DataSet1.Relations.Add(relParentChild); 
        #endregion

        #region [fill parent]
        DataRow dr2saveIn = dtParent.NewRow();
        dr2saveIn["Title"] = "a";
        dtParent.Rows.Add(dr2saveIn);

        dr2saveIn = dtParent.NewRow();
        dr2saveIn["Title"] = "b";
        dtParent.Rows.Add(dr2saveIn);
        #endregion

        #region [fill child]
        dr2saveIn = dtChild.NewRow();
        dr2saveIn["Description"] = "c";
        dr2saveIn["ParentId"] = dtParent.Rows[0]["Id"];
        dtChild.Rows.Add(dr2saveIn);

        dr2saveIn = dtChild.NewRow();
        dr2saveIn["Description"] = "d";
        dr2saveIn["ParentId"] = dtParent.Rows[1]["Id"];
        dtChild.Rows.Add(dr2saveIn);   
        #endregion

        SqlBulkCopy bulkCopy = new SqlBulkCopy("server=aaa;database=bbb;uid=ccc;password=ddd", SqlBulkCopyOptions.TableLock);
        bulkCopy.DestinationTableName = "dbo.aTestSw";
        bulkCopy.WriteToServer(dtParent);
        bulkCopy.DestinationTableName = "dbo.aTestSwChild";
        bulkCopy.WriteToServer(dtChild);

When I run it, I got data that "looks" correct in the FK column of the child table. (0 and 1) The thing is, that after save, the data gets in the database, but it's not all right. Instead of having the FK id from the parent table, I got the 0 and the 1 from the C# code. Note: the tables in the DB DO HAVE FK Constrain on them. You cannot add manually that stuff, but SqlBulkCopy WriteToServer has no problem, writes wrong data anyways (it's like it's not checked for constraints)

Look the tables:

CREATE TABLE [dbo].[aTestSw](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Title] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_aTestSw] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[aTestSwChild](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [ParentId] [int] NOT NULL,
 [Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_aTestSwChild] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[aTestSwChild]  WITH NOCHECK ADD  CONSTRAINT [FK_aTestSwChild_aTestSw] FOREIGN KEY([ParentId])
REFERENCES [dbo].[aTestSw] ([Id])

ALTER TABLE [dbo].[aTestSwChild] CHECK CONSTRAINT [FK_aTestSwChild_aTestSw]

The results look like this:

select * from aTestSw Id Title 16 a 17 b

select * from aTestSwChild Id ParentId Description 12 0 c 13 1 d

What am I doing wrong? Probably i am not supposed to get Id's like this in C#. But how? Or is it even possible? I mean how is my second bulkinsert going to know the SQL!!!! id on insert?

Answer 1

use of this option:

SqlBulkCopyOptions.KeepIdentity

before the bulk copy action set CheckConstraints of your db to OFF. after this insert your data using bulk copy. when all of your work have been done, set CheckConstraints to ON.

the upside option force your db to keep your identity values.