JackTheRipper JackTheRipper - 1 month ago 10
C# Question

How can I do SqlBulkCopy when there is a dependency in the table?

I am able to do SqlBulkCopy when there is no dependency on the table with the code below:

//TableData and FieldData have column info in memory.
using (SqlConnection connection = new SqlConnection(@"Data Source=WLO1;Initial Catalog=GenTest2;Integrated Security=True"))
{

connection.Open();
foreach (string tableName in tablesInOrderToProcess)
{
if (tableDataList.ContainsKey(tableName))
{
TableData td = tableDataList[tableName];

SqlBulkCopy copy = new SqlBulkCopy(connection);
copy.BatchSize = 10000;
copy.DestinationTableName = tableName;

System.Data.DataTable dt = new DataTable();
foreach (FieldData fd in td.FieldList.Values)
{
string fieldName = fd.Name;
string fieldDataType = fd.DataType;
string fieldSize = fd.Size.ToString(); ;
string fieldConstant = fd.constantValue;
string fieldAverage = fd.averageSize;
string fieldPickList = fd.pickList;
copy.ColumnMappings.Add(fieldName, fieldName);
switch (fieldDataType)
{
case "char":
{
dt.Columns.Add(fieldName, System.Type.GetType("System.String"));
}
break;
case "nvarchar":
{
dt.Columns.Add(fieldName, System.Type.GetType("System.String"));
}
break;
case "number":
{
if (fd.Size == 10)
dt.Columns.Add(fieldName, System.Type.GetType("System.Int64"));
else
dt.Columns.Add(fieldName, System.Type.GetType("System.Int32"));
}
break;
default:
{
dt.Columns.Add(fieldName);
}
break;
}
}
for (int i = 0; i < int.Parse(td.NumRows); i++)
{
System.Data.DataRow r = dt.NewRow();
foreach (FieldData fd in td.FieldList.Values)
{
string fieldName = fd.Name;
string fieldDataType = fd.DataType;
string fieldSize = fd.Size.ToString(); ;
string fieldConstant = fd.constantValue;
string fieldAverage = fd.averageSize;
string fieldPickList = fd.pickList;

switch (fieldDataType)
{
case "char":
{
if (fd.averageSize.Length > 0)
{
r[fieldName] = GetRandomString(Int32.Parse(fd.averageSize), true);
}
else
{
r[fieldName] = fieldConstant;
}
}
break;
case "nvarchar":
{
if (fd.averageSize.Length > 0)
{
r[fieldName] = GetRandomString(Int32.Parse(fd.averageSize), true);
}
else
{
r[fieldName] = fieldConstant;
}
}
break;
case "number":
{
if (fd.Size == 10)
{
r[fieldName] = i;
}
else
{
r[fieldName] = i;
}
}
break;
default:
{
r[fieldName] = fieldConstant;
}
break;
}

}
dt.Rows.Add(r);
}

try
{
copy.WriteToServer(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}


However, when there is a dependency, I am confused.
dependency

1)Should I create a DataTable for each table?

2)Since the table relationship info is provided by user and loaded into memory, how can I tie tables without reading information from the schema? or Do I have to read the info and get the primary key from Schema?

Thanks in advance. I hope I made it clear.

Answer

In situations like this the only way it can be done is you do your bulk copy operations to temporary tables with no key linking the two objects (and enough metadata to build the links later).

Say you have 3 tables: #item_unit_staging, #shop_order_staging, and #shop_order_operation_staging. You would create them with the same schema as your item_unit, shop_order, and shop_order_operation with auto-generated primary keys. You also would want to add whatever data you used to link the data in memory on your local machine as a "metadata column", lets say we used a extra column called metadata_id on all three tables and a column called parent_id on #shop_order_staging and #shop_order_operation_staging.

We then bulk insert in to our 3 tables, having our primary key columns filled and our foreign key columns left NULL. Once we have the data on the server we use the metadata columns to populate the foreign key columns.

update #shop_order_staging 
    set parent_item_unit_id = #item_unit_staging.item_unit_id
    from #shop_order_staging
    inner join #item_unit_staging on #shop_order_staging.parent_id = #item_unit_staging.metadata_id

update #shop_order_operation_staging 
    set parent_shop_order_id = #shop_order_staging.shop_order_id
    from #shop_order_operation_staging
    inner join #shop_order_staging on #shop_order_operation_staging.parent_id = #shop_order_staging.metadata_id

You then can copy the data from the temporary tables in to the real tables

insert into item_unit 
    select item_unit_id
         , /*all other columns except [metadata_id]*/ 
    from #item_unit_staging


insert into shop_order 
    select shop_order_id
         , parent_item_unit_id
         , /*all other columns except metadata_id and parent_id*/ 
    from #shop_order_staging

insert into shop_order_operation 
    select shop_order_operation_id
         , parent_shop_order_id
         , /*all other columns except metadata_id and parent_id*/ 
    from #shop_order_operation_staging
Comments