Adding Foreign Keys (relations) between DataTables in DataSet

by Jagadish Pulakhandam on 9/1/2011 12:54:24 PM
Rated 0 from 0 votes
Brief: Demonstrates on adding a foreign key constraint between two DataTables.
Posted to: Storing data offline (Disconnected) using ADO.NET
Add to DiggAdd to del.icio.usAdd to FURLAdd to RedditAdd to YahooAdd to BlinklistAdd to GoogleAdd to ma.gnoliaAdd to ShadowsAdd to Technorati

Demonstrates the following:
  • Working with Custom DataTables/DataSets
  • Creating a relation between two DataTables with ForeignKeyConstraint
  • Adding rows dynamically to DataTables based on user input
  • Working with DataView
  • A simple Windows Form demonstrating the entire functionality.
Screen shot:



Source code:

001.using System;
002.using System.Collections.Generic;
003.using System.ComponentModel;
004.using System.Data;
005.using System.Drawing;
006.using System.Linq;
007.using System.Text;
008.using System.Windows.Forms;
009. 
010.namespace CSForeignKeyConstraint
011.{
012.    public partial class Form1 : Form
013.    {
014. 
015.        DataSet ds;
016.        DataTable dtStudList;
017.        DataTable dtStudMarks;
018. 
019.        public Form1()
020.        {
021.            InitializeComponent();
022.        }
023. 
024.        private void Form1_Load(object sender, EventArgs e)
025.        {
026.            ds = new DataSet();
027. 
028.            dtStudList = new DataTable();
029.            ds.Tables.Add(dtStudList);
030.            DataColumn dcRegdnoList = new DataColumn();
031.            dcRegdnoList.ColumnName = "Regdno";
032.            dcRegdnoList.DataType = typeof(int);
033.            dtStudList.Columns.Add(dcRegdnoList);
034.            DataColumn dcName = new DataColumn();
035.            dcName.ColumnName = "Name";
036.            dcName.DataType = typeof(string);
037.            dtStudList.Columns.Add(dcName);
038.            dtStudList.PrimaryKey = new DataColumn[] {dcRegdnoList};
039.            dtStudList.Rows.Add(1001, "Ram");
040.            dtStudList.Rows.Add(1002, "Raj");
041. 
042.            dtStudMarks = new DataTable();
043.            ds.Tables.Add(dtStudMarks);
044.            DataColumn dcRegdnoMarks = new DataColumn();
045.            dcRegdnoMarks.ColumnName = "Regdno";
046.            dcRegdnoMarks.DataType = typeof(int);
047.            dtStudMarks.Columns.Add(dcRegdnoMarks);
048.            DataColumn dcSubject = new DataColumn();
049.            dcSubject.ColumnName = "SubjMarks";
050.            dcSubject.DataType = typeof(string);
051.            dtStudMarks.Columns.Add(dcSubject);
052.            ForeignKeyConstraint fk = new ForeignKeyConstraint("fk_list_marks", dcRegdnoList, dcRegdnoMarks);
053.            // fk.DeleteRule = Rule.SetNull;
054.            // fk.UpdateRule = Rule.Cascade
055.            dtStudMarks.Constraints.Add(fk);
056.            dtStudMarks.Rows.Add(1001, 45);
057.            dtStudMarks.Rows.Add(1002, 54);
058.            dtStudMarks.Rows.Add(1001, 99);
059.            dtStudMarks.Rows.Add(1002, 88);
060.             
061.            ds.EnforceConstraints = true;
062.            ds.AcceptChanges();
063.            this.DataGridView1.DataSource = dtStudList;
064.            this.DataGridView2.DataSource = dtStudMarks;
065.             
066.        }
067. 
068.        private void btnAdd_Click(object sender, EventArgs e)
069.        {
070.            try
071.            {
072.                dtStudList.Rows.Add(this.txtRegdno.Text, this.txtName.Text);
073.                ds.AcceptChanges();
074.            }
075.            catch (Exception ex)
076.            {
077.                MessageBox.Show(ex.Message);
078.            }
079.        }
080. 
081.        private void btnAddMarks_Click(object sender, EventArgs e)
082.        {
083.            try
084.            {
085.                dtStudMarks.Rows.Add(this.txtRegdnoMarks.Text, this.txtSubjMarks.Text);
086.                ds.AcceptChanges();
087.            }
088.            catch (Exception ex)
089.            {
090.                MessageBox.Show(ex.Message);
091.            }
092.        }
093. 
094.        private void btnUpdate_Click(object sender, EventArgs e)
095.        {
096.            DataView dv = dtStudList.DefaultView;
097.            dv.Sort = "Regdno";
098.            int rIndex = dv.Find(this.lblSelectedRegdno.Text);
099.            if (rIndex >= 0)
100.            {
101.                dv[rIndex]["Regdno"] = this.txtRegdno.Text;
102.                dv[rIndex]["Name"] = this.txtName.Text;
103.                ds.AcceptChanges();
104.            }
105.        }
106. 
107.        private void DataGridView1_Click(object sender, EventArgs e)
108.        {
109.            if (this.DataGridView1.CurrentRow != null)
110.            {
111.                this.txtRegdno.Text = this.DataGridView1.CurrentRow.Cells[0].Value.ToString();
112.                this.txtName.Text = this.DataGridView1.CurrentRow.Cells[1].Value.ToString();
113.                this.lblSelectedRegdno.Text = this.txtRegdno.Text;
114.            }
115.        }
116. 
117.        private void btnDelete_Click(object sender, EventArgs e)
118.        {
119.            DataView dv = dtStudList.DefaultView;
120.            dv.Sort = "Regdno";
121.            int rIndex = dv.Find(this.lblSelectedRegdno.Text);
122.            if (rIndex >= 0)
123.            {
124.                dv[rIndex].Delete();
125.                ds.AcceptChanges();
126.            }
127.        }
128. 
129. 
130.    }
131.}


Join the .NET Code Central Community and join the discussion!
Signing-up is FREE and quick. Do it now, we want to hear your opinion
0

Rated 0 from 0 votes ( login  to rate)
DotnetKicks DotnetKicksDe DotNetShoutout

Attachments / Source Code
You need to Login or Join for FREE to download the following