• (2 Pages)
  • +
  • 1
  • 2

Using SQLite with C#

#1 brennydoogles  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 11
  • View blog
  • Posts: 59
  • Joined: 23-June 09

Posted 23 February 2010 - 11:41 AM

*
POPULAR

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
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;

class SQLiteDatabase
{
	String dbConnection;

	/// <summary>
	///     Default Constructor for SQLiteDatabase Class.
	/// </summary>
	public SQLiteDatabase()
	{
		dbConnection = "Data Source=recipes.s3db";
	}

	/// <summary>
	///     Single Param Constructor for specifying the DB file.
	/// </summary>
	/// <param name="inputFile">The File containing the DB</param>
	public SQLiteDatabase(String inputFile)
	{
		dbConnection = String.Format("Data Source={0}", inputFile);
	}

	/// <summary>
	///     Single Param Constructor for specifying advanced connection options.
	/// </summary>
	/// <param name="connectionOpts">A dictionary containing all desired options and their values</param>
	public SQLiteDatabase(Dictionary<String, String> connectionOpts)
	{
		String str = "";
		foreach (KeyValuePair<String, String> row in connectionOpts)
		{
			str += String.Format("{0}={1}; ", row.Key, row.Value);
		}
		str = str.Trim().Substring(0, str.Length - 1);
		dbConnection = str;
	}

	/// <summary>
	///     Allows the programmer to run a query against the Database.
	/// </summary>
	/// <param name="sql">The SQL to run</param>
	/// <returns>A DataTable containing the result set.</returns>
	public DataTable GetDataTable(string sql)
	{
		DataTable dt = new DataTable();
		try
		{
			SQLiteConnection cnn = new SQLiteConnection(dbConnection);
			cnn.Open();
			SQLiteCommand mycommand = new SQLiteCommand(cnn);
			mycommand.CommandText = sql;
			SQLiteDataReader reader = mycommand.ExecuteReader();
			dt.Load(reader);
			reader.Close();
			cnn.Close();
		}
		catch (Exception e)
		{
			throw new Exception(e.Message);
		}
		return dt;
	}
	
	/// <summary>
	///     Allows the programmer to interact with the database for purposes other than a query.
	/// </summary>
	/// <param name="sql">The SQL to be run.</param>
	/// <returns>An Integer containing the number of rows updated.</returns>
	public int ExecuteNonQuery(string sql)
	{
		SQLiteConnection cnn = new SQLiteConnection(dbConnection);
		cnn.Open();
		SQLiteCommand mycommand = new SQLiteCommand(cnn);
		mycommand.CommandText = sql;
		int rowsUpdated = mycommand.ExecuteNonQuery();
		cnn.Close();
		return rowsUpdated;
	}

	/// <summary>
	///     Allows the programmer to retrieve single items from the DB.
	/// </summary>
	/// <param name="sql">The query to run.</param>
	/// <returns>A string.</returns>
	public string ExecuteScalar(string sql)
	{
		SQLiteConnection cnn = new SQLiteConnection(dbConnection);
		cnn.Open();
		SQLiteCommand mycommand = new SQLiteCommand(cnn);
		mycommand.CommandText = sql;
		object value = mycommand.ExecuteScalar();
		cnn.Close();
		if (value != null)
		{
			return value.ToString();
		}
		return "";
	}

	/// <summary>
	///     Allows the programmer to easily update rows in the DB.
	/// </summary>
	/// <param name="tableName">The table to update.</param>
	/// <param name="data">A dictionary containing Column names and their new values.</param>
	/// <param name="where">The where clause for the update statement.</param>
	/// <returns>A boolean true or false to signify success or failure.</returns>
	public bool Update(String tableName, Dictionary<String, String> data, String where)
	{
		String vals = "";
		Boolean returnCode = true;
		if (data.Count >= 1)
		{
			foreach (KeyValuePair<String, String> val in data)
			{
				vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
			}
			vals = vals.Substring(0, vals.Length - 1);
		}
		try
		{
			this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));
		}
		catch
		{
			returnCode = false;
		}
		return returnCode;
	}

	/// <summary>
	///     Allows the programmer to easily delete rows from the DB.
	/// </summary>
	/// <param name="tableName">The table from which to delete.</param>
	/// <param name="where">The where clause for the delete.</param>
	/// <returns>A boolean true or false to signify success or failure.</returns>
	public bool Delete(String tableName, String where)
	{
		Boolean returnCode = true;
		try
		{
			this.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where));
		}
		catch (Exception fail)
		{
			MessageBox.Show(fail.Message);
			returnCode = false;
		}
		return returnCode;
	}

	/// <summary>
	///     Allows the programmer to easily insert into the DB
	/// </summary>
	/// <param name="tableName">The table into which we insert the data.</param>
	/// <param name="data">A dictionary containing the column names and data for the insert.</param>
	/// <returns>A boolean true or false to signify success or failure.</returns>
	public bool Insert(String tableName, Dictionary<String, String> data)
	{
		String columns = "";
		String values = "";
		Boolean returnCode = true;
		foreach (KeyValuePair<String, String> val in data)
		{
			columns += String.Format(" {0},", val.Key.ToString());
			values += String.Format(" '{0}',", val.Value);
		}
		columns = columns.Substring(0, columns.Length - 1);
		values = values.Substring(0, values.Length - 1);
		try
		{
			this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
		}
		catch(Exception fail)
		{
			MessageBox.Show(fail.Message);
			returnCode = false;
		}
		return returnCode;
	}

	/// <summary>
	///     Allows the programmer to easily delete all data from the DB.
	/// </summary>
	/// <returns>A boolean true or false to signify success or failure.</returns>
	public bool ClearDB()
	{
		DataTable tables;
		try
		{
			tables = this.GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;");
			foreach (DataRow table in tables.Rows)
			{
				this.ClearTable(table["NAME"].ToString());
			}
			return true;
		}
		catch
		{
			return false;
		}
	}

	/// <summary>
	///     Allows the user to easily clear all data from a specific table.
	/// </summary>
	/// <param name="table">The name of the table to clear.</param>
	/// <returns>A boolean true or false to signify success or failure.</returns>
	public bool ClearTable(String table)
	{
		try
		{
			
			this.ExecuteNonQuery(String.Format("delete from {0};", table));
			return true;
		}
		catch
		{
			return false;
		}
	}       
}



Usage:

Query:
	try
	{
		db = new SQLiteDatabase();
		DataTable recipe;
		String query = "select NAME \"Name\", DESCRIPTION \"Description\",";
		query += "PREP_TIME \"Prep Time\", COOKING_TIME \"Cooking Time\"";
		query += "from RECIPE;";
		recipe = db.GetDataTable(query);
		// The results can be directly applied to a DataGridView control
		recipeDataGrid.DataSource = recipe;
		/*
		// Or looped through for some other reason
		foreach (DataRow r in recipe.Rows)
		{
			MessageBox.Show(r["Name"].ToString());
			MessageBox.Show(r["Description"].ToString());
			MessageBox.Show(r["Prep Time"].ToString());
			MessageBox.Show(r["Cooking Time"].ToString());
		}
		
		*/
	}
	catch(Exception fail)
	{
		String error = "The following error has occurred:\n\n";
		error += fail.Message.ToString() + "\n\n";
		MessageBox.Show(error);
		this.Close();
	}
	


Insert:
	db = new SQLiteDatabase();
	Dictionary<String, String> data = new Dictionary<String, String>();
	data.Add("NAME", nameTextBox.Text);
	data.Add("DESCRIPTION", descriptionTextBox.Text);
	data.Add("PREP_TIME", prepTimeTextBox.Text);
	data.Add("COOKING_TIME", cookingTimeTextBox.Text);
	data.Add("COOKING_DIRECTIONS", "Placeholder");
	try
	{
			db.Insert("RECIPE", data);
	}
	catch(Exception crap)
	{
		MessageBox.Show(crap.Message);
	}
	


Update:
	db = new SQLiteDatabase();
	Dictionary<String, String> data = new Dictionary<String, String>();
	DataTable rows;
	data.Add("NAME", nameTextBox.Text);
	data.Add("DESCRIPTION", descriptionTextBox.Text);
	data.Add("PREP_TIME", prepTimeTextBox.Text);
	data.Add("COOKING_TIME", cookingTimeTextBox.Text);
	try
	{
		db.Update("RECIPE", data, String.Format("RECIPE.ID = {0}", this.RecipeID));
	}
	catch(Exception crap)
	{
		MessageBox.Show(crap.Message);
	}
	


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

This post has been edited by JackOfAllTrades: 13 September 2010 - 08:59 AM


Is This A Good Question/Topic? 8
  • +

