Click here to Skip to main content
11,703,632 members (65,601 online)
Click here to Skip to main content
Articles » Database » Database » ADO.NET » Downloads
Add your own
alternative version

Light ORM Library for .NET

, 8 Oct 2010 CPOL 144.3K 2.6K 181
This article is about the Light Object-Relational Mapping library.
using System;
using System.Data;
using System.Text;
using Light.Model;

namespace Light
{
	/// <summary>
	/// Default implementation of Dao object for SQLite database.
	/// </summary>
	public class SQLiteDao : Dao
	{
		private IDbCommand lastRowidCmd;
		private bool disposed = false;
		
		/// <summary>
		/// Creates a new instance.
		/// </summary>
		public SQLiteDao() : base()
		{}
		
		/// <summary>
		/// Creates a new instance.
		/// </summary>
		/// <param name="cn">database connection</param>
		public SQLiteDao(IDbConnection cn) : base(cn)
		{}
		
		/// <summary>
		/// Dispose.
		/// </summary>
		/// <param name="disposing">disposing</param>
		protected override void Dispose(bool disposing)
		{
			if(disposing)
				Dispose();
		}
		
		/// <summary>
		/// Dispose.
		/// </summary>
		public override void Dispose()
		{
			if(disposed)
				return;
			disposed = true;
			if(lastRowidCmd != null)
			{
				lastRowidCmd.Dispose();
				lastRowidCmd = null;
			}
			base.Dispose();
			GC.SuppressFinalize(this);
		}
		
		/// <summary>
		/// Creates an insert command for given table.
		/// </summary>
		/// <param name="table">table to insert into</param>
		/// <returns>insert command</returns>
		protected override Command GetInsertCommand(Table table)
		{
			Command command = new Command();
			AbstractColumn[] columns = table.Columns;
			StringBuilder ins = new StringBuilder(128).Append("INSERT INTO ").Append(table.Name).Append("(");
			StringBuilder val = new StringBuilder(64).Append(" VALUES(");
			bool append = false;
			foreach(AbstractColumn column in columns)
			{
				if(column.AutoIncrement || !column.Readable)
					continue;
				if(append)
				{
					ins.Append(",");
					val.Append(",");
				}
				else
					append = true;
				ins.Append(column.Name);
				val.Append("?");
				command.Add(MakeParameter(column));
			}
			command.Text = ins.Append(")").Append(val.Append(")").ToString()).ToString();
			return command;
		}
		
		/// <summary>
		/// Always returns null.
		/// </summary>
		/// <param name="table">table</param>
		/// <returns>null</returns>
		protected override object NextSequenceValue(Table table)
		{
			return null;
		}
		
		/// <summary>
		/// Returns the last generated value after a record has been inserted.
		/// </summary>
		/// <param name="table">table into which a record was inserted</param>
		/// <param name="command">actual database command used to insert a new record</param>
		/// <returns>last inserted rowid</returns>
		protected override object AutoIncrementedValue(Table table, IDbCommand command)
		{
			if(lastRowidCmd == null)
			{
				lastRowidCmd = cn.CreateCommand();
				lastRowidCmd.CommandText = "SELECT LAST_INSERT_ROWID()";
				lastRowidCmd.CommandType = CommandType.Text;
			}
			lastRowidCmd.Connection = cn;
			object result = lastRowidCmd.ExecuteScalar();
			lastRowidCmd.Connection = null;
			return result;
		}
		
		/// <summary>
		/// Creates an update command for given table.
		/// </summary>
		/// <param name="table">table to update</param>
		/// <returns>update command</returns>
		protected override Command GetUpdateCommand(Table table)
		{
			Command command = new Command();
			AbstractColumn[] columns = table.Columns;
			StringBuilder buf = new StringBuilder(128).Append("UPDATE ").Append(table.Name).Append(" SET ");
			bool append = false;
			foreach(AbstractColumn column in columns)
			{
				if(column.AutoIncrement || column.PrimaryKey || !column.Readable)
					continue;
				if(append)
					buf.Append(",");
				else
					append = true;
				buf.Append(column.Name).Append("=?");
				command.Add(MakeParameter(column));
			}
			buf.Append(" WHERE 1=1");
			columns = table.KeyColumns;
			foreach(AbstractColumn column in columns)
			{
				buf.Append(" AND ").Append(column.Name).Append("=?");
				command.Add(MakeParameter(column));
			}
			command.Text = buf.ToString();
			return command;
		}
		
