Pages

23 November 2011

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.
Attached Image
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.
Attached Image
Right click the reference to System.Data.SQLite, and select "Properties". Set the property "Copy Local" to true.
Attached Image
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
001using System;
002using System.Collections.Generic;
003using System.Data;
004using System.Data.SQLite;
005using System.Windows.Forms;
006 
007class 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:
01try
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 }
23catch(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:
01db = new SQLiteDatabase();
02Dictionary<String, String> data = new Dictionary<String, String>();
03data.Add("NAME", nameTextBox.Text);
04data.Add("DESCRIPTION", descriptionTextBox.Text);
05data.Add("PREP_TIME", prepTimeTextBox.Text);
06data.Add("COOKING_TIME", cookingTimeTextBox.Text);
07data.Add("COOKING_DIRECTIONS", "Placeholder");
08try
09{
10         db.Insert("RECIPE", data);
11}
12catch(Exception crap)
13{
14    MessageBox.Show(crap.Message);
15}


Update:
01db = new SQLiteDatabase();
02Dictionary<String, String> data = new Dictionary<String, String>();
03DataTable rows;
04data.Add("NAME", nameTextBox.Text);
05data.Add("DESCRIPTION", descriptionTextBox.Text);
06data.Add("PREP_TIME", prepTimeTextBox.Text);
07data.Add("COOKING_TIME", cookingTimeTextBox.Text);
08try
09{
10     db.Update("RECIPE", data, String.Format("RECIPE.ID = {0}", this.RecipeID));
11}
12catch(Exception crap)
13{
14    MessageBox.Show(crap.Message);
15}


Delete:
1db = new SQLiteDatabase();
2String recipeID = "12";
3db.Delete("RECIPE", String.Format("ID = {0}", recipeID));
4db.Delete("HAS_INGREDIENT", String.Format("ID = {0}", recipeID));




Nguồn: http://www.dreamincode.net/forums/topic/157830-using-sqlite-with-c%23/

Viết bởi macashipo at 11/23/2011 10:21:00 AM