C# Dataset table relations

The DataSet in ADO.NET comprises a DataTableCollection, which holds multiple DataTable objects, and a DataRelationCollection, which manages the relationships between the DataTables. The DataSet serves as a memory-resident representation of data, providing a consistent relational programming model regardless of the data source.

In a DataSet with multiple DataTable objects, you can establish relationships between the tables using DataRelation objects. DataRelation objects allow you to define parent-child relationships, navigate through the tables, and retrieve child or parent rows based on the defined relationships.

DataRelationsCollection property

The DataRelationsCollection property, accessible through the DataSet.Relations property, represents a collection of DataRelation objects. You can create parent-child data relations between DataTables by adding DataRelation objects to this collection. The following C# Source Code shows how to implement DataRelation Class with Dataset Object.

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(); 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, "Table1"); adapter.SelectCommand.CommandText = secondSql; adapter.Fill(ds, "Table2"); adapter.Dispose(); command.Dispose(); connection.Close(); //creating data relations DataRelation relation ; DataColumn table1Column ; DataColumn table2Column ; //retrieve column table1Column = ds.Tables["Table1"].Columns[0]; table2Column = ds.Tables["table2"].Columns[0]; //relating tables relation = new DataRelation("relation", table1Column, table2Column); //assign relation to dataset ds.Relations.Add(relation); MessageBox.Show ("Data relation completed"); } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); } } } }

DataRelation objects enable you to relate one or more columns from different tables. The columns involved in the DataRelation should have identical data types. This ensures that the parent and child columns have similar data types, allowing for meaningful and accurate relationships between the tables.

Conclusion

The DataSet object in ADO.NET provides a powerful mechanism for managing related data through its DataTableCollection and DataRelationCollection. You can establish relationships between DataTables using DataRelation objects, ensuring data integrity and enabling efficient navigation. The columns involved in the DataRelation should have matching data types, ensuring compatibility and meaningful relationships between the tables.






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 with multiple tables - OLEDB
  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