Simple Student Enrollment System – Connected Approach
We will create a Simple Enrollment System that allows you to search for a student in the database using a specified StudentID. It will also allow you to add or remove a student from the database or update his/her student information. We will use the connected approach for querying, inserting, removing, and updating records. The next tutorial will show another version showing how to create the same project using the disconnected version.
Create a new windows forms application and create a user interface similar to the one shown in Figure 1.
Figure 1
| Label | Name | Properties |
|---|---|---|
| 1 | textBoxStudentId | |
| 2 | textBoxFirstName | Enabled: False |
| 3 | textBoxLastName | Enabled: False |
| 4 | textBoxGender | Enabled: False |
| 5 | textBoxAge | Enabled: False |
| 6 | textBoxAddress | Enabled: False |
| 7 | buttonDelete | Text: Delete |
| 8 | buttonEdit | Text: Edit |
| 9 | buttonAddNew | Text: Add New |
| 10 | buttonShow | Text: Show |
Figure 2
Go to the Code Editor by pressing F7 and be sure to first import the two necessary namespaces for this database connection.
using System.Data;
using System.Data.SqlClient;Add the following private fields into the Form1 class:
private SqlConnection connection;
private SqlCommand command;
private SqlDataReader reader;
private bool updateReady;
private bool insertReady;We proclaimed the fields that will be utilized for associating and issuing directions to the database. We declared them as class fields instead of as local variables so they will be shared by all database operations (For example, no need to declare Connection objects for querying and another one for inserting records). The updateReady and inserReady field will be utilized later to refresh and inserting new student. On the Form’s constructor include the codes in lines 5-11.
public Form1()
{
InitializeComponent();
connection = new SqlConnection();
command = connection.CreateCommand();
connection.ConnectionString = @"Data Source=.\SQLEXPRESS;" +
"Initial Catalog=University; Integrated Security=SSPI";
updateReady = false;
insertReady = false;
}Example 1
Line 5 initializes our Connection object. Line 6 uses the DbConnection.CreateCommand() method which returns a DbCommand object that is already connected to the DbConnection object that calls that method. In this case, calling the CreateCommand() method returns a SqlCommand object with its Connection property already referring to the SqlConnection object. Line 7-8 indicates the connection string for the connection. Lines 10-11 sets the updateReady and insertReady flags to false. This simply means that the program is initially not allowing an update or addition of new students. The text fields except the StudentID are initially disabled so no updates or insertions can be made. The program for now can only do queries to the database by typing the StudentId in textBoxStudentId and clicking the buttonShow. The code for the Click event of buttonShow is as follows:
private void buttonShow_Click(object sender, EventArgs e)
{
command.CommandText = "SELECT * FROM Students WHERE [email protected]";
command.Parameters.Clear();
command.Parameters.AddWithValue("@StudentID", textBoxStudentID.Text);
try
{
connection.Open();
reader = command.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
textBoxFirstName.Text = reader["FirstName"].ToString();
textBoxLastName.Text = reader["LastName"].ToString();
textBoxGender.Text = reader["Gender"].ToString();
textBoxAge.Text = reader["Age"].ToString();
textBoxAddress.Text = reader["Address"].ToString();
}
else
MessageBox.Show("StudentID does not exist.");
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
}Example 2
Since we declared our Connection and Command objects as class fields, we can simply change the properties of them depending on the operation to be made. Line 3 changes the CommandText property of our Command object to a SELECT statement the retrieves everything from the Students table that has a StudentID specified. The command text has one command parameter for StudentIDwhich will be replaced by the value typed by the user in textBoxStudentID as shown in line 5. Note that before that, we cleared the Parameters property first because it might already contain some values from other operation. We do that using the Clear() method (line 4). Inside a try block, we first open the connection and create a SqlDataReader using the DbCommand.ExecuteReader() method. We passed CommandBehavior.SingleRow value as an argument to that method so the reader will only return one record which is the record of the student we are searching. Line 12 uses the Read() method to retrieve that single record from the database. If it is successful, then the method will return true, and inside the if statement, we show the values of each column of that record to their respective text boxes. The rest of the code are used when no record is found and when a SqlException is thrown. Don’t forget to close the connection as shown in Line 29.
Next in the list is the buttonAddNew which is responsible for adding or “enrolling” new students to the database. The following is the event handler for the buttonAddNew’s Click event.
private void buttonAddNew_Click(object sender, EventArgs e)
{
if (!insertReady)
{
buttonAddNew.Text = "Enroll";
ClearFields();
textBoxStudentID.Text = GetNextStudentID().ToString();
textBoxStudentID.Enabled = false;
textBoxFirstName.Enabled = true;
textBoxLastName.Enabled = true;
textBoxGender.Enabled = true;
textBoxAge.Enabled = true;
textBoxAddress.Enabled = true;
buttonShow.Enabled = false;
buttonEdit.Enabled = false;
buttonDelete.Enabled = false;
insertReady = true;
}
else
{
buttonAddNew.Text = "Add New";
command.CommandText = "INSERT INTO Students " +
"(FirstName, LastName, Gender, Age, Address) VALUES " +
"(@FirstName, @LastName, @Gender, @Age, @Address)";
command.Parameters.Clear();
command.Parameters.AddWithValue("@FirstName", textBoxFirstName.Text);
command.Parameters.AddWithValue("@LastName", textBoxLastName.Text);
command.Parameters.AddWithValue("@Gender", textBoxGender.Text);
command.Parameters.AddWithValue("@Age", textBoxAge.Text);
command.Parameters.AddWithValue("@Address", textBoxAddress.Text);
try
{
connection.Open();
int result = command.ExecuteNonQuery();
if (result > 0)
MessageBox.Show("Student successfully enrolled.");
else
MessageBox.Show("Failed to enroll student.");
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
textBoxStudentID.Enabled = true;
textBoxFirstName.Enabled = false;
textBoxLastName.Enabled = false;
textBoxGender.Enabled = false;
textBoxAge.Enabled = false;
textBoxAddress.Enabled = false;
buttonShow.Enabled = true;
buttonEdit.Enabled = true;
buttonDelete.Enabled = true;
insertReady = false;
}
}Example 3
The buttonAddNew will serve as a toggle button. The insertReady field will decide the activity that this button will perform. Since insertReady is at first false, lines 5-18 will be executed. Line 5 changes the content of the button to “Select” basically to inform the client that this button ought to be clicked once the points of interest are entered. Line 6 considers a technique that will clear every one of the fields if so it will be prepared to acknowledge new points of interest from the client. The definition for the ClearFields() method is as per the following:
private void ClearFields()
{
textBoxFirstName.Text = String.Empty;
textBoxLastName.Text = String.Empty;
textBoxGender.Text = String.Empty;
textBoxAge.Text = String.Empty;
textBoxAddress.Text = String.Empty;
}Example 4
Since we need will be adding a new student, we need to generate the next unique StudentID. We can’t merely group A new student ID as a result of a similar student ID might already be within the database. That’s why, line 7 calls another utility method called GetNextStudentID(). The definition for this method is as follows:
private int GetNextStudentID()
{
command.CommandText = "SELECT IDENT_CURRENT('Students') + IDENT_INCR('Students')";
try
{
connection.Open();
int nextID = Convert.ToInt32(command.ExecuteScalar());
return nextID;
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
return 0;
}Example 5
Line 3 makes a SQL Server direction that will get the following Identity field esteem. Keep in mind that the StudentID field of the Students table is an Identity field which auto-increases when new records are included. The thing is, we can’t simply get the last StudentID and add 1 because SQL server saves the last Identity value used so if we delete a record with StudentID 5, making the new last StudentIDis 4, the next StudentID that will be generated will be 6. There is an SQL Server function called IDENT_CURRENT() which returns the current identity field and IDENT_INCR() which returns the increment value of the identity field.We passed the name of the table to those functions and else their come values to urge the latest StudentID. We open the connection in line 7 and line 8 uses the DbCommand.ExecuteScalar() since we are expecting a single value (not record) to be returned. The value is the generated last StudentID, but we need to convert it first to an integer. Finally, we returned that value to the caller of the method (line 9).
Back to the code in Example 3, after getting a new StudentID, we simply display it using textBoxStudentID. Lines 9-14 enables the text boxes so the user can enter the details for the new student and lines 15-17 disables buttonShow, buttonInsert, and buttonDelete so the user can only click the “Enroll” button. Finally, line 18 changes the value of insertReady to true so the next time the user clicks this button, lines 22-63 will be executed instead.
The first time the user clicks the buttonAddNew, the program is now ready to accept the information for the new student. After the user types the information for each field, the user should now then click the buttonAddNew again to execute the SQL INSERT command to the database. Let’s now discuss lines 22-63 of Example 3 which does the actual communication to the database. Line 22 reverts back the label of the button to “Add New”. Lines 24-26 changes the CommandText property of the Command object to an SQL INSERT statement. The command has 5 command properties for each field. Note that the StudentID was not included because it will automatically generated by SQL Server. Line 27 ensures that no existing command parameter values are in the Parameters collection of the Commandobject by using the Clear() method. Lines 28-32 assigns the value of each text box to their corresponding command parameter. After everything is set and the connection is openned, Line 38 uses the DbCommand.ExecuteNonQuery() method to execute the command to the database. Lines 54-63 disables the text boxes again and reenables the disabled buttons. The insertReady fields was also set a value of false to indicate the insertion process is complete and the user is required to click this button again if he/she will be adding another student.
The buttonEdit also goes about as a flip button and the greater part of the code is like the code of buttonAddNew.
private void buttonEdit_Click(object sender, EventArgs e)
{
if (!updateReady)
{
buttonEdit.Text = "Update";
textBoxStudentID.Enabled = false;
textBoxFirstName.Enabled = true;
textBoxLastName.Enabled = true;
textBoxGender.Enabled = true;
textBoxAge.Enabled = true;
textBoxAddress.Enabled = true;
buttonShow.Enabled = false;
buttonAddNew.Enabled = false;
buttonDelete.Enabled = false;
updateReady = true;
}
else
{
buttonEdit.Text = "Edit";
command.CommandText = "UPDATE Students SET [email protected], " +
"[email protected], Ge[email protected], [email protected], [email protected] " +
"WHERE [email protected]";
command.Parameters.Clear();
command.Parameters.AddWithValue("@FirstName", textBoxFirstName.Text);
command.Parameters.AddWithValue("@LastName", textBoxLastName.Text);
command.Parameters.AddWithValue("@Gender", textBoxGender.Text);
command.Parameters.AddWithValue("@Age", textBoxAge.Text);
command.Parameters.AddWithValue("@Address", textBoxAddress.Text);
command.Parameters.AddWithValue("@StudentID", textBoxStudentID.Text);
try
{
connection.Open();
int result = command.ExecuteNonQuery();
if (result > 0)
MessageBox.Show("Student details successfully updated.");
else
MessageBox.Show("Update failed.");
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
textBoxStudentID.Enabled = true;
textBoxFirstName.Enabled = false;
textBoxLastName.Enabled = false;
textBoxGender.Enabled = false;
textBoxAge.Enabled = false;
textBoxAddress.Enabled = false;
buttonShow.Enabled = true;
buttonAddNew.Enabled = true;
buttonDelete.Enabled = true;
updateReady = false;
}
}Example 6
Clicking the buttonEdit for the first run through will empower the text boxes and incapacitate the buttons with the exception of the buttonEdit itself. We utilized the updateReady field to make the button flip its usefulness.After the changes has been made to the current student, clicking the button the second time will send the changes to the database. Lines 19-60 does this and the codes you just need to edit are the SQL command and the parameter values.
Finally, the buttonDelete‘s Click event handler is shown in Example 7.
private void buttonDelete_Click(object sender, EventArgs e)
{
command.CommandText = "DELETE FROM Students WHERE [email protected]";
command.Parameters.Clear();
command.Parameters.AddWithValue("@StudentID", textBoxStudentID.Text);
try
{
connection.Open();
int result = command.ExecuteNonQuery();
if (result > 0)
{
MessageBox.Show("Student successfully deleted.");
ClearFields();
}
else
MessageBox.Show("Failed to delete student.");
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
}Example 7
We just changed the CommandText property of the command into a SQL DELETE statement that contains one command parameter which is the StudentID to delete. After specifying the command, adding the value for the command parameter, and openning the connection, line 11 uses the ExecuteNonQuery() method to actually delete record with the specified StudentID fromt he database table.
Our simple enrollment system is now complete. To test it, run the program and type an existing StudentID in the first text box. Click the show button to see the student’s information. If you want to edit those information, you can click the buttonEdit, type the new values, and click buttonEdit again to save the changes. You can likewise delete that understudy by clicking buttonDelete. To include or enlist another student, tap the buttonAddNew and type the data of the new student into the fields. Snap the buttonAddNew once more to include the student into the Students database table.