AdoFormExample.cs

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace SGBD_Lab2
{
    public partial class Form1 : Form
    {
        private SqlConnection connection;
        BindingSource bindingSourceParent = new BindingSource();
        BindingSource bindingSourceChild = new BindingSource();
        DataSet dataSet = new DataSet();
        SqlDataAdapter dataAdapter = new SqlDataAdapter();

        private String parentTableName;
        private String childTableName;
        private Int32 childNumberOfColumns;
        private String[] childColumns;
        private String populateParentTableCommand;

        private String insertQuery;
        private String updateQuery;
        private String deleteQuery;
        private String populateChildQuery;

        private TextBox[] textBoxes;
        private Label[] labels;

        public Form1()
        {
            InitializeComponent();

            GetComponentsFromConfig();
        }

        private void GetComponentsFromConfig()
        {
            String con = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
            connection = new SqlConnection(con);

            populateParentTableCommand = ConfigurationManager.AppSettings["populateParent"];
            parentTableName = ConfigurationManager.AppSettings["parentTableName"];
            childTableName = ConfigurationManager.AppSettings["childTableName"];
            childNumberOfColumns = Int32.Parse(ConfigurationManager.AppSettings["childNumberOfColumns"]);
            childColumns = ConfigurationManager.AppSettings["childColumnNames"].Split(',');

            insertQuery = ConfigurationManager.AppSettings["insertQuery"];
            updateQuery = ConfigurationManager.AppSettings["updateQuery"];
            deleteQuery = ConfigurationManager.AppSettings["deleteQuery"];
            populateChildQuery = ConfigurationManager.AppSettings["populateChildQuery"];

            GenerateBoxes();
        }

        private void GenerateBoxes()
        {
            textBoxes = new TextBox[childNumberOfColumns];
            labels = new Label[childNumberOfColumns];

            for (int i = 0; i < childNumberOfColumns; ++i)
            {
                textBoxes[i] = new TextBox();
                labels[i] = new Label {Text = childColumns[i]};

                textBoxPanel.Controls.Add(labels[i]);
                textBoxPanel.Controls.Add(textBoxes[i]);
            }
        }

        private void connectButton_Click(object sender, EventArgs e)
        {
            dataAdapter.SelectCommand = new SqlCommand(populateParentTableCommand, connection);

            if (dataSet.Tables.Contains(parentTableName))
            {
                dataSet.Tables[parentTableName].Clear();
            }

            dataAdapter.Fill(dataSet, parentTableName);
            bindingSourceParent.DataSource = dataSet.Tables[parentTableName];
            parentGridView.DataSource = bindingSourceParent;
            parentGridView_SelectionChanged(this, null);
        }

        private void parentGridView_SelectionChanged(object sender, EventArgs e)
        {
            if (parentGridView.SelectedRows.Count != 1)
            {
                return;
            }

            int selected = parentGridView.SelectedRows[0].Index;
            if (selected == parentGridView.RowCount - 1)
            {
                return;
            }
            String idx = parentGridView.SelectedRows[0].Cells[0].Value.ToString();
            int id = Int32.Parse(idx);


            dataAdapter.SelectCommand = new SqlCommand(populateChildQuery, connection);
            dataAdapter.SelectCommand.Parameters.AddWithValue("@parentID", id);

            if (dataSet.Tables.Contains(childTableName))
            {
                dataSet.Tables[childTableName].Clear();
            }

            dataAdapter.Fill(dataSet, childTableName);
            bindingSourceChild.DataSource = dataSet.Tables[childTableName];
            childGridView.DataSource = bindingSourceChild;
        }

        private void addButton_Click(object sender, EventArgs e)
        {
            dataAdapter.InsertCommand = new SqlCommand(insertQuery, connection);
            for (int i = 0; i < childNumberOfColumns; ++i)
                dataAdapter.InsertCommand.Parameters.AddWithValue("@" + labels[i].Text, textBoxes[i].Text);

            connection.Open();
            try
            {
                dataAdapter.InsertCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + @"Trying to insert without id value");
//                try
//                {
//                    dataAdapter.InsertCommand = new SqlCommand(insertQuery, connection);
//                    for (int i = 1; i < childNumberOfColumns; ++i)
//                    {
//                        dataAdapter.InsertCommand.Parameters.AddWithValue("@" + labels[i].Text, textBoxes[i].Text);
//                    }
//                    dataAdapter.InsertCommand.ExecuteNonQuery();
//                }
//                catch (Exception innerEx)
//                {
//                    MessageBox.Show(innerEx.Message);
//                }
            }
            connection.Close();
            parentGridView_SelectionChanged(this, null);
        }

        private void updateButton_Click(object sender, EventArgs e)
        {
            dataAdapter.UpdateCommand = new SqlCommand(updateQuery, connection);
            for (int i = 0; i < childNumberOfColumns; ++i)
                dataAdapter.UpdateCommand.Parameters.AddWithValue("@" + labels[i].Text, textBoxes[i].Text);

            connection.Open();
            try
            {
                dataAdapter.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            connection.Close();
            parentGridView_SelectionChanged(this, null);
        }

        private void deleteButton_Click(object sender, EventArgs e)
        {
            dataAdapter.DeleteCommand = new SqlCommand(deleteQuery, connection);
            dataAdapter.DeleteCommand.Parameters.AddWithValue("@" + labels[0].Text, textBoxes[0].Text);

            connection.Open();
            try
            {
                dataAdapter.DeleteCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            connection.Close();
            parentGridView_SelectionChanged(this, null);
        }

        private void childGridView_SelectionChanged(object sender, EventArgs e)
        {
            if (childGridView.SelectedRows.Count < 1)
            {
                return;
            }

            int length = childGridView.SelectedRows[0].Cells.Count;
            for (var i = 0; i < length; i++)
            {
                textBoxes[i].Text = childGridView.SelectedRows[0].Cells[i].Value.ToString();
            }
        }
    }
}