Navigating and Updating a Database

0 Comments by admin

By far, the most important application for current applications as well asfuture applications is the management of information (students lists, customerorders, CD labels, etc) through a database system. In this handout we are goingto create an application that allows the user to navigate through a database andalso provides the capabilities to update.

Navigating through a DatabaseApplication

The database used in this application is a modification of the well knownNorthWind.mdb database. In this example, we’re going to have a singletable called products. This table is shown below.

Figure 1. Products Table of MyNorthWind.mdb

We are going to create a DataAdapter to load the table. Another important feature used was themyDs.Tables["dtProducts"].Rows.Count which gave the number of rowsin the table. We then we were able to navitage through all records. Code 1 provides the listingfor this problem. Figure 2and Figure 3 displaysruntime screenshots of the application. Appropriate warning messages areprovided to the user in case he(she) reaches end or beginning of the file. Furthermore, disabling buttons when appropriate was implemented in theapplication.

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 ofForm1.cs

Figure 2. frmProductsscreenshot

Figure3. Screenshots of BOF and EOF

Updating DatabaseApplication

To implementent this option, we use a combo box to list all products in thelist, and once the user selects a product, it will load the selected productinformation on the form. There has to be an option to update the quantity onhand, and at the end of the transaction, the system should list the updatedinformation.

In order to tackle this problem I modified the firstapplication to include a second form that will allow the user to update thequantity on hand. Now the Form principal will look likeFigure 4, which basicallyincludes a link that takes the user to the Update Form.

Figure 4. Modified Form Principal

In order to communicate share the samedatatable 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 theOleDbDataAdapter myDataAdapter public variables so they can be instantiated inform 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 toupdate. 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 bedisplayed. This form is di

splayed
onFigure 5. Note that bydefault this form will display the active record from the previous form. Details of the code will be given later.

Figure 5. UpdateForm

The Update form provides a combo box that has all productsin the database. If the user selects a particular product, the system will loadthe appropriate information and allows the user to update the stock information.As an example, the figuresbelow show a typical updateprocess being performed.

Figure 6. Entering an order

Figure 7. Message boxindicating completion of update

Figure 8. Updated Product Stockdisplayed

The listing of this problem is given below, whichdetails all the subs developed to accomplish the task in hand.

private void Form2_Load(object sender, System.EventArgs e)

HereI’m creating references to the data set and data adapter created in form1.

{
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 updatingmyDataAdapter2 which is a reference of the data Adapter created inform1

}
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();
}
}
}
}