		/// <summary>
		/// Creates a delete command that would delete a single record.
		/// </summary>
		/// <param name="table">table from which to delete</param>
		/// <returns>delete command that would delete one record</returns>
		protected override Command GetDeleteCommand(Table table)
		{
			Command command = new Command();
			StringBuilder buf = new StringBuilder(64).Append("DELETE FROM ").Append(table.Name).Append(" WHERE 1=1");
			AbstractColumn[] columns = table.KeyColumns;
			foreach(AbstractColumn column in columns)
			{
				buf.Append(" AND ").Append(column.Name).Append("=?");
				command.Add(MakeParameter(column));
			}
			command.Text = buf.ToString();
			return command;
		}
		
		/// <summary>
		/// Creates a delete command for given table using the given query.
		/// If the query is null, command will delete all records.
		/// </summary>
		/// <param name="table">table to delete records from</param>
		/// <param name="query">query that identifies records to be deleted</param>
		/// <returns>delete command for given table</returns>
		protected override Command GetDeleteCommand(Table table, Query query)
		{
			Command command = new Command();
			StringBuilder buf = new StringBuilder(128).Append("DELETE FROM ").Append(table.Name);
			if(query != null)
			{
				if(!string.IsNullOrEmpty(query.Where))
				{
					buf.Append(" WHERE ").Append(query.Where);
					foreach(Parameter parameter in query.Parameters)
						command.Add(parameter);
				}
			}
			command.Text = buf.ToString();
			return command;
		}
		
		/// <summary>
		/// Creates a select command for given table using the given query.
		/// </summary>
		/// <param name="table">table to select from</param>
		/// <param name="query">query that identifies records to be selected</param>
		/// <returns>select command for given table</returns>
		protected override Command GetSelectCommand(Table table, Query query)
		{
			Command command = new Command();
			StringBuilder buf = new StringBuilder(128).Append("SELECT ");
			AbstractColumn[] columns = table.Columns;
			bool append = false;
			foreach(AbstractColumn column in columns)
			{
				if(append)
					buf.Append(",");
				else
					append = true;
				buf.Append(column.Name);
			}
			buf.Append(" FROM ").Append(table.Name);
			if(query != null)
			{
				if(!string.IsNullOrEmpty(query.Where))
				{
					buf.Append(" WHERE ").Append(query.Where);
					foreach(Parameter p in query.Parameters)
						command.Add(p);
				}
				if(!string.IsNullOrEmpty(query.Order))
					buf.Append(" ORDER BY ").Append(query.Order);
			}
			command.Text = buf.ToString();
			return command;
		}
		
		/// <summary>
		/// Returns a query that would select a single record from the given table.
		/// </summary>
		/// <param name="table">table to select from</param>
		/// <returns>single record query</returns>
		protected override Query GetFindQuery(Table table)
		{
			AbstractColumn key = table.KeyColumns[0];
			return new Query(
				(new StringBuilder(key.Name.Length + 2)).Append(key.Name).Append("=?").ToString()
			).Add(
				MakeParameter(key)
			);
		}
		
		/// <summary>
		/// Throws a not supported exception.
		/// </summary>
		protected override Command GetProcedureCommand(Procedure procedure)
		{
			throw new NotSupportedException("This feature is not supported by SQLite.");
		}
		
		/// <summary>
		/// Creates a parameter from given column.
		/// </summary>
		/// <param name="column">column from which to create a parameter</param>
		/// <returns>parameter created from given column</returns>
		private Parameter MakeParameter(AbstractColumn column)
		{
			return new Parameter().SetDBType(column.DBType);
		}
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sergey Busel
Software Developer (Senior)
United States United States
No Biography provided

You may also be interested in...

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150819.1 | Last Updated 8 Oct 2010
Article Copyright 2007 by Sergey Busel
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid