Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » ADO.NET » Downloads
 
Add your own
alternative version
Go to top

Light ORM Library for .NET

, 8 Oct 2010
This article is about the Light Object-Relational Mapping library.
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;
		}
	}
}

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140926.1 | Last Updated 8 Oct 2010
Article Copyright 2007 by Sergey Busel
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid