In this tutorial we are going to improve upon our previous article by adding the ability to not only add data into the SQL database but be able to view it, delete it and update it. This will be done by adding in 3 new SQL methods in the connect.cs. First wee will start by revamping the Graphical User Interface (GUI) as we want to be able to view our data once entered and not have to leave the application itself. The end result of this will be the image below.

To get the image to look like the one above we will need to do some reorganizing of our application GUI. TO start this we need to open the project in visual studio and make sure our toolbox is pinned open for easy access. After this is complete we can resize the application to accommodate our new tools we will be adding. The first thing we will want to add is the GroupBox control from the toolbox and set the Text to Applicants and and we are going to resize it to look like the picture above. This may be easier if you move the submit button first to snap to the right hand side of the application and then resize the GroupBox to snap to the Submit button to give the whole application a more consistent look and feel. After we have added the Groupbox and positioned it in relation to the Submit button we can now add two more buttons under the Submit button we already have. The first will be the update button so we will need to change the Text to Update and the Name of the control to btnUpdate. The last button we will give it the Text of Delete and the Name of btnDelete. After this is done and they are aligned under the first button we moved we need one more object on the form before we can proceed, and that will be the ListView object.

After dragging this into the GroupBox we named Applicants earlier we can now double click on the Delete button and then go back into design mode and double click on the update button to create the event handlers for both of those buttons. Now to make the event handlers for the ListView we will need to go into design view of our forms application and click on the icon (in the properties panel) that resembles a lightning bolt (image can be found below for reference). This will give us all of the event handlers that a particular object is capable of handling, in this case the ListView. In this case we want the DoubleClick event handler to be inserted into our code. To do this we find it in the properties panel and double click on the text that says DoubleClick and the event handler will be created in our main.cs file. We are now going to move onto completing the SQL statements as we need to complete any database work as it will be the key to manipulating our data.

The first method we will code is the select method that will pull all information from the SQL Database (including the ID) and return it to us in a ListView which we will pass to it as a parameter. Once passed as a parameter we can then use the variable to add an item to the list view and the sub items (the rest of the data) to that item. To do this we will need a SQL Statement first that will collect all of the information and should look like the following:

SELECT * FROM applicantRegistration
public void select(ListView lv)
{
SqlDataReader reader = null;
lv.Items.Clear();
try
{
connection = new SqlConnection(conn);
connection.Open();

string qry = "SELECT * FROM applicantRegistration";

SqlCommand cmd = new SqlCommand(qry, connection);

reader = cmd.ExecuteReader();

while (reader.Read())
{
string[] results = { Convert.ToString(reader[1]), Convert.ToString(reader[2]),
Convert.ToString(reader[3]), Convert.ToString(reader[4]),
Convert.ToString(reader[5]), Convert.ToString(reader[6]),
Convert.ToString(reader[7]), Convert.ToString(reader[8]),
Convert.ToString(reader[9]), Convert.ToString(reader[10]),
Convert.ToString(reader[11]) };
lv.Items.Add(Convert.ToString(reader[0])).SubItems.AddRange( results);
}
}
finally
{
if (connection != null)
connection.Close();
}
}

The above is the select method fully fleshed out and ready to accept a ListView object. You might notice that we are not using ExecuteNonQuery on this statement as we are actually querying the database for information. Thus, we will use the cmd.ExecuteReader() call to return our results to an array of strings nestled within a while loop to enable us to get one result set at a time. After the array is returned we can insert the item at index zero (0) as the item in the ListView then create an array of strings called results to insert as the sub items. This will all be done within the while loop so it will populate the entire ListView with all of the entries found within our applicantsRegistration table.

The next method we are going to code for the connect.cs class to access the information within our SQL database will be the delete method and this one will be fairly straight forward. We will only want to delete one row of data from the SQL table and to do this we will need to get the ID of the row we want to eliminate as it is the only column that cannot possibly be a duplicate (which would result in multiple row deletion). To fix this we will need to take in a integer parameter with a name of id when using the delete method call. The SQL statement should look like the following and the method will be posted below it:

