BA 371 - VC#.Net Database Communication

VC#.Net – DB Communications

VC#.Net communicates with databases through, surprise!, predefined classes and objects.
Note!!: When programming against databases, !!!ALWAYS!!!!! check each and every operation/transaction for completion (using try/catch blocks).
Some advise: modify your Windows explorer/View options such that you DO NOT HIDE file extensions. In Windows Explorer (Tools-->Folder Options -->View Tab--> Hide extensions for known file types.)

In the following console(!) application example we connect and disconnect to an Access database located at c:\foo.accdb (foo.mdb in Acces 2003) (since you most likely do not have that database, make a blank database first):


class Foo
{
  static void Main()
  {
    /*Access 2003
    string ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0; + "Data Source=c:\\foo.mdb"; */

    /* Access 2007 */
    string ConnectString  = "provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=c:\\foo.accdb";

    System.Data.OleDb.OleDbConnection Connection = new System.Data.OleDb.OleDbConnection(ConnectString);
		
    try /* opening the db connection */
    {
      System.Console.WriteLine(ConnectString);
      Connection.Open();
      System.Console.WriteLine("Connection open...");
    }
    catch(System.Exception e)
    {
      System.Console.WriteLine("Problems opening..." + e.Message);
      System.Console.WriteLine();
      System.Console.WriteLine("Press Enter to Exit.");
      System.Console.ReadLine();
      System.Environment.Exit(1);
    }

    try /* closing the connection */
    {
      Connection.Close();
      System.Console.WriteLine("Connection closed...");
    }
    catch(System.Exception e)
    {
      System.Console.WriteLine("Problems closing..." + e.Message);
      System.Console.WriteLine();
      System.Console.WriteLine("Press Enter to Exit.");
      System.Console.ReadLine();
      System.Environment.Exit(1);
    }

    System.Console.WriteLine();
    System.Console.WriteLine("Press Enter to Exit.");
    System.Console.ReadLine();
    System.Environment.Exit(0);
  }
}

Notice how the above code only connects/disconnects from the database; no database transactions (create, insert, update, etc.) occur.
Notice that the disconnect (Close()) only occurs after a successful Open() (do not disconnect from a database to which you are not connected).
Notice how the Open() call sits inside a try block so that we can gracefully catch an unsuccessful Open() attempt. Test this by trying to open a connection to a nonexisting database!!
Notice how a failed Open() or failed Close() is caught in the catch block and how the Message part of the exception is written to the console.
Try this out using a console app. and an Access database that you generate yourself (for this exercise the database does need to contain any tables). Make sure you test both successful and unsuccessful connections. (NOTE: You will need to set the startup object of the console application before compiling.)

Note!!!! The above code hardwires the path and name of the database. Although we do it here for convenience, this is bad (!!!) coding practice. If the database would ever be renamed or put on a different location on the system, the program could never establish a connection anymore and the code would have to be changed.
- ==> Another major rule of programming: Do not include network or machine-specific information in your code!
Next, let's make an SQL query and display the results:

In your Access database, create a table foo with a string column my_name and a data type(type text). The SQL for a query that returns all my_name entries from the foo table would be as follows:

select my_name from foo;

Try this out in Access itself by using its built-in SQL interpreter:

Start a new query in design view, then click away the "Show Table" dialog and click on the SQL icon on the left of the menu bar; type in your SQL (terminated by a semicolon!!) and hit the exclamation mark to run the SQL.

Try this before running your query through VC#.Net. A little trick-of-the-trade: before including SQL calls into your program code, always!! try them out using the SQL interpreter of the database software itself. That way you can exclude SQL errors from messing up your program.

In VC#.Net we use an OleDBCommand object to store the SQL statement. The object's ExecuteReader() method can be used to execute the statement. This method returns an OleDbDataReader object which holds the results of the query.
Now, manually fill up the foo table with multiple records (three or five will do fine). Now, test in Access that the above query results in more than one my_name being returned. All of these records will be returned to the OleDbDataReader object when it uses its ExecuteReader() method.

If our program must display each of these my_name strings, it must grab them, one after the other, and display them to the screen. We do this grabbing using the Read() method on the OleDbDataReader object. Each time we call this method it will give us the next record returned from the database. When no more records are returned by the reader, we are done.


class Foo
{
  static void Main()
  {
    string my_env = null;

    my_env = System.Environment.GetEnvironmentVariable("vcsharpdb");
    if (my_env == null)
    {
      System.Console.WriteLine("Problems getting env. var 'vcsharpdb'...");
      System.Console.WriteLine();
      System.Console.WriteLine("Press Enter to Exit.");
      System.Console.ReadLine();
      System.Environment.Exit(1);
    }
      
    /*Access 2003
    string ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0; + "Data Source=" + my_env; */

    /* Access 2007 */
    string ConnectString  = "provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + my_env;

    System.Data.OleDb.OleDbConnection Connection = new System.Data.OleDb.OleDbConnection(ConnectString);
		
    System.Data.OleDb.OleDbCommand SQLCommand = null;

    System.Data.OleDb.OleDbDataReader SQLDataReader = null;

    string Result_string;

    //open the connection
    Connection = new System.Data.OleDb.OleDbConnection(ConnectString);
    try
    {
      System.Console.WriteLine(ConnectString);
      Connection.Open();
      System.Console.WriteLine("Connection open...");
    }
    catch(System.Exception e)
    {
      System.Console.WriteLine("Problems opening..." + e.Message);
      System.Console.WriteLine();
      System.Console.WriteLine("Press Enter to Exit.");
      System.Console.ReadLine();
      System.Environment.Exit(1);
    }

    // set the SQL statement
    SQLCommand = new System.Data.OleDb.OleDbCommand("select my_name from foo", Connection);

    //execute the statement
    try 
    {
      SQLDataReader = SQLCommand.ExecuteReader();
    }
    catch(System.Exception e)
    {
      System. Console.WriteLine("Problems executing..." + e.Message);
      Connection.Close();
      System. Console.WriteLine("Connection closed...");
      System. Console.WriteLine();
      System. Console.WriteLine("Press Enter to Exit.");
      System.Console.ReadLine();
      System.Environment.Exit(1);
    }

    /* at this point we know that execution succeeded so we can start reading the
       results. Since we do not know how many records we get returned, we'll stick
       the read in a while() loop that keeps executing until we no longer get
       results from the reader. */

     try
     {
       while (SQLDataReader.Read())
       {
         Result_string = SQLDataReader.GetString(0);
         System.Console.WriteLine(Result_string);
       }
       SQLDataReader.Close();
    }
    catch(System.Exception e)
    {
      System.Console.WriteLine("Problems reading data..." + e.Message);
      Connection.Close();
      System.Console.WriteLine("Connection closed...");
      System.Console.WriteLine();
			
      System.Console.WriteLine("Press Enter to Exit.");
      System.Console.ReadLine();
      System.Environment.Exit(1);
    }

     //we're done so we can close the connection
     try
     {
       Connection.Close();
       System.Console.WriteLine("Connection closed...");
     }
     catch(System.Exception e)
     {
       System.Console.WriteLine("Problems closing..." + e.Message);
     }
		
     System.Console.WriteLine();
     System.Console.WriteLine("Press Enter to Exit.");
     System.Console.ReadLine();
  }
}

Note that the DataReader object never gets created in this code (no new statement for it). This is because it gets created as a side effect by the ExecuteReader() method on the OleDbCommand object.
It is good practice to Close() your DataReader once you’re done with it (in our next lab we'll see the benefit of this!).
Also note that each of the database actions is enclosed in a try/catch block. Although there is no syntactical requirement to do so, it is very wise(!!!) to check each and every action run against a database. This gives your program opportunity to 'bail out' when things go wrong when communicating with a database (what could some of things be?).
Summary of VC#.Net - Access database connectivity:

Open the connection.

Store the SQL statement on an OleDbCommand object.

Call the ExecuteReader method on the OleDbCommand object to execute the query.

Call the Read() method on the OleDbDataReader object to access the results.

When done, close the DataReader and close the Connection.

Challenge: Can you now change the program so that instead of reading from the database it writes another my_name to the foo table?