如何从自动创建id的表中检索id [英] How to retreive id from table that auto creates id
本文介绍了如何从自动创建id的表中检索id的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
嘿所有，
我有我的代码我可以创建一个新的收据NEXT我应该将我的产品列表添加到我的receiptId链接的另一个表但我的数据库自动创建receiptId和从数据库中读取收据的唯一方法是，如果我有receiptId那么我如何获得receiptId所以我可以将我的名单列表插入到另一个链接在一起的表中？
private void BtnConfirmCart_Click（ object sender，RoutedEventArgs e）
{
if （listOfP.Count！= 0 ） // ！= null？
{
string msg = 你确定你确认你的购物车吗？;
MessageBoxResult boxResult = MessageBox.Show（msg， 确认，MessageBoxButton.YesNo）;
if （boxResult == MessageBoxResult.Yes）
{
// 确认购物车/添加到SQL（创建收据，创建产品列表）/转到收据。
尝试
{
date = DateTime.Now;
decimal total = receiptCart.ReceiptTotal;
int userid = customer.UserId;
Extras.CreateReceipt（userid，total，date）;
}
catch （Exception ex）{MessageBox.Show（ex.Message.ToString（））; }
Window WindowReceipt = new ReceiptWindow（）;
WindowReceipt.Show（）;
关闭（）;
}
}
else
{
BtnGoBack.Visibility = Visibility.Hidden;
MessageBox.Show（ 购物车中没有商品。， 添加项目。）;
}
}
// 创建收据int或void（收据收据）
public static int CreateReceipt（ int userid， decimal rtotal，DateTime rdate）
{
int result = -1;
使用（SqlConnection connection = ConnectionString.GetSqlConnection（））
{
string sqlCreate = INSERT INTO订单（UserId，ReceiptDate，ReceiptTotal） +
VALUES（@uid，@ date，@ total）;
使用（SqlCommand cmdCreate = new SqlCommand（sqlCreate，connection））
{
// cmdCreate.Parameters.AddWithValue（@ rid，receipt.ReceiptID）;
cmdCreate.Parameters.AddWithValue（ @ uid，userid）;
cmdCreate.Parameters.AddWithValue（ @ date，rdate）;
cmdCreate.Parameters.AddWithValue（ rtotal，rtotal）;
尝试
{
connection.Open（）;
result = Convert.ToInt32（cmdCreate.ExecuteScalar（））;
}
catch （Exception ex）{ throw ex; }
}
}
返回结果;
}
// 阅读回执
public static 收据ReadReceipt（ int receiptID）
{
收据收据= 新收据（）;
使用（SqlConnection connection = ConnectionString.GetSqlConnection（））
{
string sqlRead = SELECT * FROM Receipts WHERE ReceiptId = @rid;
使用（SqlCommand cmdRead = new SqlCommand（sqlRead，connection））
{
cmdRead.Parameters.AddWithValue（ @ rid，receiptID）;
connection.Open（）;
使用（SqlDataReader reader = cmdRead.ExecuteReader（））
{
if （reader！= null ）
{
while （读者。 Read（））
{
receipt.ReceiptID = Convert.ToInt32（reader [ ReceiptId ]的ToString（））;
receipt.UserId = Convert.ToInt32（reader [ UserId]。ToString（） ）;
receipt.ReceiptDate = Convert.ToDateTime（reader [ ReceiptDate]。ToString（） ）;
receipt.ReceiptTotal = Convert.ToDecimal（reader [ ReceiptTotal]。ToString（） ）;
}
}
}
}
}
返回收据;
}
// 这是我的计划阅读生产者表，如果这将工作，
// 从订单中检索列表中的所有产品
< span class =code-keyword> public static List< ReturnReceiptProductList> GetReceiptProductList（ int rid）
{
List< ReturnReceiptProductList> productList = new List< ReturnReceiptProductList>（）;
ReturnReceiptProductList returnReceipt = new ReturnReceiptProductList（）;
使用（SqlConnection connection = ConnectionString.GetSqlConnection（））
{
string sqlList = SELECT * FROM OrdersList WHERE ReceiptId = @rid;
使用（SqlCommand cmdList = new SqlCommand（sqlList，connection））
{
cmdList.Parameters.AddWithValue（ @ rid，rid）;
connection.Open（）;
使用（SqlDataReader reader = cmdList.ExecuteReader（））
{
if （reader！= null ）
{
while （读者。阅读（））
{
Product product = new Product（）;
收据收据= 新收据（）;
receipt.ReceiptID = Convert.ToInt32（reader [ ReceiptId]。ToString（） ）;
receipt.UserId = Convert.ToInt32（reader [ UserId]。ToString（） ）;
// product.ProductId = Convert.ToInt32（reader [ProductId]。ToString（））;
product.ProductName = reader [ ProductName]。ToString（） ;
product.ProductQuantity = Convert.ToInt32（reader [ ProductQuantity]。ToString（） ）;
product.ProductPrice = Convert.ToDecimal（reader [ ProductPrice]。ToString（） ）;
product.ProductTax = Convert.ToDecimal（reader [ ProductTax]。ToString（） ）;
returnReceipt = new ReturnReceiptProductList（产品，收据）;
productList.Add（returnReceipt）;
}
}
}
}
}
return productList;
我尝试了什么：
插入数据但是链接另一个表我需要在同一个窗口中插入的ID，我无法从表中读取因为我需要插入特定的ID ...
解决方案
取得那个id是使用身份定义 [ ^ ]有三个系统函数最后返回插入的标识值：
- @@ IDENTITY（Transact-SQL）| Microsoft Docs [ ^ ]
- IDENT_CURRENT（Transact-SQL）| Microsoft Docs [ ^ ]
- SCOPE_IDENTITY（Transact-SQL）| Microsoft Docs [ ^ ]
它们都有不同的用途，但在你的特定情况下@@ IDENTITY如果在insert语句后执行它，应该给你正确的值。
获取值的简单方法是执行如下的查询。它返回包含最后添加的标识值的单行
SELECT @@ IDENTITY
Hey all,
I have my code to I can create a new receipt NEXT I am supposed to add my list of products to another table linked by my receiptId But my database automatically creates the receiptId and the only way to read the receipt from the database is if I have the receiptId So how would I get the receiptId so I could insert my list of names to another table having them linked together?
private void BtnConfirmCart_Click(object sender, RoutedEventArgs e)
{
if (listOfP.Count != 0) //!= null?
{
string msg = "Are you sure you CONFIRM your cart?";
MessageBoxResult boxResult = MessageBox.Show(msg, "Confirmation", MessageBoxButton.YesNo);
if(boxResult == MessageBoxResult.Yes)
{
//Confirm cart /Add to SQL(Create Receipt, Create Product List) /Go to receipt.
try
{
date = DateTime.Now;
decimal total = receiptCart.ReceiptTotal;
int userid = customer.UserId;
Extras.CreateReceipt(userid, total, date);
}
catch(Exception ex) { MessageBox.Show(ex.Message.ToString()); }
Window WindowReceipt = new ReceiptWindow();
WindowReceipt.Show();
Close();
}
}
else
{
BtnGoBack.Visibility = Visibility.Hidden;
MessageBox.Show("No items in your cart.", "Add items.");
}
}
//Create Receipt int or void (Receipt receipt)
public static int CreateReceipt(int userid, decimal rtotal, DateTime rdate)
{
int result = -1;
using(SqlConnection connection = ConnectionString.GetSqlConnection())
{
string sqlCreate = "INSERT INTO Orders(UserId, ReceiptDate, ReceiptTotal) " +
"VALUES(@uid, @date, @rtotal)";
using(SqlCommand cmdCreate = new SqlCommand(sqlCreate, connection))
{
//cmdCreate.Parameters.AddWithValue("@rid", receipt.ReceiptID);
cmdCreate.Parameters.AddWithValue("@uid", userid);
cmdCreate.Parameters.AddWithValue("@date", rdate);
cmdCreate.Parameters.AddWithValue("rtotal", rtotal);
try
{
connection.Open();
result = Convert.ToInt32(cmdCreate.ExecuteScalar());
}
catch(Exception ex) { throw ex; }
}
}
return result;
}
//Read Receipt
public static Receipt ReadReceipt(int receiptID)
{
Receipt receipt = new Receipt();
using(SqlConnection connection = ConnectionString.GetSqlConnection())
{
string sqlRead = "SELECT * FROM Receipts WHERE ReceiptId = @rid";
using(SqlCommand cmdRead = new SqlCommand(sqlRead, connection))
{
cmdRead.Parameters.AddWithValue("@rid", receiptID);
connection.Open();
using(SqlDataReader reader = cmdRead.ExecuteReader())
{
if(reader != null)
{
while (reader.Read())
{
receipt.ReceiptID = Convert.ToInt32(reader["ReceiptId"].ToString());
receipt.UserId = Convert.ToInt32(reader["UserId"].ToString());
receipt.ReceiptDate = Convert.ToDateTime(reader["ReceiptDate"].ToString());
receipt.ReceiptTotal = Convert.ToDecimal(reader["ReceiptTotal"].ToString());
}
}
}
}
}
return receipt;
}
//THIS IS MY PLAN TO READ PRODUCTLIST TABLE NOT SURE IF THIS WILL WORK YET
//Retreive all products in list from order
public static List<ReturnReceiptProductList> GetReceiptProductList(int rid)
{
List<ReturnReceiptProductList> productList = new List<ReturnReceiptProductList>();
ReturnReceiptProductList returnReceipt = new ReturnReceiptProductList();
using (SqlConnection connection = ConnectionString.GetSqlConnection())
{
string sqlList = "SELECT * FROM OrdersList WHERE ReceiptId = @rid";
using (SqlCommand cmdList = new SqlCommand(sqlList, connection))
{
cmdList.Parameters.AddWithValue("@rid", rid);
connection.Open();
using (SqlDataReader reader = cmdList.ExecuteReader())
{
if (reader != null)
{
while (reader.Read())
{
Product product = new Product();
Receipt receipt = new Receipt();
receipt.ReceiptID = Convert.ToInt32(reader["ReceiptId"].ToString());
receipt.UserId = Convert.ToInt32(reader["UserId"].ToString());
//product.ProductId = Convert.ToInt32(reader["ProductId"].ToString());
product.ProductName = reader["ProductName"].ToString();
product.ProductQuantity = Convert.ToInt32(reader["ProductQuantity"].ToString());
product.ProductPrice = Convert.ToDecimal(reader["ProductPrice"].ToString());
product.ProductTax = Convert.ToDecimal(reader["ProductTax"].ToString());
returnReceipt = new ReturnReceiptProductList(product, receipt);
productList.Add(returnReceipt);
}
}
}
}
}
return productList;
What I have tried:
inserting data but to link another table i need the id inserted in the same window and i cannot read from table because i need that specific id inserted...
解决方案
Taken that the id is created using an identity definition[^] there are three system functions that return lastly inserted identity value:
- @@IDENTITY (Transact-SQL) | Microsoft Docs[^]
- IDENT_CURRENT (Transact-SQL) | Microsoft Docs[^]
- SCOPE_IDENTITY (Transact-SQL) | Microsoft Docs[^]
They all serve a different purpose but in your specific case@@IDENTITYshould give you the correct value if you execute it right after the insert statement.
A simple way to fetch the value is to execute a query like the following. It returns a single row containing the last added identity value
SELECT @@IDENTITY
这篇关于如何从自动创建id的表中检索id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