DELETE FROM applicantRegistration WHERE ID = id;
public void delete(int id)
{
try
{
connection = new SqlConnection(conn);
connection.Open();

string qry = "DELETE FROM applicantRegistration WHERE ID ='" + id + "';";

SqlCommand cmd = new SqlCommand(qry, connection);

cmd.ExecuteNonQuery();
}
finally
{
if (connection != null)
connection.Close();
}
}

The final method call we are going to code is update and it will take a total of twelve (12) parameters. This is because we need to pull in all of the form data and the id (which we will use to update just a single row). Both the delete and update statements will be calling the cmd.ExecuteNonQuery function as they are not querying anything. The following is what the SQL statement should look like and the method fully fleshed out will be below it.

UPDATE applicantRegistration SET firstname = firstname, lastname = lastname, address = address, city = city, zipcode = zipcode, email = email, phone = phone, day = day, month = month, year = year, gender = gender WHERE id = id;
public void update(int id, string firstname, string lastname, string address, string city, string zipcode, string email, string phone, string day, string month, string year, string gender)
{
try
{
connection = new SqlConnection(conn);
connection.Open();

string qry = "UPDATE applicantRegistration SET firstname = '" + firstname + "', lastname = '" + lastname + "', address = '" + address + "', city = '" + city + "', zipcode = '" + zipcode + "', email = '" + email + "', phone = '" + phone + "', day = '" + day + "', month = '" + month + "', year = '" + year + "', gender = '" + gender + "' WHERE id = " + id + ";";

SqlCommand cmd = new SqlCommand(qry, connection);

cmd.ExecuteNonQuery();
}
finally
{
if (connection != null)
connection.Close();
}
}

The only method left in connect.cs that hasn’t been coded is the call to update our ListView with the latest settings. To do this the method will be called custLV and will accept a parameter of a ListView object. A brief overview of the settings we will be modifying will include:

  • Clear – will clear the entire ListView object including all data and columns created
  • Enabled – will decide if the ListView is greyed out and accessable to be modified and manipulated
  • View – can be set to a variety of options including
    • View.Details
    • View.LargeIcon
    • View.List
    • View.SmallIcon
    • View.Tile
  • FullRowSelect – will allow the selection of the whole row instead of just the first column in the list
  • GridLines – will show faint lines denoting different rows
  • Scrollable – will make scroll bars appear when the space on the visible part of the ListView has been consumed by data
  • Columns – we add these in manually using the .Add function and these will appear across the top of the ListView object (horizontally)
    • First Name
    • Last Name
    • Address
    • City
    • Zip Code
    • Email
    • Phone
    • Day
    • Month
    • Year
    • Gender

Below you will find the completely fleshed out method if you haven’t already coded it yet.

public void custLV(ListView lv)
{
lv.Clear();
lv.Enabled = true;
lv.View = View.Details;
lv.FullRowSelect = true;
lv.GridLines = true;
lv.Scrollable = true;
lv.Columns.Add("ID", 25, HorizontalAlignment.Left);
lv.Columns.Add("First Name", 50, HorizontalAlignment.Left);
lv.Columns.Add("Last Name", 50, HorizontalAlignment.Left);
lv.Columns.Add("Address", 50, HorizontalAlignment.Left);
lv.Columns.Add("City", 50, HorizontalAlignment.Left);
lv.Columns.Add("Zip Code", 50, HorizontalAlignment.Left);
lv.Columns.Add("Email", 50, HorizontalAlignment.Left);
lv.Columns.Add("Phone", 50, HorizontalAlignment.Left);
lv.Columns.Add("Day", 50, HorizontalAlignment.Left);
lv.Columns.Add("Month", 50, HorizontalAlignment.Left);
lv.Columns.Add("Year", 50, HorizontalAlignment.Left);
lv.Columns.Add("Gender", 50, HorizontalAlignment.Left);
}
}

Now we need to jump back to our Main.cs and view our event handlers we created earlier. Now that we are in the code view we will need to go directly under

public partial class Main : Form
{
connect conn = new connect();

and add in the reference to the connect.cs class we created so we are able to use any methods coded in that class. Before we start adding in code for the event handlers we will need to go to the Main_Load event handler and add in at the very bottom just before the } so that your code for Main_Load will look like:

private void Main_Load(object sender, EventArgs e)
{
for (int i = DateTime.Now.Year; i >= 1900; --i)
cbYear.Items.Add(i);

cbMonth.Items.Add("January");
cbMonth.Items.Add("February");
cbMonth.Items.Add("March");
cbMonth.Items.Add("April");
cbMonth.Items.Add("May");
cbMonth.Items.Add("June");
cbMonth.Items.Add("July");
cbMonth.Items.Add("August");
cbMonth.Items.Add("September");
cbMonth.Items.Add("October");
cbMonth.Items.Add("November");
cbMonth.Items.Add("December");

conn.custLV(lvApplicantsView);
conn.select(lvApplicantsView);
//lvApplicantsView = conn.select();
}

After this is done we can turn our coding eye onto the three event handlers and look at how we are going to make our application able to edit and delete items. The first event handler we are going to tackle will be the Delete Button. To accomplish this deletion we need to make sure something is selected in the ListView. To do this we can use the lvApplicantsView.SelectedItems.Count method to return a value of the number of selected items in the list view and if it is greater than zero (0) we will take the ID from the selected item and pass it to our delete method in connect.cs. After the delete has been performed we will want to run the select statement again so we can update the ListView to show the most currrent up to date information. Below is the code for the Delete Button event handler:

private void btnDelete_Click(object sender, EventArgs e)
{
if(lvApplicantsView.SelectedItems.Count > 0)
conn.delete(Convert.ToInt32(lvApplicantsView.SelectedItems[0].Text));
conn.select(lvApplicantsView);
}

Next we have the DoubleClick event handler for the ListView. We will want to make a global integer called id for use with updating the ListView information and can be inserted at the top of our main.cs just outside any event handlers. At the start of this method we are going to want to set our global id variable to zero so we don’t update the wrong item. After this is done we will check to make sure that a row of data is actually selected then we can go through the row of the ListView and insert the data found there back into their respective textboxes. After the data is replicated in the Applicant Registration form above the ListView any data that is edited can be saved by pressing the Update button. On the same note you can also press the submit button and it would submit a new line of data. The code below is the DoubleClick event handler:

private void lvApplicantsView_DoubleClick(object sender, EventArgs e)
{
id = 0;
if (lvApplicantsView.SelectedItems.Count > 0)
{
id = Convert.ToInt32( lvApplicantsView.SelectedItems[0].SubItems[0].Text);
txtFirstName.Text = lvApplicantsView.SelectedItems[0].SubItems[1].Text;
txtLastName.Text = lvApplicantsView.SelectedItems[0].SubItems[2].Text;
txtAddress.Text = lvApplicantsView.SelectedItems[0].SubItems[3].Text;
txtCity.Text = lvApplicantsView.SelectedItems[0].SubItems[4].Text;
txtZipCode.Text = lvApplicantsView.SelectedItems[0].SubItems[5].Text;
txtEmail.Text = lvApplicantsView.SelectedItems[0].SubItems[6].Text;
txtPhone.Text = lvApplicantsView.SelectedItems[0].SubItems[7].Text;
cbDay.Text = lvApplicantsView.SelectedItems[0].SubItems[8].Text;
cbMonth.Text = lvApplicantsView.SelectedItems[0].SubItems[9].Text;
cbYear.Text = lvApplicantsView.SelectedItems[0].SubItems[10].Text;
if (lvApplicantsView.SelectedItems[0].SubItems[11].Text == "female")
rbFemale.Checked = true;
else
rbMale.Checked = true;
}

}

