This part assumes you have added SQLite support to your application as described in the previous page. It goes on with developing the sample application.
-
Add a connection object to the project, using the Tools - Add Object menu:
And name it Connection
The "connection" object is an object found in the SQLite library, that handles details regarding the connection with a specific database. Since SQLite uses a single file as a database, the connection object receives a filename as an argument, indicating the name of the database to which to connect.
-
Add a command object the same way...
... and name it Command:
-
The first thing to do is to establish connection to the database. We will do this in a separate Sub called CreateConnection. Add the following lines to the programs code, below the last line of code:
1 Sub CreateConnection 2 Connection.New1
3 Command.New1(
"", Connection.Value)4 Connection.Open(
"Data Source = " & AppPath & "\Rest.lit")5 End Sub
Add the code above at the end of your program, below the End Sub of the Sub App_Start
These lines of code form the basic connection to the database:
-
In line 2 we instantiate a new instance of the Connection object we have previously added. We use the New1 constructor, which takes no parameters.
-
In line 3 we instantiate a new instance of the Command object. Its New1 constructor takes two parameters: a default command text, left empty in this example, and a connection to be attached to. The command text property will be manually filled later. At this moment we are only interesting in creating the instance, and opening the connection.
-
In line 4 we open the connection. The Open sub takes a string as a parameter. This string is called Connection String and is used by many databases as a unique identifier of the database location relative to the application and other database related information. Using SQLite we only need to specify the filename (since it is a single-file, serverless database) to use. In this example we have named the file "Rest.lit", and located it in the application folder using the keyword AppPath.
The Open Sub actually tells the connection object which file to use. This file represents the entire database. If the file does not exist, Open will create it. -
Notes:
-
We could have omited line 3 at this stage. It is for convenience only that we have included it the same place where other objects and connection are initialized.
-
When creating the command, we use the Value property of the connection object. This Sub returns a reference to the connection object in a format expected by the command object.
-
Of course, after the first time you have created the database there is no need to create it anymore. This code either create a database and connects to is, or just connects to it, if it already exists.
After writing this Sub, add a call to the sub in the App_Start Sub as follows:
|
|
Sub
App_Start CreateConnection Form1.Show End Sub |
|
This ensures the newly-created Sub (CreateConnection) will be called when the program starts.
We also have to make sure we close the connection when the program ends, typically when the main form (Form1 in our example) is closed.
Add the following code:
|
|
Sub
Form1_Close Con.Close End Sub |
|
These are the actions required to open a connection. Next we shall create the tables.