//	Spludlow Software
//	Copyright © Samuel P. Ludlow 2020 All Rights Reserved
//	Distributed under the terms of the GNU General Public License version 3
//	Distributed WITHOUT ANY WARRANTY; without implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE
//	https://www.spludlow.co.uk/LICENCE.TXT
//	The Spludlow logo is a registered trademark of Samuel P. Ludlow and may not be used without permission
//	v1.14

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using System.Data.Common;

namespace Spludlow.Data
{
	public class DALCommon
	{
		public static string[] Operations = new string[]
		{
				"SHRINK DATA",
				"SHRINK LOG",
				"IDENTITY INSERT",
		};

		public class CreateTableInfo
		{
			public string DatabaseType;

			public string QuoteOpen;
			public string QuoteClose;

			public string AllowNull;
			public string DontAllowNull;

			public string Identity;

			public string ColumnLine;
			public string PrimaryLine;
			public string UniqueLine;
			public string ForeignLine;
			public string IndexLine;
		}

		public static string MakeConnectionString(string[] names, string[] values, string quote)
		{
			StringBuilder connectionString = new StringBuilder();

			for (int index = 0; index < values.Length; ++index)
			{
				string value = values[index];
				if (value == null)
					continue;

				connectionString.Append(names[index]);
				connectionString.Append("=");
				connectionString.Append(quote);
				connectionString.Append(value);
				connectionString.Append(quote);
				connectionString.Append(";");
			}

			return connectionString.ToString();
		}

		public static string FixedParameterName(string columnName)
		{
			StringBuilder result = new StringBuilder();

			foreach (char ch in columnName)
			{
				if (Char.IsLetterOrDigit(ch) == true)
					result.Append(ch);
				else
					result.Append('_');
			}

			return result.ToString();
		}