The last event handler is the Update button which will pull all of the information from our form into the update method in connect.cs and drop all of the text into string variables for updating in the database. We first need to check and make sure the global variable id is not zero before doing anything else. If it is then we need to let the user know they need to double click on an item before proceeding. Once the user does have valid data we need to drop off of the objects text into the update method and after it had run we need to call the select method to get the most current information. The completed method is below for reference:

private void btnUpdate_Click(object sender, EventArgs e)
{
string gender;
if (rbFemale.Checked == true)
gender = "female";
else
gender = "male";

if (id != 0)
{
conn.update(id, txtFirstName.Text, txtLastName.Text,
txtAddress.Text, txtCity.Text,
txtZipCode.Text, txtEmail.Text,
txtPhone.Text, cbDay.Text, cbMonth.Text, cbYear.Text, gender);
conn.select(lvApplicantsView);
}
else
MessageBox.Show("Please double click an item to update it!");
}

Now with all of the event handlers coded and the SQL statements in place we can run our application and should see the first entry we posted in the previous article. If this works correctly we can then enter new information in the form and click submit again and watch as the information is updated in the ListView. In that same respect this can be done for the Delete and Update buttons as well. For the sake of clearing up any errors that may occur due to mistyped code the complete source of connect.cs and main.cs will be included below.