Replies To: Using SQLite with C#

#2 Guest_Nick*


Reputation:

Posted 01 March 2010 - 09:44 AM

*
POPULAR

Exception name of crap needs to become an industry standard.
Was This Post Helpful? 5

#3 Guest_selom*


Reputation:

Posted 21 March 2010 - 06:49 AM

:helpsmilie: hey nice tutorial, how to use slqlite with vb.net?
Was This Post Helpful? -1

#4 Choscura  Icon User is offline

  • D.I.C Lover


Reputation: 461
  • View blog
  • Posts: 2,217
  • Joined: 18-October 08

Posted 25 March 2010 - 02:38 PM

I'm going to save this tutorial as a PDF. this is extremely useful for me.
Was This Post Helpful? 0
  • +
  • -

#5 Guest_Ncl8*


Reputation:

Posted 29 March 2010 - 09:43 AM

Excellent guide. Thou I can't seem to get the Update to work. I get no warnings or anything, it just doesn't update.

Also, you have "DataTable rows;" in the update section which is not used.
Was This Post Helpful? 0

#6 Guest_Eisen*


Reputation:

Posted 11 September 2010 - 06:23 AM

Very nice guide, Tanks.
In the Updat is a litel typing error.


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


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


MOD EDIT: Thanks, updated tutorial accordingly.

This post has been edited by JackOfAllTrades: 13 September 2010 - 08:59 AM

Was This Post Helpful? 1

#7 Guest_RandomHajile*


Reputation:

Posted 27 October 2010 - 07:41 AM

Exactly what I'd been looking for. Worked a charm!
Was This Post Helpful? 0

#8 Guest_Daniel*


Reputation:

Posted 30 November 2010 - 09:31 AM

Error 1 'WFADM16.Form1' does not contain a definition for 'RecipeID' and no extension method 'RecipeID' accepting a first argument of type 'WFADM16.Form1' has been found (a using directive or a reference to assembly is it missing?) C: \ Documents and Settings \ Daniel Morais \ my documents \ Visual Studio 2010 \ Projects \ WFADM16 \ WFADM16 \ Form1.cs 91 69 WFADM16

this is the error i am reciving
for this line in the update commande

db.Update("RECIPE", data, String.Format("RECIPE.ID = {0}", this.RecipeID));
Was This Post Helpful? 0

#9 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6021
  • View blog
  • Posts: 23,396
  • Joined: 23-August 08

Posted 30 November 2010 - 01:17 PM

this.RecipeID refers to a RecipeID property of the class in which it's being called. You do not appear to have a RecipeID property in the class.
Was This Post Helpful? 0
  • +
  • -

#10 Guest_Guest*


Reputation:

Posted 22 December 2010 - 09:50 AM

for some reason on VS2010 Ultimate when i add reference i do not have System.Data.SQLite any idea why?
Was This Post Helpful? 0

#11 Guest_Ahmed Shawky Zidan*


Reputation:

Posted 05 January 2011 - 07:06 AM

Thanks very much, this is really helpful for me :bigsmile: .
Was This Post Helpful? 0

#12 sonidevyang  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 27-February 11

Posted 27 February 2011 - 10:33 PM

hey very nice tutorial.

could you please let me know how could i Insert/Update Images in the SQlite database?

Thanks,
Dev

This post has been edited by sonidevyang: 27 February 2011 - 10:33 PM

Was This Post Helpful? 0
  • +
  • -

#13 rbsoft  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 25-June 12

Posted 25 June 2012 - 10:57 PM

You should change execute scalar return value to object and let developer cast the value to appropriate type. Thanks for million dollar code. Saved my time.
Was This Post Helpful? 0
  • +
  • -

#14 rbsoft  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 25-June 12

Posted 30 July 2012 - 02:22 AM

My implementation of this class with support for transaction.


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Globalization;
using System.Linq;
using System.Windows.Forms;

namespace Simple_Disk_Catalog
{
    public class SQLiteDatabase
    {
        String DBConnection;

        private readonly SQLiteTransaction _sqLiteTransaction;

        private readonly SQLiteConnection _sqLiteConnection;

        private readonly bool _transaction;

