Browse DevX
Sign up for e-mail newsletters from DevX


ADO.NET Best Practices, Part II : Page 4

In ADO, inner joins are accomplished using proper SQL statements within a single batch. With ADO.NET, you can obtain the same data organized in independent but related tables which greatly simplifies updates. What's faster? That depends on the application.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Retrieving Server-Generated Values
More often than not, when you make updates across tables, you need a previously generated value to successfully run the next statement within a stored procedure. Such a typical situation might involve triggers or identity columns. If you're using direct commands or stored procedures, there's not much that ADO.NET does for you. You must address the issue yourself. One common workaround entails reading critical values back through a subsequent query. You can return the values you need through output parameters or additional result sets. This solution works but you have to code it yourself. If you use the ADO.NET batch update instead, you can rely on a couple of built-in mechanisms that save you from extra coding. The first mechanism is based on the UpdatedRowSource property of the command object. The other mechanism leverages the RowUpdated event on the data adapter object.

The UpdatedRowSource property looks for output parameters, the first row of the next query, both, or neither. By default, it looks for both. The following SQL batch inserts a new row into a database where custID is an identity column. The second statement retrieves the last identity value generated in the scope and returns it as the updated value of the custID column.

INSERT INTO customers (custID, custName) VALUES (@CustID, @CustName) SELECT SCOPE_IDENTITY() AS custID

If you assign the above statements to the InsertCommand property of a data adapter, the UpdatedRowSource property will catch the newly generated identity value and pass it to the DataRow object being updated. If you set the UpdatedRowSource property to Both (the default) or FirstReturnedRecord, each inserted row processed through the batch update will have the identity column updated. And, more importantly, you don't have to write any additional code. You only need to select a checkbox in the Visual Studio .NET wizard that configures a data adapter.

A data adapter that is about to update a row during a batch update process fires the RowUpdating and RowUpdated events, respectively, before and after the update. You handle the RowUpdated event as follows:

adapter.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);

The event handler gets a data structure of type SqlRowUpdatedEventArgs in which the Row property returns the row as updated by the underlying database. You'll need to modify this code database. You must design effective queries and update strategies to make a scalable application. To write effective SQL code, you should know the SQL engine you're using, and realize that they may not be equal. If you're building a .NET application, you also need a good understanding of the ADO.NET model and its capabilities. ADO.NET is powerful but it doesn't reduce database programming to mere point-and-click programming. ADO.NET offers a set of powerful tools but without practice and experience you may not do a lot with them. The command builder object or even the batch update process, for example, greatly simplifies the update process but you must know what they do and how they work.

Dino Esposito is Wintellect's ADO.NET and XML expert, and a trainer and consultant based in Rome, Italy. A speaker at many industry events including TechEd, WinSummit, and DevConnections, Dino is the author of Applied XML Programming with the .NET Framework and Programming ASP.NET, both for Microsoft Press. You can reach Dino via email here.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date