// File: Main.cs
// Author: Greg R. Jacobs
// Date: June 1st, 2011

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace ApplicantRegistration
{
public partial class Main : Form
{
connect conn = new connect();
int id = 0;
public Main()
{
InitializeComponent();
}

private void Main_Load(object sender, EventArgs e)
{
for (int i = DateTime.Now.Year; i >= 1900; --i)
cbYear.Items.Add(i);

cbMonth.Items.Add("January");
cbMonth.Items.Add("February");
cbMonth.Items.Add("March");
cbMonth.Items.Add("April");
cbMonth.Items.Add("May");
cbMonth.Items.Add("June");
cbMonth.Items.Add("July");
cbMonth.Items.Add("August");
cbMonth.Items.Add("September");
cbMonth.Items.Add("October");
cbMonth.Items.Add("November");
cbMonth.Items.Add("December");

conn.custLV(lvApplicantsView);
conn.select(lvApplicantsView);
//lvApplicantsView = conn.select();
}

private void btnSubmit_Click(object sender, EventArgs e)
{
string gender;
if (rbFemale.Checked == true)
gender = "female";
else
gender = "male";
using (FileStream fileStream = new FileStream(@"C:\applicants.txt",
FileMode.Append, FileAccess.Write))
{
StreamWriter sr = new StreamWriter(fileStream, Encoding.UTF8);

sr.WriteLine(txtFirstName.Text + "," + txtLastName.Text +
"," + txtAddress.Text + "," + txtCity.Text + "," +
txtZipCode.Text + "," + txtEmail.Text + "," +
txtPhone.Text + "," + gender + "," + cbYear.Text +
"," + cbMonth.Text + "," + cbDay.Text +
"~");
sr.Close();
}
try
{
conn.insert(txtFirstName, txtLastName, txtAddress, txtCity, txtZipCode, txtEmail, txtPhone, gender,
cbDay, cbMonth, cbYear);
}
catch (Exception ex)
{
MessageBox.Show("Error has occured - " + ex, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
txtFirstName.Text = "";
txtLastName.Text = "";
txtAddress.Text = "";
txtCity.Text = "";
txtZipCode.Text = "";
txtEmail.Text = "";
txtPhone.Text = "";

conn.select(lvApplicantsView);
}

private void rbMale_CheckedChanged(object sender, EventArgs e)
{
if (rbMale.Checked == true)
rbFemale.Checked = false;
else
rbFemale.Checked = true;
}

private void rbFemale_CheckedChanged(object sender, EventArgs e)
{
if (rbFemale.Checked == true)
rbMale.Checked = false;
else
rbMale.Checked = true;
}

private void cbMonth_SelectedValueChanged(object sender, EventArgs e)
{
cbDay.Items.Clear();
if (cbMonth.Text == "September" || cbMonth.Text == "April" || cbMonth.Text == "June" || cbMonth.Text == "November")
{
for (int i = 1; i <= 30; ++i)
cbDay.Items.Add(i);
}
else if (cbMonth.Text == "January" || cbMonth.Text == "March" || cbMonth.Text == "May" || cbMonth.Text == "July" || cbMonth.Text == "August" || cbMonth.Text == "October" || cbMonth.Text == "December")
{
for (int i = 1; i <= 31; ++i)
cbDay.Items.Add(i);
}
else
{
for (int i = 1; i <= 28; ++i)
cbDay.Items.Add(i);
}
}

private void btnUpdate_Click(object sender, EventArgs e)
{
string gender;
if (rbFemale.Checked == true)
gender = "female";
else
gender = "male";

if (id != 0)
{
conn.update(id, txtFirstName.Text, txtLastName.Text,
txtAddress.Text, txtCity.Text,
txtZipCode.Text, txtEmail.Text,
txtPhone.Text, cbDay.Text, cbMonth.Text, cbYear.Text, gender);
conn.select(lvApplicantsView);
}
else
MessageBox.Show("Please double click an item to update it!");
}

private void btnDelete_Click(object sender, EventArgs e)
{
if(lvApplicantsView.SelectedItems.Count > 0)
conn.delete( Convert.ToInt32(lvApplicantsView.SelectedItems[0].Text));
conn.select(lvApplicantsView);
}

private void lvApplicantsView_DoubleClick(object sender, EventArgs e)
{
id = 0;
if (lvApplicantsView.SelectedItems.Count > 0)
{
id = Convert.ToInt32 (lvApplicantsView.SelectedItems[0].SubItems[0].Text);
txtFirstName.Text = lvApplicantsView.SelectedItems[0].SubItems[1].Text;
txtLastName.Text = lvApplicantsView.SelectedItems[0].SubItems[2].Text;
txtAddress.Text = lvApplicantsView.SelectedItems[0].SubItems[3].Text;
txtCity.Text = lvApplicantsView.SelectedItems[0].SubItems[4].Text;
txtZipCode.Text = lvApplicantsView.SelectedItems[0].SubItems[5].Text;
txtEmail.Text = lvApplicantsView.SelectedItems[0].SubItems[6].Text;
txtPhone.Text = lvApplicantsView.SelectedItems[0].SubItems[7].Text;
cbDay.Text = lvApplicantsView.SelectedItems[0].SubItems[8].Text;
cbMonth.Text = lvApplicantsView.SelectedItems[0].SubItems[9].Text;
cbYear.Text = lvApplicantsView.SelectedItems[0].SubItems[10].Text;
if (lvApplicantsView.SelectedItems[0].SubItems[11].Text == "female")
rbFemale.Checked = true;
else
rbMale.Checked = true;
}

}
}
}
// File: Connect.cs
// Author: Greg R. Jacobs
// Date: June 1st, 2011

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace ApplicantRegistration
{
class connect
{
public string conn = "Data Source=EXCELLENCEPC;Initial Catalog=applicants;Integrated Security=SSPI;";
SqlConnection connection;

public void insert(TextBox firstname, TextBox lastname, TextBox address,
TextBox city, TextBox zipcode, TextBox email, TextBox phone,
String gender, ComboBox day, ComboBox month, ComboBox year)
{
try
{
SqlCommand cmd;

connection = new SqlConnection(conn);
string qry = "INSERT INTO applicantRegistration(firstname, lastname, address, city, zipcode, email, phone, day, month, year, gender)VALUES('" + firstname.Text + "','" + lastname.Text + "','" + address.Text + "','" + city.Text + "','" + zipcode.Text + "','" + email.Text + "','" + phone.Text + "','" + day.Text + "','" + month.Text + "','" + year.Text + "','" + gender + "');";
cmd = new SqlCommand(qry, connection);
if (connection.State == ConnectionState.Open)
connection.Close();
else
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
finally
{
if (connection != null)
connection.Close();
}
}

public void update(int id, string firstname, string lastname, string address, string city, string zipcode, string email, string phone, string day, string month, string year, string gender)
{
try
{
connection = new SqlConnection(conn);
connection.Open();

string qry = "UPDATE applicantRegistration SET firstname = '" + firstname + "', lastname = '" + lastname + "', address = '" + address + "', city = '" + city + "', zipcode = '" + zipcode + "', email = '" + email + "', phone = '" + phone + "', day = '" + day + "', month = '" + month + "', year = '" + year + "', gender = '" + gender + "' WHERE id = " + id + ";";

SqlCommand cmd = new SqlCommand(qry, connection);

cmd.ExecuteNonQuery();
}
finally
{
if (connection != null)
connection.Close();
}
}

public void select(ListView lv)
{
SqlDataReader reader = null;
lv.Items.Clear();
try
{
connection = new SqlConnection(conn);
connection.Open();

string qry = "SELECT * FROM applicantRegistration";

SqlCommand cmd = new SqlCommand(qry, connection);

reader = cmd.ExecuteReader();

while (reader.Read())
{
string[] results = { Convert.ToString(reader[1]), Convert.ToString(reader[2]),
Convert.ToString(reader[3]), Convert.ToString(reader[4]),
Convert.ToString(reader[5]), Convert.ToString(reader[6]),
Convert.ToString(reader[7]), Convert.ToString(reader[8]),
Convert.ToString(reader[9]), Convert.ToString(reader[10]),
Convert.ToString(reader[11]) };
lv.Items.Add( Convert.ToString(reader[0])).SubItems.AddRange(results);
}
}
finally
{
if (connection != null)
connection.Close();
}
}

public void delete(int id)
{
try
{
connection = new SqlConnection(conn);
connection.Open();

string qry = "DELETE FROM applicantRegistration WHERE ID ='" + id + "';";

SqlCommand cmd = new SqlCommand(qry, connection);

cmd.ExecuteNonQuery();
}
finally
{
if (connection != null)
connection.Close();
}
}

public void custLV(ListView lv)
{
lv.Clear();
lv.Enabled = true;
lv.View = View.Details;
lv.FullRowSelect = true;
lv.GridLines = true;
lv.Scrollable = true;
lv.Columns.Add("ID", 25, HorizontalAlignment.Left);
lv.Columns.Add("First Name", 50, HorizontalAlignment.Left);
lv.Columns.Add("Last Name", 50, HorizontalAlignment.Left);
lv.Columns.Add("Address", 50, HorizontalAlignment.Left);
lv.Columns.Add("City", 50, HorizontalAlignment.Left);
lv.Columns.Add("Zip Code", 50, HorizontalAlignment.Left);
lv.Columns.Add("Email", 50, HorizontalAlignment.Left);
lv.Columns.Add("Phone", 50, HorizontalAlignment.Left);
lv.Columns.Add("Day", 50, HorizontalAlignment.Left);
lv.Columns.Add("Month", 50, HorizontalAlignment.Left);
lv.Columns.Add("Year", 50, HorizontalAlignment.Left);
lv.Columns.Add("Gender", 50, HorizontalAlignment.Left);
}
}

}

With our application now able to connect to an SQL Server 2008 database and manipulate data by adding in, updating and deleting information we could say we have a complete application but how do we deploy it for many users to access and try it out? In the next tutorial we will cover deploying an application to a website and having users download it and when the application starts it will check the web page first for updates to the application. Until the next tutorial, Happy Hacking!

Share This
%d bloggers like this: