Problem solve Get help with specific problems with your technologies, process and projects.

Using SqlDataAdapter to update/insert two+ tables at one time

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:

  • http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatacommondataadapterclasstablemappingstopic.asp
  • http://msdn.microsoft.com/library/en-us/cpguide/html/cpconsettingupdatatabledatacolumnmappings.asp
  • http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommondbdataadapterclassupdatetopic5.asp
  • Dig Deeper on SQL Server and .NET development

    Start the conversation

    Send me notifications when other members comment.

    Please create a username to comment.