A Guide to Using SQLite From a C# Application

How to Use SQLite From a C# Application

Download SQLite manager. SQLite is an excellent database with good free admin tools. This tutorial uses SQLite Manager, which is an extension for the Firefox browser. If you have Firefox installed, select ​Add-ons, then Extensions from the pull-down menu at the top of the Firefox screen. Type "SQLite Manager" in the search bar. Otherwise, visit the SQLite-manager website.

Create a Database and Table

After SQLite Manager is installed and Firefox restarted, access it from the Firefox Web Developer menu off the main Firefox menu. From the Database menu, create a new database. named "MyDatabase" for this example. The database is stored in the MyDatabase.sqlite file, in whatever folder you select. You'll see the Window caption has the path to the file.

On the Table menu, click Create Table. Create a simple table and call it "friends" (type it in the box on the top). Next, define a few columns and populate it from a CSV file. Call the first column idfriend, select INTEGER in the Data Type combo and click the Primary Key> and Unique? checkboxes.

Add three more columns: firstname and lastname, which are type VARCHAR, and age, which is INTEGER. Click OK to create the table. It will display the SQL, which should look something like this.

Click the Yes button to create the table, and you should see it on the left side under Tables(1). You can modify this definition at any time by selecting Structure on the tabs on the right side of the SQLite Manager window. You can select any column and right-click Edit Column/Drop Column or add a new column at the bottom and click the Add Column button.

Prepare and Import Data

Use Excel to create a spreadsheet with columns: idfriend, firstname, lastname, and age. Populate a few rows, making sure that the values in idfriend are unique. Now save it out as a CSV file. Here's an example that you can cut and paste into a CSV file, which is just a text file with data in a comma delimited format.

On the database menu, click Import and choose Select File. Browse to the folder and select the file and then click Open in the dialog. Enter the name of the table (friends) on the CSV tab and confirm the "First row contains column names" is ticked and "Fields Enclosed by" is set to none. Click OK. It asks you to click OK before importing, so click it then again. If all goes well, you'll have three rows imported into the friends table.

Click Execute SQL and change tablename in SELECT * from tablename to friends and then click the Run SQL button. You should see the data.

Accessing the SQLite Database From a C# Program

Now it's time to setup Visual C# 2010 Express or Visual Studio 2010. First, you need to install the ADO driver. You'll find several, depending on 32/64 bit and PC Framework 3.5/4.0 on the System.Data.SQLite download page.

Create a blank C# Winforms project. When that's done and opened, in the Solution Explorer add a reference to System.Data.SQLite. View the Solution Explorer—it's on the View Menu if not open)— and right-click on References and click Add Reference. In the Add Reference dialog that opens, click the Browse tab and browse to:

It may be in C:\Program Files (x86)\System.Data.SQLite\2010\bin depending on if you are running 64 bit or 32 bit Windows. If you've installed it already, it will be in there. In the bin folder, you should see System.Data.SQLite.dll. Click OK to select it in the Add Reference dialog. It should pop up in the list of References. You need to add this for any future SQLite/C# projects you create.

Read More

Programming SQLite in C Tutorial Two

By David Bolton

A Demo Adding SQLite to the C# Application

In the example, DataGridView, which is renamed to "grid" and two buttons—"Go" and "Close"—are added to the screen. Double-click to generate a click-handler and add the following code.

When you click the Go button, this creates a SQLite connection to the file MyDatabase.sqlite. The format of the connection string is from the website connectionstrings.com. There are several listed there.

You need to alter the path and filename to that of your own SQLite database that you created earlier. When you compile and run this, click Go and you should see the results of the "select * from friends" displayed in the grid.

If the connection correctly opens, a SQLiteDataAdapter returns a DataSet from the result of the query with the da.fill(ds); statement. A DataSet can include more than one table, so this returns just the first, obtains the DefaultView and hooks it up to the DataGridView, which then displays it.

The real hard work is adding the ADO Adapter and then the reference. After that is done, it works like any other database in C#/.NET.