In my project I am filling two database tables with two DataSets. The method which I am using to fill those database tables is shown below:
SqlCommand sqlCommand = new SqlCommand();
SqlCommand command;
using (SqlConnection conn = new SqlConnection(strConn))
{
using (SqlCommand cmd = new SqlCommand(strQuery, conn))
{
using (SqlCommand cmdReset = new SqlCommand("DBCC CHECKIDENT('Doctor', RESEED, 0)", conn))
{
using (SqlCommand cmdUnCheck = new SqlCommand("alter table [dbo].[Doctor] nocheck constraint all",conn))
{
using (SqlCommand cmdCheck = new SqlCommand("alter table [dbo].[Doctor] with check check constraint all",conn))
{
SqlDataAdapter da = new SqlDataAdapter(cmd);
conn.Open();
SqlTransaction sqlTransaction = conn.BeginTransaction();
cmd.Transaction = sqlTransaction;
cmdReset.Transaction = sqlTransaction;
cmdUnCheck.Transaction = sqlTransaction;
cmdCheck.Transaction = sqlTransaction;
try
{
cmdUnCheck.ExecuteNonQuery();
/*ERROR*/ cmd.ExecuteNonQuery();
cmdReset.ExecuteNonQuery();//deleting database table data
foreach (DataRow dr in ds.Tables[0].Rows) //Inserting new data into the Database table
{
command = new SqlCommand(query.createDoctorRow("Doctor",
dr[1].ToString(), dr[2].ToString(), Convert.ToInt64(dr[3]), Convert.ToInt32(dr[4])), conn, sqlTransaction);
command.ExecuteNonQuery();
}
cmdCheck.ExecuteNonQuery();
sqlTransaction.Commit();
conn.Close();
}
catch (Exception e)
{
sqlTransaction.Rollback();
throw;
}
}
}
}
}
}
The above code was working fine till the second Database table was empty but when I inserted some data in it and then run the code, VS is showing error -->
The DELETE statement conflicted with the REFERENCE constraint "FK_Dr_ID". The conflict occurred in database "Hospital", table "dbo.Patient", column 'Doctor_ID'. The statement has been terminated.
This seems clear that the foreign key is restricting the query to take place but as I am a beginner and dont know what to do I am asking your help..
In the above code I made changes referring to this SO answer but no joy, the error still exists at the same point.
Optional: Is there anyway to reduce the using statements in my code?