		public static string MakeCreateTableCommandText(string tableName, DataTable schemaColumns, DataTable schemaKeys, CreateTableInfo info)		//	Make so only column lists are quoted, table name would be al;rady sepplyed quoted !!!!
		{
			List<string> lines = new List<string>();

			StringBuilder line = new StringBuilder();
			StringBuilder tempText = new StringBuilder();
			string tempValue;

			foreach (DataRow columnRow in schemaColumns.Rows)
			{
				line.Length = 0;
				line.Append(info.ColumnLine);

				string columnName = (string)columnRow["ColumnName"];
				string dataTypeId = (string)columnRow["DataTypeId"];
				bool allowDBNull = (bool)columnRow["AllowDBNull"];
				bool autoIncrement = (bool)columnRow["AutoIncrement"];

				//	If strange datatype, that will become Boolean and allow NULLs (null breaks for PKS)
				if (dataTypeId.Contains(":") == true && dataTypeId.StartsWith(info.DatabaseType) == false)
				{
					//allowDBNull = true;

					Spludlow.Log.Warning("Schemas MakeCreateTableCommandText; Strange datatype set whole column to Boolean=False: " + tableName + "." + columnName + ", " + dataTypeId);
				}
				
				string nativeDataType = Spludlow.Data.Schemas.CommonToNative(dataTypeId, info.DatabaseType);

				tempText.Length = 0;
				tempText.Append(info.QuoteOpen);
				tempText.Append(columnName);
				tempText.Append(info.QuoteClose);
				line.Replace("@ColumnName", tempText.ToString());

				tempText.Length = 0;
				tempText.Append(nativeDataType);

				//	Can hit maximum row size in MySQL

				if (nativeDataType.EndsWith(")") == false)		//	Has scale/max in DataTypes lookup table already
				{
					if (dataTypeId == "Decimal")
					{
						tempText.Append("(");
						tempText.Append((int)columnRow["Precision"]);
						tempText.Append(",");
						tempText.Append((int)columnRow["Scale"]);
						tempText.Append(")");
					}
					else
					{
						if (dataTypeId.EndsWith("Char") == true || dataTypeId.EndsWith("Binary") == true)
						{
							int maxLength = (int)columnRow["MaxLength"];

							if (maxLength <= 0)
								maxLength = (int)columnRow["Longest"];

							if (maxLength <= 0)
								throw new ApplicationException("DAL Make Create Table bad max length: " + tableName + "." + columnName);	//, new object[] { schemaColumns });

							//	4000	8000	limits deal with !!!!!!!!!!!!!!!!!!!!!!!!!!!! not here in implimentation !!!!!!!!!!!!!

							tempText.Append("(");
							tempText.Append(maxLength);
							tempText.Append(")");
						}
					}
				}

				line.Replace("@DataType", tempText.ToString());

				tempValue = "";
				if (autoIncrement == true)
					tempValue = info.Identity;
				line.Replace("@Identity", tempValue);

				tempValue = info.DontAllowNull;
				if (allowDBNull == true)
					tempValue = info.AllowNull;
				line.Replace("@Null", tempValue);

				lines.Add(line.ToString());
			}

			//	Normally only create PK when creating tables then UIF addedd afterwards, depends on which lines are passed in info. SQLite must create keys when table created !!!

			foreach (string keyType in new string[] { "P", "U", "I", "F" })
			{
				List<string> keyNames = new List<string>();

				foreach (DataRow schemaKeyRow in schemaKeys.Select("KeyType = '" + keyType + "'"))
				{
					string keyName = (string)schemaKeyRow["KeyName"];
					if (keyNames.Contains(keyName) == false)
						keyNames.Add(keyName);
				}

				if (keyNames.Count == 0)
					continue;

				if (keyType == "P" && keyNames.Count > 1)
					throw new ApplicationException("Create Table, schemaKeys; More than one primary key tableName: " + tableName);

				foreach (string keyName in keyNames)
				{
					DataRow[] keyColumns = schemaKeys.Select("KeyName = '" + keyName + "'");

					line.Length = 0;

					string lineTemp = null;

					switch (keyType)
					{
						case "P":
							lineTemp = info.PrimaryLine;
							break;

						case "U":
							lineTemp = info.UniqueLine;
							break;

						case "I":
							lineTemp = info.IndexLine;
							break;

						case "F":
							lineTemp = info.ForeignLine;
							break;

						default:
							throw new ApplicationException("Unknown key type: " + keyType);
					}

					if (lineTemp == null)
						continue;

					line.Append(lineTemp);

					tempText.Length = 0;
					tempText.Append(info.QuoteOpen);
					tempText.Append(keyName);
					tempText.Append(info.QuoteClose);
					line.Replace("@KeyName", tempText.ToString());

					string columnNames = ColumnNames(keyColumns, "ColumnName", info.QuoteOpen, info.QuoteClose);
					line.Replace("@Columns", columnNames);

					if (keyType == "F")
					{
						tempText.Length = 0;
						tempText.Append(info.QuoteOpen);
						tempText.Append(keyColumns[0]["ParentTable"]);
						tempText.Append(info.QuoteClose);
						line.Replace("@ParentTable", tempText.ToString());

						columnNames = ColumnNames(keyColumns, "ParentColumn", info.QuoteOpen, info.QuoteClose);
						line.Replace("@ParentColumns", columnNames);
					}

					lines.Add(line.ToString());
				}
			}

			tempText.Length = 0;

			tempText.Append("CREATE TABLE ");
			tempText.Append(info.QuoteOpen);
			tempText.Append(tableName);
			tempText.Append(info.QuoteClose);
			tempText.Append(" (");
			tempText.AppendLine();

			for (int index = 0; index < lines.Count; ++index)
			{
				tempText.Append(lines[index]);

				if (index < (lines.Count - 1))
					tempText.Append(",");

				tempText.AppendLine();
			}

			tempText.AppendLine(")");

			return tempText.ToString();
		}

		public class IndexCommandInfo
		{
			public string CommandTextPrimary;
			public string CommandTextUnique;
			public string CommandTextIndex;
			public string CommandTextForeign;

			public string CommandTextXmlPrimary;
			public string CommandTextXmlSecondary;

			public string QuoteOpen;
			public string QuoteClose;
		}


		public static string[] TableAddKeysCommandText(
			string[] tableNames, DataSet schema, string keyTypesPUIF, string commandTextPrimary, string commandTextUnique, string commandTextIndex, string commandTextForeign, string quoteOpen, string quoteClose)
		{
			keyTypesPUIF = keyTypesPUIF.ToUpper();

			List<string> commandTexts = new List<string>();

			foreach (string tableName in tableNames)
			{
				foreach (char keyType in keyTypesPUIF)
				{
					string commandTextTemplate = null;

					switch (keyType)
					{
						case 'P':
							commandTextTemplate = commandTextPrimary;
							break;

						case 'U':
							commandTextTemplate = commandTextUnique;
							break;

						case 'I':
							commandTextTemplate = commandTextIndex;
							break;

						case 'F':
							commandTextTemplate = commandTextForeign;
							break;
					}

					if (commandTextTemplate == null)
					{
						Spludlow.Log.Warning("DAL Adding Key Type not supported: tablename:" + tableName + ", type:" + keyType);
						continue;
					}

					foreach (string commandText in MakeKeysCommandText(tableName, schema, commandTextTemplate, quoteOpen, quoteClose, keyType.ToString()))
						commandTexts.Add(commandText);
				}
			}

			return commandTexts.ToArray();
		}

