A simple SQLite wrapper

Sometimes you don’t need to implement a gigantic ORM-system to save just two tables in a smaller application. In such a case it’s faster to just implement a small database manager and execute a couple of raw SQL lines:

app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<connectionStrings>
	<add 	name="SQLiteConnection"
			connectionString="Data Source=Data.db; Version=3;New=False;Compress=True"
			providerName="System.Data.SQLite" />
	</connectionStrings>
	<appSettings>
 	</appSettings>
</configuration>

DbManager.cs

/*
 * Created by SharpDevelop.
 * User: maffelu
 * Date: 2011-08-06
 * Time: 20:39
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */
using System;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
 
namespace Test
{
	/// <summary>
	/// Description of DbManager.
	/// </summary>
	public class DbManager
	{
		private string m_ConnectionString;
		private SQLiteCommand m_Command;
		private SQLiteConnection m_Connection;
 
		public DbManager()
		{
			m_ConnectionString = ConfigurationManager.ConnectionStrings["SQLiteConnection"].ConnectionString;
		}
 
		public int ExecuteNonQuery(string sql)
		{
			int rowsUpdated = -1;
			try
			{
				m_Connection = new SQLiteConnection(m_ConnectionString);
				m_Connection.Open();
				m_Command = new SQLiteCommand(sql, m_Connection);
				rowsUpdated = m_Command.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				throw new Exception(e.Message);
			}
			finally
			{
				if(m_Connection != null)
				{
					if(m_Connection.State == ConnectionState.Open)
					{
						m_Connection.Close();
					}
				}
			}
 
			return rowsUpdated;
		}
 
		public DataTable ExecuteReader(string sql)
		{
			DataTable table = new DataTable();
			try
			{
				m_Connection = new SQLiteConnection(m_ConnectionString);
				m_Connection.Open();
				m_Command = new SQLiteCommand(sql, m_Connection);
				SQLiteDataReader reader = m_Command.ExecuteReader();
				table.Load(reader);
				reader.Close();
			}
			catch(Exception e)
			{
				throw new Exception(e.Message);
			}
			finally
			{
				if(m_Connection != null)
				{
					if(m_Connection.State == ConnectionState.Open)
					{
						m_Connection.Close();
					}
				}
			}
 
			return table;
		}
 
		public string ExecuteScalar(string sql)
		{
			string value = string.Empty;
 
			try
			{
				m_Connection = new SQLiteConnection(m_ConnectionString);
				m_Connection.Open();
				m_Command = new SQLiteCommand(sql, m_Connection);
				object result = m_Command.ExecuteScalar();
 
				if(result != null)
				{
					value = result.ToString();
				}
			}
			catch(Exception e)
			{
				throw new Exception(e.Message);
			}
			finally
			{
				if(m_Connection != null)
				{
					if(m_Connection.State == ConnectionState.Open)
					{
						m_Connection.Close();
					}
				}
			}
 
			return value;
		}
	}
}

Och ett litet test:
program.cs

/*
 * Created by SharpDevelop.
 * User: maffelu
 * Date: 2011-08-06
 * Time: 18:44
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */
using System;
using System.Data;
using System.Reflection;
using System.Resources;
 
namespace Test
{
	class Program
	{
		public static void Main(string[] args)
		{
			int result = -2;
			DbManager manager = new DbManager();
			result = manager.ExecuteNonQuery(@"	CREATE TABLE IF NOT EXISTS Person 
												(
												Name varchar(255) NOT NULL,
												Age int(3) NOT NULL
												);");
			Console.WriteLine("Table created: {0}", result);
			result = -2;
 
			result = manager.ExecuteNonQuery("INSERT INTO Person (Name, Age) VALUES (\"Magnus\", 27)");
			Console.WriteLine("Person inserted: {0}", result);
			result = -2;
 
			DataTable table = manager.ExecuteReader("SELECT * FROM Person");
			Console.WriteLine("Datatable fetched, results: {0}", table.Rows.Count);
 
			Console.ReadKey(true);
		}
	}
}