Using SQLite With C#

Overview

Adding a database to your application can be an easy way to store data and settings between sessions for your program, but it is not always feasible to use a server based DBMS to store your database. SQLite is a small, fast, and reliable database which can be used without the end user having to install anything extra (achieved by referencing a single .dll in your project). There are a few things we as developers must do to get started with SQLite:

• Install the .NET provider for SQLite from Sourceforge.net
  
• Add a reference to System.Data.SQLite to your project (and mark the .dll to be copied locally to your project)
  
• Optionally Download a SQLite GUI Client and use it to design your DB (Feel free to code it by hand if that is your preference)

If the above section made sense to you, feel free to jump down to the section titled "Interacting with your Database", otherwise keep reading!

Getting Started

Referencing System.Data.SQLite
After you have installed the .NET provider for SQLite, you need to make sure that your project can access the required .dll. In Visual Studio 2008, this can be done by selecting "Project -> Add Reference..." from the main menu bar. A window will pop up, and under the ".NET" tab, scroll down and find System.Data.SQLite.
 
Select it and click ok. It is now referenced in your project. The last thing we need to do is make sure Visual Studio copies the .dll for System.Data.SQLite to the project folder, which is necessary for SQLite to work without the provider. If the Solution Explorer window is not currently visible, open it by selecting "View -> Solution Explorer" from the main menu bar. Under the current project, click the + sign next to References to see a list of all currently referenced libraries.

Right click the reference to System.Data.SQLite, and select "Properties". Set the property "Copy Local" to true. 

You have now successfully referenced SQLite, and it can be added to any file by "using System.Data.SQLite;".

Using the SQLite GUI Client

SQLite Administrator is a very straightforward Client, and I am not going to go into much detail with its use. I will however note a few things that were not immediately evident to me when I first used it.

• SQLite does not currently support foreign key constraints. Therefore SQLite Administrator does not have any way of linking tables via Foreign Key. That is certainly something to keep in mind.
  
• The box on the left hand side is for viewing the current Database and all of it's objects. If you see something you don't want to see, or don't see something you want to see, the buttons at the top of the box are toggle switches for tables, views, triggers, indexes, and so on. Since there are no tooltips, you'll just have to play around to figure out which is which function.

Interacting with your Database

Once the database is set up, it is time to begin reading from it and writing to it. In order to facilitate the interaction with the DB, I have written a helper class. It should be noted that a portion of this code is adapted from sample code in this tutorial by Mike Duncan. The Methods GetDataTable(), ExecuteNonQuery(), and ExecuteScalar() are his code and not mine.

Using the SQLiteDatabase Helper Class

SQLiteDatabase.cs

001	using System;

002	using System.Collections.Generic;

003	using System.Data;

004	using System.Data.SQLite;

005	using System.Windows.Forms;

006

007	class SQLiteDatabase

008

