C# Dataset with multiple tables - OLEDB

The DataSet in ADO.NET consists of a DataTableCollection, which holds zero or more DataTable objects. The DataTableCollection provides a way to manage and store multiple DataTables within a single DataSet.

To populate DataTables within a DataSet, we can use the OleDbDataAdapter object. The OleDbDataAdapter allows us to retrieve data from a data source and populate the DataTables within the DataSet. The Fill method of the OleDbDataAdapter is particularly useful for populating the DataTables, as it seamlessly integrates the data from the data source into the DataTables within the DataSet.

Manage multiple tables within the DataSet

In ADO.NET, you have the ability to create DataTable objects and add them to an existing DataSet. This allows you to organize and manage multiple tables within the DataSet. To establish relationships between these tables, you can use DataRelation objects. DataRelation objects enable you to define relationships between tables, navigate through the tables, and retrieve child or parent rows based on the defined relationships. The following C# source code shows how to a single OleDbDataAdapter fill Dataset with multiple tables.

Full Source C#
using System; using System.Data; using System.Data.OleDb; using System.Windows.Forms; namespace WindowsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; OleDbConnection connection ; OleDbDataAdapter oledbAdapter ; DataSet ds = new DataSet(); string firstSql = null; string secondSql = null; int i = 0; connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your mdb filename;"; firstSql = "Your First SQL Statement Here"; secondSql = "Your Second SQL Statement Here"; connection = new OleDbConnection(connetionString); try { connection.Open(); oledbAdapter = new OleDbDataAdapter(firstSql, connection); oledbAdapter.Fill(ds, "First Table"); oledbAdapter.SelectCommand.CommandText = secondSql; oledbAdapter.Fill(ds, "Second Table"); oledbAdapter.Dispose(); connection.Close(); //retrieve first table data for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { MessageBox.Show(ds.Tables[0].Rows[i].ItemArray[0] + " -- " + ds.Tables[0].Rows[i].ItemArray[1]); } //retrieve second table data for (i = 0; i <= ds.Tables[1].Rows.Count - 1; i++) { MessageBox.Show(ds.Tables[1].Rows[i].ItemArray[0] + " -- " + ds.Tables[1].Rows[i].ItemArray[1]); } } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); } } } }

Conclusion

ADO.NET provides the ability to create DataTable objects and add them to a DataSet. The DataSet, with its DataTableCollection, allows for managing multiple DataTables. The OleDbDataAdapter object facilitates the population of DataTables within the DataSet from a data source. Additionally, you can establish relationships between tables using DataRelation objects, enabling efficient navigation and retrieval of related data.






Related Topics
  1. What is C# ADO.NET Dataset
  2. C# Datset with Sql Server Data Provider
  3. C# Datset with OLEDB Data Provider
  4. Find Tables in a Dataset - Sql Server
  1. Find Tables in a Dataset - OLEDB
  2. How to Dataset rows count - Sql Server
  3. How to Dataset rows count - OLEDB
  4. How to find Column Definition SqlServer
  5. How to find Column Definition OLEDB
  6. How to Dyanamic Dataset in C#
  7. C# Dataset with multiple tables - Sql Server
  8. C# Dataset table relations
  9. C# Dataset merge tables - Sql Server
  10. C# Dataset merge tables - OLEDB
  11. Bind a dataset to a combo box in C#, bind enum to combobox , bind dictionary to combobox
  12. How to find tables in a Database in C#

Related Links
  1. How to insert image in a database
  2. How to retrieve image from database