The Fill method of the DataAdapter is used to populate a DataSet with the results of the SelectCommand of the DataAdapter. Fill takes as its arguments a DataSet to be populated, and a DataTable object, or the name of the DataTable to be filled with the rows returned from the SelectCommand.

Example:-

The following code example creates an instance of a SqlDataAdapter that uses a SqlConnection to the Microsoft SQL Server Northwind database and populates a DataTable in a DataSet with the list of customers. The SQL statement and SqlConnection arguments passed to the SqlDataAdapter constructor are used to create the Select Command property of the SqlDataAdapter.

string queryString = "SELECT CustomerID, CompanyName FROM dbo.Customers";

SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);

DataSet customers = new DataSet();

adapter.Fill(customers, "Customers");

Table-Mapping Mechanism

The .NET data provider assigns a default name to each result set generated by the query. The default name is Table or any name specified by the programmer in the call to Fill. The adapter looks up its Table -Mappings collection for an entry that matches the default name of the result set being read. If a match is found, the data adapter reads the mapped name. Next, it attempts to locate in the DataSet a DataTable object with the name specified in the mapping.

The TableMappings property represents a collection object of type DataTableMappingCollection. Each contained DataTableMapping object defines a pair of names: a source table name and an in-memory table name. Here's how to configure a few table mappings:

DataSet ds = new DataSet();

DataTableMapping dtm1, dtm2, dtm3;

dtm1 = adapter.TableMappings.Add("Table", "Employees");

dtm2 = adapter.TableMappings.Add("Table1", "Products");

dtm3 = adapter.TableMappings.Add("Table2", "Orders");

adapter.Fill(ds);

It goes without saying that the default names you map onto your own names must coincide with the default names originated by the call to the Fill method. In other words, suppose you change the last line of the previous code snippet with the following one:

adapter.Fill(ds, "MyTable");

In this case, the code won't work any longer because the default names will now be MyTable, MyTable1, and MyTable2. For these names, the TableMappings collection would have no entries defined. Finally, bear in mind you can have any number of table mappings. The overall number of mappings doesn't necessarily have to be related to the expected number of result sets.

Using DataTable

The DataTable object represents one table of in-memory data. Mostly used as a container of data within a DataSet, the DataTable object is also valid as a standalone object that contains tabular data. The DataTable and DataSet are the only ADO.NET objects that can be remoted and serialized. Just as with a DataSet, a DataTable can be created programmatically. In this case, you first define its schema and then add new rows. The following code snippet shows how to create a new table within a DataSet:

DataSet ds = new DataSet();

DataTable tableEmp = new DataTable("Employees");

tableEmp.Columns.Add("ID", typeof(int));

tableEmp.Columns.Add("Name", typeof(string));

ds.Tables.Add(tableEmp);

The table is named Employees and features two columns—ID and Name. The table is empty because no rows have been added yet. To add rows, you first create a new row object by using the NewRow method:

DataRow row = tableEmp.NewRow();

row["ID"] = 1;

row["Name"] = "Joe Users";

tableEmp.Rows.Add(row);

DataRelation Object

A data relation represents a parent/child relationship between two DataTable objects in the same DataSet. In the .NET Framework, a data relation is represented by a DataRelation object. You set a relation between two tables based on matching columns in the parent and child tables. The matching columns in the two related tables can have different names, but they must have the same type. All the relations for the tables in a DataSet are stored in the Relations collection.