        /// <summary>
        ///     Default Constructor for SQLiteDatabase Class.
        /// </summary>
        /// <param name="transaction">Allow programmers to insert, update and delete values in one transaction</param>
        public SQLiteDatabase(bool transaction = false)
        {
            _transaction = transaction;
            DBConnection = "Data Source=recipes.s3db";
            if (transaction)
            {
                _sqLiteConnection = new SQLiteConnection(DBConnection);
                _sqLiteConnection.Open();
                _sqLiteTransaction = _sqLiteConnection.BeginTransaction();
            }
        }

        /// <summary>
        ///     Single Param Constructor for specifying the DB file.
        /// </summary>
        /// <param name="inputFile">The File containing the DB</param>
        public SQLiteDatabase(String inputFile)
        {
            DBConnection = String.Format("Data Source={0}", inputFile);
        }

        /// <summary>
        ///     Commit transaction to the database.
        /// </summary>
        public void CommitTransaction()
        {
            _sqLiteTransaction.Commit();
            _sqLiteTransaction.Dispose();
            _sqLiteConnection.Close();
            _sqLiteConnection.Dispose();
        }

        /// <summary>
        ///     Single Param Constructor for specifying advanced connection options.
        /// </summary>
        /// <param name="connectionOpts">A dictionary containing all desired options and their values</param>
        public SQLiteDatabase(Dictionary<String, String> connectionOpts)
        {
            String str = connectionOpts.Aggregate("", (current, row) => current + String.Format("{0}={1}; ", row.Key, row.Value));
            str = str.Trim().Substring(0, str.Length - 1);
            DBConnection = str;
        }

