CS 112D Lecture Notes - Using DataAdapter to Populate DataSet from Database (Chapter 6 part 1)

DataAdapter Overview

• DataAdapter - a core ADO.NET class which acts as a bridge between a DataTable (DataSet) and a data source (database). Remember that DataSets and DataTables are provider-independent: they are the same for SQL Server, OleDB, and ODBC. But we use a Connection object specific to our data provider in order to connect to our data source (usually a database). Therefore:
• There is a DataAdapter class for each data provider: SqlDataAdapter, OledbDataAdapter, and OdbcDataAdapter.
• Fill() - a method of a DataAdapter that fills a DataTable with data from a data source via a Connection object.
• Update() - a method of a DataAdapter that updates the data source with changes made to the data in the DataSet tables.
• Command Properties - The DataAdapter stores 4 Command objects that are used by Fill() and Update() to retrieve and modify data. When Fill() is called, the SelectCommand retrieves the desired rows. When Update() is called, a command is called for each DataRow that has been changed:
  • SelectCommand - the Command object that is executed when Fill() is called. It contains the select query which retrieves the data we want for our DataTable.
  • InsertCommand - the Command object that is executed when Update() is called and a new record must be inserted into a table.
  • UpdateCommand - the Command object that is executed when Update() is called and a record must be modified.
  • DeleteCommand - the Command object that is executed when Update() is called and a record must be deleted.
• DataTableMappings - a collection of table and column mappings to designate correspondance between DataSet and data source

Populating a DataSet from a Data Source

• To simply populate a DataTable from a data source, the DataAdapter needs only a Connection object and a Command object for the select query.
• Connection - a property of the DataAdapter which stores a reference to the Connection object used to access the data source
• SelectCommand - a property of the DataAdapter which stores a reference to the Command object that is executed when Fill() is called.
• The above two properties can be set and their objects created from CommmandText and ConnectionStrings specified in the DataAdapter's constructor, e.g.:
  Dim da As SqlDataAdapter = New SqlDataAdapter( "SELECT * FROM tblDepartment", "server=localhost; uid=sa; database=novelty")
• the DataAdapter's Fill() method
  • Requires at least one argument: the DataTable or DataSet object which is to be filled.
  • The second argument is optional when a DataSet is given, specifying the name for the DataTable to be created or updated in the DataSet.
  • If a DataSet is given without a DataTable name, the name "Table" is used.
  • If a DataTable is given, there can be no second argument.
  • Returns an Integer specifying how many rows were retrieved.
  • The Fill method does all of the following for you:
    1. Open the Connection, if it's not already open
    2. Execute the SelectCommand using the ExecuteReader method
    3. Use a DataReader to get column names and types from the data source, and use those to setup the DataTable if it doesn't already exist. (No constraints are set)
    4. Populate the DataTable using the DataReader returned from ExecuteReader
    5. Close the DataReader
    6. Close the Connection if it was opened for this Fill. If it had already been open, leave it open.
• You can call Fill() more than once to fill the same DataTable. It will simply add the data it gets from each subsequent call, or, if a primary key is defined in the DataTable, then rows that have already been loaded will be updated by subsequent calls to Fill().
• The default behavior of Fill is to setup column names and data types, but not constraints or keys. To make the DataAdapter setup constraints to match those in the database, do one of the following:
  • Call the FillSchema method of the DataAdapter, with arguments specifying the DataSet to constrain, where the constraints should come from (Source means from the database), and which DataTable to create or use.
  • OR
  • Set the DataAdapter's MissingSchemaAction to MissingSchemaAction.AddWithKey before calling Fill()

Return to the main CS 112D page