正在检查要插入的表的条件

本文关键字：条件 插入 检查 | 更新日期: 2023-09-27 18:26:21

我想检查"refno"是否已经存在于Tbdelivery表中，如果"refno"存在，则它将插入"Tbdeliverydetails"中，因为"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();
    }
}

正在检查要插入的表的条件

我认为首先需要安排代码。在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)
}

查询该表并查看它是否存在。

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlCommand sqlCommand = 
        new SqlCommand("SELECT * FROM dbo.Tbldelivery WHERE refno=@refno", 
                        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
    }
}
相关文章：