c# - 自增外键

Question

• 表 1 - 项目
• 表 2 - 销售(具有自动增量主键)
• 表3 - sale_item(该表的主键是表1的itemId和表2的自增saleID)

如何使用自动增量 saleID 更新第三个表？

我的下面的代码不起作用。

string query1 = "Update item set qty = '" + final + "'where itemId ='" + item + "'";

int rows = db.SaveUpdateDelete(query1);
DateTime d = DateTime.Now;
int day = d.Day;
int month = d.Month;
int year = d.Year;

string dt = year + "/" + month + "/" + day;

string query2 = "Insert into sale (empID,date) values ('" + usename + ",'" + dt + "')";
int rows1 = db.SaveUpdateDelete(query2);

string query3 = "Insert into sale_item (itemId, qty, unit_price) values ('" + item + "','" + qty + "','" + price + "'";
int rows2 = db.SaveUpdateDelete(query3);

Answer 1

一旦在第二个表 sale 中完成插入，就执行 sale 表中的 top 1基于 ID。从该输出中获取销售 ID，插入到第三个表中。是否使用虚拟值执行了以下代码并且它有效。

        var conn = new SqlConnection("connection string");
        conn.Open();
        SqlCommand comm = new SqlCommand("Update item set qty = '" + 30 + "'where itemId ='" + 1 + "'",conn);
        comm.ExecuteNonQuery();

        DateTime d = DateTime.Now;
        int day = d.Day;
        int month = d.Month;
        int year = d.Year;

        string dt = year + "/" + month + "/" + day;
        string username = "jyothi";
        SqlCommand query2 = new SqlCommand("Insert into sale (empID,date1) values ('" + 22 + "','" + dt + "')",conn);
        query2.ExecuteNonQuery();

        int saleid=0;
        SqlCommand query3 = new SqlCommand("select top 1 id from sale order by id desc",conn);
        var dreader = query3.ExecuteReader();
        if (dreader.Read())
        {
            saleid = Convert.ToInt32(dreader[0].ToString());

        }
        dreader.Close();


        SqlCommand query4 = new SqlCommand("Insert into saleitem (saleid,itemId, qty, price) values ('" + saleid + "','" + 1 + "','" + 20 + "','" + 25 + "')", conn);
        query4.ExecuteNonQuery();