This post will show my attempt at the integration of the ExpenseIt WPF (Windows Presentation Foundation) application example
of Microsoft as I implemented it as a straight C# application in the previous
post into a more realistic example.
That is, the WPF example and hence the C# only implementation of the
previous post had canned data. This
post will illustrate the use of multiple Windows forms to show the use of a
database of the employees and their expenses along with the some of the
required maintenance of the database.
The methods to be illustrated are inadequate for an actual
application and are only meant to show some of the considerations as well as
demonstrate my attempt to use the eXploratory Framework to communicate between
the Windows Forms of the display and framework components such as to access the
database.
This post isn’t meant to illustrate a proper employee
database. For instance, normally there
would be an associated identifier for an employee – two employees might have
the same name for instance – and a permanent record would be maintained of past
employees, associated pension data, IRS data, etc etc. But since ExpenseIt doesn’t have such an
identifier it was ignored. It does
illustrate some usage of an Access database.
A real application would separate applications to maintain employees
from that to maintain expenses from one to report the expenses.
Getting Started
I first tried to do a Console application like the other
eXploratory Framework applications but found that I then couldn't add
forms. So I instead created a Windows
Form Application and then added two other forms using the Project|Add Windows
Form… option of the menu bar. I had
Form1 be a Menu form to choose options and had Form3 be the one to add
employees. Later I added Form2 and
Form4 to maintain employees and to add or report expenses.
Since the framework can't be initiated from the Program.cs
file I had the constructor of Form1 do so.
I immediate ran into problems in my attempt to build a
message with the newly filled in text boxes for the added employee. I could call a class that installed the
message to be sent and the response message to be received. But I couldn't invoke a Form3 method to clear
the text boxes. I could get around this
by having a global string in Form3 and monitoring it after calling the class
that sends the message for when it became a non-null string in a loop. Then clearing the form text boxes or
displaying the error message.
However, after the delivery of one or two messages the app
would hang. After trying a number of
times, I finally suspected that there must be problems mixing Windows control
threads to respond to the controls/widgets and the Threading threads used by the
eXploratory Framework.
Workaround
First I tried the application as a console application
without the forms to see if I had a problem in how I was sending and receiving
the messages. No problem there.
So then I did a minimal Forms application where I directly
invoked a class to access the database.
This worked well enough and the call to invoke the method to access the
database could just return the error string or one of "success". Then the textboxes could be cleared if the
response was "success:".
So I could continue and duplicate ExpenseIt but with code to
add employees and add their expenses and then report the expenses of a selected
employee.
But I had greater hopes.
Possibilities
Doing a search for "c# form application with extra
threads" I came across a stackoverflow response to "Multi-threading calls in Windows Forms application?"
where a response suggested
"Check
out Jon Skeet's article on multi-threading, particularly the page on multi-threading
winforms. It should fix you right up. Basically you need to check to see if an invoke is required, and
then perform the invoke if needed. After reading the article you should be able
to refactor your UI-updating code into blocks that look like this …".
This article has a number of web pages but none of them
mention Jon Skeet by name or anything else about him. Doing a search on Jon Skeet, it would appear that he has the
coding blog https://codeblog.jonskeet.uk and
another at https://stackoverflow.blog/2018/05/01/jon-skeet-answers-your...
although this isn't a complete link.
You can follow-up as you like.
Anyway I will see where this leads in the future.
Continuing
For
now, I proceeded without the framework and just did the Windows forms with a
direct interface to an Expenses Database
class to do the SQL to access and update Employees and Expenses tables.
I ended up with four Windows forms, the Menu form, the
Update Employee form, the Add Employee form, and the Expenses form.
The Menu form allows the user to select the activity to be
done. Note - as with the C# version of
the ExpenseIt application, there is a panel across the top to allow navigation
buttons to select the previous and the next form. These buttons could have had the left and right arrow images and
have had their button clicks implemented to move between the forms to
display. However, this was left for
some future time.
Clicking the Add button selects Form3 to add an employee to
the Employees table of the database.
Likewise the Select button causes Form2 to be displayed to allow the
data about an existing employee to be modified or for the employee to be
deleted. The Expense button displays
Form4 for the entry of employee expenses while the Display button displays a
different version of Form4 for the selection and display of the expenses
previously entered for an employee.
Form3 allows the first and last names of an
employee to be entered along with the employee's
department.
Clicking the Add button indicates that the database should
be updated. The lower textbox is for
the display of error messages. One is
in case not all the values are supplied.
Likewise, the database, where the first and last names together form the
key, will result in an error if the combination is attempted to be added
twice. If an error is displayed, the
entered values continue to be displayed to allow for correction. Otherwise, the entered values are cleared
for entry of the next new employee.
Form2 is a little more complicated. It allows for entry of the first and last
names of an employee to be modified or removed or for a list of all current
employees to be displayed so that one can be selected from the list box.
If Modify is selected, a Department label and text box are
displayed so that the only employee data being maintained can be modified. As well as an Update button to allow the
user to indicate that the employee should be updated in the database.
If Remove is selected the Employee table of the database
will have the employee deleted.
If ShowList is clicked, a list box is displayed with all the
employees of the Employee table along with a Cancel button. Selecting one the employees fills in the
first and last names of the employee along with their department. The employee can then be Modified or Removed
or ShowList can be used once again to select a different employee.
Form4 has a number of different options. It is used both for the entry of expenses
for an employee or, like in ExpenseIt, for the display of an employee's
expenses. Allowing for the various
options meant that a number of labels, text boxes, and buttons along with the
employee list box and the expense list boxes of a selected employee have to be
made visible or hidden depending upon the particular option chosen.
Whether or not the Enter or Display Expenses option is
selected on the Menu form, the list of current employees will be displayed to
allow a particular employee to be selected.
The CloseList button can be clicked to return to the Menu. (Note, if the navigation arrows had been
implemented, the left arrow would do the same.)
Then, if Enter Expenses was chosen, the following version of
the form is displayed. The form
displayed shows the expense Description, Amount, and Date already filled in by
the user following the selection of Mona Lisa from the employee list. To cause the database to be updated, the
Enter button has to be clicked. This
will clear the three expense fields to allow another expense to be
entered. To indicate that all the new
expenses of the employee have been entered, the Done button has to be
clicked. When this is done, the
employee list is again displayed.
If the Display Expenses Menu option was chosen, the employee
list is again displayed to allow for the selection of a particular
employee. When an employee is selected,
the list of expenses of the employee is displayed. In this case, Lisa Mona is the same as Lisa in the ExpenseIt
application where Lisa has been supplied with a last name. The Done button indicates that the user has
finished viewing the expenses and the list of employees is again displayed. The CloseList button returns to the Menu.
The Access Tables
In the following tables, more fields have been declared than
have been used. For instance,
EmployeeID and Salary.
Note: John Glenn
(the John of Engineering of ExpenseIt) was inadvertently removed while working
out the descriptions of the forms.
Therefore, he doesn't appear in the above employees table but was there
when the expenses were entered.
The Code
Form1.cs:
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;
namespace Apps
{
public partial class Form1 : Form
{
Form2 updateEmployee = new
Form2();
Form3 addEmployee = new
Form3();
static public Form4 enterExpenses = new
Form4();
public Form1()
{
InitializeComponent();
} // end constructor
private void
Form1_Load(object sender, EventArgs e)
{
}
private void
leftArrow_Click(object sender, EventArgs e)
{
// The implementation needs to keep track of the use of the
arrow buttons
// in common on the various forms. That is, the left arrow needs to be
// able to determine what the previous form was to go back
to it. etc
}
private void
rightArrow_Click(object sender, EventArgs e)
{
}
private void
AddEmployee_Click(object sender, EventArgs e)
{
addEmployee.Show();
} // end AddEmployee_Click
private void
UpdateEmployee_Click(object sender, EventArgs e)
{
updateEmployee.Show();
} // UpdateEmployee_Click
private void
Expenses_Click(object sender, EventArgs e)
{
enterExpenses.expenseEntry = true;
enterExpenses.DisplayEmployeeList();
enterExpenses.Show();
} // // Expenses_Click
private void
Display_Click(object sender, EventArgs e)
{
enterExpenses.expenseEntry = false;
enterExpenses.DisplayEmployeeList();
enterExpenses.Show();
} // Display_Click
} // end Form1 class
} // end namespace
Form2.cs:
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;
namespace Apps
{
public partial class Form2 : Form
{
private EmployeeDatabase
employeeDatabase;
public Form2() // constructor
{
InitializeComponent();
errorMessageBox.Hide();
CancelEmployeeList.Hide();
employeeDatabase = new EmployeeDatabase(this);
} // end constructor
private
void Form2_Load(object
sender, EventArgs e)
{
}
public void
ClearTextBoxes()
{
FirstName.Clear();
LastName.Clear();
} // end ClearTextBoxes
private void
Remove_Click(object sender, EventArgs e)
{
// Clear any error message
errorMessageBox.Clear();
errorMessageBox.Hide();
DepartmentLabel.Hide();
Department.Hide();
ShowList.Show();
// Obtain entered data
string
firstName, lastName;
firstName = FirstName.Text;
lastName = LastName.Text;
// Check that values supplied
if ((firstName == "")
|| (lastName == ""))
{
errorMessageBox.Text = "Error: all
fields must be supplied";
errorMessageBox.Show();
return;
}
// Update database
string response = EmployeeDatabase.RemoveEmployee
(firstName, lastName);
if (response == "success")
{
ClearTextBoxes();
errorMessageBox.Hide();
}
else
{
errorMessageBox.Text = response;
errorMessageBox.Show();
}
} // end Remove_Click
private void
Modify_Click(object sender, EventArgs e)
{
Modify.Hide();
Remove.Hide();
UpdateButton.Show();
DepartmentLabel.Show();
Department.Clear();
Department.Show();
} // end Modify_Click
private void
UpdateButton_Click(object sender, EventArgs e)
{
// Clear any error message
errorMessageBox.Clear();
// Obtain
entered data
string firstName, lastName, department;
firstName = FirstName.Text;
lastName = LastName.Text;
department = Department.Text;
// Check that values supplied
if
((firstName == "") || (lastName ==
"") || (department == ""))
{
errorMessageBox.Text = "Error: all
fields must be supplied";
errorMessageBox.Show();
return;
}
// Update database
string response = EmployeeDatabase.ModifyEmployeeData
(firstName, lastName, department);
if (response == "success")
{
errorMessageBox.Hide();
ClearTextBoxes();
DepartmentLabel.Hide();
Department.Clear();
Department.Hide();
UpdateButton.Hide();
Modify.Show();
Remove.Show();
}
else
{
errorMessageBox.Text = response;
errorMessageBox.Show();
}
} // end UpdateButton_Click
private void
ShowList_Click(object sender, EventArgs e)
{
// Make room to overwrite with listbox
ClearTextBoxes();
Department.Hide();
UpdateButton.Hide();
Modify.Hide();
Remove.Hide();
FirstName.Hide();
LastName.Hide();
DepartmentLabel.Hide();
Department.Clear();
ClearTextBoxes();
firstNameLabel.Hide();
lastNameLabel.Hide();
ShowList.Hide();
errorMessageBox.Hide();
// Display the list box
employeeList.Items.Clear();
employeeList.Show();
CancelEmployeeList.Show();
// Obtain the list of employees
EmployeeDatabase.EmployeeListType
employees =
new EmployeeDatabase.EmployeeListType();
employees = EmployeeDatabase.EmployeeList();
// Transfer to the list box
for (int i = 0; i
< employees.count; i++)
{
employeeList.Items.Add(employees.list[i].lastName+"\t"+
employees.list[i].firstName+"\t"+
employees.list[i].department);
}
} // end ShowList_Click
private void
employeeList_SelectedIndexChanged(object
sender, System.EventArgs e)
{
string firstName = "";
string lastName = "";
string department = "";
// Get currently selected entry
string curItem =
employeeList.SelectedItem.ToString();
int index = employeeList.FindString(curItem);
// If the item was not found in employeeList display a
message box,
// otherwise select it in employeeList.
if (index == -1)
MessageBox.Show("Item is not available in employee list");
else
{
int i = curItem.IndexOf('\t');
if (i > 0)
{
lastName = curItem.Substring(0, i);
int l = curItem.Length - i - 1;
string restOf =
curItem.Substring(i+1,l);
i = restOf.IndexOf('\t');
if (i > 0)
{
firstName = restOf.Substring(0, i);
l = restOf.Length - i - 1;
department = restOf.Substring(i + 1, l);
}
}
// Display selected employee to allow Remove
or Modify
employeeList.Hide();
employeeList.Items.Clear();
CancelEmployeeList.Hide();
firstNameLabel.Show();
lastNameLabel.Show();
DepartmentLabel.Show();
ShowList.Show();
Modify.Show();
Remove.Show();
FirstName.Text = firstName;
LastName.Text = lastName;
Department.Text = department;
FirstName.Show();
LastName.Show();
Department.Show();
UpdateButton.Hide();
}
} // employeeList_SelectedIndexChanged
private void
CancelEmployeeList_Click(object sender, EventArgs e)
{
employeeList.Hide();
employeeList.Items.Clear();
CancelEmployeeList.Hide();
firstNameLabel.Show();
lastNameLabel.Show();
DepartmentLabel.Hide();
ShowList.Show();
Modify.Show();
Remove.Show();
ShowList.Show();
Modify.Show();
Remove.Show();
FirstName.Text = "";
LastName.Text = "";
Department.Text = "";
FirstName.Show();
LastName.Show();
Department.Hide();
} // CancelEmployeeList_Click
} // end Form2
} // end namespace
Form3.cs:
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;
namespace Apps
{
public partial class Form3 : Form
{
private EmployeeDatabase
employeeDatabase;
public Form3() //
constructor
{
InitializeComponent();
employeeDatabase = new EmployeeDatabase(this);
} // end constructor
public void
ClearTextBoxes()
{
FirstName.Clear();
LastName.Clear();
Department.Clear();
} // end ClearTextBoxes
private void
AddEmployee_Click(object sender, EventArgs e)
{
// Clear any error message
errorMessageBox.Clear();
// Obtain entered data
string firstName, lastName, department;
firstName = FirstName.Text;
lastName = LastName.Text;
department = Department.Text;
// Check that values supplied
if ((firstName == "")
|| (lastName == "") || (department
== ""))
{
errorMessageBox.Text = "Error: all
fields must be supplied";
return;
}
// Update database
string response = EmployeeDatabase.AddEmployee
(firstName, lastName, department);
if (response == "success")
{
ClearTextBoxes();
}
else
{
errorMessageBox.Text = response;
}
} // end AddEmplyee_Click
} // end Form3 class
} // end namespace
Form4.cs:
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;
namespace Apps
{
public partial class Form4 : Form
{
private EmployeeDatabase
employeeDatabase;
private ExpensesDatabase
expensesDatabase;
public bool
expenseEntry = true;
public Form4()
{
InitializeComponent();
employeeDatabase = new EmployeeDatabase(this);
expensesDatabase = new ExpensesDatabase(this);
} // end constructor
public void
DisplayEmployeeList()
{
DescListBox.Hide();
DateListBox.Hide();
AmtListBox.Hide();
employeeList.Items.Clear();
EmployeeDatabase.EmployeeListType
employees =
new EmployeeDatabase.EmployeeListType();
employees = EmployeeDatabase.EmployeeList();
closeList.Show();
// Transfer to the list box
for (int i = 0; i
< employees.count; i++)
{
employeeList.Items.Add(employees.list[i].lastName + "\t" +
employees.list[i].firstName
+ "\t" +
employees.list[i].department);
}
employeeList.Show();
}
private void
employeeList_SelectedIndexChanged_1(object
sender, EventArgs e)
{
closeList.Hide();
string firstName = "";
string lastName = "";
string department = "";
// Get currently selected entry
string curItem =
employeeList.SelectedItem.ToString();
int index = employeeList.FindString(curItem);
// If the item was not found in employeeList display a
message box,
// otherwise select it in employeeList.
if (index == -1)
MessageBox.Show("Item is not available in employee list");
else
{
int i = curItem.IndexOf('\t');
if (i > 0)
{
lastName = curItem.Substring(0, i);
int l = curItem.Length - i - 1;
string restOf = curItem.Substring(i + 1,
l);
i = restOf.IndexOf('\t');
if (i > 0)
{
firstName = restOf.Substring(0, i);
l = restOf.Length - i - 1;
department =
restOf.Substring(i + 1, l);
}
}
// Display selected employee to enter or
display of expenses
employeeList.Hide();
FirstName.Show();
LastName.Show();
Department.Show();
if (expenseEntry)
{
EnterExpenses(firstName, lastName, department);
}
else
{
DisplayExpenses(firstName, lastName, department);
}
}
} // end employeeList_SelectedIndexChanged_1
private void
EnterExpenses(string firstName, string lastName, string
department)
{
// Display employee
FirstName.Text = firstName;
LastName.Text = lastName;
Department.Text = department;
// Prompt expense
enterExpenseLabel.Show();
DoneButton.Show();
DescriptionLabel.Show();
ExpenseDescription.Show();
AmountLabel.Show();
ExpenseAmount.Show();
DateLabel.Show();
PurchaseDate.Show();
EnterButton.Show();
} // end EnterExpenses
private void DisplayExpenses(string firstName, string
lastName, string department)
{
// Display employee
FirstName.Text = firstName;
LastName.Text = lastName;
Department.Text = department;
DescListBox.Items.Clear();
DateListBox.Items.Clear();
AmtListBox.Items.Clear();
// Display employee's expenses
ExpensesDatabase.ExpensesListType
expenses =
new ExpensesDatabase.ExpensesListType();
expenses = ExpensesDatabase.ExpensesList(firstName,lastName);
string description, date, amount;
string datetime;
for (int i = 0; i
< expenses.count; i++)
{
description = expenses.list[i].description;
DescListBox.Items.Add(description);
datetime = expenses.list[i].date;
int ii = datetime.IndexOf(' '); // find "
12:00:00 AM"
if (ii > 0)
{
date = datetime.Substring(0, ii);
}
else
{
date = datetime;
}
DateListBox.Items.Add(date);
amount = expenses.list[i].amount;
AmtListBox.Items.Add(amount);
}
DescListBox.Height = DescListBox.PreferredHeight;
DateListBox.Height = DateListBox.PreferredHeight;
AmtListBox.Height = AmtListBox.PreferredHeight;
ExpensesHeader.Show();
DescListBox.Show();
DateListBox.Show();
AmtListBox.Show();
closeList.Show();
Done.Show();
} // end DisplayExpenses
private void
ExpenseDescription_TextChanged(object sender, EventArgs e)
{
} // end ExpenseDescription_TextChanged
private void
ExpenseAmount_TextChanged(object sender, EventArgs e)
{
} // end ExpenseAmount_TextChanged
private void
PurchaseDate_TextChanged(object sender, EventArgs e)
{
} // end PurchaseDate_TextChanged
private void
EnterButton_Click(object sender, EventArgs e)
{
// Capture data and verify filled in
string firstName = FirstName.Text;
string
lastName = LastName.Text;
string department = Department.Text;
string description = ExpenseDescription.Text;
string amount = ExpenseAmount.Text;
string date = PurchaseDate.Text;
// Check that values supplied
if ((firstName == "")
|| (lastName == "") || (department
== "") ||
(description == "") ||
(amount == "") || (date == ""))
{
errorMessageBox.Text = "Error: all
fields must be supplied";
errorMessageBox.Show();
return;
}
// Check that amount is decimal and date is formatted
correctly
Decimal amt;
bool canConvert = decimal.TryParse(amount,
out amt);
if
(!canConvert)
{
errorMessageBox.Text = "Error: Amount
must be xx.xx format";
errorMessageBox.Show();
return;
}
DateTime d;
canConvert = DateTime.TryParse(date, out d);
if (!canConvert)
{
errorMessageBox.Text = "Error: Date
must be mm/dd/yy format";
errorMessageBox.Show();
return;
}
// Add expense to database
string response =
ExpensesDatabase.AddExpense(firstName,
lastName, description, amt, d);
if (response == "success")
{
errorMessageBox.Hide();
ExpenseDescription.Clear();
ExpenseAmount.Clear();
PurchaseDate.Clear();
}
else
{
errorMessageBox.Text = response;
errorMessageBox.Show();
}
} // end EnterButton_Click
private void
DoneButton_Click(object sender, EventArgs e)
{
// End display for entry of expenses for an employee
FirstName.Hide();
LastName.Hide();
Department.Hide();
enterExpenseLabel.Hide();
DoneButton.Hide();
DescriptionLabel.Hide();
ExpenseDescription.Hide();
AmountLabel.Hide();
ExpenseAmount.Hide();
DateLabel.Hide();
PurchaseDate.Hide();
EnterButton.Hide();
errorMessageBox.Hide();
Done.Hide();
// Display employee list again to select an employee
closeList.Show();
employeeList.Show();
} // end DoneButton_Click
private void
closeList_Click(object sender, EventArgs e)
{
closeList.Hide();
Done.Hide();
FirstName.Hide();
LastName.Hide();
Department.Hide();
ExpensesHeader.Hide();
Form1.enterExpenses.expenseEntry = false;
Form1.enterExpenses.Hide();
} // end closeList_Click
private void
Done_Click(object sender, EventArgs e)
{
Done.Hide();
FirstName.Hide();
LastName.Hide();
Department.Hide();
ExpensesHeader.Hide();
// Display employee list again to select an employee
DisplayEmployeeList();
} // end Done_Click
} // end class Form4
} // end namespace
EmployeeDatabase.cs:
using System;
using
System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
namespace Apps
{
public class EmployeeDatabase
{
// This class implements a component that interfaces with a
database of
// employees and their expenses.
//
// It is an on demand component running whenever a message
topic is
// received.
static string myConnectionString
= @"Provider=Microsoft.Jet.OLEDB.4.0;"
+
"Data
Source=C:\\Source\\XP2\\Employees.mdb;" + "Persist
Security Info=True;";
static OleDbConnection
myConnection = new OleDbConnection(myConnectionString);
static public Form2 form2;
static public Form3 form3;
static public Form4 form4;
public struct EmployeeDataType
{
public string
firstName;
public string lastName;
public string
department;
}
public class EmployeeListType
{
public int count;
public EmployeeDataType[]
list =
new EmployeeDataType[50];
}
public EmployeeDatabase(Form2
addForm) // constructor
{
form2 = addForm;
} // end constructor
public EmployeeDatabase(Form3
addForm) // constructor
{
form3 = addForm;
} // end constructor
public EmployeeDatabase(Form4
addForm) // constructor
{
form4 = addForm;
} // end constructor
// Treat the EMPLOYEE ADD of Form3
public static string AddEmployee(string
firstName,
string lastName,
string
department)
{
string responseMessage = "";
// Add the employee.
An error will occur if a duplicate first
// and last name.
bool opened = false;
try
{
// Open OleDb Connection
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
opened = true;
}
catch (Exception
ex)
{
Console.WriteLine("OLEDB Connection FAILED: " +
ex.Message);
responseMessage = "OLEDB Connection
FAILED: ";
return responseMessage;
}
// Check that
name is new.
if (opened)
{
OleDbCommand cmd =
myConnection.CreateCommand();
string insertInto = "INSERT INTO Employees (EmployeeID, LastName,
FirstName, DepartmentName, Salary) ";
string values = "VALUES
(?, ?, ?, ?, ?)";
cmd.CommandText = insertInto + values;
cmd.Parameters.AddWithValue("EmployeeID",
0);
cmd.Parameters.AddWithValue("LastName",
lastName);
cmd.Parameters.AddWithValue("FirstName", firstName);
cmd.Parameters.AddWithValue("DepartmentName",
department);
cmd.Parameters.AddWithValue("Salary",
0);
bool success = true;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception
ex)
{
// return the exception to display as an error
message
success = false;
string exceptionString = ex.ToString();
string restOf = "";
string except = "";
int ii = exceptionString.IndexOf(':');
if (ii > 0)
{
int ll = exceptionString.Length - ii - 1;
restOf = exceptionString.Substring(ii + 1,
ll);
ii = restOf.IndexOf('\r');
except = restOf.Substring(0, ii);
}
responseMessage = "error: " + except;
}
if (success)
{
responseMessage = "success";
}
// Close the connection and return the
response message.
myConnection.Close();
return responseMessage;
}
else
{
return "error:
database not opened";
}
} // AddEmployee
// Treat EMPLOYEE MODIFY of Form2
public static string ModifyEmployeeData(string
firstName,
string lastName,
string department)
{
string responseMessage = "";
bool
opened = false;
try
{
// Open OleDb Connection
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
opened = true;
}
catch (Exception ex)
{
string except = ex.ToString();
responseMessage = "OLEDB Connection
FAILED: ";
return responseMessage;
}
// Check that name is exists.
if
(opened)
{
OleDbCommand cmd =
myConnection.CreateCommand();
cmd = new OleDbCommand("SELECT COUNT(*) FROM Employees " +
"WHERE
LastName = ? AND FirstName = ?",
myConnection);
cmd.Parameters.AddWithValue("LastName",
lastName);
cmd.Parameters.AddWithValue("FirstName",
firstName);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = cmd;
// Name combination valid?
int result;
bool success = true;
try
{
result = (int)cmd.ExecuteScalar();
if
(result == 0)
{
responseMessage = "error:
not in database";
success = false;
}
}
catch (Exception
ex)
{
// return the exception to display as an error
message
success = false;
string exceptionString = ex.ToString();
string restOf = "";
string except = "";
int
ii = exceptionString.IndexOf(':');
if (ii > 0)
{
int ll =
exceptionString.Length - ii - 1;
restOf = exceptionString.Substring(ii + 1,
ll);
ii = restOf.IndexOf('\r');
except = restOf.Substring(0, ii);
}
responseMessage = "error: "
+ except;
}
if (success)
{
cmd = new
OleDbCommand("UPDATE
Employees " +
"SET
DepartmentName = ?" +
"WHERE
LastName = ? AND FirstName = ?",
myConnection);
cmd.Parameters.AddWithValue("DepartmentName",
department);
cmd.Parameters.AddWithValue("LastName",
lastName);
cmd.Parameters.AddWithValue("FirstName",
firstName);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception
ex)
{
// return the
exception to display as an error message
success = false;
string
exceptionString = ex.ToString();
string
restOf = "";
string
except = "";
int ii =
exceptionString.IndexOf(':');
if
(ii > 0)
{
int ll
= exceptionString.Length - ii - 1;
restOf = exceptionString.Substring(ii +
1, ll);
ii = restOf.IndexOf('\r');
except = restOf.Substring(0,
ii);
}
responseMessage = "error:
" + except;
}
}
if (success)
{
responseMessage = "success";
}
// Close the connection and return the
response message.
myConnection.Close();
return responseMessage;
}
else
{
return "error:
database not opened";
}
} // end ModifyEmployeeData
// Treat EMPLOYEE REMOVE of Form2
public static string RemoveEmployee(string
firstName,
string lastName)
{
string responseMessage = "";
bool opened = false;
try
{
// Open OleDb Connection
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
opened = true;
}
catch (Exception
ex)
{
string except = ex.ToString();
responseMessage = "OLEDB Connection
FAILED: ";
return responseMessage;
}
// Check
that name is exists.
if (opened)
{
OleDbCommand cmd =
myConnection.CreateCommand();
cmd = new OleDbCommand("SELECT COUNT(*) FROM Employees " +
"WHERE LastName = ? AND
FirstName = ?",
myConnection);
cmd.Parameters.AddWithValue("LastName",
lastName);
cmd.Parameters.AddWithValue("FirstName",
firstName);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = cmd;
// Name combination valid?
int result;
bool success = true;
try
{
result = (int)cmd.ExecuteScalar();
if (result == 0)
{
responseMessage = "error:
not in database";
success = false;
}
}
catch
(Exception ex)
{
// return the exception to display as an error
message
success = false;
string exceptionString = ex.ToString();
string restOf = "";
string except = "";
int ii = exceptionString.IndexOf(':');
if (ii > 0)
{
int ll =
exceptionString.Length - ii - 1;
restOf = exceptionString.Substring(ii + 1,
ll);
ii = restOf.IndexOf('\r');
except = restOf.Substring(0, ii);
}
responseMessage = "error: "
+ except;
}
if
(success)
{
cmd = new OleDbCommand("DELETE * FROM Employees " +
"WHERE
LastName = ? AND FirstName = ?",
myConnection);
cmd.Parameters.AddWithValue("LastName",
lastName);
cmd.Parameters.AddWithValue("FirstName",
firstName);
dataAdapter.DeleteCommand = cmd;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception
ex)
{
// return the
exception to display as an error message
success = false;
string
exceptionString = ex.ToString();
string
restOf = "";
string
except = "";
int ii =
exceptionString.IndexOf(':');
if (ii >
0)
{
int ll
= exceptionString.Length - ii - 1;
restOf = exceptionString.Substring(ii +
1, ll);
ii = restOf.IndexOf('\r');
except = restOf.Substring(0, ii);
}
responseMessage = "error:
" + except;
}
}
if (success)
{
responseMessage = "success";
}
// Close the connection and return the
response message.
myConnection.Close();
return responseMessage;
}
else
{
return "error:
database not opened";
}
} // end RemoveEmployee
static public EmployeeListType EmployeeList()
{
// Initialize list to return
EmployeeListType employeeData = new EmployeeListType();
employeeData.count = 0;
bool
opened = false;
string select = "SELECT
* FROM Employees";
try
{
// Open OleDb Connection
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
opened = true;
}
catch (Exception
ex)
{
string except = ex.ToString();
return employeeData;
}
if (opened)
{
OleDbCommand cmd = new OleDbCommand(select,
myConnection);
using (OleDbDataReader
reader = cmd.ExecuteReader())
{
while (reader.Read())
{
employeeData.list[employeeData.count].lastName
=
reader["LastName"].ToString();
employeeData.list[employeeData.count].firstName =
reader["FirstName"].ToString();
employeeData.list[employeeData.count].department
=
reader["DepartmentName"].ToString();
employeeData.count++;
}
}
}
// Close the connection and return the response message.
myConnection.Close();
return employeeData;
} // end EmployeeList
} // end class EmployeeDatabase
} // end namespace
ExpensesDatabase.cs:
using System;
using
System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
namespace Apps
{
public class ExpensesDatabase
{
// This class implements a component that interfaces with a
database of
// employees and their expenses.
//
// It is an on demand component running whenever a message
topic is
// received.
static string
myConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;"
+
"Data Source=C:\\Source\\XP2\\Expenses.mdb;" +
"Persist
Security Info=True;";
static OleDbConnection
myConnection = new OleDbConnection(myConnectionString);
static public Form4 form4;
public struct ExpensesDataType
{
public string
description;
public string date;
public string amount;
}
public class ExpensesListType
{
public int count;
public ExpensesDataType[] list =
new ExpensesDataType[50];
}
public ExpensesDatabase(Form4
addForm) // constructor
{
form4 = addForm;
} // end constructor
// Add an Expense
public static string
AddExpense(string firstName,
string
lastName,
string
description,
decimal
amount,
DateTime date)
{
string responseMessage = "";
bool opened = false;
try
{
// Open OleDb Connection
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
opened = true;
}
catch (Exception
ex)
{
Console.WriteLine("OLEDB Connection FAILED: " +
ex.Message);
responseMessage = "OLEDB Connection
FAILED: ";
return responseMessage;
}
if (opened)
{
OleDbCommand cmd =
myConnection.CreateCommand();
string insertInto =
"INSERT INTO Expenses (LastName, FirstName,
Description, AmountSpent, DatePurchased)";
string values = "VALUES
(?, ?, ?, ?, ?)";
cmd.CommandText = insertInto + values;
cmd.Parameters.AddWithValue("LastName",
lastName);
cmd.Parameters.AddWithValue("FirstName",
firstName);
cmd.Parameters.AddWithValue("Description",
description);
cmd.Parameters.AddWithValue("AmountSpent",
amount);
cmd.Parameters.AddWithValue("DatePurchased",
date);
bool success = true;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception
ex)
{
// return the exception to display as an error
message
success = false;
string exceptionString = ex.ToString();
string restOf = "";
string except = "";
int ii = exceptionString.IndexOf(':');
if (ii > 0)
{
int ll =
exceptionString.Length - ii - 1;
restOf = exceptionString.Substring(ii + 1,
ll);
ii = restOf.IndexOf('\r');
except = restOf.Substring(0,
ii);
}
responseMessage = "error: "
+ except;
}
if (success)
{
responseMessage = "success";
}
// Close the connection and return the
response message.
myConnection.Close();
return responseMessage;
}
// end if opened
else
{
return "error:
database not opened";
}
} // end AddExpense
static public ExpensesListType ExpensesList(string firstName, string
lastName)
{
// Initialize list to return
ExpensesListType expenseData = new ExpensesListType();
expenseData.count = 0;
bool opened = false;
string select = "SELECT
* FROM Expenses";
try
{
// Open OleDb Connection
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
opened = true;
}
catch (Exception
ex)
{
string except = ex.ToString();
return expenseData;
}
if (opened)
{
OleDbCommand cmd = new OleDbCommand(select,
myConnection);
using (OleDbDataReader
reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if
((reader["LastName"].ToString() ==
lastName) &&
(reader["FirstName"].ToString()
== firstName))
{
expenseData.list[expenseData.count].description =
reader["Description"].ToString();
expenseData.list[expenseData.count].date =
reader["DatePurchased"].ToString();
expenseData.list[expenseData.count].amount
=
reader["AmountSpent"].ToString();
expenseData.count++;
}
}
}
}
// end if opened
// Close the connection and return the response message.
myConnection.Close();
return expenseData;
} // end ExpensesList
} // end class ExpensesDatabase
} // end namespace
No comments:
Post a Comment