{

009	String dbConnection;

010

011	/// <summary>

012	///     Default Constructor for SQLiteDatabase Class.

013	/// </summary>

014	public SQLiteDatabase()

015

{

016	dbConnection = "Data Source=recipes.s3db";

017

}

018

019	/// <summary>

020	///     Single Param Constructor for specifying the DB file.

021	/// </summary>

022	/// <param name="inputFile">The File containing the DB</param>

023	public SQLiteDatabase(String inputFile)

024

{

025	dbConnection = String.Format("Data Source={0}", inputFile);

026

}

027

028	/// <summary>

029	///     Single Param Constructor for specifying advanced connection options.

030	/// </summary>

031	/// <param name="connectionOpts">A dictionary containing all desired options and their values</param>

032	public SQLiteDatabase(Dictionary<String, String> connectionOpts)

033

{

034	String str = "";

035	foreach (KeyValuePair<String, String> row in connectionOpts)

036

{

037	str += String.Format("{0}={1}; ", row.Key, row.Value);

038

}

039	str = str.Trim().Substring(0, str.Length - 1);

040	dbConnection = str;

041

}

042

043	/// <summary>

044	///     Allows the programmer to run a query against the Database.

045	/// </summary>

046	/// <param name="sql">The SQL to run</param>

047	/// <returns>A DataTable containing the result set.</returns>

048	public DataTable GetDataTable(string sql)

049

{

050	DataTable dt = new DataTable();

051

try

052

{

053	SQLiteConnection cnn = new SQLiteConnection(dbConnection);

054	cnn.Open();

055	SQLiteCommand mycommand = new SQLiteCommand(cnn);

056	mycommand.CommandText = sql;

057	SQLiteDataReader reader = mycommand.ExecuteReader();

058	dt.Load(reader);

059	reader.Close();

060	cnn.Close();

061

}

062	catch (Exception e)

063

{

064	throw new Exception(e.Message);

065

}

066	return dt;

067

}

068

069	/// <summary>

070	///     Allows the programmer to interact with the database for purposes other than a query.

071	/// </summary>

072	/// <param name="sql">The SQL to be run.</param>

073	/// <returns>An Integer containing the number of rows updated.</returns>

074	public int ExecuteNonQuery(string sql)

075

{

076	SQLiteConnection cnn = new SQLiteConnection(dbConnection);

077	cnn.Open();

078	SQLiteCommand mycommand = new SQLiteCommand(cnn);

079	mycommand.CommandText = sql;

080	int rowsUpdated = mycommand.ExecuteNonQuery();

081	cnn.Close();

082	return rowsUpdated;

083

}

084

085	/// <summary>

086	///     Allows the programmer to retrieve single items from the DB.

087	/// </summary>

088	/// <param name="sql">The query to run.</param>

089	/// <returns>A string.</returns>

090	public string ExecuteScalar(string sql)

091

{

092	SQLiteConnection cnn = new SQLiteConnection(dbConnection);

093	cnn.Open();

094	SQLiteCommand mycommand = new SQLiteCommand(cnn);

095	mycommand.CommandText = sql;

096	object value = mycommand.ExecuteScalar();

097	cnn.Close();

098	if (value != null)

099

{

100	return value.ToString();

101

}

102	return "";

103

}

104

105	/// <summary>

106	///     Allows the programmer to easily update rows in the DB.

107	/// </summary>

108	/// <param name="tableName">The table to update.</param>

109	/// <param name="data">A dictionary containing Column names and their new values.</param>

110	/// <param name="where">The where clause for the update statement.</param>

111	/// <returns>A boolean true or false to signify success or failure.</returns>

112	public bool Update(String tableName, Dictionary<String, String> data, String where)

113

{

114	String vals = "";

115	Boolean returnCode = true;

116	if (data.Count >= 1)

117

{

118	foreach (KeyValuePair<String, String> val in data)

119

{

120	vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());

121

}

122	vals = vals.Substring(0, vals.Length - 1);

123

}

124

try

125

{

126	this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));

127

}

128

catch

129

{

130	returnCode = false;

131

}

132	return returnCode;

133

}

134

135	/// <summary>

136	///     Allows the programmer to easily delete rows from the DB.

137	/// </summary>

138	/// <param name="tableName">The table from which to delete.</param>

139	/// <param name="where">The where clause for the delete.</param>

140	/// <returns>A boolean true or false to signify success or failure.</returns>

141	public bool Delete(String tableName, String where)

142

{

143	Boolean returnCode = true;

144

try

145

{

146	this.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where));

147

}

148	catch (Exception fail)

149

{

150	MessageBox.Show(fail.Message);

151	returnCode = false;

152

}

153	return returnCode;

154

}

155

156	/// <summary>

157	///     Allows the programmer to easily insert into the DB

158	/// </summary>

159	/// <param name="tableName">The table into which we insert the data.</param>

160	/// <param name="data">A dictionary containing the column names and data for the insert.</param>

161	/// <returns>A boolean true or false to signify success or failure.</returns>

162	public bool Insert(String tableName, Dictionary<String, String> data)

163

