C#与SQL实现多表批量插入：基于ClassMaterial和ClassSubMatRelation表

Alright, let's break down how to handle bulk inserts across two related tables (ClassMaterial and ClassSubMatRelation) in C# and SQL. The tricky part here is capturing the auto-generated ClassMaterialID values from the first table and mapping them correctly to the second table's foreign key references. Below are two robust approaches depending on your needs:

First, let's clarify: ClassMaterial is our parent table with an identity column (ClassMaterialID), and ClassSubMatRelation is the child/association table that needs to reference this ID (I'll assume there's a ClassMaterialFK column in ClassSubMatRelation since it's an association table—adjust if your actual schema uses a different name). Our goal is to batch insert multiple parent records, capture their generated IDs, then batch insert the corresponding child records with those IDs.

This approach leverages SqlBulkCopy for fast bulk inserts, plus a temporary table and OUTPUT clause to capture the identity IDs we need for mapping.

2.1 Step 1: Define C# Entities #

Start with simple classes to represent your table data (skip the identity column since SQL generates it):

public class ClassMaterial
{
    public string Name { get; set; }
    public string Description { get; set; }
    public string EbookLink { get; set; }
    public short? Status_Info { get; set; }
    public string SEOTitle { get; set; }
    public string SEOKeyword { get; set; }
    public string SEODesc { get; set; }
}

public class ClassSubMatRelation
{
    public int BoardFK { get; set; }
    public int ClassFK { get; set; }
    public int ClassSubjectFK { get; set; }
    public int ClassMaterialFK { get; set; } // Will be populated after parent insert
}

2.2 Step 2: Bulk Insert Parent & Capture IDs #

Here's the full C# code to handle the bulk insert and ID mapping, wrapped in a transaction for safety:

var classMaterialsList = new List<ClassMaterial>(); // Populate your parent data here
var classSubMatRelationsList = new List<ClassSubMatRelation>(); // Populate child data (leave ClassMaterialFK empty)

