代码之家 › 专栏 › 技术社区 › kombo

C#执行sql语句的顺序是什么?

ado.net

kombo · 技术社区 · 13 年前

我有两个由外键和主键约束相关的表。Visit_Number in Patient表必须存在于Visit表中。在我的代码中,define创建我的连接字符串的两个实例,这样我就可以使用一个实例插入记录:VisitNumber in Visit表,然后另一个实例在Patient表中插入记录,相信绳子是从上到下的。但事实并非如此。我仍然得到外键约束错误:

Error Number:547 
Error MessageThe INSERT statement conflicted with the FOREIGN KEY constraint "Patient_Vist_FK".
 The conflict occurred in database "TestDB", table "dbo.Visit", column 'Visit_Number'.
The statement has been terminated.On line Number: 1

这意味着代码正在按我预期的方式运行。请问你有更好的方法吗?为什么我的方法不起作用代码:

protected void btn_save_Click(object sender, EventArgs e)
    {




        string connect = System.Configuration.ConfigurationManager.ConnectionStrings["db_connection"].ToString();
        SqlConnection con = new SqlConnection(connect);
        SqlConnection con2 = new SqlConnection(connect);
        string visitnumber = txtVistNumber.Text.ToString();
        string insert_statement = "Insert into Patient(Patient_Number,FirstName,LastName,Gender,Tribe,Date_Of_Birth,Visit_Number)"
            + "Values(@Patient_Number,@FirstName,@LastName,@Gender,@Tribe,@Date_Of_Birth,@Visit_Number)";
        string insert_stament2 = "Insert into Visit(Visit_Number)"
            + "Values(@Visit_Number)";

        SqlCommand cmd = new SqlCommand(insert_statement, con);
        SqlCommand cmd2 = new SqlCommand(insert_stament2, con2);

        cmd2.Parameters.AddWithValue("@Visit_Number", txtVistNumber.Text);
        cmd.Parameters.AddWithValue("@Patient_Number",txtpatientNum.Text);
        cmd.Parameters.AddWithValue("@FirstName",txtfirstName.Text);
        cmd.Parameters.AddWithValue("@LastName",txtlastname.Text);
        cmd.Parameters.AddWithValue("@Gender", drl_gender.SelectedValue);
        cmd.Parameters.AddWithValue("@Tribe",DropDownList1.Text);
        cmd.Parameters.AddWithValue("@Date_Of_Birth", val_age.Text);
        cmd.Parameters.AddWithValue("@Visit_Number", txtVistNumber.Text);





        try
        {
            using (con)
            {
                con.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    Response.Write("<script language=javascript>alert('Record Sucessfully Inserted!');</script>");
                    //Success_Message.Text = "Record inserted";
                    txtpatientNum.Text = String.Empty;
                    txtfirstName.Text = String.Empty;
                    txtlastname.Text = String.Empty;
                    txtVistNumber.Text = String.Empty;
                    DropDownList1.Text = String.Empty;
                    val_age.Text = String.Empty;

                }


            }

        }
        catch (SqlException ex)
        {


            {
                VisitError_Message.Text = "Error Number:" + ex.Number.ToString() + " Error Message" + ex.Message + "On line Number" + ": " + ex.LineNumber;
            }



        }
        catch (NullReferenceException nullexception)
        {
            VisitError_Message.Text = "Error Occurred, Error Type:" + nullexception.GetType().ToString() + "Error Message:" + nullexception.Message;
        }
        catch (DllNotFoundException dllexception)
        {
            VisitError_Message.Text = dllexception.GetType().ToString() + dllexception.Message;
        }

        finally
        {
            con.Close();
        }

    }

1 回复 | 直到 13 年前

Akrem 13 年前

如果不执行cmd2,则必须在cmd2中执行insert Visit_Number,然后执行cmd,可以测试此代码

         using (con2)
        {
            con2.Open();
            cmd2.ExecuteNonQuery();
        }

然后你可以执行命令

        using (con)
        {
            con.Open();
            int count = cmd.ExecuteNonQuery();
        }

如果你愿意,你可以用同样的关系来做这项工作

    string connect = System.Configuration.ConfigurationManager.ConnectionStrings["db_connection"].ToString();
    SqlConnection con = new SqlConnection(connect);
    string visitnumber = txtVistNumber.Text.ToString();
    string insert_statement = "Insert into Patient(Patient_Number,FirstName,LastName,Gender,Tribe,Date_Of_Birth,Visit_Number)"
        + "Values(@Patient_Number,@FirstName,@LastName,@Gender,@Tribe,@Date_Of_Birth,@Visit_Number)";
    string insert_stament2 = "Insert into Visit(Visit_Number)"
        + "Values(@Visit_Number)";

        using(con)
        {
            con.open;

            SqlCommand cmd2 = new SqlCommand(insert_stament2, con);

            cmd2.Parameters.AddWithValue("@Visit_Number", txtVistNumber.Text);
            cmd2.ExecuteNonQuery();

            SqlCommand cmd = new SqlCommand(insert_statement, con);

            cmd.Parameters.AddWithValue("@Visit_Number", txtVistNumber.Text);
            cmd.Parameters.AddWithValue("@Patient_Number",txtpatientNum.Text);
            cmd.Parameters.AddWithValue("@FirstName",txtfirstName.Text);
            cmd.Parameters.AddWithValue("@LastName",txtlastname.Text);
            cmd.Parameters.AddWithValue("@Gender", drl_gender.SelectedValue);
            cmd.Parameters.AddWithValue("@Tribe",DropDownList1.Text);
            cmd.Parameters.AddWithValue("@Date_Of_Birth", val_age.Text);
            cmd.Parameters.AddWithValue("@Visit_Number", txtVistNumber.Text);
            cmd.ExecuteNonQuery();      
    }