iAnywhere.com - Lesson 3: Connect to the database

Tutorial: Build an UltraLite.NET Application

Lesson 3: Connect to the database

The following procedure adds a control to your UltraLite.NET application that establishes a connection to an UltraLite database.

If an UltraLite application attempts to connect to a database that does not exist, it 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 created a directory with a different name, use that directory throughout the tutorial.

To add an UltraLite connection to your application

Add the ConnectionParms control to your form.

From the UltraLite.NET CF tab in the toolbox, choose ULConnectionParms and add it to your form. The control is named UlConnectionParms1 (Visual Basic) or ulConnectionParms1 (C#).

If you have not rebooted since installing SQL Anywhere Studio, you may get an error message at this point. Reboot your machine to solve this problem.
Specify the connection parameters.

Ensure the following properties are set:
- DatabaseOnCE \Program Files\CSApp\CSApp.udb for a Visual C# application, or \Program Files\VBApp\VBApp.udb for a Visual Basic application.
- SchemaOnCE \Program Files\CSApp\CSApp.usm for a Visual C# application, or \Program Files\VBApp\VBApp.usm for a Visual Basic application.
Open the source code for the form.

Double-click the form to open the source file.
Import the iAnywhere.Data.UltraLite namespace.

Add the following statement as the very first line of the file.
```
//Visual C#
using iAnywhere.Data.UltraLite;
```
```
'Visual Basic
Imports iAnywhere.Data.UltraLite
```
Add global variables to the form declaration.

For a Visual C# application, add the following code after the code describing the form components and before the first method declaration.
```
//Visual C#
private ULConnection Conn;
private int[] ids;
```
For a Visual Basic project, add the following code after the declaration of the form components (FriendsWithEvents declarations) and before the first method declaration.
```
'Visual Basic
Dim Conn As ULConnection
Dim ids() As Integer
```
These variables are used as follows:
- ULConnection A Connection object is the root object for all actions executed on a connection to a database.
- ids The ids array is used to hold the ID column values returned after executing a query.
  Although the ListBox control itself allows you access to sequential numbers, those numbers differ from the value of the ID column once a row has been deleted. For this reason, the ID column values must be stored separately.
Double-click a blank area of your form to create a Form1_Load method.

This method performs the following tasks.
- Opens a connection to the database using the connection parameters set in the ULConnectionParms1 control.
- Calls the RefreshListBox 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 about the error code, see ASA Error Messages.
For C#, add the following code to the method.
```
//Visual C#
try {
    Conn = new ULConnection( 
        ulConnectionParms1.ToString() );
    Conn.OpenWithCreate();
    Conn.DatabaseID = 1;
    RefreshListBox();
}
catch ( System.Exception t ) {
    MessageBox.Show( "Exception: " + t.Message);
}
```
For Visual Basic , add the following code to the method.
```
'Visual Basic
Try
    Conn = New ULConnection( _
        UlConnectionParms1.ToString())
    Conn.OpenWithCreate()
    Conn.DatabaseID = 1
    RefreshListBox()
Catch
    MsgBox("Exception: " + err.Description)
End Try
```
Build the project.

From the Build menu choose Build Solution. At this stage, you should get a single error reported, which is that the Name RefreshListBox is not declared. You will fix this error by adding the function in the next lesson.

If you get other errors, fix them. Check for common errors, such as case inconsistencies in C#. For example, UltraLite and ULConnection must match case exactly.