Tutorial: MSSQL Database Google Employee Form [ Part 2]
Add Employee, Edit Employee, Delete Employee
Dr. Thomas E. Hicks
Computer Science Department
Trinity University
1] This tutorial was designed to show how to create a basic windows form which will enable the user to update a Microsoft SQL Server database.
2] User skills will vary; for the sake of completeness, I have created a number of support tutorials that have been created to illustrate and teach various aspects of the Visual Studio .NET application environment and SQL Server 2008 along the way. These tutorial are all hyper-linked in red and appear just before you are likely to need them. Good Luck!
Tutorial: MSSQL Database Google Employee Form [ Part 1]
Tutorial: MSSQL Database Google Employee Form [ Part 2]
1] All of the code developed in Part 1 of this tutorial is available for download on our Server.
Tutorial: Visual Studio Windows Application - Forms - Menu Driven Application With Multiple Forms
Tutorial : Visual Studio Windows Application - Forms - MenuStrip Driven Application
1] Create another C# Windows Application Form
2] Name the form AddEmployee.
3] Apply your desired Font and Color Scheme to the AddEmployee Form.
4] Include a Text commercial "Google Application - Add Employee - Written By ...."
5] We would like the MenuStrip to control all form opening and closing for the project. The following block of code disables the close button on our form. Add it to the AddEmployee form.
private const int CS_NOCLOSE = 0x200;
protected override CreateParams CreateParams
{
get
{
CreateParams cp = base.CreateParams;
cp.ClassStyle = cp.ClassStyle | CS_NOCLOSE;
return cp;
}
}
6] Add a button, named btnClose, to the AddEmployee form.
7] Enable the click event of button btnClose to hide the AddEmployee form.
8] We would like the MainMenu to create, hide, and show all forms in the Google Application.
9] Return to the MainMenu form. Create an AddEmployee object, called addEmployee, at the top of the MainMenu class; this is the first step toward making this form available to other forms and functions in our project.
10] Creating the form object does not make it visible.
Return to the MainMenu form. Double-Click on
Add Employee to create a click event for the MenuStrip button. Add the code to
display form AddEmployee.
11] Associate this AddEmployee form with the MDI container MainMenu. This association should be done in the MainMenu initialization. This binds the our Add Employee form to our MainMenu parent container.
12] Run the application. Note that the close button is disabled on both forms. Note that the AddEmployee form may be opened and closed repeatedly. Note that the AddEmployee form stays within the confines of the MainMenu window. Note that you may change the size of the MainMenu window. Note that the AddEmployee form can be minimized; it drops to the bottom of the MainMenu window.
Tutorial: Visual Studio Windows Application - Forms - Basic Forms
Tutorial: Visual Studio Windows Application - Forms - Labels & TextBoxes
Tutorial: Visual Studio Windows Application - Forms - Buttons & Color Picker
1] Create a "First" and a textbox named txtFirst.label
2] Create a label "Last" and a textbox named txtLast.
3] Create a label "City" and a textbox named txtCity.
4] Create a label "State" and a textbox named txtState.
5] Create a label "Zip" and a textbox named txtZip.
6] Create a label "Major ID" and a textbox named txtMajorID.
7] Create a label "Education (#Years)" and a textbox named txtEdYears.
Tutorial: Database Form Guidelines
8] Align the prompt labels and the named textboxes in accordance with the good database form guidelines.
1] Just in case you have had problems, I have zipped the Google Application above and made it available for download on our Server.
Tutorial: Visual Studio Windows Application - Forms - MSSQL GUI Connections & DataGrids
1] We are going to use SQL in a number most of the rest of our forms. It contains a number of built in class functions that provide SQL access to our data. Include the System.Data.SqlClient Namespace at the top of the AddEmployee.cs.
1] You can run your program and collect data, but as of yet, we have no way to put it into the database.
1] Let us change the text on the Close button to read "Cancel/Close".
2] Add a button, called btnAdd whose text is "Add This Record To Our Database".
3] In accordance with ConnectionStrings.com, let us define our Connection String
to load the
Google
database on
Laptop\SQL. Note the need to use the
\\ escape sequence in the connection string. Create a string object, called
ConnectionString, and fill it in accordance with your SQL Server Name.
Add the line below to your form and test it; your form should load, but there
will be no evidence that the database stuff is working yet. (See Below!)
4] Create a connection object, called Conn. Add the line below to
your form and test it; your form should load, but there will be no evidence that
the database stuff is working yet. (See Below!)
6] We have established a connection and created a query command. We are now
going to create a data adapter object, called da.
The SqlDataAdapter holds the SQL commands
and connection object for reading and writing data. We are going to initialize
it by passing our SQL Query and our Connection object. The code above creates a new SqlDataAdapter, da. The SQL select
statement specifies what data will be read into a DataSet. The connection
object, Conn, has already been instantiated, but not opened. As you
can see in the diagram above, It is the SqlDataAdapter's responsibility to open
and close the connection when Fill and Update method are called. Add the line below to your form and test it; your form should load, but there
will be no evidence that the database stuff is working yet.
7] The results of our query command are going to be returned to the data
table in our application. We are now going to create a data table object, called
dt. Add the line below to your form and test it; your form should load,
but there will be no evidence that the database stuff is working yet. (See
Below!)
8] Once we have created the data table, we can use the data adapter to
fill it with data in accordance with our diagram above. Add the line below to
your form and test it; your form should load, but there will be no evidence that
the database stuff is working yet. (See Below!)
9] This will enable us to test and see if the Add is working. 10] The entire code for btnAdd may be seen below. 11] You can see the Tom record when you run the DisplayEmpoyees form.
Try it. 12] Those of you, who prefer to use SQL Server Management
Studio Express, can see the Tom record as well. Try it.
13] Do not proceed until you get this working. Double-check the configuration
above if problems. You must be running this program on the computer which
contains the SQL Server. If this works, our connection is working. If not, fix
it.
14] My students have difficulty getting the queries perfect. You can use the
message box function to check it out.
15] When you run the program, the following message box appears and an
additional Tom record has been added the Employee Table.
16] I am now ready to work on the query more seriously. I try and I get the following: 17] Some of my students would think it is right and uncomment out the query
and go for it. 18] It fails, but the 19] The error is near the ) ==> at the end ==> don't need a comma at
the end. Try again. The correct code is below. 20] Well I tried it and nothing seemed to happen. I do check the
DisplayEmployees. The record is there?String ConnectionString = "Data Source=
Laptop\SQL;Integrated Security=True;" +
"Initial Catalog=Google";
SqlConnection Conn = new SqlConnection(ConnectionString);
5] Create string object, called Query. We may, or may not be successful running the query. To begin with, I will execute a query that I know will work.
String Query = "INSERT INTO EMPLOYEES (First) VALUES ('Tom')";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
String ConnectionString = "Data Source=Laptop\\SQL;Integrated Security=True;" +
"Initial Catalog=Google";
SqlConnection Conn = new SqlConnection(ConnectionString);
String Query = "INSERT INTO EMPLOYEES (First) VALUES ('Tom') ";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
String ConnectionString = "Data Source=Laptop\\SQL;Integrated Security=True;" +
"Initial Catalog=Google";
SqlConnection Conn = new SqlConnection(ConnectionString);
String Query = " INSERT INTO EMPLOYEES (First, Last, City, State, Zip, MajorID, EDYears) VALUES (" +
" '" + txtFirst.Text + "', " +
" '" + txtLast.Text + "', " +
" '" + txtCity.Text + "', " +
" '" + txtState.Text + "', " +
" '" + txtZip.Text + "', " +
" " + txtMajorID.Text + ", " +
" " + txtEdYears.Text + ", " + ")";
MessageBox.Show(Query);
// SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
// DataTable dt = new DataTable();
// da.Fill(dt);
21] We need to let the user know that we have added data and maybe blank out the form for the next time. I change the code to the following:
String EmployeeName = txtFirst.Text + " " + txtLast.Text;
MessageBox.Show("Employee [" + EmployeeName + "] Has Been Added To The Database");
txtFirst.Text = "";
txtLast.Text = "";
txtCity.Text = "";
txtState.Text = "";
txtZip.Text = "";
txtMajorID.Text = "";
txtEdYears.Text = "";
this.Hide();
22] I enter Dr. Eggen and push the Add Button
23] I get verification
24] My window goes away and is blank next time I use it.
25] Dr. Eggen Shows Up In My Database.
1] All of the code developed in Part 1 of this tutorial is available for download on our Server.
1] The EditEmployee Form is going to be much like the AddEmployee form. We could start another form, set the fonts, set the colors, add the labels and textboxes, etc. That sounds like too much work! We could reduce it some by copy and past from our AddForm, but that is also too much work.
2] There are lots of ways you can reduce the workload. I tried a number of them, including custom templates. I prefer to make an extra copy of the project. I launch the copy and change (1) every occurrence of AddEmployee to EditEmployee and (2) every occurrence of Add Employee to Edit Employee. I then copy files into the folder containing the original project.
3] This is simple and can be done in 2-3 minutes. I have had student try a number of other approaches; it is amazing how many hours they can spend trying to undo the messes they create.
4] I have included my EditEmployee form files below; you are welcome to add them to your application folder.
5] Right-mouse click on the Google application ==> Select Add == Select Existing Item
6] Hold the shift key with the left hand and select the three EditEmployee files with the right hand. Push the Add button.
7] The program continues to work, but the page is not linked into the application.
8] We would like the MainMenu to create, hide, and show all forms in the Google Application.
9] Return to the MainMenu form. Create an EditEmployee object, called editEmployee, at the top of the MainMenu class; this is the first step toward making this form available to other forms and functions in our project.
10] Creating the form object does not make it visible.
Return to the MainMenu form. Double-Click on
Edit Employee to create a click event for the MenuStrip button. Add the code to
display form EditEmployee.
11] Associate this EditEmployee form with the MDI container MainMenu. This association should be done in the MainMenu initialization. This binds the our Edit Employee form to our MainMenu parent container.
12] This isn't quite the way we plan to call the Edit Employee Form, but it is a good starting point. We need some way to determine which employee to edit?
Tutorial: Visual Studio Windows Application - Forms - Menu Driven Application With Multiple Forms
Tutorial : Visual Studio Windows Application - Forms - MenuStrip Driven Application
1] Create another C# Windows Application Form
2] Name the form EditGetID.
3] Apply your desired Font and Color Scheme to the EditGetID Form.
4] Include a Text commercial "Google Application - Get Employee ID"
5] Add btnClose
6] Add btnEditNow
7] Return to the MainMenu form. Create an EditGetID object, called editEmployee, at the top of the MainMenu class; this is the first step toward making this form available to other forms and functions in our project.
8] Creating the form object does not make it visible.
Return to the MainMenu form. Change the Edit
Employee click event to load the EditGetID form.
9] Associate this EditEmployee form with the MDI container MainMenu. This association should be done in the MainMenu initialization. This binds the our Edit Employee form to our MainMenu parent container.
10] We are now launching the EditGetID form from the application menu.
11] Add the code for the close button.
1] Just in case you have had problems, I have zipped the Google Application above and made it available for download on our Server.
1] Return to the MainMenu form. Let us declare a public container, called EmployeeRecNo in MainMenu. MainMenu is our parent form. We would like form EditGetID to fill this public variable with the numerical value entered by the user. We would like form EditEmployee to read the EmployeeRecNo from the MainMenu and load the contents of the designated ID into the form fields for editing. EditGetID and EditEmployee are child forms.
2] Return to form EditGetID. Declare a reference, pf, to our parent form.
3] Configure the EditGetID form to be a client form.
4] Return to the MainMenu parent form. We are going to have to alter the way we handle editGetID.
public partial class MainMenu : Form
{
public long EmployeeRecNo = 4;
DisplayMajors displayMajors = new DisplayMajors();
DisplayEmployees displayEmployees = new DisplayEmployees();
AddEmployee addEmployee = new AddEmployee();
public EditEmployee editEmployee;
public EditGetID editGetID;
5] In order to transfer data to and from the parent, the child form is launched slightly differently.
editGetID = new EditGetID(this);
editGetID.MdiParent = this;
editGetID.Show();
7] Return to form EditGetID. Let us put enough code in btnEditNow to verify that we can both access and change public variable EmployeeRecNo (which resides in the MainMenu parent form.
MessageBox.Show("RecNo = " + pf.EmployeeRecNo.ToString());
pf.EmployeeRecNo = Convert.ToInt32(txtRecID.Text);
MessageBox.Show("RecNo = " + pf.EmployeeRecNo.ToString());
8] Run the application. Push the Edit Employee button.
9] RecNo = 4. This verifies that the child form, EditGetID, can access EmployeeRecNo in the parent.
10] RecNo = 14. This verifies that the child form, EditGetID, can access EmployeeRecNo in the parent.
11] Now that we know that we have stored the EmployeeRecNo properly, let us create the editEmployee form and open it and close the ediitGetID form.
12] This Works. Try It! We have used form editGetID to store the EmployeeRecNo in MainMenu.
1] MainMenu still contains a public container, called EmployeeRecNo We would like form EditEmployee to read the EmployeeRecNo from the MainMenu and load the contents of the designated ID into the form fields for editing. EditGetID and EditEmployee are child forms.
2] Return to form EditEmployee. Declare a reference, pf, to our parent form.
3] Configure the EditEmployee form to be a client form.
4] Change the following:
5] We can use btnAdd to verify that we have access to the EmployeeRecNo. Comment out all of remainder of the SQL stuff left over from function Add.
6] We have access to the EmployeeRecNo.
12] This Works. Try It! We have used form editEmployee to retreive the EmployeeRecNo from the MainMenu.
1] We know that pf.EmployeeRecNo contains the designated ID. When this form is loaded, we would like to read the record from the database.
2] Since many of my students crash the system with invalid queries, we shall immediately display the Query; we will later delete both of these MessageBox displays.
3] If the query is successful, dt will be an array that contains all of the records matching the query (if any).
String ConnectionString = "Data Source=Laptop\\SQL;Integrated Security=True;" +
"Initial Catalog=Google";
SqlConnection Conn = new SqlConnection(ConnectionString);
String Query = " SELECT * " +
" FROM Employees " +
" WHERE ID = " + pf.EmployeeRecNo.ToString();
MessageBox.Show(Query);
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
MessageBox.Show("First = " + dt.Rows[0]["First"].ToString());
4] Running the program with an ID = 1
produces a correct query
which reads Mickey Mouse into dt.Rows[0]
5] We can now get rid of the two MessageBox displays and add the code to populate the textboxes of our form with the data in dt.Rows[0]
private void EditEmployee_Load(object sender, EventArgs e)
{
txtFirst.Text = "";
txtLast.Text = "";
txtCity.Text = "";
txtState.Text = "";
txtZip.Text = "";
txtMajorID.Text = "";
txtEdYears.Text = "";
String ConnectionString = "Data Source=Laptop\\SQL;Integrated Security=True;" +
"Initial Catalog=Google";
SqlConnection Conn = new SqlConnection(ConnectionString);
String Query = " SELECT * " +
" FROM Employees " +
" WHERE ID = " + pf.EmployeeRecNo.ToString();
MessageBox.Show(Query);
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
txtFirst.Text = dt.Rows[0]["First"].ToString();
txtLast.Text = dt.Rows[0]["Last"].ToString();
txtCity.Text = dt.Rows[0]["City"].ToString();
txtState.Text = dt.Rows[0]["State"].ToString();
txtZip.Text = dt.Rows[0]["Zip"].ToString();
txtMajorID.Text = dt.Rows[0]["MajorID"].ToString();
txtEdYears.Text = dt.Rows[0]["EdYears"].ToString();
}
6] Running the program with an ID = 1
now takes you directly to
1] Delete btnAdd
and replace it with btnUpdate
2] Create a query to update the database record. Add a temporary MessageBox so that you can double-check the query; then delete it.
3] Suppose the Employees were
and that we chose to edit ID = 2
and that the Miney record started out with
and that Miney's record sere changed to
When updated, we should get the confirmation
and DisplayEmployees should verify the changes. Unfortunately, the display does not reflect the change? You may have already discovered this problem.
4] If you load Microsoft SQL Server Management Studio Express, you note that the changes were correctly made. If you restart the program, you get the following? It is now time to correct the probelm.
1] When possible, we generally find it more efficient to create the forms in the MainMenu and hide and show them. The GridView is updated only when it is created. I am sure there is a way to update the grid after creation, but I have not worked to find it.
2] Thus I offer a solution that is somewhat of a hack job.
3] Return to the MainMenu. We will re-create the form each time we want to display it; thus it will populate.
4] The form will actually be created when clicking the DisplayEmployee button.
5] Return to the DisplayEmployeeForm and close the form.
6] Now all will work well.
1] Just in case you have had problems, I have zipped the Google Application above and made it available for download on our Server.
1] The DeleteGetID Form is going to be much like the EditGetID form. We could start another form, set the fonts, set the colors, add the labels and textboxes, etc. That sounds like too much work! We could reduce it some by copy and past from our AddForm, but that is also too much work.
2] There are lots of ways you can reduce the workload. I tried a number of them, including custom templates. I prefer to make an extra copy of the project. I launch the copy and change (1) every occurrence of Edit to Delete and (2) every occurrence of edit to delete. I then copy files into the folder containing the original project.
3] This is simple and can be done in 2-3 minutes. I have had student try a number of other approaches; it is amazing how many hours they can spend trying to undo the messes they create.
4] I have included my DeleteGetID form files below; you are welcome to add them to your application folder.
5] Right-mouse click on the Google application ==> Select Add == Select Existing Item
6] Hold the shift key with the left hand and select the three DeleteGetID files with the right hand. Push the Add button.
7] The program continues to work, but the page is not linked into the application.
8] Form DeleteGetID need not share information in a parent-child relationship. Remove the parent stuff.
9] Remove the code associated with btnDeleteNow on DeleteGetID form.
10] We would like the MainMenu to create, hide, and show all forms in the Google Application.
11] Return to the MainMenu form. Create an DeleteEmployee object, called DeleteEmployee, at the top of the MainMenu class; this is the first step toward making this form available to other forms and functions in our project.
12] Creating the form object does not make it visible.
Return to the MainMenu form. Double-Click on
Delete Employee to create a click event for the MenuStrip button. Add the code to
display form DeleteEmployee.
13] Associate this DeleteGetID form with the MDI container MainMenu. This association should be done in the MainMenu initialization. This binds the our DeleteGetID form to our MainMenu parent container.
14] We are ready to add the query and delete the record. See our form below!
1] Add the SQL Library to the DeleteGetID form.
2] Add the code for the btnDeleteNow
1] Just in case you have had problems, I have zipped the Google Application above and made it available for download on our Server.