By Carlo A. Mora-Monge
By far, the most important application for current applications as well as
future applications is the management of information (students lists, customer
orders, CD labels, etc) through a database system. In this handout we are going
to create an application that allows the user to navigate through a database and
also provides the capabilities to update.
Navigating through a Database
Application
The database used in this application is a modification of the well known
NorthWind.mdb database. In this example, we’re going to have a single
table called products. This table is shown below.
Figure 1.
Products Table of MyNorthWind.mdbWe are going to create a Data
Adapter to load the table. Another important feature used was the
myDs.Tables["dtProducts"].Rows.Count which gave the number of rows
in the table. We then we were able to navitage through all records.
Code 1 provides the listing
for this problem. Figure 2
and Figure 3 displays
runtime screenshots of the application. Appropriate warning messages are
provided to the user in case he(she) reaches end or beginning of the file.
Furthermore, disabling buttons when appropriate was implemented in the
application.
Private void Form1_Load(object sender, System.EventArgs e)
{
string strConn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\A\MyFirstDb\MyNorthwind.mdb";
MessageBox.Show(strConn);
OleDbConnection myConn = new OleDbConnection(strConn);
myConn.Open();
string strSql ="SELECT * FROM Products";
myDs= new DataSet();
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(strSql, myConn);
OleDbCommandBuilder myCmdBuilder= new OleDbCommandBuilder(myDataAdapter);
//Apply Fill method to populate the dataset dtProducts
myDataAdapter.Fill(myDs, "dtProducts");
intRow=0;
FillForm();
}
private void FillForm ()//Fills the form with the current record of the DataTable.
{
txtPID.Text= myDs.Tables["dtProducts"].Rows[ intRow]["ProductID"].ToString();
txtName.Text= myDs.Tables["dtProducts"].Rows[ intRow]["ProductName"].ToString();
txtPrice.Text= myDs.Tables["dtProducts"].Rows[ intRow]["UnitPrice"].ToString();
//MessageBox.Show(intRow.ToString());
}
private void btnFirst_Click(object sender, System.EventArgs e)
{
intRow = 0;
btnFirst.Enabled=false;
btnLast.Enabled=true;
FillForm();
}
private void btnPrevious_Click(object sender, System.EventArgs e)
{
if (intRow==0)
{
MessageBox.Show("You are at the beggining of the file","BOF");
intRow=0;
btnFirst.Enabled=false;
FillForm();
}
else
{
btnFirst.Enabled=true;
btnLast.Enabled=true;
intRow = intRow-1;
FillForm();
}
}
private void btnNext_Click(object sender, System.EventArgs e)
{//First we check if the we're at EOF; if so, a message will popup and take the user to last record
if (intRow==myDs.Tables["dtProducts"].Rows.Count-1)
{
MessageBox.Show("You are at the end of the file","EOF");
intRow=myDs.Tables["dtProducts"].Rows.Count-1;
btnLast.Enabled=false;
FillForm();
}
else
{
btnLast.Enabled=true;
btnFirst.Enabled=true;
intRow = intRow +1;
FillForm();
}
}
private void btnLast_Click(object sender, System.EventArgs e)
{
intRow= myDs.Tables["dtProducts"].Rows.Count-1;
btnLast.Enabled=false;
btnFirst.Enabled=true;
FillForm();
}
}Code 1. Relevant Code of
Form1.cs
Figure 2. frmProducts
screenshot
Figure
3. Screenshots of BOF and EOF
Updating Database
Application
To implementent this option, we use a combo box to list all products in the
list, and once the user selects a product, it will load the selected product
information on the form. There has to be an option to update the quantity on
hand, and at the end of the transaction, the system should list the updated
information.
In order to tackle this problem I modified the first
application to include a second form that will allow the user to update the
quantity on hand. Now the Form principal will look like
Figure 4, which basically
includes a link that takes the user to the Update Form.
Figure 4.
Modified Form PrincipalIn order to communicate share the same
datatable between the two forms, I had to modified the cod of form1.cs slightly.
The changes are highlithted and explained below:
- I made the Dataset myDs, the integer variable intRow, and the
OleDbDataAdapter myDataAdapter public variables so they can be instantiated in
form 2. I also made an instante of form2.
private Form2 form2;
public DataSet myDs;
public int intRow=0;
public OleDbDataAdapter myDataAdapter;
- I developed this code that is activatect when the user clicks on go to
update. It will basically hide Form1 and show Form2.
private void lnkGotoUpdate_LinkClicked(object sender, System.Windows.Forms.LinkLabelLinkClickedEventArgs e)
{
if (form2==null)
form2=new Form2();
form2.previousForm=this;
form2.Show();
this.Hide();
//Application.Exit();
}Once the user clicks on the LinkLabel, the Update form will be
displayed. This form is displayed on
Figure 5. Note that by
default this form will display the active record from the previous form.
Details of the code will be given later.
Figure 5. Update
FormThe Update form provides a combo box that has all products
in the database. If the user selects a particular product, the system will load
the appropriate information and allows the user to update the stock information.
As an example, the figures
below show a typical update
process being performed.
Figure 6.
Entering an order
Figure 7. Message box
indicating completion of update
Figure 8. Updated Product Stock
displayedThe listing of this problem is given below, which
details all the subs developed to accomplish the task in hand.
private void Form2_Load(object sender, System.EventArgs e)
Here
I’m creating references to the data set and data adapter created in form
1.
{
Form1Image=this.previousForm;
myDs2= Form1Image.myDs;
myDataAdapter2= Form1Image.myDataAdapter;
intRow2=Form1Image.intRow ;
Loadcbo();
cboProductID.SelectedIndex =intRow2;
FillForm();
}
private void FillForm()
{
lblStock.Text = myDs2.Tables["dtProducts"].Rows[ intRow2]["UnitsInStock"].ToString();
}
private void cboProductID_SelectedIndexChanged(object sender, System.EventArgs e)
{
intRow2=cboProductID.SelectedIndex;
FillForm();
}
private void clearForm()
{
txtOrder.Text="";
}
private void lnkPrincipal_LinkClicked(object sender, System.Windows.Forms.LinkLabelLinkClickedEventArgs e)
{
Form1 myPrincipal;
myPrincipal=this.previousForm;
//myPrincipal.m
this.previousForm.Show();
this.Hide();
//this.previousForm.Load;
Form1Image.Refresh();
}
private void Loadcbo()
{
foreach (DataRow r in myDs2.Tables["dtProducts"].Rows)
cboProductID.Items.Add(r["ProductID"]);
//MessageBox.Show("comboBox loaded");We are updating
myDataAdapter2 which is a reference of the data Adapter created in
form1
}
private void btnUpdate_Click(object sender, System.EventArgs e)
{
int intUnits = Int32.Parse(txtOrder.Text);
int intStock= Int32.Parse(lblStock.Text);
if (intUnits>intStock)
{
MessageBox.Show("Invalid Number of Units");
clearForm();
}
else
{
myDs2.Tables["dtProducts"].Rows[ intRow2]["UnitsInStock"]=
(int)myDs2.Tables["dtProducts"].Rows[ intRow2]["UnitsInStock"]-intUnits;
myDataAdapter2.Update(myDs2,"dtProducts"); //Updating the DB
MessageBox.Show("Record Has been updated");
myDataAdapter2.Fill(myDs2, "dtProducts");
FillForm();
clearForm();
}
}
}
}Code
2. Partial Listing of Form2.csThere are other important code that
is worthwhile mention. In the declaration section I declared the
following:
public Form1 previousForm;
private Form1 Form1Image;
DataSet myDs2;
OleDbDataAdapter myDataAdapter2;
int intRow2;
These variables as it can be seen from listing
Code 2 where declared to
make instances of the dataTable created in Form1.cs.
