UltraLite.NET User's Guide
  Tutorial: Visual Studio Application

Lesson 3: Connect to the database

In this lesson you add code to your .NET application that connects to a database using the schema you have created.

UltraLite database files have an extension of .udb. If an application attempts to connect to a database and the specified database file does not exist, UltraLite uses the schema file to create the database.

This tutorial assumes that if you are designing a C# application, your files are in the directory c:\tutorial\uldotnet\CSapp and that if you are designing a Visual Basic application, your files are in the directory c:\tutorial\uldotnet\VBapp. If you create a directory with a different name, use that directory instead of c:\tutorial\uldotnet\CSApp or c:\tutorial\uldotnet\VBApp throughout the tutorial.

To connect to an UltraLite database

1. Right click on your form and select View Code. For a Visual C# application, add the following statement at the beginning of the file:

   using iAnywhere.UltraLite;

   This references the iAnywhere.UltraLite.NET libraries. Visual Basic projects do not need this explicit declaration.

   Add the following global variables to the form declaration of your Visual C# project, after the code describing the form components.

   private DatabaseManager DbMgr = new DatabaseManager();
   private Connection Conn;
   private PreparedStatement PrepStmt;
   private int[] ids;

   For a Visual Basic project, add the following code after the declaration of the form's components:

   Dim DbMgr As New iAnywhere.UltraLite.DatabaseManager
   Dim Conn As iAnywhere.UltraLite.Connection
   Dim PrepStmt As iAnywhere.UltraLite.PreparedStatement
   Dim ids[] As Integer

2. Double click on a blank area of your form to create a Form1_Load method. If developing a C# application, add the following code to the method:

   try
   {
      CreateParms parms = new CreateParms();
      parms.DatabaseOnCE = 
         "\\Program Files\\CSApp\\tutorial.udb";
      parms.Schema.SchemaOnCE = 
         "\\Program Files\\CSApp\\tutorial.usm";

      try
      {
         Conn = DbMgr.OpenConnection( parms );
         MessageBox.Show( 
            "Connected to an existing database." );
      }
      catch( SQLException err )
      {
         if( err.ErrorCode == 
           SQLCode.SQLE_ULTRALITE_DATABASE_NOT_FOUND )
         {
            Conn = DbMgr.CreateDatabase( parms );
            MessageBox.Show( 
               "Connected to a new database.");
         }
         else
         {
            throw err;
         }
      RefreshLB();
      }
   }
   catch( SQLException err )
   {
      MessageBox.Show("Exception: " + err.Message +
               " sqlcode=" + err.ErrorCode);
   }
   catch ( System.Exception t )
   {
      MessageBox.Show( "Exception: " + t.Message);
   }

   For a Visual Basic application, use this code:

   Try
      Dim parms As New iAnywhere.UltraLite.CreateParms
      parms.DatabaseOnCE = _
         "\\Program Files\\VBApp\\tutorial.udb"
      parms.Schema.SchemaOnCE = _
         "\\Program Files\\VBApp\\tutorial.usm"
      Try
         Conn = DbMgr.OpenConnection(parms)
         MessageBox.Show( _
           "Connected to an existing database.")
      Catch err As iAnywhere.UltraLite.SQLException
         If err.ErrorCode = _
           SQLCode.SQLE_ULTRALITE_DATABASE_NOT_FOUND _
         Then
            Conn = DbMgr.CreateDatabase(parms)
            MessageBox.Show("Connected to a new database.")
         Else
            Throw err
         End If
      End Try
   RefreshLB()
   Catch
      MsgBox("Exception: " + err.Description)
   End Try

   This code carries out the following tasks:

   • Instantiates and defines a new CreateParms object. CreateParms stores the parameters necessary to connect to or create a database. Here, the parameters are the location of the database file on the device, and the location of the schema file on the device to use if the database does not exist.

   • Opens a connection to the database using the CreateParms object.

     If the database file does not exist, a SQLException is thrown. The code that catches this exception uses the schema file to create a new database and establish a connection to it.

     If the database file does exist, a connection is established.

   • Calls the RefreshLB method, which you will define later in this tutorial.

   • If an error occurs, prints the error message. For SQL errors, the code also prints the error code. For more information on the error code, you can look it up in the Adaptive Server Anywhere Error Messages book that is part of this documentation set.

3. Build the project.

   Choose Build > Build Solution. At this stage, you should get a single error reported, which is that the name RefreshLB does not exist.

   If you get other errors, check the code until they are fixed. Check for common errors, such as case differences (for example, UltraLite, DbMgr must match case exactly).

Once the project builds with a single error, you can move to the next lesson.