ADO.Net CRUD Operations Notes

ADO.Net CRUD Operations

Introduction to ADO.Net

• ADO.Net allows applications to connect to databases to perform Create, Read, Update, and Delete (CRUD) operations.

Connecting Application to Database using SQL Server

• Connect to SQL Server using Windows Authentication.
• Specify the Server type and Server name.

Database and Table Creation

• Create a database named BelgiumCampusDB.
• Create a Students table with columns: StudentID (int), LastName (varchar(255)), FirstName (varchar(255)), and CourseID (varchar(255)).

SQL Code:

/*Creating database*/
CREATE DATABASE BelgiumCampusDB;
Use BelgiumCampusDB

/*Creating Tables*/
CREATE TABLE Students (
    StudentID int,
    LastName varchar(255),
    FirstName varchar(255),
    CourseID varchar(255)
);

/*Inserting values into table*/
Use BelgiumCampusDB
INSERT INTO dbo.Students
VALUES
(13456, 'Cardinal', 'Erichsen', 'PRG282'),
(13458, 'Ryan', 'Zengeni', 'PRG281'),
(13476, 'Mbali', 'Senyane', 'PRG282'),
(13416, 'Misha', 'Lillah', 'PRG272');

/*Displaying table*/
SELECT * FROM dbo.Students;

Class Diagram and Program Classes

• Use a class diagram to create program classes and files.

Form Functionality

• Adding a New Student:
  • The user enters student details, and the app registers the new student in the database.
• Searching for a Student:
  • When the user clicks the Search button, a new window (SearchForm) opens.
  • Entering a StudentID fetches all details of that student from the database.
• Deleting a Student:
  • When the user clicks the Delete button, a new window (DeleteForm) opens.
  • Entering a StudentID deletes all details of that student from the database.
• Updating Student Details:
  • The user enters details of an existing student and updates the database.

Students Class

• Defines a Student class with properties for studentID (int), Name (string), Surname (string), and CourseID (string).

internal class Student
{
    internal int studentID {get; set; }
    internal string Name { get; set; }
    internal string Surname { get; set; }
    internal string CourseID { get; set; }
    public Student() { }
}

DataHandler Class

• Handles database interactions.
• Includes methods for registering, updating, deleting, and searching student records.

internal class DataHandler
{
    public DataHandler() { }
    static string connect = "Data Source=.; Initial Catalog = StudentInfo; Integrated Security= SSPI;";
    SqlConnection con;
    SqlCommand command;
    SqlDataAdapter adapt;
}

DataHandler Methods

• Register(): Adds a new student record to the database.

public void Register(int stID, string n, string s, string c)
{
    string query = $"INSERT INTO Students VALUES ('{stID}', '{n}', '{s}', '{c}')";
    con = new SqlConnection(connect);
    con.Open();
    command = new SqlCommand(query, con);
    try
    {
        command.ExecuteNonQuery();
        MessageBox.Show("Details Saved");
    }
    catch (Exception ex)
    {
        MessageBox.Show("Details Not Saved" + ex.Message);
    }
    finally
    {
        con.Close();
    }
}

• Update(): Modifies an existing student record in the database.

public void Update (int stID, string n, string s, string c)
{
    string query = $"UPDATE Students SET [StudentID] = '{stID}', [LastName] = '{s}', " +
                    $" [FirstName] = '{n}', [CourseID] = '{c}' WHERE [StudentID] = '{stID}";
    con = new SqlConnection(connect);
    con.Open();
    command = new SqlCommand(query, con);
    try
    {
        command.ExecuteNonQuery();
        MessageBox.Show("Details Updated");
    }
    catch (Exception ex)
    {
        MessageBox.Show("Details Not Updated" + ex.Message);
    }
    finally
    {
        con.Close();
    }
}

• Delete(): Removes a student record from the database.

public void Delete(int stID)
{
    string query = $"DELETE FROM Students WHERE StudentID = '{stID}'";
    con = new SqlConnection(connect);
    con.Open();
    command = new SqlCommand(query, con);
    try
    {
        command.ExecuteNonQuery();
        MessageBox.Show("Details deleted");
    }
    catch (Exception ex)
    {
        MessageBox.Show("Details Not Deleted" + ex.Message);
    }
    finally
    {
        con.Close();
    }
}

• Search(): Retrieves a student record from the database.

public DataTable Search(int stID)
{
    string query = $"SELECT * FROM Students WHERE StudentID = '{stID}';";
    con = new SqlConnection(connect);
    adapt = new SqlDataAdapter(query, con);
    DataTable table = new DataTable();
    adapt.Fill(table);
    return table;
}

Implementation in Form1

• Declare instances of DataHandler and Student classes.

DataHandler handler = new DataHandler();
Student student = new Student();

• Register Button Click Event: Get values from textboxes and call the Register() method.

private void button1_Click(object sender, EventArgs e)
{
    student.studentID = int.Parse(textBox1.Text);
    student.Name = textBox2.Text;
    student.Surname = textBox3.Text;
    student.CourseID = textBox4.Text;
    handler.Register(student.studentID, student.Name, student.Surname, student.CourseID);
}

• Update Button Click Event: Get values from textboxes and call the Update() method.

private void button3_Click(object sender, EventArgs e)
{
    student.studentID = int.Parse(textBox1.Text);
    student.Name = textBox2.Text;
    student.Surname = textBox3.Text;
    student.CourseID = textBox4.Text;
    handler.Update(student.studentID, student.Name, student.Surname, student.CourseID);
}

• Search Button Click Event: Open the SearchForm window.

private void button2_Click(object sender, EventArgs e)
{
    SearchForm s = new SearchForm();
    s.Show();
}

• Delete Button Click Event: Open the DeleteForm window.

private void button4_Click(object sender, EventArgs e)
{
    DeleteForm d = new DeleteForm();
    d.Show();
}

Implementation in SearchForm

• Search Button Click Event: Get the student ID from the textbox, call the Search() method, and display the results in a DataGridView.

private void button1_Click(object sender, EventArgs e)
{
    Student student = new Student();
    student.studentID = int.Parse(textBox1.Text);
    DataHandler handler = new DataHandler();
    dataGridView1.DataSource = handler.Search(student.studentID);
}

Implementation in DeleteForm

• Delete Button Click Event: Get the student ID from the textbox and call the Delete() method.

private void button1_Click(object sender, EventArgs e)
{
    Student student = new Student();
    student.studentID = int.Parse(textBox1.Text);
    DataHandler handler = new DataHandler();
    handler.Delete(student.studentID);
}

Exit Button

private void button2_Click(object sender, EventArgs e)
{
    Environment.Exit(0);
}

Exercise

• Create classes (DataHandler, DogDetails, Form1) and a form for a