How to create a simple Student Management Software with Visual Studio 2022 and Microsoft SQL Server 2022

Timothytnk — Wed, 22 Mar 2023 08:43:56 +0000

This guide is for demonstrating how to use Visual Studio 2022 and Microsoft SQL Server 2022 to create a new Windows Forms Application that let's you manage student records stored in a Microsoft SQL Server database environment.

The first step is to make sure you have Visual Studio Software development tool, you can download either the Professional or Community versions from the official Microsoft Visual Studio website.
The second step is to make sure you have Microsoft SQL Server 2022 installed and you can download it from the official Microsoft SQL Server website. Choose the Developer edition and hit the download option. Run the installer once the download is complete and you should see the window below.

Choose basic and accept the Terms and Conditions in the next window that shows up which should be similar to the window below.

After you have accepted the terms and conditions you will be prompted to confirm the location you would like to install the program in the next screen.

After you have specified the location where you would like Microsoft SQL Server to be installed then you will see a progress bar for the installation process in the next screen, wait for the installation to finish.

After Microsoft SQL Server has finished installing then you will see the Setup Successful screen with very important information you can use for establishing a connection to the database from the Windows Forms application.

In the above screen you can see the Text Box labeled as Connection String click on the clipboard icon next to it to copy the connection string and save it to a text file using Notepad or any other relevant text editing tool.
Close the SQL Server 2022 installer window by clicking the button labeled Close at the bottom of the screen.

How to create a new Project in Visual Studio 2022

Launch Visual Studio 2022 and you should see the Visual Studio screen launcher which looks similar to the screenshot below.

Then the Window with the options to either Create New project or Open an existing Project will show up

From the above window click on Create New Project and
you should see a window with Combo box options to specify the Programing Language , Operating System and the Platform the project is targeting. The window should look like similar to the screenshot attached below.

From the above window click on the Programming Language Combo box and select C# then click on the next Combo box to choose Windows as the Operating System targeted and finally click on the last Combo box and set the platform targeted to be Desktop. You will see the templates list dynamically updating itself to list all those templates that fit the criteria you specified above. Several templates such as Windows Forms App, WPF App, WPF Class Library, WPF User Control Library, WPF Custom Control Library et cetera will be listed similar to the output shown below.

Click on the Windows Forms App template on the screen above and then click on Next to view the Window that let's you specify the Name of the Project and the Location you would like to save the project.

From the window above type the Name (mandatory) of the project as you please and you can also(optional) choose where you would like to save the project. Click on Next to bring up the Window that should let you choose the .NET Framework you would like to work with.

While in the Window above you can either choose .NET 6.0(Long Term Support) or .NET 7.0(Standard Term Support), click on Create to load the new Project in Visual Studio 2022.

Using the Designer to add User Interface Controls

After the project is successfully created and loaded into Visual Studio 2022 then you should see the Designer open as a tab in Visual Studio. The designer window looks similar to the output shown below.

While in the above Window you can click on View->Toolbox or simply type the keyboard shortcut Ctrl + Alt + X to show the Toolbox that will be useful for adding Text Box elements that will help us in capturing student information from a user using the application. After pressing the keyboard shortcut above then you should see the Toolbox exposed similar to the output shown below.

You can also search for a control by typing in the search box at the top of the Toolbox like in the screenshot attached below. I have searched for the control Label

Click on a Label control from the Toolbox and move the cursor back to the form designer, the cursor should look like a cross hair. Click on the Form and the control should be generated on the form like in the screenshot below.

Click on the Label control above and right click on it to show a context menu similar to the screenshot output below.

Click on Properties to show the Properties Window of the Label similar to the screenshot attached below.

Locate the Text property of the Label control from the Properties window highlighted above and change its value to be Student First Name. After changing the property hit Enter on the keyboard, it should be similar to the output shown below.

Hit the keyboard shortcut Ctr + Alt + X to show the Toolbox, type Textbox in the search bar and click on the TextBox Control to copy it and then move the cursor back to the form designer and click on the area below the first label we created, it should automatically generate the Textbox like shown in the output shown below.

The Textbox is generated below the first label, You can drag it and place it to the right of our Label so that the design is similar to the output below.

You can always resize the Form by dragging the edges of the for either vertically or horizontally to increase the width and height respectively. To do this click on the little white boxes on the edges of the Form and drag them right, left or down to increase the size of the form. See attached screenshot for details on locating those squares on the designer.

After dragging the squares to increase the size of the form you should see an output similar to the screenshot below.

Repeat the same procedure for the Fields Student Last Name, Student Email, Student Phone, Student Address, Student City and Student Nationality to have an interface that looks something similar to the screenshot shown below.

Reopen the Toolbox via the keyboard shortcut Ctrl + Alt + X and search for a Button control and Drag it to the Form to have an interface for the application that looks like below.

Click on the Button control you just added to show the Properties of that Button, find the Text Property and change it to be Register Student, see the screenshot below for more details.

You will notice that when you update the Text property of the button from the Properties window then the text inside the Button in the Form also changes.
Save all the changes made by hitting Ctrl + S
Add a Menu Strip to the top of the Form which will contain the commands to Edit, Retrieve and Delete Student information from the database. To add a Menu Strip to the form hit the keyboard shortcut (Ctrl + Alt + X) to show the Toolbox and then type MenuStrip in the search bar, click on it and drag it to the designer. You can also just click it then release the mouse button then move the cursor to the designer and click on an empty area on the designer and the menu strip will be autogenerated on the form.

Click on the above control, release the mouse and once again click on the designer to generate the control on the form. You should expect to see an output like the one in the screenshot below.

Click on the menuStrip1 item at the bottom of the window labeled with the yellow arrow and you should see an empty textbox at the top of the form where you will need to type the name of the first item of the Menu Strip, after typing the text Retrieve you should see an output that matches the screenshot below.

Click on the Textbox next to the one we just typed the text Retrieve in and type the text Edit so the interface is updated to look like the screenshot below.

Then click on the Textbox next to the Edit menu strip and add a third menu strip item called Delete. After adding the third menu strip the interface of the app should be updated to look like the screenshot below.

Save changes by hitting Ctrl + S on the keyboard.

Configuring MySQL Server 2022 and connecting it to the Windows Forms App

Since our app needs to read and write the data of these students to and from a database server we have to create a Database called College and a Table called Students in that Database which will store the actual information captured from a student via the interface of the Windows Forms App.

SQL Server 2022 runs automatically when the device boots and we only need to connect to it from a Command Line Interface and run all queries to create the Databases and Tables.
In order to do that we need to follow the steps below religiously:

Open Command Prompt and type the following command to connect to SQL Server 2022 remotely.

sqlcmd -S localhost -E.

If the above command is successful then you should see the path on your Command Line Terminal replaced with the text >1 like in the screenshot shown below.

After you have connected to the Database Server then you need to run commands to create a new Database named College with the syntax below.

CREATE DATABASE College
GO

If the command is successful then you will see a screenshot similar to the one below.

Create a new Table named Students in the same College Database with the columns for student_id, student_fname, student_lname, student_phone, student_address, student_city and student_nationality with the syntax below.

USE College;
CREATE TABLE Student (
    student_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    address VARCHAR(100),
    city VARCHAR(50),
    nationality VARCHAR(50)
);

After running the command above successfully then the terminal will show a new line for you to type in the next command like in the screenshot below.

Now go back to the Form Designer and double click on the Form and on a section on the form with no controls for example you could double click on the section with the red spot like in the screenshot below.

After you have clicked on the red spot shown in the screenshot above then the IDE will autogenerate a method that gets invoked when the form is loaded. The method signature takes after the method called **** on the screenshot below.

In that method we are going to write C# source code that connects to the SQL Server Database and shows a Message Box alert if the connection was successful.
Open the text file where you saved the connection string and copy the connection string by clicking Ctrl + C. If you didn't save the connection string to a text file then you can copy it from the screenshot below.

Copy the above connection string then double click on the Form to open the method handler for the Form's OnLoad event handler , migrate to the top of the class and add a definition called connectionString like shown in the screenshot below.

Scroll Down and place the cursor inside the method with the signature private void Form1_Load(object sender, EventArgs e){} and place the following code in it.

