Using databases with web apps is not much different than using them with desktop apps, but there are a few things to keep in mind. The most important thing to note is that a web app allows multiple users. This means you’ll want your database connection to be unique for each user that connects to the web app, rather than global to the app itself (as is common in desktop apps). The simplest way to ensure this is to create the connection to your database in the WebSession.Open event handler, saving a reference to the connection as a property that you add to WebSession.
A property such as “DB As SQLiteDatabase” on the Session object works well. Then in its Open event handler, you actually connect to the database:
Dim dbFile As FolderItem = GetFolderItem("MyDatabase.sqlite")
DB = New SQLiteDatabase
DB.DatabaseFile = dbFile
If Not db.Connect Then
' Display an error page and log the error
' You should not show specifics of the error to users
ErrorPage.Show
End If
In the rest of your project, you can refer to the database like this:
Session.DB
The second thing to remember is that because a web app can be accessed by many more users it is more susceptible to SQL Injection. To limit your exposure here, you should always use SQL Prepared Statements with any SQL statements that contain parameters with values from user-entered input.
To learn more about databases and web apps, view the Database Usage with Web Apps topic in the Dev Center and watch our Webinar: Connecting to Databases.