Light ORM Library for .NET

using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using Light.Model;

namespace Light
{
	/// <summary>
	/// Default implementation of Dao object for Sql Server database.
	/// </summary>
	public class SqlServerDao : Dao
	{
		/// <summary>
		/// Creates a new instance.
		/// </summary>
		public SqlServerDao() : base()
		{}
		
		/// <summary>
		/// Creates a new instance.
		/// </summary>
		/// <param name="cn">database connection</param>
		public SqlServerDao(IDbConnection cn) : base(cn)
		{}
		
		/// <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 ");
			StringBuilder val = new StringBuilder(64).Append(" VALUES(");
			if(!string.IsNullOrEmpty(table.Schema))
				ins.Append("[").Append(table.Schema).Append("].");
			ins.Append("[").Append(table.Name).Append("](");
			bool append = false;
			int count = 1;
			foreach(AbstractColumn column in columns)
			{
				if(column.AutoIncrement || !column.Readable)
					continue;
				if(append)
				{
					ins.Append(",");
					val.Append(",");
				}
				else
					append = true;
				string pn = string.Concat("@", (count++).ToString());
				ins.Append("[").Append(column.Name).Append("]");
				val.Append(pn);
				Parameter p = MakeParameter(pn, column);
				command.Add(p);
			}
			ins.Append(")");
			val.Append(")");
			AbstractColumn ident = table.AutoIncrementColumn;
			if(ident != null)
			{
				val.Append(";SET @id=SCOPE_IDENTITY()");
				command.Add(MakeParameter("@id", ident).SetDirection(ParameterDirection.Output));
			}
			command.Text = ins.Append(val.ToString()).ToString();
			return command;
		}
		
		/// <summary>
		/// Always returns null becuase Sql Server does not use explicit sequences.
		/// </summary>
		/// <param name="table">table</param>
		/// <returns>null</returns>
		protected override object NextSequenceValue(Table table)
		{
			return null;
		}
		
		/// <summary>
		/// Returns the generated value of the identity column for given table
		/// 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>value of the identity column of the new record</returns>
		protected override object AutoIncrementedValue(Table table, IDbCommand command)
		{
			IDbDataParameter p = (IDbDataParameter) command.Parameters["@id"];
			if(p != null)
				return DBNull.Value.Equals(p.Value) ? null : p.Value;
			return null;
		}
		
		/// <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 ");
			if(!string.IsNullOrEmpty(table.Schema))
				buf.Append("[").Append(table.Schema).Append("].");
			buf.Append("[").Append(table.Name).Append("] SET ");
			bool append = false;
			int count = 1;
			foreach(AbstractColumn column in columns)
			{
				if(column.AutoIncrement || column.PrimaryKey || !column.Readable)
					continue;
				if(append)
					buf.Append(",");
				else
					append = true;
				string pn = string.Concat("@", (count++).ToString());
				buf.Append("[").Append(column.Name).Append("]=").Append(pn);
				command.Add(MakeParameter(pn, column));
			}
			buf.Append(" WHERE 1=1");
			columns = table.KeyColumns;
			foreach(AbstractColumn column in columns)
			{
				string pn = string.Concat("@", (count++).ToString());
				buf.Append(" AND ").Append("[").Append(column.Name).Append("]=").Append(pn);
				command.Add(MakeParameter(pn, 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();
			AbstractColumn[] columns = table.KeyColumns;
			StringBuilder buf = new StringBuilder(64).Append("DELETE FROM ");
			if(!string.IsNullOrEmpty(table.Schema))
				buf.Append("[").Append(table.Schema).Append("].");
			buf.Append("[").Append(table.Name).Append("] WHERE 1=1");
			int count = 1;
			foreach(AbstractColumn column in columns)
			{
				string pn = string.Concat("@", (count++).ToString());
				buf.Append(" AND [").Append(column.Name).Append("]=").Append(pn);
				command.Add(MakeParameter(pn, 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 ");
			if(!string.IsNullOrEmpty(table.Schema))
				buf.Append("[").Append(table.Schema).Append("].");
			buf.Append("[").Append(table.Name).Append("]");
			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();
			AbstractColumn[] columns = table.Columns;
			StringBuilder buf = new StringBuilder(128).Append("SELECT ");
			bool append = false;
			foreach(AbstractColumn column in columns)
			{
				if(append)
					buf.Append(",");
				else
					append = true;
				
				buf.Append("[").Append(column.Name).Append("]");
			}
			buf.Append(" FROM ");
			if(!string.IsNullOrEmpty(table.Schema))
				buf.Append("[").Append(table.Schema).Append("].");
			buf.Append("[").Append(table.Name).Append("]");
			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 + 5)).Append("[").Append(key.Name).Append("]=@1").ToString()
			).Add(
				MakeParameter("@1", key)
			);
		}
		
		/// <summary>
		/// Returns a command for calling the given procedure.
		/// </summary>
		/// <param name="procedure">procedure to call</param>
		/// <returns>command that calls given procedure</returns>
		protected override Command GetProcedureCommand(Procedure procedure)
		{
			Command cmd = new Command();
			StringBuilder buf = new StringBuilder(32);
			buf.Append("EXECUTE ").Append(procedure.Name);
			IList<Parameter> list = procedure.Parameters;
			if(list.Count > 0)
			{
				buf.Append(" ");
				bool comma = false;
				foreach(Parameter p in list)
				{
					if(comma) buf.Append(",");
					else comma = true;
					buf.Append(p.ParameterName);
					cmd.Add(p);
				}
			}
			cmd.Text = buf.ToString();
			return cmd;
		}
		
		/// <summary>
		/// Creates a parameter from given column using given parameter name.
		/// </summary>
		/// <param name="name">parameter name</param>
		/// <param name="column">column from which to create a parameter</param>
		/// <returns>parameter for given column</returns>
		private Parameter MakeParameter(string name, AbstractColumn column)
		{
			Parameter p = new Parameter();
			p.ParameterName = name;
			p.DBType = column.DBType;
			p.Size = column.Size;
			p.Precision = column.Precision;
			p.Scale = column.Scale;
			p.ColumnName = column.Name;
			return p;
		}
	}
}