c# - 使用 SqlDatRecord 结果列表插入行 数据库中的外键错误

Question

我正在尝试使用 #SqlDataRecord 和 #MetaData 的技术将记录列表插入数据库。在一切正常的情况下，我遇到了外键约束的问题：

首先如您所知，我们在 sql 中创建 UserType：

USE [db_Visitors]
GO

/****** Object:  UserDefinedTableType [dbo].[type_PathType]    Script Date: 9/7/2018 10:14:29 AM ******/
DROP TYPE [dbo].[type_PathType]
GO

/****** Object:  UserDefinedTableType [dbo].[type_PathType]    Script Date: 9/7/2018 10:14:29 AM ******/
CREATE TYPE [dbo].[type_PathType] AS TABLE(
    [GateID] [int] NULL,
    [VisitRequestID] [int] NULL,
    [VisitorID] [int] NULL,
    [Confirmation] [bit] NULL,
    [Finished] [bit] NULL
)
GO

第二：我们创建存储过程：

USE [db_Visitors]
GO
/****** Object:  StoredProcedure [dbo].[AddTypePathList]    Script Date: 9/7/2018 9:16:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AddTypePathList]




    @TBL_PathType type_PathType READONLY
    AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO TBL_Path 
    (GateID , VisitRequestID , VisitorID , Confirmation , Finished)
    SELECT 
     pat.GateID , 
     pat.VisitRequestID ,
      pat.VisitorID , 
      pat.Confirmation , 
      pat.Finished FROM @TBL_PathType pat
END

第三：：后端数据访问：创建 SqlDataRecords 列表：

   private List<SqlDataRecord> SqlList_TypePath(List<TypePath> PathTypeList)
       {
           List<SqlDataRecord> SqlDataRecordList = new List<SqlDataRecord>();
           SqlMetaData[] metadata = new SqlMetaData[5];
           metadata[0] = new SqlMetaData("GateID", SqlDbType.Int);
           metadata[1] = new SqlMetaData("VisitRequestID", SqlDbType.Int);
           metadata[2] = new SqlMetaData("VisitorID", SqlDbType.Int);
           metadata[3] = new SqlMetaData("Confirmation", SqlDbType.Bit);
           metadata[4] = new SqlMetaData("Finished", SqlDbType.Bit);
           foreach (TypePath path in PathTypeList)
           {
               SqlDataRecord record = new SqlDataRecord(metadata);
               TypePath pathType = new TypePath();
               record.SetInt32(0, pathType.GateID);
               record.SetInt32(1, pathType.VisitRequestID);
               record.SetInt32(2, pathType.VisitorID);
               record.SetBoolean(3, pathType.Confirmation);
               record.SetBoolean(4, pathType.Finished);
               SqlDataRecordList.Add(record);
           }
           return SqlDataRecordList;
       }

第四：连接-命令-参数-执行：

 private void _Command_AddTypePath(List<TypePath> PathTypeList)
       {
           string procedureName = "AddTypePathList";
           string ConnectionString = ConfigurationManager.ConnectionStrings["VisitorConnectionString"].ConnectionString;
           SqlConnection connection = new SqlConnection(ConnectionString);
           SqlCommand command = new SqlCommand(procedureName, connection);
           command.CommandType = CommandType.StoredProcedure;
           if (connection.State != ConnectionState.Open)
           {
               connection.Close();
               connection.Open();
           }
           var parameter = new SqlParameter("@TBL_PathType", SqlDbType.Structured);
           parameter.TypeName = "type_PathType";
           parameter.Value = List_TypePath(PathTypeList);
           command.Parameters.Add(parameter);
           command.ExecuteNonQuery();
       }

我检查了 sql 列表中的记录，它们都存在。我已经检查了表中存在的所有外键 ID ** 但它保留了外键问题 **

第五：表：TBL_Path 包含：

[PathID]
      ,[VisitRequestID] : Primary
      ,[GateID] : Foreign
      ,[DateOfEntrance]
      ,[DateOfLeave]
      ,[Finished]
      ,[CardID] : Foreign
      ,[VisitorID] : Foreign
      ,[Confirmation]
      ,[Remarks]
  FROM [dbo].[TBL_Path]

最后：我已经放弃了外键的约束。行被插入，但使用默认值，而不是实际值。问题出在 c# 代码内部。我附上了一个屏幕截图，让您看到列表已正确填写

标签： c#sqlsql-servermetadata