try
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            MessageBox.Show("Connection successful!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Connection failed. Error message: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }

After pasting the code you should see an output similar to the screenshot below where the type SqlConnection shows a compile time error.

To remove the error above, Hit the keyboard shortcut Ctrl + Enter and you should see an option named Install Package System.Data.SqlClient that corresponds the screenshot below.

From the screenshot above, choose Install Package System.Data.Client and then choose Find and Install The Latest version
which should find and install the System.Data.SqlClient into the project. This will get rid of the error so that the final state of the code in the Form is like in the screenshot below.

Debugging the Program

In order for us to test if the program is working as expected, we need to use the Debug Button to Run the program. You can always find the Debug option at the top of the window as a green rotated triangle pointing to the right and it's highlighted in the screenshot below.

Click on one of the two green triangles in the screen above to run the program and you should get an output similar to the screenshot below where the connection to SQL Server Database is successful.

The info in screenshot above shows that the program is now connected to SQL Server successfully.
Click on OK on the Message Dialog in the dialog above to clear it so that the form application is shown like in the screenshot below.

Close the Form and click the Form1.cs file in the Solution Explorer, Right Click on it and choose View Designer. The step is clearly highlighted in the screenshot below.

On the Designer double click on the Register Student button and the code for handling Click Events for this button should be generated in similar output to the screenshot below.

Validating User Input On the Form

Since the program requires the Textbox fields on the program to be filled, we need logic in the event handler of the button to check if the user tries to submit empty fields and prompt him/her to enter the correct values. For example the Student First Name field has some few rules like Name should not be less than three characters, Name should not contain Numbers or Special Characters, Name should not be empty. To achieve this we have the reference of the Textbox as textbox1, we add C# code into the button handler that will make sure those conditions are met. Sample code is shown below.

string input = textBox1.Text;
    if (input. Length < 3)
    {
        MessageBox.Show("The text must be at least 3 characters long.", "Invalid Input", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        return;
    }
    else if (string.IsNullOrWhiteSpace(input))
    {
        MessageBox.Show("The text cannot be empty.", "Invalid Input", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        return;
    }
    else if (Regex.IsMatch(input, @"[^a-zA-Z0-9 ]"))
    {
        MessageBox.Show("The text contains special characters.", "Invalid Input", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        return;
}

After pasting the code above in the button_1_Click event then my code looks like the screenshot attached below.

After updating the code, Run the program by clicking on the green run button and you should see that when you attempt to click on the Register Student button while the Textbox for Student First Name is empty then the program shows errors for the user to correct. Sample output is shown in the screenshots below.

You can define the validation rules for the other Textbox elements by changing the reference of the code above to include textBox1, textBox2 and so on and so forth.
We are now going to insert our first student record into the database and to do that we will write a method that takes the input from the form as parameters and runs a query against the database and returns true if the operation was successful otherwise it will return false if there was a problem or an exception.
The method code is highlighted below.

public bool InsertStudentRecord(string firstName, string lastName, string phone, string address, string city, string nationality)
        {
            string query = "INSERT INTO Student (first_name, last_name, phone, address, city, nationality) VALUES (@studentId, @firstName, @lastName, @phone, @address, @city, @nationality);";

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(query, connection);
                    command.Parameters.AddWithValue("@firstName", firstName);
                    command.Parameters.AddWithValue("@lastName", lastName);
                    command.Parameters.AddWithValue("@phone", phone);
                    command.Parameters.AddWithValue("@address", address);
                    command.Parameters.AddWithValue("@city", city);
                    command.Parameters.AddWithValue("@nationality", nationality);
                    int result = command.ExecuteNonQuery();

                    if (result > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

Open the Form1.cs file and paste the method above into it so that the code file is updated to look something similar to the screenshot below.

Scroll to the method that handles the events for our Register Student button and use the method we pasted above in a conditional structure similar to the code highlighted below

private void button1_Click_1(object sender, EventArgs e)
        {
            string input = textBox1.Text;

            if (input.Length < 3)
            {
                MessageBox.Show("The text must be at least 3 characters long.", "Invalid Input", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            else if (string.IsNullOrWhiteSpace(input))
            {
                MessageBox.Show("The text cannot be empty.", "Invalid Input", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            else if (Regex.IsMatch(input, @"[^a-zA-Z0-9 ]"))
            {
                MessageBox.Show("The text contains special characters.", "Invalid Input", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            } 
            //Validation for other Input fields
            //if everything works as expected then get student details

            //get the first name of the student
            var fname = textBox1.Text;
            //get the last name of the student
            var lname = textBox2.Text;
            //get the contact of the student
            var phone = textBox3.Text;
            //get the address of the student
            var address = textBox4.Text;
            //get the city of the student
            var city = textBox5.Text;
            //get the nationality of the student
            var nation = textBox6.Text;
            if (InsertStudentRecord(fname, lname, phone, address, city, nation))
            {
                MessageBox.Show("Student record inserted successfully into the database");
            }
            else {
                MessageBox.Show("Insert operation failed, check exception for details");

            }
        }

Run the program and make sure you fill all the fields and then click on the Register Student button.
If you have done everything correctly then the program should be successful and will show an alert that the Student information was inserted into database successfully. See screenshot below for more details.

Now open the designer to view the Menu Strip items Retrieve, Edit and Delete. The opened designer will take after the screenshot below. Double Click on the Menu Strip item labeled Retrieve which is pointed at by an orange arrow.

After double clicking on the Retrieve menu strip item then the IDE will generate a method that will enable us to program the Click event for that particular menu strip. We need to program this menu strip item to accept a student ID from the user and returns a list of data about that student from database. The method is generated and shown in a similar fashion to the screenshot below.

We need to capture student_id information from the user so that we can use it to retrieve all the information about the student with that student_id from database , we will show an Input Dialog to prompt the user to enter the student ID using the code below. Paste the code below in the method shown in the screenshot above.

var studentID = Microsoft.VisualBasic.Interaction.InputBox("Please enter your student ID:", "Enter Student ID");
            //check if the user did not enter anything or a non number literal
            var empty = studentID == string.Empty;
            if (empty)
            {
                //user did not enter student ID, show a message box explainging why
                MessageBox.Show("The student ID is required to be able to retrieve information about that student");
                //do not execute further code
                return;
            }
            else
            {
                //check if the user did enter something but a non integer literal
                if(Int32.TryParse(studentID, out int result))
                {
                    //user entered the correct input
                    //get the integer and query for all the information for that student from database

                }
                else
                {
                    //user entered a an incorrect input
                    MessageBox.Show("You entered an incorrect integer literal");
                    return;
                }
            }

After pasting the above code in the retrieveToolStripMenuItem_Click method then the user is prompted to enter the student ID when he clicks on the item.

We will write another method that takes an integer argument as student ID, queries the database for all the information about that student and returns a List of data about that student. The method signature is shown in the screenshot below.

public List<string> GetStudentData(string studentId)
        {
            // Set up a connection to your database
            SqlConnection connection = new SqlConnection(connectionString);
            // Create a SQL query that selects all columns from a table called "students"
            // where the "id" column matches the specified studentId.
            string query = "SELECT * FROM student WHERE studet_id = @studentId";
            SqlCommand command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@studentId", studentId);

            // Open the connection to the database and execute the query.
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

            // Read the data returned by the query and add each column's value to a list of strings.
            List<string> studentData = new List<string>();
            while (reader.Read())
            {
                studentData.Add(reader["id"].ToString());
                studentData.Add(reader["name"].ToString());
                studentData.Add(reader["age"].ToString());
                studentData.Add(reader["major"].ToString());
                // Add more columns here as needed.
            }

            // Close the reader and the connection to the database.
            reader.Close();
            connection.Close();

            // Return a tuple containing the student data list and the count of the list.
            return studentData;
        }

Paste the code in your Form1.cs file so that it is nested inside the class like shown in the screenshot below.

After pasting the code inside your class then update your retrieveToolStripMenuItem_Click handler to look like below.

private void retrieveToolStripMenuItem_Click(object sender, EventArgs e)
        {
            var studentID = Microsoft.VisualBasic.Interaction.InputBox("Please enter your student ID:", "Enter Student ID");
            //check if the user did not enter anything or a non number literal
            var empty = studentID == string.Empty;
            if (empty)
            {
                //user did not enter student ID, show a message box explainging why
                MessageBox.Show("The student ID is required to be able to retrieve information about that student");
                //do not execute further code
                return;
            }
            else
            {
                //check if the user did enter something but a non integer literal
                if(Int32.TryParse(studentID, out int result))
                {
                    //user entered the correct input
                    //get the integer and query for all the information for that student from database\
                    var data = GetStudentData(studentID);
                    if (data.Count == 0)
                    {
                        //no student was found with that ID
                        MessageBox.Show("No student with such ID exists in the database");
                        return;
                    }
                    if(data.Count >0)
                    {
                        //build a student info string
                        var info = "Names: " + data[0] + " " + data[1] + "\n" +
                            "Phone: " + data[2] + "\n" +
                            "Address: " + data[3] + "\n" +
                            "City: " + data[4] + "\n" + "\n" +
                            "Nationality: " + data[5];
                        MessageBox.Show(info,"Student Found");
                    }
                }
                else
                {
                    //user entered a an incorrect input
                    MessageBox.Show("You entered an incorrect integer literal");
                    return;
                }
            }
        }

Run the program, click on Retrieve and pass 1 as the studentID parameter and click OK to view information about the student with that ID.

If you inserted a student record into the database then this operation should be successful and you should see a message box with the student information similar to the screenshot below.

Open the Designer by clicking on the file Form1.cs[Design]*. **Double Click the Edit menu strip item at the top of the window, the menu strip item to double click is pointed at with the orange arrow in the screenshot below.

After double clicking on the menu strip above you should be able to see a method that has been generated by the IDE for handling the click events made on the Edit menu strip item. The method should be similar to the screenshot below.

We will place code in that method that will also require the student ID parameter from the user and then shows input dialog with the title for each property of that record that the user intends to edit. The code is highlighted in the code section below.

    //display an input dialog for the student ID the user would like to edit
            var studentID = Microsoft.VisualBasic.Interaction.InputBox("Please enter the  student ID you want to edit:", "Enter Student ID");
            //cegck if the input is empty
            var empty = studentID == string.Empty;
            if (empty)
            {
                MessageBox.Show("You have to enter the ID of the student you would like to edit", "Student ID error");
                //do not execute further code
                return;
            }
            else
            {
                //if not empty try and parse an integer literal out of the string
                if(Int32.TryParse(studentID,out var b))
                {
                    //integer parsed legal
                    //check if such a record exists in the database
                    //if true show the input dialog to capture the new values for the student object
                }
                else
                {
                    //integer parsed is illegal
                    MessageBox.Show("The integer literal you entered is invalid");
                }
            }

Paste the code above in the editToolStripMenuItem_Click method in the Form1.cs file. After that we need to define a Boolean method that will check if a row with the student ID specified for editing purposes exists in the database table then we show a message box if a record is found and show fields for the user to edit the record if the record is found. Code section is shown below.

public bool CheckIfRecordExists(string studentId)
        {
            bool recordExists = false;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = "SELECT COUNT(*) FROM Student WHERE student_id = @studentId";
                SqlCommand command = new SqlCommand(query, connection);
                command.Parameters.AddWithValue("@studentId", studentId);
                connection.Open();
                int count = (int)command.ExecuteScalar();
                if (count > 0)
                {
                    recordExists = true;
                }
                else
                {
                }
            }
            return recordExists;
        }

Paste the code above in the method level of your Form1.cs file. After pasting the method above you should see the file updated to be similar to the output below.

Use the method above in the editToolStripMenuItem_Click method and to check if there is an existing database table entry associated with that student ID or not. Here is a screenshot showing how to use the method to check for the record.

Run the program, find the Edit menu strip command and click on it, immediately you should be prompted for a student ID parameter entry via an Input Dialog message box as shown in the screenshot below.

Pass a sample student ID parameter, in this case I passed the parameter 1 and clicked on OK. You will see a prompt to edit the first name of the student with the ID you just passed.
I will type the name Williams to update the first name of the record with the student ID 1, I will then click OK and wait for the program to show the message that the Update operation was successful. The screenshot below shows the value I entered for updating the first name of the entry with the student ID parameter 1.

DEV Community: Timothytnk

How to create a simple Student Management Software with Visual Studio 2022 and Microsoft SQL Server 2022

How to create a new Project in Visual Studio 2022

Using the Designer to add User Interface Controls

Configuring MySQL Server 2022 and connecting it to the Windows Forms App

Debugging the Program

Validating User Input On the Form