.NET framework SQL Server CRUD using data sets and data adapters

Question

I have the following 2 tables in a database:

Movies (id, title, year) which is the parent of table Language
Language (movie_id, language) ----movie_id references Movies.id

I wrote the following code but I still have to do the update/delete part and to add a new record in the child table

when I select a record from the child (Language), I have to delete and update a record
when I select a record from the parent (Movies), I have to add a new record in the child table

Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace lab1_v2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string connectionString = "Server=DESKTOP-T33VBF8;Database=lab1SGBD_v6;Integrated Security=true";

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    // Open connection
                    connection.Open();
                    MessageBox.Show("State of the connection: " + connection.State.ToString());

                    // Create the data set
                    DataSet dataset = new DataSet();
                    
                    // Create the two SQL Data Adapters for parent and child tables.
                    SqlDataAdapter parentAdapter = new SqlDataAdapter("SELECT * FROM Movies;", connection);
                    SqlDataAdapter childAdapter = new SqlDataAdapter("SELECT * FROM Languages;", connection);
                    
                    // Create and populate the parent DataTable and the child DataTable
                    parentAdapter.Fill(dataset, "Movies");
                    childAdapter.Fill(dataset, "Languages");
                    
                    // Create the two BindingSources for parent and child DataTable
                    BindingSource parentBS = new BindingSource();
                    BindingSource childBS = new BindingSource();
                    
                    // Show all the records from parent DataTable in dataGridViewParent
                    parentBS.DataSource = dataset.Tables["Movies"];
                    dataGridViewParent.DataSource = parentBS;
                   
                    // Create and add in the DataSet the DataRelation between parent DataTable and child DataTable
                    DataColumn parentPK = dataset.Tables["Movies"].Columns["id"];
                    DataColumn childFK = dataset.Tables["Languages"].Columns["movie_id"];
                    DataRelation relation = new DataRelation("fk_parent_child", parentPK, childFK);
                    dataset.Relations.Add(relation);
                    
                    // Show in dataGridView the child records which are from the parent record selected
                    childBS.DataSource = parentBS;
                    childBS.DataMember = "fk_parent_child";
                    dataGridViewChild.DataSource = childBS;
                }
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message.ToString());
            }
        }
    }
}

.NET framework SQL Server CRUD using data sets and data adapters

Answers (1)

Related Questions