c# - 如何将外键插入到 SQL Server 中的子类型表中

Question

我有一个名为 Student 的父类(super class)型表，及其名为 OtherStudents 的子类型表。我想知道如何编写一个存储过程，每次将具有学生类型 Other 的新记录插入 Student 时，将外键值插入 OtherStudents 表。

学生表:

    CREATE TABLE [dbo].[Student]
    (
        [STUD_ID] [INT] IDENTITY(1000009,1) NOT NULL,
        [STUD_NAM] [VARCHAR](30) NOT NULL,
        [STUD_EMAIL] [VARCHAR](50) NULL,
        [CAMP_NAM] [VARCHAR](50) NULL,
        [CAMP_ZIP] [INT] NULL,
        [STUD_TYP] [CHAR](5) NOT NULL,

        CONSTRAINT [PK_Student] 
            PRIMARY KEY CLUSTERED ([STUD_ID] ASC)
    )
    GO

    ALTER TABLE [dbo].[Student] WITH CHECK 
        ADD CONSTRAINT [FK_Student_Campus] 
            FOREIGN KEY([CAMP_NAM], [CAMP_ZIP]) REFERENCES [dbo].[Campus] ([CAMP_NAM], [CAMP_ZIP])
    GO

    ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_Campus]
    GO


`OtherStudents` table:

    CREATE TABLE [dbo].[OtherStudents]
    (
        [OSTUD_ID] [INT] IDENTITY(1000009,1) NOT NULL,
        [STUD_ST] [VARCHAR](30) NULL,
        [STUD_APT] [VARCHAR](5) NULL,
        [STUD_CITY] [CHAR](6) NULL,
        [STUD_STATE] [CHAR](2) NULL,
        [STUD_ZIP] [INT] NULL,
        [RPT_ATTM] [VARBINARY](4000) NULL,
        [RPT_EYESCR] [VARBINARY](4000) NULL,
        [DATE_LASTPASS] [DATE] NULL,
        [DATE_LASTVSP] [DATE] NULL,
        [STUD_ID] [INT] NULL,

        CONSTRAINT [PK_OtherStudents] 
            PRIMARY KEY CLUSTERED ([OSTUD_ID] ASC)
    )
    GO

    ALTER TABLE [dbo].[OtherStudents] WITH CHECK 
        ADD CONSTRAINT [FK_OtherStudents_Student] 
            FOREIGN KEY([STUD_ID]) REFERENCES [dbo].[Student] ([STUD_ID])
    GO

    ALTER TABLE [dbo].[OtherStudents] CHECK CONSTRAINT [FK_OtherStudents_Student]
    GO

I wrote two stored procedures:

        ALTER PROCEDURE [dbo].[BusPassStudents_Insert]
            (@STUD_ST VARCHAR(30),
             @STUD_APT VARCHAR(5),
             @STUD_CITY CHAR(6),
             @STUD_STATE CHAR(2),
             @STUD_ZIP INT,
             @RPT_ATTM AS VARBINARY(4000) = NULL,
             @RPT_EYESCR AS VARBINARY(4000) = NULL,
             @DATE_LASTPASS DATE,
             @DATE_LASTVSP AS DATE = NULL)
        AS
        BEGIN
            SET NOCOUNT ON

            INSERT INTO dbo.OtherStudents (STUD_ST, STUD_CITY, STUD_STATE, STUD_ZIP, RPT_ATTM, RPT_EYESCR,DATE_LASTPASS, DATE_LASTVSP)
            VALUES (@STUD_ST, @STUD_CITY, @STUD_STATE, @STUD_ZIP, @RPT_ATTM, @RPT_EYESCR, @DATE_LASTPASS, @DATE_LASTVSP)
        END

        ALTER PROCEDURE [dbo].[Stud_InsertNew]
            (@STUD_NAM VARCHAR(30),
             @STUD_EMAIL VARCHAR(50),
             @CAMP_NAM VARCHAR(50),
             @CAMP_ZIP INT,
             @STUD_TYP CHAR(5))
        AS
        BEGIN
            SET NOCOUNT ON

            INSERT INTO dbo.Student(STUD_NAM, STUD_EMAIL, CAMP_NAM, CAMP_ZIP, STUD_TYP)
            VALUES (@STUD_NAM, @STUD_EMAIL, @CAMP_NAM, @CAMP_ZIP, @STUD_TYP);

            DECLARE @STUD_ID INT
            SET @STUD_ID = SCOPE_IDENTITY()
            SELECT @STUD_ID
            WHERE @STUD_TYP = 'Other'

        END