		public static string[] MakeKeysCommandText(string tableName, DataSet schema, string commandTextTemplate, string quoteOpen, string quoteClose, string keyTypePUIF)
		{
			DataTable schemaKeys = schema.Tables[tableName + "_Keys"];

			List<string> keyNames = new List<string>();

			foreach (DataRow schemaKeyRow in schemaKeys.Select("KeyType = '" + keyTypePUIF + "'"))
			{
				string keyName = (string)schemaKeyRow["KeyName"];
				if (keyNames.Contains(keyName) == false)
					keyNames.Add(keyName);
			}

			if (keyNames.Count == 0)
				return new string[0];

			List<string> commandTexts = new List<string>();

			foreach (string keyName in keyNames)
			{
				DataRow[] keyColumns = schemaKeys.Select("KeyName = '" + keyName + "'");

				StringBuilder commandText = new StringBuilder(commandTextTemplate);

				commandText.Replace("@TableName", QuoteTableNameInner(tableName, quoteOpen, quoteClose));
				commandText.Replace("@KeyName", keyName);
				commandText.Replace("@Columns", Spludlow.Data.DALCommon.ColumnNames(keyColumns, "ColumnName", quoteOpen, quoteClose));

				if (keyTypePUIF == "F")
				{
					string parentTable = (string)keyColumns[0]["ParentTable"];
					commandText.Replace("@ParentTable", QuoteTableNameInner(parentTable, quoteOpen, quoteClose));
					commandText.Replace("@ParentColumns", Spludlow.Data.DALCommon.ColumnNames(keyColumns, "ParentColumn", quoteOpen, quoteClose));
				}

				commandTexts.Add(commandText.ToString());
			}

			return commandTexts.ToArray();
		}

		public static DataSet SchemaConnection(System.Data.Common.DbConnection connection)
		{
			return SchemaConnection(connection, null, null);
		}
		public static DataSet SchemaConnection(System.Data.Common.DbConnection connection, string parameterName, string parameterValue)
		{
			List<string> names = new List<string>();

			foreach (DataRow row in connection.GetSchema().Rows)
			{
				string name = (string)row["CollectionName"];

				if (name == "JavaClasses")      //	Oracle don't work with this; ORA-00942: table or view does not exist.
					continue;

				names.Add(name);
			}
			
			DataTable restrictionsTable = null;
			
			DataSet dataSet = new DataSet();

			foreach (string name in names)
			{
				//	Sometimes hit problems with certian Collections, seen this in ODBC
				//	"Indexes"	The ODBC managed provider requires that the TABLE_NAME restriction be specified and non-null for the GetSchema indexes collection
				//	Some just break; ODBC Sage Line 50 - "DataTypes"	Index was outside the bounds of the array.

				try
				{
					DataTable table;

					if (parameterName != null)
					{
						if (restrictionsTable == null)
							restrictionsTable = connection.GetSchema("Restrictions");

						string restrictionColumnName = null;
						foreach (string columnName in new string[] { "RestrictionName", "ParameterName" })  //	not same in all DBs
						{
							if (restrictionsTable.Columns.Contains(columnName) == true)
							{
								restrictionColumnName = columnName;
								break;
							}
						}

						if (restrictionColumnName == null)
							throw new ApplicationException("DAL Command SchemaStandard; Can not fine RestrictionName column in Restrictions table");

						DataRow[] restrictionRows = restrictionsTable.Select("CollectionName='" + name + "'", "RestrictionNumber");
						string[] restrictionValues = new string[restrictionRows.Length];
						for (int index = 0; index < restrictionValues.Length; ++index)
						{
							if ((string)restrictionRows[index][restrictionColumnName] == parameterName)
								restrictionValues[index] = parameterValue;
						}
						table = connection.GetSchema(name, restrictionValues);
					}
					else
					{
						table = connection.GetSchema(name);
					}

					table.TableName = name;

					if (table.DataSet != null)
						table.DataSet.Tables.Remove(table);

					dataSet.Tables.Add(table);
				}
				catch (Exception ee)
				{
					Spludlow.Log.Warning("SchemaStandard; connection.GetSchema() for CollectionName: " + name, ee);
				}
			}

			return dataSet;
		}
		
