BA 371 Business Systems Analysis - VC# GUI-DB Communication

BA 371: VC# GUI-DB Communication

Now we have run our GUI and DB labs, we can now combine the GUI components of a program (see VCSharpGUI.htm) with the DB (SQL) communications parts of a program (see VCSharpDB.htm).
Let’s build a simple interface that consists of a window with a Query button, a Close button and an empty ListBox. When the user clicks the Query button, the database is queried. The results get displayed in the ListBox as shown below:

Using the techniques you learned in VCSharpGUI.htm set up a new Windows Forms Application project.
Set up a basic Form interface with a Query button, a Close button and a ListBox.
Change the Text and Name attributes of these classes as follows:

Form: Name: QueryForm, Text: Sample Query

Query Button: Name: QueryButton, Text: Query

Close Button: Name: CloseButton, Text: Close

List Box: Name: ResultList

Write skeleton event handlers for the QueryButton class and the CloseButton class. Add a comment where the code for querying the database will go:


private void QueryButton_Click(object sender, EventArgs e) 
{
  //code for the query and listing the results in ResultList goes here
}
  
private void CloseButton_Click(object sender, EventArgs e)
{
   //Handles CloseButton_Click()
}

Now add a new class DB that we will use to hold the database connection as well as the code for establishing and closing the connection:


public class DB
{
  public static System.Data.OleDb.OleDbConnection Connection;

  public static void DBOpen()
  {
    //Access 2007
    string ConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=c:\\temp\\foo.accdb";
    Connection = new System.Data.OleDb.OleDbConnection(ConnectString);

    try { Connection.Open(); }
    catch(System.Exception e)
    {
      //Pass on the exception to the caller
      throw e;
    }
  }
	
  public static void DBClose()
  {
    try { Connection.Close(); }
    catch(System.Exception e)
    {
      //Pass on the exception to the caller
      throw e;
    }
  }
}

Change the skeleton code for the QueryButton class so that it opens the connection, does the query and closes the connection:


private void QueryButton_Click(object sender, EventArgs e)
{
  try { DB.DBOpen(); }
  catch (System.Exception openex)
  {
     MessageBox.Show("Error opening database", openex.Message);
     System.Environment.Exit(1);
  }

  //query goes here

  try { DB.DBClose(); }
  catch (System.Exception closeex)
  {
     MessageBox.Show("Error closing database", closeex.Message);
     System.Environment.Exit(1);
  }
}

Notice how in the above code we call the public static(!!) function DBOpen(), on the DB class.

Change the code for the CloseButton class so that it closes the dialog:


private void CloseButton_Click(object sender, EventArgs e)
{
  Dispose();
}

Test the application. Clicking the Query button should connect to the database; clicking the Close button takes the QueryForm away.

Now add the database query functionality to the QueryButton_Click() event handler (check the SQL query in the code below and make sure that either your database supports it or change the query such that your database supports it):


System.Data.OleDb.OleDbCommand SQLCommand;
System.Data.OleDb.OleDbDataReader SQLDataReader = null;
string result_string;

//Open the database connection. Exit the app if this fails

try { DB.DBOpen(); }
catch(System.Exception ex)
{
  MessageBox.Show("Problem opening database..." + ex.Message);
  //Since we have a problem, we exit with a nonzero status.
  System.Environment.Exit(1);
}

//Connection is open; now do the query

SQLCommand = new System.Data.OleDb.OleDbCommand("select my_name from foo", DB.Connection); //modify this query to fit your database
try { SQLDataReader = SQLCommand.ExecuteReader(); }
catch(System.Exception f)
{
  MessageBox.Show("Problem in ExecuteReader..." + f.Message);
  //Query failed; try closing the database connection on our way out
  try { DB.DBClose(); }
  catch(System.Exception g) { MessageBox.Show("Problem closing database..." + g.Message); }
  System.Environment.Exit(1);
}

//Data got retrieved from the database; now get it from the reader; one record at the time
//and add it to the resultList  
try
{
  while (SQLDataReader.Read())
  {
    result_string = SQLDataReader.GetString(0);
    ResultList.Items.Add(result_string);
  }
}
catch(System.Exception h)
{
  MessageBox.Show("Problems reading data..." + h.Message);
  //Query failed; try closing the database connection on our way out
  try { DB.DBClose(); }
  catch(System.Exception i) { MessageBox.Show("Problem closing database..." + i.Message); }
  System.Environment.Exit(1);
}

//Query succeeded; close the database connection

try { DB.DBClose(); }
catch(System.Exception ex)
{
  MessageBox.Show("Problem closing database..." + ex.Message);
  //Since we have a problem, we exit with a nonzero status.
  System.Environment.Exit(1);
}

You now know pretty much everything you need to write a GUI-based VC# application that interfaces with any SQL database (provided there is a VC#/OLE driver available for that database).
Also note that the above techniques, although slightly different from language to language and from database to database, can be used in most other programming languages and database platforms.
However, VC# also offers a few idiosyncratic ways of displaying database query results. For instance, it offers the DataGridclass . This class will automatically display multi-column, multi-row query results in a table format as shown below.

To use a DataGridView, modify the application above such that rather than including a ListBox on the Form window, you include a DataGridView object (see picture above. Note that when you create the object using your VC# ToolBox, the DataGridView will be empty; in fact, it won't even show a grid):

In your DB class, keep the code for opening and closing the database connection and form.

Change the name of the Data Grid to DataGrid.
Now modify the code for the Query button to do the query and load the results in the DataGridView using a so-called DataAdapter and a DataTable. The DataAdapter creates the DataTable which is then pointed to (referenced) by the DataGrid:


private void QueryButton_Click(object sender, EventArgs e)
{
  DataTable dt;
  System.Data.OleDb.OleDbDataAdapter myAdapter = null;
  string QueryString = "select my_name as 'my_name', my_name as 'another my_name' from foo"; //modify this query to fit your database

  try { DB.DBOpen(); }
  catch(System.Exception ex)
  {           
    MessageBox.Show("Problem opening database..." + ex.Message);
    System.Environment.Exit(1);
  } 

  try { myAdapter = new System.Data.OleDb.OleDbDataAdapter(QueryString, DB.Connection); } 
  catch(System.Exception  g)
  {
    MessageBox.Show("Problem creating dataAdapter..." + g.Message);
    try { DB.DBClose(); }
    catch(System.Exception h) { MessageBox.Show("Problem closing database..." + h.Message); }
    System.Environment.Exit(1);
  }
                 
  dt = new DataTable();
		
  myAdapter.Fill(dt);
  myAdapter.Dispose();
  DataGrid.DataSource = dt;
  Refresh();
}

Build and run.

Explore the Datagrid a bit more. For instance, add few more colums to your database table, populate them with data and replace the SQL in the program above with the following:
select * from your_table_name
Build and run and see what happens.