这是我的 C# 代码:

int campzip = int.Parse(ddlCamp.SelectedValue.Trim());
int StudentZip = int.Parse(txtStdZip.Text);
DateTime DateLastPass = DateTime.ParseExact(txtDateLastPass.Text, "yyyy-MM-dd", null);

using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("cis-laredoConnectionString")))
{
    connection.Open();

    var cmd = new SqlCommand("dbo.Stud_InsertNew", connection);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@STUD_NAM", txtStdName.Text);
    cmd.Parameters.AddWithValue("@STUD_EMAIL", txtStdEmail.Text);
    cmd.Parameters.AddWithValue("@CAMP_NAM", ddlCamp.SelectedItem.ToString());
    cmd.Parameters.AddWithValue("@CAMP_ZIP", campzip);
    cmd.Parameters.AddWithValue("@STUD_TYP", "Other");

    int getStudID = (int) cmd.ExecuteScalar();
    cmd.Dispose();

    connection.Close();

    connection.Open();
    var cmd2 = new SqlCommand("BusPassStudents_Insert", connection);
    cmd2.CommandType = CommandType.StoredProcedure;

    cmd2.Parameters.AddWithValue("@STUD_ID", getStudID);
    cmd2.Parameters.AddWithValue("@STUD_ST", txtStdStreet.Text);
    cmd2.Parameters.AddWithValue("@STUD_APT", txtStdApt.Text);
    cmd2.Parameters.AddWithValue("@STUD_CITY", txtStdCity.Text);
    cmd2.Parameters.AddWithValue("@STUD_STATE", txtStdState.Text);
    cmd2.Parameters.AddWithValue("@STUD_ZIP", StudentZip);
    cmd2.Parameters.AddWithValue("@RPT_ATTM", fuAttend.FileBytes);
    cmd2.Parameters.Add("@DATE_LASTPASS", SqlDbType.Date).Value = DateLastPass;

    cmd2.ExecuteNonQuery();
    cmd2.Dispose();

    connection.Close();
}

我正在尝试使用 ExecuteScalar 检索 Student 表中插入的 STUD_ID 并将其发回 OtherStudents表。

我遇到错误:

System.InvalidCastException: 'Specified cast is not valid.'

如有任何帮助，我们将不胜感激!

Answer 1

你必须从你的存储过程中选择一些东西来获得你错过的 C# 中的标量值，所以在你的 Stud_InsertNew 中你可以选择 STUD_ID 并使用ExecuteScalar()在 C# 代码中为:

int getStudID = (int)cmd.ExecuteScalar();

存储过程的变化:

ALTER PROCEDURE [dbo].[Stud_InsertNew]
    (@STUD_NAM VARCHAR(30),
     @STUD_EMAIL VARCHAR(50),
     @CAMP_NAM VARCHAR(50),
     @CAMP_ZIP INT,
     @STUD_TYP CHAR(5))
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO dbo.Student(STUD_NAM, STUD_EMAIL, CAMP_NAM, CAMP_ZIP, STUD_TYP)
    VALUES (@STUD_NAM, @STUD_EMAIL, @CAMP_NAM, @CAMP_ZIP, @STUD_TYP);

    // Need this line which returns the latest inserted Student Id
    SELECT TOP 1 [STUD_ID]
    FROM dbo.Student
    ORDER BY [STUD_ID] DESC
END

来自 Mitch Wheat 的评论后还找到了here ，如果你想获得最新插入的记录主键的值，那么使用 SCOPE_IDENTIY() 的最佳方法，所以你只需要将 SELECT 语句更改为:

DECLARE @STUD_ID INT
SET @STUD_ID = SCOPE_IDENTITY()
SELECT @STUD_ID