I know two methods to access a database to select, update or insert through ADO.NET.
Method 1: Use data command such as SQlCommand then call ExecuteNonQuery or ExecuteReader method.
Method 2: Use Data Adpater and DataSet such as SqlDataAdapter then call fill or update method.
I would like to know if I can use method 2 for every case to access the database. I'd especially like to know, if I want to update/insert two or more tables at one time, can I do that with method 2? If so, how do I prepare update/insert command for data adapter? Finally, can you give me a link to a site that has examples about this? Thanks.
SqlDataAdapter uses SqlCommand instances to perform the update/insert/delete operations. As such, it doesn't actually differ from method1, except from the usability point of view, and in its versatility to perform multiple operations at the same time.
You assign its SelectCommand, UpdateCommand, InsertCommand and DeleteCommand properties to the exact same instances you would use for method 1.
As for multi-table updates, you can either do it inside an SProc, or call different adapters with the same dataset. You can take also take advantage of the TableMappings adapter property to correlate the tables/columns to command parameters. MSDN documentation is always a good place to start:
Dig Deeper on SQL Server and .NET development
Related Q&A from Daniel Cazzulino
Here Daniel Cazzulino explains how to load a DSL (domain specific language) domain model instance file programmatically. This requires the .NET type ... Continue Reading
Here we offer a glimpse at 12 of .NET development expert Danny Cazzulino's top ASP.NET questions and answers. Continue Reading
C# developers should NOT be modifying InitializeComponent method in the code-behind (or any of the variable definitions) by hand. Continue Reading