时间戳

首页 > 解决方案 > 使用 C# 应用程序生成随机 ID 时的 SQL TOP 1 查询

c# - 使用 C# 应用程序生成随机 ID 时的 SQL TOP 1 查询

问题描述

我有一个应用程序，它在to和C#中创建一个条目，当用户从应用程序出售特定项目时，一旦用户输入投标金额，就会在 customer_invoice 表中插入一个 ID 是我的身份列通过 ( ) 读取最后输入的 ID 以使用该 ID 插入 table_customer_invoice_detail。SQL databasetable_customer_invoicetable_customer_invoice_detailSELECT TOP 1 ID FROM CUSTOMERINVOICE ORDER BY ID DESC

它运作良好，唯一的问题是当两个用户在同一时间从不同的计算机一起销售一件物品时，而不是 ( SELECT TOP 1 ID) 冲突，并且两个用户在查找最后创建的 ID 时获得相同的 ID。有没有其他方法可以获得特定用户的确切 ID？

这是应用程序中的代码


SqlCommand cmd3 = new SqlCommand("insert into CUSTOMERINVOICE(INVOICEDATE,INVOICESTATUS,ISDEBTORINVOICE,ISLAYBYEINVOICE,INVOICETYPE,INVOICETOTAL,salesrep,dayended)" + "values(GETDATE(),0,0,0,1,'" + Convert.ToDecimal(lblTotalAmount.Text.ToString()) + "','" + txtSalesRep.Text + "',0)", conn);
cmd3.ExecuteNonQuery();

SqlDataAdapter ada2 = new SqlDataAdapter("SELECT TOP 1 ID FROM CUSTOMERINVOICE ORDER BY ID DESC", conn);
  DataTable dt2 = new DataTable();
  ada2.Fill(dt2);
  DataRow dr2 = dt2.Rows[0];
  CUSTID = Int32.Parse(dr2["ID"].ToString());

                        foreach (DataGridViewRow row in dgSaleBasket.Rows)
                        {

                            SqlDataAdapter ada5 = new SqlDataAdapter("SELECT itemcode,onhand,costincl FROM stmast where itemcode ='" + row.Cells[1].Value + "'", conn);
                            DataTable dt5 = new DataTable();
                            ada5.Fill(dt5);
                            int quantityPurchased = Int32.Parse(row.Cells[4].Value.ToString());
                            for (int i = 0; i < dt5.Rows.Count; i++)
                            {
                                DataRow dr5 = dt5.Rows[i];

                                double SellPrice = Convert.ToDouble(row.Cells[5].Value.ToString());
                                costinc = Convert.ToDouble(dr5["costincl"].ToString());
                                profit = (SellPrice - costinc) * quantityPurchased;
                                totalprofit = profit + totalprofit;
                                SqlCommand cmd4 = new SqlCommand("insert into CUSTOMERINVOICEDETAIL(INVOICEID,ITEMCODE,DESCRIPTION,QUANTITY,PRICE,profit,refund)" + "values(" + CUSTID + ",'" + row.Cells[1].Value.ToString() + "','" + row.Cells[2].Value.ToString() + "'," + row.Cells[4].Value.ToString() + ",'" + Convert.ToDecimal(row.Cells[5].Value.ToString()) + "'," + profit + ",0)", conn);
                                cmd4.ExecuteNonQuery();


                                SqlCommand cmd6 = new SqlCommand("UPDATE stmast SET onhand =onhand-" +quantityPurchased + ", lastSold =GETDATE() , lastSoldPrice=" + Convert.ToDecimal(row.Cells[5].Value.ToString()) + ",totalQtySold=totalQtySold+" + quantityPurchased + " WHERE itemcode ='" + row.Cells[1].Value + "'", conn);
                                cmd6.ExecuteNonQuery();

                                SqlCommand cmd2 = new SqlCommand("UPDATE customerinvoice set invoiceprofit=" + totalprofit + " WHERE id =" + CUSTID, conn);
                                cmd2.ExecuteNonQuery();


                            }

标签： c#sqlsql-server

解决方案

也许您可以尝试使用 INSERTED 临时表示例：

CREATE DATABASE TEST

USE TEST

CREATE TABLE client
(
id INT IDENTITY(1,1),
name varchar(20)
)

DECLARE @tempTable TABLE(id INT,
                         [name] varchar(20))
INSERT client(name)
OUTPUT INSERTED.* INTO @tempTable
VALUES ('Marc');
SELECT * FROM @tempTable
-- Return |1|Marc|

推荐阅读