--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Source/Data/Sql/SqlProvider/AccessSqlProvider.cs	Thu Mar 27 21:46:09 2014 +0400
@@ -0,0 +1,429 @@
+using System;
+using System.Collections.Generic;
+using System.Text;
+
+namespace BLToolkit.Data.Sql.SqlProvider
+{
+	using DataProvider;
+	using Reflection;
+
+	public class AccessSqlProvider : BasicSqlProvider
+	{
+		public override int CommandCount(SqlQuery sqlQuery)
+		{
+			return sqlQuery.IsInsert && sqlQuery.Insert.WithIdentity ? 2 : 1;
+		}
+
+		protected override void BuildCommand(int commandNumber, StringBuilder sb)
+		{
+			sb.AppendLine("SELECT @@IDENTITY");
+		}
+
+		//public override bool IsSkipSupported           { get { return SqlQuery.Select.TakeValue != null; } }
+		public override bool IsSkipSupported           { get { return false; } }
+		public override bool TakeAcceptsParameter      { get { return false; } }
+		public override bool IsCountSubQuerySupported  { get { return false; } }
+		public override bool IsNestedJoinSupported     { get { return false; } }
+		public override bool IsInsertOrUpdateSupported { get { return false; } }
+
+		public override bool ConvertCountSubQuery(SqlQuery subQuery)
+		{
+			return !subQuery.Where.IsEmpty;
+		}
+
+		#region Skip / Take Support
+
+		protected override string FirstFormat { get { return "TOP {0}"; } }
+
+		protected override void BuildSql(StringBuilder sb)
+		{
+			if (NeedSkip)
+			{
+				AlternativeBuildSql2(sb, base.BuildSql);
+				return;
+			}
+
+			if (SqlQuery.From.Tables.Count == 0 && SqlQuery.Select.Columns.Count == 1)
+			{
+				if (SqlQuery.Select.Columns[0].Expression is SqlFunction)
+				{
+					var func = (SqlFunction)SqlQuery.Select.Columns[0].Expression;
+
+					if (func.Name == "Iif" && func.Parameters.Length == 3 && func.Parameters[0] is SqlQuery.SearchCondition)
+					{
+						var sc = (SqlQuery.SearchCondition)func.Parameters[0];
+
+						if (sc.Conditions.Count == 1 && sc.Conditions[0].Predicate is SqlQuery.Predicate.FuncLike)
+						{
+							var p = (SqlQuery.Predicate.FuncLike)sc.Conditions[0].Predicate;
+
+							if (p.Function.Name == "EXISTS")
+							{
+								BuildAnyAsCount(sb);
+								return;
+							}
+						}
+					}
+				}
+				else if (SqlQuery.Select.Columns[0].Expression is SqlQuery.SearchCondition)
+				{
+					var sc = (SqlQuery.SearchCondition)SqlQuery.Select.Columns[0].Expression;
+
+					if (sc.Conditions.Count == 1 && sc.Conditions[0].Predicate is SqlQuery.Predicate.FuncLike)
+					{
+						var p = (SqlQuery.Predicate.FuncLike)sc.Conditions[0].Predicate;
+
+						if (p.Function.Name == "EXISTS")
+						{
+							BuildAnyAsCount(sb);
+							return;
+						}
+					}
+				}
+			}
+
+			base.BuildSql(sb);
+		}
+
+		SqlQuery.Column _selectColumn;
+
+		void BuildAnyAsCount(StringBuilder sb)
+		{
+			SqlQuery.SearchCondition cond;
+
+			if (SqlQuery.Select.Columns[0].Expression is SqlFunction)
+			{
+				var func  = (SqlFunction)SqlQuery.Select.Columns[0].Expression;
+				cond  = (SqlQuery.SearchCondition)func.Parameters[0];
+			}
+			else
+			{
+				cond  = (SqlQuery.SearchCondition)SqlQuery.Select.Columns[0].Expression;
+			}
+
+			var exist = ((SqlQuery.Predicate.FuncLike)cond.Conditions[0].Predicate).Function;
+			var query = (SqlQuery)exist.Parameters[0];
+
+			_selectColumn = new SqlQuery.Column(SqlQuery, new SqlExpression(cond.Conditions[0].IsNot ? "Count(*) = 0" : "Count(*) > 0"), SqlQuery.Select.Columns[0].Alias);
+
+			BuildSql(0, query, sb, 0, 0, false);
+
+			_selectColumn = null;
+		}
+
+		protected override IEnumerable<SqlQuery.Column> GetSelectedColumns()
+		{
+			if (_selectColumn != null)
+				return new[] { _selectColumn };
+
+			if (NeedSkip && !SqlQuery.OrderBy.IsEmpty)
+				return AlternativeGetSelectedColumns(base.GetSelectedColumns);
+
+			return base.GetSelectedColumns();
+		}
+
+		protected override void BuildSkipFirst(StringBuilder sb)
+		{
+			if (NeedSkip)
+			{
+				if (!NeedTake)
+				{
+					sb.AppendFormat(" TOP {0}", int.MaxValue);
+				}
+				else if (!SqlQuery.OrderBy.IsEmpty)
+				{
+					sb.Append(" TOP ");
+					BuildExpression(sb, Add<int>(SqlQuery.Select.SkipValue, SqlQuery.Select.TakeValue));
+				}
+			}
+			else
+				base.BuildSkipFirst(sb);
+		}
+
+		#endregion
+
+		protected override ISqlProvider CreateSqlProvider()
+		{
+			return new AccessSqlProvider();
+		}
+
+		protected override bool ParenthesizeJoin()
+		{
+			return true;
+		}
+
+		public override ISqlPredicate ConvertPredicate(ISqlPredicate predicate)
+		{
+			if (predicate is SqlQuery.Predicate.Like)
+			{
+				var l = (SqlQuery.Predicate.Like)predicate;
+
+				if (l.Escape != null)
+				{
+					if (l.Expr2 is SqlValue && l.Escape is SqlValue)
+					{
+						var text = ((SqlValue) l.Expr2).Value.ToString();
+						var val  = new SqlValue(ReescapeLikeText(text, (char)((SqlValue)l.Escape).Value));
+
+						return new SqlQuery.Predicate.Like(l.Expr1, l.IsNot, val, null);
+					}
+
+					if (l.Expr2 is SqlParameter)
+					{
+						var p = (SqlParameter)l.Expr2;
+						var v = "";
+						
+						if (p.ValueConverter != null)
+							v = p.ValueConverter(" ") as string;
+
+						p.SetLikeConverter(v.StartsWith("%") ? "%" : "", v.EndsWith("%") ? "%" : "");
+
+						return new SqlQuery.Predicate.Like(l.Expr1, l.IsNot, p, null);
+					}
+				}
+			}
+
+			return base.ConvertPredicate(predicate);
+		}
+
+		static string ReescapeLikeText(string text, char esc)
+		{
+			var sb = new StringBuilder(text.Length);
+
+			for (var i = 0; i < text.Length; i++)
+			{
+				var c = text[i];
+
+				if (c == esc)
+				{
+					sb.Append('[');
+					sb.Append(text[++i]);
+					sb.Append(']');
+				}
+				else if (c == '[')
+					sb.Append("[[]");
+				else
+					sb.Append(c);
+			}
+
+			return sb.ToString();
+		}
+
+		public override ISqlExpression ConvertExpression(ISqlExpression expr)
+		{
+			expr = base.ConvertExpression(expr);
+
+			if (expr is SqlBinaryExpression)
+			{
+				var be = (SqlBinaryExpression)expr;
+
+				switch (be.Operation[0])
+				{
+					case '%': return new SqlBinaryExpression(be.SystemType, be.Expr1, "MOD", be.Expr2, Precedence.Additive - 1);
+					case '&':
+					case '|':
+					case '^': throw new SqlException("Operator '{0}' is not supported by the {1}.", be.Operation, GetType().Name);
+				}
+			}
+			else if (expr is SqlFunction)
+			{
+				var func = (SqlFunction) expr;
+
+				switch (func.Name)
+				{
+					case "Coalesce":
+
+						if (func.Parameters.Length > 2)
+						{
+							var parms = new ISqlExpression[func.Parameters.Length - 1];
+
+							Array.Copy(func.Parameters, 1, parms, 0, parms.Length);
+							return new SqlFunction(func.SystemType, func.Name, func.Parameters[0], new SqlFunction(func.SystemType, func.Name, parms));
+						}
+
+						var sc = new SqlQuery.SearchCondition();
+
+						sc.Conditions.Add(new SqlQuery.Condition(false, new SqlQuery.Predicate.IsNull(func.Parameters[0], false)));
+
+						return new SqlFunction(func.SystemType, "Iif", sc, func.Parameters[1], func.Parameters[0]);
+
+					case "CASE"      : return ConvertCase(func.SystemType, func.Parameters, 0);
+					case "CharIndex" :
+						return func.Parameters.Length == 2?
+							new SqlFunction(func.SystemType, "InStr", new SqlValue(1),    func.Parameters[1], func.Parameters[0], new SqlValue(1)):
+							new SqlFunction(func.SystemType, "InStr", func.Parameters[2], func.Parameters[1], func.Parameters[0], new SqlValue(1));
+
+					case "Convert"   : 
+						{
+							switch (Type.GetTypeCode(TypeHelper.GetUnderlyingType(func.SystemType)))
+							{
+								case TypeCode.String   : return new SqlFunction(func.SystemType, "CStr",  func.Parameters[1]);
+								case TypeCode.DateTime :
+									if (IsDateDataType(func.Parameters[0], "Date"))
+										return new SqlFunction(func.SystemType, "DateValue", func.Parameters[1]);
+
+									if (IsTimeDataType(func.Parameters[0]))
+										return new SqlFunction(func.SystemType, "TimeValue", func.Parameters[1]);
+
+									return new SqlFunction(func.SystemType, "CDate", func.Parameters[1]);
+
+								default:
+									if (func.SystemType == typeof(DateTime))
+										goto case TypeCode.DateTime;
+									break;
+							}
+
+							return func.Parameters[1];
+						}
+
+						/*
+					case "Convert"   :
+						{
+							string name = null;
+
+							switch (((SqlDataType)func.Parameters[0]).DbType)
+							{
+								case SqlDbType.BigInt           : name = "CLng"; break;
+								case SqlDbType.TinyInt          : name = "CByte"; break;
+								case SqlDbType.Int              :
+								case SqlDbType.SmallInt         : name = "CInt"; break;
+								case SqlDbType.Bit              : name = "CBool"; break;
+								case SqlDbType.Char             :
+								case SqlDbType.Text             :
+								case SqlDbType.VarChar          :
+								case SqlDbType.NChar            :
+								case SqlDbType.NText            :
+								case SqlDbType.NVarChar         : name = "CStr"; break;
+								case SqlDbType.DateTime         :
+								case SqlDbType.Date             :
+								case SqlDbType.Time             :
+								case SqlDbType.DateTime2        :
+								case SqlDbType.SmallDateTime    :
+								case SqlDbType.DateTimeOffset   : name = "CDate"; break;
+								case SqlDbType.Decimal          : name = "CDec"; break;
+								case SqlDbType.Float            : name = "CDbl"; break;
+								case SqlDbType.Money            :
+								case SqlDbType.SmallMoney       : name = "CCur"; break;
+								case SqlDbType.Real             : name = "CSng"; break;
+								case SqlDbType.Image            :
+								case SqlDbType.Binary           :
+								case SqlDbType.UniqueIdentifier :
+								case SqlDbType.Timestamp        :
+								case SqlDbType.VarBinary        :
+								case SqlDbType.Variant          :
+								case SqlDbType.Xml              :
+								case SqlDbType.Udt              :
+								case SqlDbType.Structured       : name = "CVar"; break;
+							}
+
+							return new SqlFunction(name, func.Parameters[1]);
+						}
+						*/
+				}
+			}
+
+			return expr;
+		}
+
+		SqlFunction ConvertCase(Type systemType, ISqlExpression[] parameters, int start)
+		{
+			var len = parameters.Length - start;
+
+			if (len < 3)
+				throw new SqlException("CASE statement is not supported by the {0}.", GetType().Name);
+
+			if (len == 3)
+				return new SqlFunction(systemType, "Iif", parameters[start], parameters[start + 1], parameters[start + 2]);
+
+			return new SqlFunction(systemType, "Iif", parameters[start], parameters[start + 1], ConvertCase(systemType, parameters, start + 2));
+		}
+
+		public override void BuildValue(StringBuilder sb, object value)
+		{
+			if (value is bool)
+				sb.Append(value);
+			else if (value is Guid)
+				sb.Append("'").Append(((Guid)value).ToString("B")).Append("'");
+			else
+				base.BuildValue(sb, value);
+		}
+
+		public override SqlQuery Finalize(SqlQuery sqlQuery)
+		{
+			sqlQuery = base.Finalize(sqlQuery);
+
+			switch (sqlQuery.QueryType)
+			{
+				case QueryType.Delete : return GetAlternativeDelete(sqlQuery);
+				default               : return sqlQuery;
+			}
+		}
+
+		protected override void BuildUpdateClause(StringBuilder sb)
+		{
+			base.BuildFromClause(sb);
+			sb.Remove(0, 4).Insert(0, "UPDATE");
+			base.BuildUpdateSet(sb);
+		}
+
+		protected override void BuildFromClause(StringBuilder sb)
+		{
+			if (!SqlQuery.IsUpdate)
+				base.BuildFromClause(sb);
+		}
+
+		public override object Convert(object value, ConvertType convertType)
+		{
+			switch (convertType)
+			{
+				case ConvertType.NameToQueryParameter:
+				case ConvertType.NameToCommandParameter:
+				case ConvertType.NameToSprocParameter:
+					return "@" + value;
+
+				case ConvertType.NameToQueryField:
+				case ConvertType.NameToQueryFieldAlias:
+				case ConvertType.NameToQueryTableAlias:
+					{
+						var name = value.ToString();
+
+						if (name.Length > 0 && name[0] == '[')
+							return value;
+					}
+
+					return "[" + value + "]";
+
+				case ConvertType.NameToDatabase:
+				case ConvertType.NameToOwner:
+				case ConvertType.NameToQueryTable:
+					{
+						var name = value.ToString();
+
+						if (name.Length > 0 && name[0] == '[')
+							return value;
+
+						if (name.IndexOf('.') > 0)
+							value = string.Join("].[", name.Split('.'));
+					}
+
+					return "[" + value + "]";
+
+				case ConvertType.SprocParameterToName:
+					if (value != null)
+					{
+						var str = value.ToString();
+						return str.Length > 0 && str[0] == '@'? str.Substring(1): str;
+					}
+
+					break;
+			}
+
+			return value;
+		}
+
+		protected override void BuildDateTime(StringBuilder sb, object value)
+		{
+			sb.Append(string.Format("#{0:yyyy-MM-dd HH:mm:ss}#", value));
+		}
+	}
+}