I have to insert into two tables (contact and patient) using one transaction. The insert into the contact table works fine, although it is incrementing the contactID by 2. For example, if the most recently added row has a contactID of 25, this method will insert a new row with a contactID of 27. However, the insert into the patient table does nothing at all. The patient table is comprised of only two columns:
patientID INT PRIMARY KEY contactID INT FOREIGN KEY
The contactID column in the patient table references the contactID column in the contact table. Since the insert is done into the contact table first, I'm not sure why there would be any problems.
public static bool CreatePatient(string lName, string fName, DateTime dob, string streetAddress, string city, string state, string zip, string phone, string gender, string ssn)
{
bool isCreated = false;
int newContactID = 0;
string insertStmt1 = "INSERT INTO contact (lName, fName, dob, mailingAddressStreet, mailingAddressCity, mailingAddressState, mailingAddressZip, phoneNumber, gender, SSN, userType) " +
"VALUES (@last, @first, @dob, @street, @city, @state, @zip, @phone, @gender, @ssn, 4)";
string selStmt = "SELECT MAX(contactID) AS MaxContactID FROM contact";
string insertStmt2 = "INSERT INTO patient (contactID) VALUES (@contact);";
using (SqlConnection connect = DBConnection.GetConnection())
{
connect.Open();
SqlTransaction tran = connect.BeginTransaction();
try
{
using (SqlCommand cmd = new SqlCommand(insertStmt1, connect, tran))
{
cmd.Parameters.AddWithValue("@last", lName);
cmd.Parameters.AddWithValue("@first", fName);
cmd.Parameters.AddWithValue("@dob", dob);
cmd.Parameters.AddWithValue("@street", streetAddress);
cmd.Parameters.AddWithValue("@city", city);
cmd.Parameters.AddWithValue("@state", state);
cmd.Parameters.AddWithValue("@zip", zip);
cmd.Parameters.AddWithValue("@phone", phone);
cmd.Parameters.AddWithValue("@gender", gender);
cmd.Parameters.AddWithValue("@ssn", ssn);
cmd.ExecuteNonQuery();
}
using (SqlCommand cmd = new SqlCommand(selStmt, connect, tran))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
newContactID = (int)reader["MaxContactID"];
}
}
}
if (newContactID > 0)
{
using (SqlCommand cmd = new SqlCommand(insertStmt2, connect, tran))
{
cmd.Parameters.AddWithValue("@contact", newContactID);
cmd.ExecuteNonQuery();
}
}
isCreated = true;
tran.Commit();
connect.Close();
}
catch
{
tran.Rollback();
return false;
}
}
return isCreated;
}
public static List<Patient> SearchPatientByFirstAndLastName(string fName, string lName)
{
List<Patient> patientList = new List<Patient>();
string selectStatement = "SELECT * FROM contact INNER JOIN patient ON contact.contactID = patient.contactID "
+ "WHERE contact.fName LIKE '%'+@fName+'%' AND contact.lName LIKE '%'+@lName+'%'";
try
{
using (SqlConnection connection = DBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@fName", fName);
selectCommand.Parameters.AddWithValue("@lName", lName);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Patient patient = new Patient();
patient.PatientID = (int)reader["patientID"];
patient.ContactID = (int)reader["contactID"];
patient.LastName = reader["lName"].ToString();
patient.FirstName = reader["fName"].ToString();
patient.Dob = (DateTime)reader["dob"];
patient.Address = reader["mailingAddressStreet"].ToString();
patient.City = reader["mailingAddressCity"].ToString();
patient.State = reader["mailingAddressState"].ToString();
patient.Zip = reader["mailingAddressZip"].ToString();
patient.Phone = reader["phoneNumber"].ToString();
patient.Gender = reader["gender"].ToString();
patient.Ssn = reader["ssn"].ToString();
patientList.Add(patient);
}
reader.Close();
}
}
connection.Close();
}
}
catch (SqlException ex)
{
throw;
}
catch (Exception ex)
{
throw;
}
return patientList;
}
EDIT
I am now trying a different approach. Instead of handling this all in the program, I created a stored procedure as follows:
CREATE PROCEDURE [dbo].[uspCreatePatient] @last VARCHAR(45), @first VARCHAR(45), @dob DATE, @street VARCHAR(100), @city VARCHAR(100), @state CHAR(2), @zip CHAR(5),
@phone VARCHAR(20), @gender CHAR(1), @ssn CHAR(9), @isCreated BIT OUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @contact INT;
BEGIN TRAN
BEGIN TRY
INSERT INTO contact (lName, fName, dob, mailingAddressStreet, mailingAddressCity, mailingAddressState, mailingAddressZip, phoneNumber, gender, SSN, userType)
VALUES (@last, @first, @dob, @street, @city, @state, @zip, @phone, @gender, @ssn, 4);
SET @contact = SCOPE_IDENTITY();
INSERT INTO patient (contactID) VALUES (@contact)
COMMIT TRAN
SET @isCreated = 1;
END TRY
BEGIN CATCH
ROLLBACK TRAN
SET @isCreated = 0;
END CATCH
END
I then updated the CreatePatient method in C# as follows:
public static bool CreatePatient(string lastName, string firstName, DateTime dob, string streetAddress, string city, string state, string zip, string phone, string gender, string ssn)
{
int result = 0;
bool isCreated = false;
using (SqlConnection connect = DBConnection.GetConnection())
{
using (SqlCommand cmd = new SqlCommand("uspCreatePatient", connect))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@last", lastName);
cmd.Parameters.AddWithValue("@first", firstName);
cmd.Parameters.AddWithValue("@dob", dob);
cmd.Parameters.AddWithValue("@street", streetAddress);
cmd.Parameters.AddWithValue("@city", city);
cmd.Parameters.AddWithValue("@state", state);
cmd.Parameters.AddWithValue("@zip", zip);
cmd.Parameters.AddWithValue("@phone", phone);
cmd.Parameters.AddWithValue("@gender", gender);
cmd.Parameters.AddWithValue("@ssn", ssn);
result = cmd.ExecuteNonQuery();
}
}
if (result == 1)
{
isCreated = true;
}
return isCreated;
}
However, the same problem is happening. Only the contact table is being updated. When I run these same commands in SQL Server with hardcoded values, both tables are updated like I want.
