The code at callout A in Listing 3 starts by passing the server and database names to the top of the routine and setting the SqlConnection object's ConnectionString property. The code creates a SqlDataAdapter object by using a T-SQL SELECT statement as the first argument in the constructor and the SqlConnection object as the second argument. At callout B, the subroutine creates a SqlCommand object and assigns that object to the SqlDataAdapter's InsertCommand property. At this point, the code creates the SqlCommand object, for which the first argument is a T-SQL INSERT statement, and sets the SqlCommand object's CommandType property to CommandType.Text.
Next, the code at callout C creates a DataSet and populates it by using the SqlDataAdapter's Fill method inside the Try-Catch loop. In this case, instead of letting the Fill method open and close the connection to the database, the code calls the SqlConnection's Open method to open the connection. This technique lets the On-RowUpdated event handler (which I use later in the subroutine that Listing 4 shows) use the SqlConnection Open connection object.
The statements at callout D show how to create the SqlParameter objects and add them to the collection of parameters for the DataAdapter's InsertCommand. Each parameter is defined with a parameter marker, data type, length, and source column that corresponds to the values needed for the associated SqlCommand object's parameters. The code at callout E initializes a new row in the Employees DataTable and returns a DataRow object. Then, the subroutine enters data into each of the new DataRow's input columns and adds the new row to the Employees DataTable's Rows collection.
The most important point to notice in Listing 3 is the AddHandler statement at callout F, which adds an event handler for the SqlDataAdapter's RowUpdated event. This event will fire after a row in the database changes. The code then calls the SqlDataAdapter's Update method, which executes the SqlDataAdapter's InsertCommand and inserts the new row into the database table, causing SQL Server to assign a new identity value. The Update method causes the RowUpdated event to fire, then the OnRowUpdated subroutine that Listing 4 shows executes.
Listing 4's OnRowUpdated subroutine retrieves the identity value for the newly inserted row. Inside the OnRowUpdated subroutine, you can see that an iEmpId variable is initialized to 0. Next, the code creates a SqlCommand object that uses a SELECT statement to return an IDENTITY column as the first argument and the open connection as the second argument. (Remember that I defined the sqlConnection object with a global scope so that I could use it in more than one example subroutine.) The code then checks the StatementType. If the StatementType isn't an INSERT, the subroutine terminates. If the statement that fired the event is an INSERT statement, the SqlCommand executes the ExecuteScalar method by using a SELECT @@IDENTITY command. The Execute-Scalar method returns just the first column of the first row in the query result. In this case, the SqlCommand query returns the new identity number, which the subroutine puts in the EmployeeID column of the current row in the Employees DataTable. Finally, the subroutine closes the connection to the data source.
Behind the Secret Identity
ADO.NET's disconnected nature is great for developing highly scalable Web applications, but it can also make some development tasks—such as retrieving IDENTITY column values—a bit tougher than they were in the connected model. By using the techniques in this article, you can get your ADO.NET applications to reveal the secrets of your SQL Server IDENTITY columns when you and your customers need to know them.
End of Article
Prev. page
1
[2]
next page -->
