Retrieving New Identity Column Values

Chia sẻ: Tuan Nghia | Ngày: | Loại File: PDF | Số trang:3

Thêm vào BST

Báo xấu

83
lượt xem 5
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Retrieving New Identity Column Values The ProductID column of the Products table is an identity column. In this section, you'll see how to insert a new row into to Products table

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Retrieving New Identity Column Values

Retrieving New Identity Column Values The ProductID column of the Products table is an identity column. In this section, you'll see how to insert a new row into to Products table and retrieve the new value generated by the database for the ProductID identity column. Note You'll find a complete program named UsingIdentityColumn.cs in the ch11 directory that illustrates the use of the methods shown in this section. This program listing is omitted from this book for brevity. In the examples, assume you have a DataTable named productsDataTable that is populated with the rows retrieved by the following SELECT statement: SELECT ProductID, ProductName, UnitPrice FROM Products ORDER BY ProductID The following example sets the PrimaryKey property of productsDataTable: productsDataTable.PrimaryKey = new DataColumn[] { productsDataTable.Columns["ProductID"] }; The next example sets the AllowDBNull, AutoIncrement, AutoIncrementSeed, AutoIncrementStep, ReadOnly, and Unique properties for the ProductID DataColumn of productsDataTable: DataColumn productIDDataColumn = productsDataTable.Columns["ProductID"]; productIDDataColumn.AllowDBNull = false; productIDDataColumn.AutoIncrement = true; productIDDataColumn.AutoIncrementSeed = -1; productIDDataColumn.AutoIncrementStep = -1; productIDDataColumn.ReadOnly = true; productIDDataColumn.Unique = true; Because of these settings, when you add a new DataRow to productsDataTable the ProductID DataColumn of your new DataRow will initially have the value -1.
As in the earlier section, "Modifying Rows in a DataTable," you need to set your DataAdapter object's InsertCommand, UpdateCommand, and DeleteCommand properties with appropriate Command objects. The CommandText property of the Command object used in the UpdateCommand property is as follows: myUpdateCommand.CommandText = "UPDATE Products " + "SET " + " ProductName = @NewProductName, " + " UnitPrice = @NewUnitPrice " + "WHERE ProductID = @OldProductID " + "AND ProductName = @OldProductName " + "AND UnitPrice = @OldUnitPrice"; The CommandText property of the Command object used in the DeleteCommand property is as follows: myDeleteCommand.CommandText = "DELETE FROM Products " + "WHERE ProductID = @OldProductID " + "AND ProductName = @OldProductName " + "AND UnitPrice = @OldUnitPrice"; Notice the CommandText of these two Command objects isn't substantially different from those shown in the previous section, except that it goes against the Products table rather than the Customers table. The real difference is in the CommandText of the Command object used in the InsertCommand property, which must retrieve the ProductID value generated by the database for the new row. To do this, you can use the following code that contains an INSERT statement that adds a new row, along with a SELECT statement that retrieves the ProductID value using a call to the SQL Server SCOPE_IDENTITY() function: myInsertCommand.CommandText = "INSERT INTO Products (" + " ProductName, UnitPrice " + ") VALUES (" + " @MyProductName, @MyUnitPrice" + ");" + "SELECT @MyProductID = SCOPE_IDENTITY();"; myInsertCommand.Parameters.Add( "@MyProductName", SqlDbType.NVarChar, 40, "ProductName"); myInsertCommand.Parameters.Add(
"@MyUnitPrice", SqlDbType.Money, 0, "UnitPrice"); myInsertCommand.Parameters.Add("@MyProductID", SqlDbType.Int, 0, "ProductID"); myInsertCommand.Parameters["@MyProductID"].Direction = ParameterDirection.Output; The SCOPE_IDENTITY() function returns the last inserted identity value into any table performed within the current database session and stored procedure, trigger, function, or batch. For example, calling SCOPE_IDENTITY() in the previous example returns the last identity value inserted into the Products table, which is the ProductID of the new row. Note For details on the SCOPE_IDENTITY() function, refer to Chapter 4, "Introduction to Transact-SQL Programming." When you add a new DataRow to productsDataTable, the ProductID DataColumn of your new DataRow will initially have the value -1. When you call the Update() method of your SqlDataAdapter to push the new row to the database, the following steps occur: 1. Your new DataRow is pushed to the database using the INSERT statement set in myInsertCommand, with the ProductID column of the Products table being set to a new identity value generated by the database. 2. The ProductID identity value is retrieved by the SELECT statement set in myInsertCommand. 3. The ProductID DataColumn in your DataRow is set to the retrieved identity value. Feel free to examine, compile, and run the UsingIdentityColumn.cs program located in the ch11 directory. This program performs the following high-level actions: 1. Retrieves rows from the Products table into a DataTable named productsDataTable. 2. Adds a DataRow to productsDataTable. 3. Modifies the new DataRow. 4. Deletes the new DataRow. When you run this program you'll notice the change in the ProductID DataColumn value for a newly added DataRow from -1 to an actual value retrieved from the database.