c# - 如何在不同的表中写入我的 FK 值？ C# | SQLite

Question

我正在做一个学校项目，我正在创建一个客户管理系统。由于需要，我需要将我的数据库架构从使用 1 个表更改为使用 2 个。目前，我能够写入数据库中的两个表，但是，我无法获得外键的概念。我的 SQL 经验非常有限。我当前的执行语句正在写入我的两个表，除了 MedicalInformation 表中的客户端 ID 列。它显示为空，我不确定我应该如何从这里开始。

cnn.Execute("INSERT INTO ClientInformation (FirstName, MiddleInitial, LastName, Phone, Email, Address, City, State, Zip, DateOfBirth, Occupation, Employer, EmergencyContact, EmergencyContactRelationship, EmergencyContactPhone) " +
            "VALUES (@FirstName, @MiddleInitial, @LastName, @Phone, @Email, @Address, @City, @State, @Zip, @DateOfBirth, @Occupation, @Employer, @EmergencyContact, @EmergencyContactRelationShip, @EmergencyContactPhone)", client);


cnn.Execute("INSERT INTO MedicalInformation (CurrentMedications, ChronicPain, ChronicPainWhere, OrthopedicPain, OrthopedicPainWhere, Pregnant, PreferedPressureLight, PreferedPressureMedium, PreferedPressureDeep, Allergies, AllergiesWhat, Cancer, HeadacheMigraine, Arthritis, Diabetes, JointReplacement, HighLowBloodPressure, Neuropathy, Fibromyalgia, Stroke, HeartAttack, KidneyDysfunction, BloodClots, Numbness, SprainsStrains, AreasOfDiscomfort) " +
            "VALUES (@CurrentMedication, @ChronicPain, @ChronicPainWhere, @OrthopedicPain, @OrthopedicPainWhere, @Pregnant, @PeferredPressureLight, @PeferredPressureMedium, @PeferredPressureDeep, @Allergies, @AllergiesWhat, @Cancer, @HeadacheMigraine, @Arthritis, @Diabetes, @JointReplacement, @HighLowBloodPressure, @Neuropathy, @Fibromyalgia, @Stroke, @HeartAttack, @KidneyDysfunction, @BloodClots, @Numbness, @SprainsStrains, @AreasOfDiscomfort) ", client.MedicalInfo);

这是表的一部分

CREATE TABLE "MedicalInformation" (
    "_ID"   INTEGER NOT NULL UNIQUE,
    "Client_ID" INTEGER,
    
    PRIMARY KEY("_ID" AUTOINCREMENT),
    FOREIGN KEY("Client_ID") REFERENCES "ClientInformation"("Client_Id")
);

CREATE TABLE "ClientInformation" (
    "Client_Id" INTEGER NOT NULL UNIQUE,
   
    PRIMARY KEY("Client_Id" AUTOINCREMENT)
);

我的客户端信息类

public class ClientInformation
    {
        //Client Information
        public int Client_ID { get; set; }
        public string FirstName { get; set; }
        public string MiddleInitial { get; set; }
        public string LastName { get; set; }
        public string Phone { get; set; }
        public string Email { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
        public string DateOfBirth { get; set; }
        public string Occupation { get; set; }
        public string Employer { get; set; }
        public string EmergencyContact { get; set; }
        public string EmergencyContactRelationship { get; set; }
        public string EmergencyContactPhone { get; set; }
        public MedicalInformation MedicalInfo { get; set; }
        public ClientInformation(string firstname, string middleinitial, string lastname, string phone, string email, string address, string city, string state, string zip, string dateofbirth, string occupation, string employer,
        string emergencycontact, string emergencycontactrelationship, string emergencycontactphone, MedicalInformation clientMedicainfo)
        {
            this.FirstName = firstname;
            this.MiddleInitial = middleinitial;
            this.LastName = lastname;
            this.Phone = phone;
            this.Email = email;
            this.Address = address;
            this.City = city;
            this.State = state;
            this.Zip = zip;
            this.DateOfBirth = dateofbirth;
            this.Occupation = occupation;
            this.Employer = employer;
            this.EmergencyContact = emergencycontact;
            this.EmergencyContactRelationship = emergencycontactrelationship;
            this.EmergencyContactPhone = emergencycontactphone;
            this.MedicalInfo = clientMedicainfo;
        }

    }

Answer 1

由于您的 ClientInformation.Client_Id key 是由 SQLite 自动生成的，您需要在插入 MedicalInformation 之前以某种方式检索它 table 。
我对 SQLite 没有经验，但快速浏览一下 SQLite documentation , 我发现你可以使用 last_insert_rowid()函数来检索最后一行插入的 ID。
你需要这个 Client_ID您的 client.MedicalInfo 中的值(value)对象供以后处理，所以我认为可以做这样的事情:

client.MedicalInfo.Client_ID = cnn.ExecuteScalar<int>("INSERT INTO ClientInformation (FirstName, MiddleInitial, LastName, Phone, Email, Address, City, State, Zip, DateOfBirth, Occupation, Employer, EmergencyContact, EmergencyContactRelationship, EmergencyContactPhone) " +
            "VALUES (@FirstName, @MiddleInitial, @LastName, @Phone, @Email, @Address, @City, @State, @Zip, @DateOfBirth, @Occupation, @Employer, @EmergencyContact, @EmergencyContactRelationShip, @EmergencyContactPhone);" +
            "SELECT last_insert_rowid();", client);

cnn.Execute("INSERT INTO MedicalInformation (Client_ID, CurrentMedications, ChronicPain, ChronicPainWhere, OrthopedicPain, OrthopedicPainWhere, Pregnant, PreferedPressureLight, PreferedPressureMedium, PreferedPressureDeep, Allergies, AllergiesWhat, Cancer, HeadacheMigraine, Arthritis, Diabetes, JointReplacement, HighLowBloodPressure, Neuropathy, Fibromyalgia, Stroke, HeartAttack, KidneyDysfunction, BloodClots, Numbness, SprainsStrains, AreasOfDiscomfort) " +
            "VALUES (@Client_ID, @CurrentMedication, @ChronicPain, @ChronicPainWhere, @OrthopedicPain, @OrthopedicPainWhere, @Pregnant, @PeferredPressureLight, @PeferredPressureMedium, @PeferredPressureDeep, @Allergies, @AllergiesWhat, @Cancer, @HeadacheMigraine, @Arthritis, @Diabetes, @JointReplacement, @HighLowBloodPressure, @Neuropathy, @Fibromyalgia, @Stroke, @HeartAttack, @KidneyDysfunction, @BloodClots, @Numbness, @SprainsStrains, @AreasOfDiscomfort); ", client.MedicalInfo);

只要确保 client.MedicalInfo.Client_ID属性存在于您的 DTO 类中。