        /// <summary>
        ///     Allows the programmer to create new database file.
        /// </summary>
        /// <param name="filePath">Full path of a new database file.</param>
        /// <returns>true or false to represent success or failure.</returns>
        public static bool CreateDB(string filePath)
        {
            try
            {
                SQLiteConnection.CreateFile(filePath);
                return true;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, e.GetType().ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        /// <summary>
        ///     Allows the programmer to run a query against the Database.
        /// </summary>
        /// <param name="sql">The SQL to run</param>
        /// <param name="allowDBNullColumns">Allow null value for columns in this collection.</param>
        /// <returns>A DataTable containing the result set.</returns>
        public DataTable GetDataTable(string sql, IEnumerable<string> allowDBNullColumns = null)
        {
            var dt = new DataTable();
            if (allowDBNullColumns != null)
                foreach (var s in allowDBNullColumns)
                {
                    dt.Columns.Add(s);
                    dt.Columns[s].AllowDBNull = true;
                }
            try
            {
                var cnn = new SQLiteConnection(DBConnection);
                cnn.Open();
                var mycommand = new SQLiteCommand(cnn) {CommandText = sql};
                var reader = mycommand.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                cnn.Close();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            return dt;
        }

        public string RetrieveOriginal(string value)
        {
            return
                value.Replace("&amp;", "&").Replace("&lt;", "<").Replace("&gt;", "<").Replace("&quot;", "\"").Replace(
                    "&apos;", "'");
        }

        /// <summary>
        ///     Allows the programmer to interact with the database for purposes other than a query.
        /// </summary>
        /// <param name="sql">The SQL to be run.</param>
        /// <returns>An Integer containing the number of rows updated.</returns>
        public int ExecuteNonQuery(string sql)
        {
            if (!_transaction)
            {
                var cnn = new SQLiteConnection(DBConnection);
                cnn.Open();
                var mycommand = new SQLiteCommand(cnn) {CommandText = sql};
                var rowsUpdated = mycommand.ExecuteNonQuery();
                cnn.Close();
                return rowsUpdated;
            }
            else
            {
                var mycommand = new SQLiteCommand(_sqLiteConnection) { CommandText = sql };
                return mycommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Allows the programmer to retrieve single items from the DB.
        /// </summary>
        /// <param name="sql">The query to run.</param>
        /// <returns>A string.</returns>
        public string ExecuteScalar(string sql)
        {
            if (!_transaction)
            {
                var cnn = new SQLiteConnection(DBConnection);
                cnn.Open();
                var mycommand = new SQLiteCommand(cnn) {CommandText = sql};
                var value = mycommand.ExecuteScalar();
                cnn.Close();
                return value != null ? value.ToString() : "";
            }
            else
            {
                var sqLiteCommand = new SQLiteCommand(_sqLiteConnection) { CommandText = sql };
                var value = sqLiteCommand.ExecuteScalar();
                return value != null ? value.ToString() : "";
            }
        }

        /// <summary>
        ///     Allows the programmer to easily update rows in the DB.
        /// </summary>
        /// <param name="tableName">The table to update.</param>
        /// <param name="data">A dictionary containing Column names and their new values.</param>
        /// <param name="where">The where clause for the update statement.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Update(String tableName, Dictionary<String, String> data, String where)
        {
            String vals = "";
            Boolean returnCode = true;
            if (data.Count >= 1)
            {
                vals = data.Aggregate(vals, (current, val) => current + String.Format(" {0} = '{1}',", val.Key.ToString(CultureInfo.InvariantCulture), val.Value.ToString(CultureInfo.InvariantCulture)));
                vals = vals.Substring(0, vals.Length - 1);
            }
            try
            {
                ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));
            }
            catch
            {
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        ///     Allows the programmer to easily delete rows from the DB.
        /// </summary>
        /// <param name="tableName">The table from which to delete.</param>
        /// <param name="where">The where clause for the delete.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Delete(String tableName, String where)
        {
            Boolean returnCode = true;
            try
            {
                ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where));
            }
            catch (Exception fail)
            {
                MessageBox.Show(fail.Message, fail.GetType().ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        ///     Allows the programmer to easily insert into the DB
        /// </summary>
        /// <param name="tableName">The table into which we insert the data.</param>
        /// <param name="data">A dictionary containing the column names and data for the insert.</param>
        /// <returns>returns last inserted row id if it's value is zero than it means failure.</returns>
        public long Insert(String tableName, Dictionary<String, String> data)
        {
            String columns = "";
            String values = "";
            String value;
            foreach (KeyValuePair<String, String> val in data)
            {
                columns += String.Format(" {0},", val.Key.ToString(CultureInfo.InvariantCulture));
                values += String.Format(" '{0}',", val.Value);
            }
            columns = columns.Substring(0, columns.Length - 1);
            values = values.Substring(0, values.Length - 1);
            try
            {
                if (!_transaction)
                {
                    var cnn = new SQLiteConnection(DBConnection);
                    cnn.Open();
                    var sqLiteCommand = new SQLiteCommand(cnn)
                                        {
                                            CommandText =
                                                String.Format("insert into {0}({1}) values({2});", tableName, columns,
                                                              values)
                                        };
                    sqLiteCommand.ExecuteNonQuery();
                    sqLiteCommand = new SQLiteCommand(cnn) { CommandText = "SELECT last_insert_rowid()" };
                    value = sqLiteCommand.ExecuteScalar().ToString();
                }
                else
                {
                    ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
                    value = ExecuteScalar("SELECT last_insert_rowid()");
                }
            }
            catch (Exception fail)
            {
                MessageBox.Show(fail.Message, fail.GetType().ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0;
            }
            return long.Parse(value);
        }

        /// <summary>
        ///     Allows the programmer to easily delete all data from the DB.
        /// </summary>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool ClearDB()
        {
            try
            {
                var tables = GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;");
                foreach (DataRow table in tables.Rows)
                {
                    ClearTable(table["NAME"].ToString());
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        ///     Allows the user to easily clear all data from a specific table.
        /// </summary>
        /// <param name="table">The name of the table to clear.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool ClearTable(String table)
        {
            try
            {
                ExecuteNonQuery(String.Format("delete from {0};", table));
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        ///     Allows the user to easily reduce size of database.
        /// </summary>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool CompactDB()
        {
            try
            {
                ExecuteNonQuery("Vacuum;");
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}




If you want to use transaction than use following code.

            var sqLiteDatabase = new SQLiteDatabase(true);

            sqLiteDatabase.Delete("Disks", string.Format(" id = '{0}'", ID));

            sqLiteDatabase.Delete("Folders", string.Format(" DiskID = '{0}'", ID));

            sqLiteDatabase.Delete("Files", string.Format(" DiskID = '{0}'", ID));

            sqLiteDatabase.Delete("Loan", string.Format(" DiskID = '{0}'", ID));

            sqLiteDatabase.CommitTransaction();



if something happen before commit transaction call then changes won't reflect in database. For many insert, update and delete operations use this method. just set first argument of SQLiteDatabase to true and call commit transaction to end it.
Was This Post Helpful? 0
  • +
  • -

#15 rjl  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 30-August 12

Posted 30 August 2012 - 12:39 PM

Where can I get recipes.s3db? The mike duncan link seems to be dead.
Thanks
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2