C# Dataset with multiple tables - Sql Server

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

The SqlDataAdapter object plays a crucial role in populating DataTables within the DataSet. It provides methods like Fill() that retrieve data from a data source and populate the DataTables accordingly. The Fill() method of the SqlDataAdapter allows for seamless integration of data from the data source into the DataTables within the DataSet.

Manage multiple tables within the DataSet

With ADO.NET, you can create DataTable objects and add them to an existing DataSet. This allows you to organize and manage multiple tables within the DataSet. You can define relationships between these tables using DataRelation objects. DataRelation objects enable you to establish connections between tables, navigate through them, and retrieve child or parent rows based on the defined relationships.

Full Source C#
using System; using System.Data; using System.Data.SqlClient; 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; SqlConnection connection ; SqlCommand command ; SqlDataAdapter adapter = new SqlDataAdapter(); DataSet ds = new DataSet(); int i = 0; string firstSql = null; string secondSql = null; connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"; firstSql = "Your First SQL Statement Here"; secondSql = "Your Second SQL Statement Here"; connection = new SqlConnection(connetionString); try { connection.Open(); command = new SqlCommand(firstSql, connection); adapter.SelectCommand = command; adapter.Fill(ds, "First Table"); adapter.SelectCommand.CommandText = secondSql; adapter.Fill(ds, "Second Table"); adapter.Dispose(); command.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 capability to create DataTable objects and add them to a DataSet. The DataSet, with its DataTableCollection, allows for managing multiple DataTables. The SqlDataAdapter object facilitates the population of DataTables within the DataSet from a data source. Additionally, you can establish relationships between tables using DataRelation objects. This enables efficient navigation and retrieval of related data.