{

164	String columns = "";

165	String values = "";

166	Boolean returnCode = true;

167	foreach (KeyValuePair<String, String> val in data)

168

{

169	columns += String.Format(" {0},", val.Key.ToString());

170	values += String.Format(" '{0}',", val.Value);

171

}

172	columns = columns.Substring(0, columns.Length - 1);

173	values = values.Substring(0, values.Length - 1);

174

try

175

{

176	this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));

177

}

178	catch(Exception fail)

179

{

180	MessageBox.Show(fail.Message);

181	returnCode = false;

182

}

183	return returnCode;

184

}

185

186	/// <summary>

187	///     Allows the programmer to easily delete all data from the DB.

188	/// </summary>

189	/// <returns>A boolean true or false to signify success or failure.</returns>

190	public bool ClearDB()

191

{

192	DataTable tables;

193

try

194

{

195	tables = this.GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;");

196	foreach (DataRow table in tables.Rows)

197

{

198	this.ClearTable(table["NAME"].ToString());

199

}

200	return true;

201

}

202

catch

203

{

204	return false;

205

}

206

}

207

208	/// <summary>

209	///     Allows the user to easily clear all data from a specific table.

210	/// </summary>

211	/// <param name="table">The name of the table to clear.</param>

212	/// <returns>A boolean true or false to signify success or failure.</returns>

213	public bool ClearTable(String table)

214

{

215

try

216

{

217

218	this.ExecuteNonQuery(String.Format("delete from {0};", table));

219	return true;

220

}

221

catch

222

{

223	return false;

224

}

225

}

226

}

Usage:

Query:

01

try

02

{

03	db = new SQLiteDatabase();

04	DataTable recipe;

05	String query = "select NAME \"Name\", DESCRIPTION \"Description\",";

06	query += "PREP_TIME \"Prep Time\", COOKING_TIME \"Cooking Time\"";

07	query += "from RECIPE;";

08	recipe = db.GetDataTable(query);

09	// The results can be directly applied to a DataGridView control

10	recipeDataGrid.DataSource = recipe;

11

/*

12	// Or looped through for some other reason

13	foreach (DataRow r in recipe.Rows)

14

{

15	MessageBox.Show(r["Name"].ToString());

16	MessageBox.Show(r["Description"].ToString());

17	MessageBox.Show(r["Prep Time"].ToString());

18	MessageBox.Show(r["Cooking Time"].ToString());

19

}

20

21

*/

22

}

23	catch(Exception fail)

24

{

25	String error = "The following error has occurred:\n\n";

26	error += fail.Message.ToString() + "\n\n";

27	MessageBox.Show(error);

28	this.Close();

29

}

Insert:

01	db = new SQLiteDatabase();

02	Dictionary<String, String> data = new Dictionary<String, String>();

03	data.Add("NAME", nameTextBox.Text);

04	data.Add("DESCRIPTION", descriptionTextBox.Text);

05	data.Add("PREP_TIME", prepTimeTextBox.Text);

06	data.Add("COOKING_TIME", cookingTimeTextBox.Text);

07	data.Add("COOKING_DIRECTIONS", "Placeholder");

08

try

09

{

10	db.Insert("RECIPE", data);

11

}

12	catch(Exception crap)

13

{

14	MessageBox.Show(crap.Message);

15

}

Update:

01	db = new SQLiteDatabase();

02	Dictionary<String, String> data = new Dictionary<String, String>();

03	DataTable rows;

04	data.Add("NAME", nameTextBox.Text);

05	data.Add("DESCRIPTION", descriptionTextBox.Text);

06	data.Add("PREP_TIME", prepTimeTextBox.Text);

07	data.Add("COOKING_TIME", cookingTimeTextBox.Text);

08

try

09

{

10	db.Update("RECIPE", data, String.Format("RECIPE.ID = {0}",this.RecipeID));

11

}

12	catch(Exception crap)

13

{

14	MessageBox.Show(crap.Message);

15

}

Delete:

1	db = new SQLiteDatabase();

2	String recipeID = "12";

3	db.Delete("RECIPE", String.Format("ID = {0}", recipeID));

4	db.Delete("HAS_INGREDIENT", String.Format("ID = {0}", recipeID));