检查条件要插入哪个表

问题描述：

我想检查Tbldelivery表中已经存在的“refno”，如果“refno”存在，那么它将插入“Tbldeliverydetails”中，因为“refno”是第一个表中的主键。我在哪里检查条件？检查条件要插入哪个表

以下是我在C＃编写的代码：

protected void btndlysave_Click(object sender, EventArgs e) 
{ 
    SqlConnection SqlCon = new SqlConnection("server=(local);Initial Catalog=TestDB;Integrated Security=SSPI;"); 
    try 
    { 
     SqlCon.Open(); 

     SqlCommand cmd = new SqlCommand("insert into Tbldelivery (refno,deliverdate,requestby,projectcode) values 
     (@refno,@deliverdate,@requestby,@projectcode)    WHERE not exists (select refno from Tblinkdelivery where refno = @refno)", SqlCon); 
     cmd.CommandType = CommandType.Text; 

     if (need check here) 

     cmd.Parameters.AddWithValue("@refno", txtdelrefno.Text.Trim()); 
     cmd.Parameters.AddWithValue("@deliverdate", txtdeldate.Text.Trim()); 
     cmd.Parameters.AddWithValue("@requestby", txtdelreq.Text.Trim()); 
     cmd.Parameters.AddWithValue("@projectcode", ddlprojcode.Text.Trim()); 
     } 

     else 
     { 

     SqlCommand cmd2 = new SqlCommand("insert into Tbldeliverdetails (refno,printercode,inkcode,quantity,price,notes) values    (@refno,@printercode,@inkcode,@quantity,@price,@notes)", SqlCon); 
     cmd2.CommandType = CommandType.Text; 

     cmd2.Parameters.AddWithValue("@refno", txtdelrefno.Text.Trim()); 
     cmd2.Parameters.AddWithValue("@printercode", ddldelprcode.Text.Trim()); 
     cmd2.Parameters.AddWithValue("@inkcode", ddlinkcode.Text.Trim()); 
     cmd2.Parameters.AddWithValue("@quantity", txtdelqty.Text.Trim()); 
     cmd2.Parameters.AddWithValue("@price", txtdelprice.Text.Trim()); 
     cmd2.Parameters.AddWithValue("@notes", txtdelnotes.Text.Trim()); 

     int val1 = cmd.ExecuteNonQuery(); 
     int val2 = cmd2.ExecuteNonQuery(); 
    } 
    finally 
    { 
     SqlCon.Close(); 
    } 
}

，这实际上编译？看起来你注释了一个if，除非我错了，否则在C＃中没有try/else/finally块这样的事情。 – Zoidberg 2012-02-29 12:02:55

尝试 var da = new SqlDataAdapter（“select refname from Tblinkdelivery where refno ='”+ txtdelrefno.Text +“'”，SqlCon）; var dt = new DataTable（）; da.Fill（dt）; if（dt.Rows.Count == 0） {All Codes} – Hari 2012-04-29 14:17:33

我计算表中的行数。如果该行为零，则“if”内的代码将执行另一个明智的代码“else”被执行。它运行Mr.Zoidberg – Hari 2012-04-29 14:21:11

答

您查询表，看看它是否存在。

using (SqlConnection connection = new SqlConnection(connectionString)) 
{ 
    connection.Open(); 

    SqlCommand sqlCommand = 
     new SqlCommand("SELECT * FROM dbo.Tbldelivery WHERE [email protected]", 
         connection); 

    sqlCommand.Parameters.Add("@refno", System.Data.SqlDbType.VarChar); 
    sqlCommand.Parameters["@refno"].Value = refnoValue; 

    SqlDataReader reader = sqlCommand.ExecuteReader(); 
    reader.Read(); 
    if (reader.HasRows) 
    { 
    // refno exists 
    } 
    else 
    { 
    // refno does not exist 
    } 
}

答

我觉得首先你需要安排你的代码。写入button click事件内的所有内容都不是很好。如果你可以分开业务逻辑并分开放置，那更好。尝试这样的事情。您可以创建处理数据访问的类Data Access。在你的数据访问类

public SqlConnection OpenConnection() 
     { 
      try 
      { 
       var conn = new SqlConnection(“xxx”); 
       conn.Open(); 
       return conn; 
      } 
      catch (Exception ex) 
      { 
       //log the exception 
       return null; 
      } 
     } 

YourFunction(parameters) 
{ 
    var conn = OpenConnection(); 
    if(conn != null) 
    { 
     //your code 
     // you can do something similar as JeremyK explained here 
    } 

}

而在你点击链接

protected void btndlysave_Click(object sender, EventArgs e) 
{ 
    //CHECK THE PARAMETERS AND PASS 
    //DataAccess. YourFunction(parameters) 
}

检查条件要插入哪个表

相关推荐