		public static DataSet SchemaReader(DbConnection connection, DbTransaction transaction, string[] tableNames, string openQuote, string closeQuote)
		{
			DataSet dataSet = new DataSet();

			foreach (string tableName in tableNames)
			{
				using (DbCommand command = connection.CreateCommand())
				{
					command.Transaction = transaction;
					command.CommandText = "SELECT * FROM " + QuoteTableName(tableName, openQuote, closeQuote);	// openQuote + tableName + closeQuote;

					using (DbDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
					{
						DataTable table = reader.GetSchemaTable();
						table.TableName = tableName;
						dataSet.Tables.Add(table);
					}
				}
			}

			return dataSet;
		}

		public static string QuoteCommandTextNative(string commonCommandText, string nativeQuoteOpen, string nativeQuoteClose)
		{
			bool open = commonCommandText.Contains("[");
			bool close = commonCommandText.Contains("]");

			if (open == false && close == false)
				return commonCommandText;

			StringBuilder result = new StringBuilder(commonCommandText);

			if (open == true)
				result.Replace("[", nativeQuoteOpen);

			if (close == true)
				result.Replace("]", nativeQuoteClose);

			return result.ToString();
		}

		public static string QuoteTableName(string tableName, string openQuote, string closeQuote)
		{
			string[] schemaTableName = SplitSchemaTableName(tableName);

			StringBuilder text = new StringBuilder();

			if (schemaTableName[0] == null)
			{
				text.Append(openQuote);
				text.Append(schemaTableName[1]);
				text.Append(closeQuote);
				return text.ToString();
			}

			text.Append(openQuote);
			text.Append(schemaTableName[0]);
			text.Append(closeQuote);
			text.Append(".");
			text.Append(openQuote);
			text.Append(schemaTableName[1]);
			text.Append(closeQuote);

			return text.ToString();
		}

		private static string QuoteTableNameInner(string tableName, string openQuote, string closeQuote)
		{
			string result = QuoteTableName(tableName, openQuote, closeQuote);
			return result.Substring(1, result.Length - 2);
		}

		public static string[] SplitSchemaTableName(string fullTableName)
		{
			string[] parts = fullTableName.Split(new char[] { '.' });

			if (parts.Length == 1)
				return new string[] { null, fullTableName };

			if (parts.Length == 2)
				return parts;

			throw new ApplicationException("SplitSchemaTableName; Did not find 1 or 2 parts in full table name: " + fullTableName);
		}

		public static string ColumnNames(DataRow[] keyColumns, string nameColumn, string quoteOpen, string quoteClose)
		{
			StringBuilder tempText = new StringBuilder();

			foreach (DataRow keyColumn in keyColumns)
			{
				string columnName = (string)keyColumn[nameColumn];

				if (tempText.Length > 0)
					tempText.Append(", ");
				tempText.Append(quoteOpen);
				tempText.Append(columnName);
				tempText.Append(quoteClose);

				if ((bool)keyColumn["Descending"] == true)
					tempText.Append(" DESC");
			}

			return tempText.ToString();
		}

		public static string[] ExtractParameterNames(string commandText)		//	Will not include more than one @ sign variables			needs to work with [] and `` handle with spaces !!!!!!!!!!!!!
		{
			List<string> parameters = new List<string>();

			StringBuilder current = new StringBuilder();
			bool inside = false;

			for (int index = 0; index < commandText.Length; ++index)
			{
				char ch = commandText[index];

				if (ch == '@' && (index == 0 || commandText[index - 1] != '@'))
				{
					inside = true;
					continue;
				}

				if (inside == true)
				{
					if (Char.IsLetter(ch) == true || Char.IsDigit(ch) == true || ch == '_')
					{
						current.Append(ch);
						continue;
					}

					if (current.Length > 0)
					{
						parameters.Add(current.ToString());
						current.Length = 0;
					}
					inside = false;
				}
			}

			if (current.Length > 0)
				parameters.Add(current.ToString());

			return parameters.ToArray();
		}

		public static object[] ObjectsFromRow(DataRow row, DAL.CommandInfo commandInfo) //	pass column names instead DOING
		{
			object[] objectRow = new object[commandInfo.ParameterNames.Length];

			for (int index = 0; index < commandInfo.ParameterNames.Length; ++index)
			{
				string paramterName = commandInfo.ParameterNames[index];

				string columnName = commandInfo.ColumnNames[index];
				if (columnName == null)
					columnName = paramterName.Substring(1);

				objectRow[index] = row[columnName];
			}

			return objectRow;
		}

		public delegate void SetDataTypeDelegate(DAL.CommandInfo info, DataRow columnRow, int index);

		public static DAL.CommandInfo MakeCommandInfo(string commandText, string[] parameterNames, DataSet schema, SetDataTypeDelegate setDataType)
		{
			DAL.CommandInfo info = new DAL.CommandInfo();

			info.CommandText = commandText;

			if (parameterNames == null)
				parameterNames = new string[0];

			info.ParameterNames = new string[parameterNames.Length];
			info.ParameterTypes = new int[parameterNames.Length];
			info.ParameterMaxLengths = new int[parameterNames.Length];

			info.ColumnNames = new string[parameterNames.Length];

			for (int index = 0; index < parameterNames.Length; ++index)
			{
				string name = parameterNames[index];
				string typeInfo = null;

				int seperateIndex = name.IndexOf(":");
				if (seperateIndex != -1)
				{
					typeInfo = name.Substring(seperateIndex + 1);
					name = name.Substring(0, seperateIndex);
				}

				info.ParameterNames[index] = name;
				info.ParameterTypes[index] = -1;
				info.ParameterMaxLengths[index] = -1;

				//	@ParamName
				//	@ParamAndColumn:TableName
				//	@ParamName:TableName.ColumnName
				
				if (typeInfo != null)
				{
					string tableName;
					string columnName;

					seperateIndex = typeInfo.IndexOf(".");
					if (seperateIndex == -1)
					{
						tableName = typeInfo;
						columnName = name.Substring(1);
					}
					else
					{
						tableName = typeInfo.Substring(0, seperateIndex);
						columnName = typeInfo.Substring(seperateIndex + 1);
					}

					DataRow columnRow = Spludlow.Data.Schemas.GetColumn(schema, tableName, columnName);
					setDataType(info, columnRow, index);

					info.ColumnNames[index] = columnName;
				}
			}

			return info;
		}

		public static string CommandTextPairs(string[] columns, string seperator)
		{
			StringBuilder text = new StringBuilder();
			foreach (string columnName in columns)
			{
				if (text.Length > 0)
					text.Append(seperator);

				text.Append(columnName);
				text.Append(" = @");
				text.Append(columnName);
			}
			return text.ToString();
		}

		public static long InsertOrUpdate(DataRow row, string tableName, IDAL dal)
		{
			DataSet schema = dal.Schema();

			DataTable columnsTable = schema.Tables[tableName + "_Columns"];

			List<string> primaryKeys = new List<string>();
			List<string> primaryKeyParams = new List<string>();

			foreach (DataRow columnRow in columnsTable.Rows)
			{
				if ((bool)columnRow["PrimaryKey"] == false)
					continue;

				string keyName = (string)columnRow["ColumnName"];

				primaryKeys.Add(keyName);
				primaryKeyParams.Add("@" + keyName);
			}

			if (primaryKeys.Count == 0)
				throw new ApplicationException("DAL SQL; InsertOrUpdate, table has no primary key: " + tableName);

			StringBuilder commandText = new StringBuilder("SELECT @FirstKey FROM @TableName WHERE (@Where)");

			commandText.Replace("@FirstKey", primaryKeys[0]);
			commandText.Replace("@TableName", tableName);
			commandText.Replace("@Where", DALCommon.CommandTextPairs(primaryKeys.ToArray(), " AND "));

			object[] keyData = new object[primaryKeys.Count];
			for (int index = 0; index < primaryKeys.Count; ++index)
				keyData[index] = row[primaryKeys[index]];

			object result = dal.ExecuteScalar(commandText.ToString(), primaryKeyParams.ToArray(), keyData);

			if (result == null)
				return dal.Insert(tableName, row);

			dal.Update(tableName, row);

			return 0;
		}

		public static void DatabaseCreate(Spludlow.Data.IDAL database, string databaseName, DataSet schema)
		{
			database.DatabaseCreate(databaseName);

			database.ChangeDatabase(databaseName);

			string[] tableNames = Spludlow.Data.Schemas.TableNames(schema);

			foreach (string tableName in tableNames)
				database.TableCreate(tableName, schema);

			database.TableAddKeys(schema, "UIXTF");

			database.SchemaRefresh();
		}

		public static DAL.CommandInfo MakeUpdater(IDAL dal, DataSet schema, string tableName, string[] updateColumns)
		{
			DataTable columnsTable = schema.Tables[tableName + "_Columns"];

			if (columnsTable == null)
				throw new ApplicationException("DAL MakeUpdater Table not found in schema: " + tableName);

			List<string> primaryKeyColumns = new List<string>();
			List<string> nonPrimaryKeyColumns = new List<string>();

			foreach (DataRow columnRow in columnsTable.Rows)
			{
				string columnName = (string)columnRow["ColumnName"];

				if ((bool)columnRow["PrimaryKey"] == true)
					primaryKeyColumns.Add(columnName);
				else
					nonPrimaryKeyColumns.Add(columnName);
			}

			if (updateColumns.Length == 0)
				updateColumns = nonPrimaryKeyColumns.ToArray();

			StringBuilder commandText = new StringBuilder("UPDATE @TableName SET @Set WHERE (@Where)"); //	Quotes ??? like insert

			string set = DALCommon.CommandTextPairs(updateColumns, ", ");
			string where = DALCommon.CommandTextPairs(primaryKeyColumns.ToArray(), " AND ");		//	Replace bad chars in varible(@) names ?????

			commandText.Replace("@TableName", tableName);
			commandText.Replace("@Set", set);
			commandText.Replace("@Where", where);

			List<string> allColumns = new List<string>();
			allColumns.AddRange(primaryKeyColumns);
			foreach (string updateColumn in updateColumns)
			{
				if (allColumns.Contains(updateColumn) == true)
					throw new ApplicationException("DAL Make Updater; Update column is part of the primary Key: " + updateColumn);
				allColumns.Add(updateColumn);
			}

			List<string> parameterNames = new List<string>();
			foreach (string columnName in allColumns)
				parameterNames.Add("@" + columnName + ":" + tableName);

			return dal.MakeCommandInfo(commandText.ToString(), parameterNames.ToArray());
		}

		public static DAL.CommandInfo MakeInserter(IDAL dal, string tableName, string selectLastId)
		{
			string openQuote = "[";
			string closeQuote = "]";

			DataSet schema = dal.Schema();

			DataTable columnsTable = schema.Tables[tableName + "_Columns"];

			List<string> columnNames = new List<string>();

			bool identity = false;

			foreach (DataRow columnRow in columnsTable.Rows)
			{
				if ((bool)columnRow["AutoIncrement"] == true)
					identity = true;
				else
					columnNames.Add((string)columnRow["ColumnName"]);
			}

			List<string> parameterNames = new List<string>();

			StringBuilder columnsText = new StringBuilder();
			StringBuilder valuesText = new StringBuilder();

			foreach (string columnName in columnNames)
			{
				if (columnsText.Length != 0)
					columnsText.Append(", ");
				columnsText.Append(openQuote);
				columnsText.Append(columnName);
				columnsText.Append(closeQuote);

				if (valuesText.Length != 0)
					valuesText.Append(", ");
				valuesText.Append("@");

				string parameterName = Spludlow.Data.DALCommon.FixedParameterName(columnName);

				valuesText.Append(parameterName);

				parameterNames.Add("@" + parameterName + ":" + tableName + "." + columnName);
			}

			StringBuilder commandText = new StringBuilder("INSERT INTO " + openQuote + "@TableName" + closeQuote + " (@ColumnNames) VALUES (@ValueNames)");

			if (identity == true)
				commandText.Append(selectLastId + ";");
			
			commandText.Replace("@TableName", tableName);
			commandText.Replace("@ColumnNames", columnsText.ToString());
			commandText.Replace("@ValueNames", valuesText.ToString());

			DAL.CommandInfo commandInfo = dal.MakeCommandInfo(commandText.ToString(), parameterNames.ToArray());
			commandInfo.Identity = identity;

			return commandInfo;
		}
	}
}