I'm working on a school project where I am creating a client management system. Due to a requirement, I was required to change my database schema from using 1 table to using 2. Currently, I am able to write to both tables in my database, however, I am having trouble getting the concepts of foreign keys. I have very limited SQL experience. My current execute statements are writing to both my tables except my the client ID column in the MedicalInformation table. It is showing null and I'm not sure how I should proceed from here.
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);
This is a portion of the tables
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)
);
My clientinfo class
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;
}
}
Since your ClientInformation.Client_Id key is automatically generated by SQLite, you'll need to retrieve it somehow before inserting on the MedicalInformation table.
I'm not experienced with SQLite, but from a quick glance at the SQLite documentation, I found out that you can use the last_insert_rowid() function to retrieve the ID of the last row insert.
You'll need this Client_ID value inside your client.MedicalInfo object for later processing, so I think it's possible to do something like this:
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);
Just make sure the client.MedicalInfo.Client_ID property exists in your DTO class.