using (var connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        try
        {
            // 1. Create temp table to hold parent data (with row number for mapping)
            var createTempTableSql = @"
                CREATE TABLE #TempClassMaterial (
                    Name NVARCHAR(100) NOT NULL,
                    Description NVARCHAR(1000) NULL,
                    EbookLink NVARCHAR(500) NULL,
                    Status_Info SMALLINT NULL,
                    SEOTitle NVARCHAR(100) NULL,
                    SEOKeyword NVARCHAR(50) NULL,
                    SEODesc NVARCHAR(500) NULL,
                    RowNumber INT IDENTITY(1,1) NOT NULL
                )";
            using (var cmd = new SqlCommand(createTempTableSql, connection, transaction))
            {
                cmd.ExecuteNonQuery();
            }

            // 2. Bulk copy parent data into temp table
            using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
            {
                bulkCopy.DestinationTableName = "#TempClassMaterial";
                // Map columns explicitly to avoid mismatches
                bulkCopy.ColumnMappings.Add("Name", "Name");
                bulkCopy.ColumnMappings.Add("Description", "Description");
                bulkCopy.ColumnMappings.Add("EbookLink", "EbookLink");
                bulkCopy.ColumnMappings.Add("Status_Info", "Status_Info");
                bulkCopy.ColumnMappings.Add("SEOTitle", "SEOTitle");
                bulkCopy.ColumnMappings.Add("SEOKeyword", "SEOKeyword");
                bulkCopy.ColumnMappings.Add("SEODesc", "SEODesc");

                // Convert parent list to DataTable
                var parentTable = new DataTable();
                parentTable.Columns.Add("Name", typeof(string));
                parentTable.Columns.Add("Description", typeof(string));
                parentTable.Columns.Add("EbookLink", typeof(string));
                parentTable.Columns.Add("Status_Info", typeof(short));
                parentTable.Columns.Add("SEOTitle", typeof(string));
                parentTable.Columns.Add("SEOKeyword", typeof(string));
                parentTable.Columns.Add("SEODesc", typeof(string));

                foreach (var material in classMaterialsList)
                {
                    parentTable.Rows.Add(
                        material.Name,
                        material.Description,
                        material.EbookLink,
                        material.Status_Info ?? (short)1, // Use default if null
                        material.SEOTitle,
                        material.SEOKeyword,
                        material.SEODesc
                    );
                }

                bulkCopy.WriteToServer(parentTable);
            }

            // 3. Insert from temp table to ClassMaterial, capture generated IDs
            var insertAndGetIdsSql = @"
                INSERT INTO ClassMaterial (Name, Description, EbookLink, Status_Info, SEOTitle, SEOKeyword, SEODesc)
                OUTPUT inserted.ClassMaterialID, t.RowNumber
                SELECT Name, Description, EbookLink, ISNULL(Status_Info, 1), SEOTitle, SEOKeyword, SEODesc
                FROM #TempClassMaterial t";
            var idMap = new Dictionary<int, int>();
            using (var cmd = new SqlCommand(insertAndGetIdsSql, connection, transaction))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int materialId = reader.GetInt32(0);
                        int rowNumber = reader.GetInt32(1);
                        idMap[rowNumber] = materialId;
                    }
                }
            }

            // 4. Map IDs to child records
            for (int i = 0; i < classSubMatRelationsList.Count; i++)
            {
                // Assumes child list is in the same order as parent list
                classSubMatRelationsList[i].ClassMaterialFK = idMap[i + 1]; // RowNumber starts at 1
            }

            // 5. Bulk insert child records
            using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
            {
                bulkCopy.DestinationTableName = "ClassSubMatRelation";
                bulkCopy.ColumnMappings.Add("BoardFK", "BoardFK");
                bulkCopy.ColumnMappings.Add("ClassFK", "ClassFK");
                bulkCopy.ColumnMappings.Add("ClassSubjectFK", "ClassSubjectFK");
                bulkCopy.ColumnMappings.Add("ClassMaterialFK", "ClassMaterialFK");

                // Convert child list to DataTable
                var childTable = new DataTable();
                childTable.Columns.Add("BoardFK", typeof(int));
                childTable.Columns.Add("ClassFK", typeof(int));
                childTable.Columns.Add("ClassSubjectFK", typeof(int));
                childTable.Columns.Add("ClassMaterialFK", typeof(int));

                foreach (var relation in classSubMatRelationsList)
                {
                    childTable.Rows.Add(
                        relation.BoardFK,
                        relation.ClassFK,
                        relation.ClassSubjectFK,
                        relation.ClassMaterialFK
                    );
                }

                bulkCopy.WriteToServer(childTable);
            }

            // Clean up and commit
            using (var cmd = new SqlCommand("DROP TABLE #TempClassMaterial", connection, transaction))
            {
                cmd.ExecuteNonQuery();
            }
            transaction.Commit();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            throw; // Handle error as needed
        }
    }
}

This approach centralizes the insert logic in a SQL stored procedure, using TVPs to pass bulk data from C#. It's great if you need to add business logic to the insert process.

3.1 Step 1: Create SQL Table Types #

First, define custom table types in SQL to match your bulk data:

-- Type for ClassMaterial bulk data
CREATE TYPE dbo.ClassMaterialType AS TABLE (
    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,
    RowNumber INT NOT NULL -- Used to map to child records
);

-- Type for ClassSubMatRelation bulk data
CREATE TYPE dbo.ClassSubMatRelationType AS TABLE (
    BoardFK INT NULL,
    ClassFK INT NULL,
    ClassSubjectFK INT NULL,
    RowNumber INT NOT NULL -- Matches RowNumber in ClassMaterialType
);

3.2 Step 2: Create the Stored Procedure #

This procedure handles both inserts and ID mapping in one transaction:

CREATE PROCEDURE dbo.BulkInsertClassMaterialsAndRelations
    @ClassMaterials dbo.ClassMaterialType READONLY,
    @ClassSubMatRelations dbo.ClassSubMatRelationType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    BEGIN TRY
        -- Temp table to capture inserted parent IDs and their row numbers
        CREATE TABLE #InsertedMaterials (
            ClassMaterialID INT NOT NULL,
            RowNumber INT NOT NULL
        );

        -- Insert parent records and capture IDs
        INSERT INTO ClassMaterial (Name, Description, EbookLink, Status_Info, SEOTitle, SEOKeyword, SEODesc)
        OUTPUT inserted.ClassMaterialID, t.RowNumber INTO #InsertedMaterials
        SELECT 
            Name, 
            Description, 
            EbookLink, 
            ISNULL(Status_Info, 1), 
            SEOTitle, 
            SEOKeyword, 
            SEODesc
        FROM @ClassMaterials t;

        -- Insert child records with mapped parent IDs
        INSERT INTO ClassSubMatRelation (BoardFK, ClassFK, ClassSubjectFK, ClassMaterialFK)
        SELECT 
            r.BoardFK, 
            r.ClassFK, 
            r.ClassSubjectFK, 
            m.ClassMaterialID
        FROM @ClassSubMatRelations r
        INNER JOIN #InsertedMaterials m ON r.RowNumber = m.RowNumber;

        DROP TABLE #InsertedMaterials;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW; -- Propagate error to C#
    END CATCH
END;

3.3 Step 3: Call the Procedure from C# #

Pass the bulk data as TVPs to the stored procedure:

var classMaterialsList = new List<ClassMaterial>(); // Populate parent data
var classSubMatRelationsList = new List<ClassSubMatRelation>(); // Populate child data

using (var connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();
    using (var cmd = new SqlCommand("dbo.BulkInsertClassMaterialsAndRelations", connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        // Prepare parent TVP
        var parentTable = new DataTable();
        parentTable.Columns.Add("Name", typeof(string));
        parentTable.Columns.Add("Description", typeof(string));
        parentTable.Columns.Add("EbookLink", typeof(string));
        parentTable.Columns.Add("Status_Info", typeof(short));
        parentTable.Columns.Add("SEOTitle", typeof(string));
        parentTable.Columns.Add("SEOKeyword", typeof(string));
        parentTable.Columns.Add("SEODesc", typeof(string));
        parentTable.Columns.Add("RowNumber", typeof(int));

        for (int i = 0; i < classMaterialsList.Count; i++)
        {
            var material = classMaterialsList[i];
            parentTable.Rows.Add(
                material.Name,
                material.Description,
                material.EbookLink,
                material.Status_Info ?? (short)1,
                material.SEOTitle,
                material.SEOKeyword,
                material.SEODesc,
                i + 1
            );
        }

        // Prepare child TVP
        var childTable = new DataTable();
        childTable.Columns.Add("BoardFK", typeof(int));
        childTable.Columns.Add("ClassFK", typeof(int));
        childTable.Columns.Add("ClassSubjectFK", typeof(int));
        childTable.Columns.Add("RowNumber", typeof(int));

        for (int i = 0; i < classSubMatRelationsList.Count; i++)
        {
            var relation = classSubMatRelationsList[i];
            childTable.Rows.Add(
                relation.BoardFK,
                relation.ClassFK,
                relation.ClassSubjectFK,
                i + 1 // Match parent's RowNumber
            );
        }

        // Add TVP parameters
        cmd.Parameters.Add(new SqlParameter("@ClassMaterials", SqlDbType.Structured)
        {
            Value = parentTable,
            TypeName = "dbo.ClassMaterialType"
        });

        cmd.Parameters.Add(new SqlParameter("@ClassSubMatRelations", SqlDbType.Structured)
        {
            Value = childTable,
            TypeName = "dbo.ClassSubMatRelationType"
        });

        // Execute
        cmd.ExecuteNonQuery();
    }
}

• Transactions: Always wrap both inserts in a transaction to ensure data consistency—if one insert fails, both roll back.
• Column Mapping: Double-check column names and data types between C# DataTables and SQL tables/types to avoid runtime errors.
• Performance: Both methods are far faster than row-by-row inserts. SqlBulkCopy is ideal for 10k+ records, while TVPs are better for smaller batches with business logic.
• Default Values: Use ISNULL in SQL or handle defaults in C# to ensure columns like Status_Info get their default values when null.

内容的提问来源于stack exchange，提问作者Deepak