//	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.IO;
using System.Data;
using System.Data.Common;

using System.Data.SqlClient;

namespace Spludlow.Data
{
	/// <summary>
	/// MS SQL Server DAL Implimentation - Fully implimented
	/// </summary>
	public class DALSql : Spludlow.Data.IDAL
	{
		private SqlConnection Connection;
		private SqlTransaction Transaction = null;

		private string ChangedDatabase = null;
		private string CurrentDatabaseCache = null;

		private string CacheKeyPrefix;

		private Dictionary<string, string> ConvertDataTypes;

		private string QuoteOpen = "[";
		private string QuoteClose = "]";

		private int CommandTimeoutSeconds = -1;

		private List<string> SystemDatabaseNames = new List<string>(new string[]
		{
			"master",
			"tempdb",
			"model",
			"msdb",
		});

		public DALSql()
		{
		}
		public DALSql(string connectionString)
		{
			this.Initialize(connectionString);
		}
		public void Initialize(string connectionString)
		{
			if (connectionString == null || connectionString.Length == 0)   //	For use with utility methods
				return;

			if (connectionString.StartsWith("@") == true)
				connectionString = Spludlow.Config.ConnectionString(connectionString.Substring(1));

			//	Short hand	HOST@DATABSE or HOST (databse will be 'master')
			if (connectionString.Contains(";") == false)
			{
				string host = null;
				string database = null;

				int index = connectionString.IndexOf("@");
				if (index == -1)
				{
					host = connectionString;
				}
				else
				{
					host = connectionString.Substring(0, index).Trim();
					database = connectionString.Substring(index + 1).Trim();
				}

				connectionString = MakeConnectionString(host, database);
			}
			this.Connection = new SqlConnection(connectionString);

			this.CacheKeyPrefix = "DALSql-Schemas-" + this.DataSource() + "-";

			this.ConvertDataTypes = new Dictionary<string, string>();
			string[] convertLookups = new string[]
			{
				"image			varbinary(max)",
				"ntext			nvarchar(max)",
				"text			varchar(max)",
				"numeric		decimal",
				"smallmoney		money",
				"smalldatetime	datetime",
				"timestamp		rowversion",		//	But still using timestamp in type enum !!!
			};
			foreach (string convertLookup in convertLookups)
			{
				string[] words = Spludlow.Text.Split(convertLookup, '\t', true, false);
				this.ConvertDataTypes.Add(words[0], words[1]);
			}

			this.ChangeDatabase(null);
		}

		public static string MakeConnectionString(string host, string database)
		{
			return MakeConnectionString(host, database, null, null);
		}
		public static string MakeConnectionString(string host, string database, string userName, string password)
		{
			string[] names = new string[] { "Data Source", "Initial Catalog", "User ID", "Password" };
			string[] values = new string[] { host, database, userName, password };

			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(value);
				connectionString.Append("';");
			}

			if (userName == null)
				connectionString.Append("Integrated Security=True;");

			return connectionString.ToString();
		}

		private void Open()
		{
			this.Connection.Open();

			if (this.ChangedDatabase != null)
			{
				try
				{
					string database = this.ChangedDatabase;
					if (database == "")
						database = "master";

					this.Connection.ChangeDatabase(database);
				}
				catch
				{
					this.Connection.Close();
					throw;
				}
			}
		}
		private void Close()
		{
			this.Connection.Close();
		}

		public string DataSource()
		{
			return this.Connection.DataSource;
		}

		public void CommandTimeout(int minutes)
		{
			if (minutes < 0)
				this.CommandTimeoutSeconds = -1;
			else
				this.CommandTimeoutSeconds = minutes * 60;
		}

		//
		//	Current
		//

		public string CurrentDatabase()
		{
			return this.CurrentDatabaseCache;
		}
		public void ChangeDatabase(string databaseName)
		{
			this.ChangedDatabase = databaseName;

			databaseName = (string)this.ExecuteScalar("SELECT DB_NAME();");
			if (databaseName == "master")
				databaseName = null;

			this.CurrentDatabaseCache = databaseName;
		}

		//
		//	Transactions
		//

		public void Begin()
		{
			if (this.Transaction != null)
				throw new ApplicationException("DALSql: Begin Transaction already present.");

			this.Open();

			try
			{
				this.Transaction = this.Connection.BeginTransaction();
			}
			catch
			{
				this.Close();
				throw;
			}
		}

		public void Commit()
		{
			if (this.Transaction == null)
				throw new ApplicationException("DALSql: Commit Transaction not present.");

			try
			{
				this.Transaction.Commit();
				this.Transaction = null;
			}
			finally
			{
				this.Close();
			}
		}

		public void Rollback()
		{
			if (this.Transaction == null)
				throw new ApplicationException("DALSql: Rollback Transaction not present.");

			try
			{
				this.Transaction.Rollback();
				this.Transaction = null;
			}
			finally
			{
				this.Close();
			}
		}

		//
		//	Schema
		//

		public void SchemaRefresh()
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DALSql; To Refresh the schema you must have a current database");

			string key = this.CacheKeyPrefix + databaseName;

			Spludlow.Caching.Remove(key);
		}

		public DataSet Schema()
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DALSql; To get the schema you must have a current database");

			string key = this.CacheKeyPrefix + databaseName;

			DataSet schema = (DataSet)Spludlow.Caching.Get(key);

			if (schema != null)
				return schema;

			schema = new DataSet();

			DataSet schemaReader = this.SchemaReader();
			DataSet schemaNative = this.SchemaNative();

			foreach (string fullTableName in this.TableList())
			{
				string[] schemaTableName = DALCommon.SplitSchemaTableName(fullTableName);
				string schemaName = schemaTableName[0];
				if (schemaName == null)
					schemaName = "dbo";
				string tableName = schemaTableName[1];

				DataRow nativeSchemaRow = schemaNative.Tables["schemas"].Rows.Find(schemaName);
				if (nativeSchemaRow == null)
					throw new ApplicationException("DALSql Schema; Can not find Native schema row: " + schemaName);
				int schemaId = (int)nativeSchemaRow["schema_id"];

				DataRow nativeTableRow = schemaNative.Tables["tables"].Rows.Find(new object[] { tableName, schemaId });
				if (nativeTableRow == null)
					throw new ApplicationException("DALSql Schema; Can not find Native Table row: " + fullTableName);

				this.SchemaColumns(schema, fullTableName, schemaReader, schemaNative, nativeTableRow);
				this.SchemaKeys(schema, fullTableName, schemaReader, schemaNative, nativeTableRow);
			}

			Spludlow.Caching.Set(key, schema);

			return schema;
		}

		public DataSet Schema(string tableName)
		{
			return this.Schema(new string[] { tableName });
		}

		public DataSet Schema(string[] tableNames)
		{
			DataSet fullSchema = this.Schema();

			DataSet partSchema = new DataSet();

			foreach (string tableName in tableNames)
			{
				foreach (string suffix in new string[] { "_Columns", "_Keys" })
					partSchema.Tables.Add(fullSchema.Tables[tableName + suffix].Copy());
			}

			return partSchema;
		}

		private void SchemaColumns(DataSet schema, string fullTableName, DataSet schemaReader, DataSet schemaNative, DataRow nativeTableRow)
		{
			DataTable schemaTable = Spludlow.Data.Schemas.NewSchemaColumnsTable(fullTableName);

			int tableObjectId = (int)nativeTableRow["object_id"];

			foreach (DataRow readerRow in schemaReader.Tables[fullTableName].Rows)
			{
				string columnName = (string)readerRow["ColumnName"];

				DataRow nativeRow = schemaNative.Tables["columns"].Rows.Find(new object[] { tableObjectId, columnName });

				if (nativeRow == null)
					throw new ApplicationException("DALSql Schema; Can not find Native Schema row for column: " + fullTableName + "." + columnName);

				string typeName = (string)readerRow["DataTypeName"];

				if (this.ConvertDataTypes.ContainsKey(typeName) == true)
					typeName = this.ConvertDataTypes[typeName];

				int columnSize = (int)readerRow["ColumnSize"];

				if (columnSize == 0x7FFFFFFF && (typeName == "varbinary" || typeName == "nvarchar" || typeName == "varchar"))
				{
					typeName += "(max)";
				}

				int index = typeName.LastIndexOf(".");
				if (index != -1)
					typeName = typeName.Substring(index + 1);

				DataRow schemaRow = schemaTable.NewRow();

				schemaRow["ColumnName"] = columnName;
				schemaRow["Ordinal"] = (int)readerRow["ColumnOrdinal"];
				schemaRow["DataTypeId"] = Spludlow.Data.Schemas.NativeToCommon(typeName, "SQL");
				schemaRow["MaxLength"] = columnSize;
				schemaRow["Precision"] = (int)((Int16)readerRow["NumericPrecision"]);
				schemaRow["Scale"] = (int)((Int16)readerRow["NumericScale"]);
				schemaRow["PrimaryKey"] = (bool)readerRow["IsKey"];
				schemaRow["AllowDBNull"] = (bool)readerRow["AllowDBNull"];
				schemaRow["AutoIncrement"] = (bool)readerRow["IsAutoIncrement"];
				schemaRow["Longest"] = 0;

				schemaTable.Rows.Add(schemaRow);
			}

			Spludlow.Data.Schemas.TidySchemaTable(schemaTable);

			schema.Tables.Add(schemaTable);
		}

		private void SchemaKeys(DataSet schema, string fullTableName, DataSet schemaReader, DataSet schemaNative, DataRow nativeTableRow)
		{
			DataTable keysTable = Spludlow.Data.Schemas.NewSchemaKeysTable(fullTableName);

			DataRow[] rows;

			int object_id = (int)nativeTableRow["object_id"];

			foreach (DataRow indexRow in schemaNative.Tables["indexes"].Select("object_id = " + object_id))
			{
				string indexTypeDesc = (string)indexRow["type_desc"];

				if (indexTypeDesc == "HEAP")
					continue;

				string keyType;

				if (indexTypeDesc == "XML")
				{
					keyType = "X";
				}
				else
				{
					keyType = "I";

					if ((bool)indexRow["is_primary_key"] == true)
					{
						keyType = "P";
					}
					else
					{
						if ((bool)indexRow["is_unique"] == true || (bool)indexRow["is_unique_constraint"] == true)
							keyType = "U";
					}
				}
				
				int index_id = (int)indexRow["index_id"];

				string indexName = (string)indexRow["name"];

				foreach (DataRow indexColumnRow in schemaNative.Tables["index_columns"].Select("object_id = " + object_id + " AND index_id = " + index_id + ""))
				{
					int column_id = (int)indexColumnRow["column_id"];

					rows = schemaNative.Tables["columns"].Select("object_id = " + object_id + " AND column_id = " + column_id);
					if (rows.Length != 1)
						throw new ApplicationException("DAL SchemaKeys; columns row not found");
					string columnName = (string)rows[0]["name"];

					int key_ordinal = (int)((byte)indexColumnRow["key_ordinal"]);
					bool is_descending_key = (bool)indexColumnRow["is_descending_key"];
					
					Spludlow.SysTables.SchemaKeysRow keyRow = (Spludlow.SysTables.SchemaKeysRow)keysTable.Rows.Add(new object[] { indexName, keyType, key_ordinal, is_descending_key, columnName, null, null });

					if (keyType == "X")
					{
						DataRow xmlIndexRow = schemaNative.Tables["xml_indexes"].Rows.Find(new object[] { object_id, index_id });
						if (xmlIndexRow == null)
							throw new ApplicationException("DAL SchemaKeys; Can not find xml index row indexname:" + indexName);

						//	Is a secondary XML Index
						if (xmlIndexRow.IsNull("secondary_type_desc") == false)
						{
							DataRow xmlPrimaryIndexRow = schemaNative.Tables["xml_indexes"].Rows.Find(new object[] { object_id, (int)xmlIndexRow["using_xml_index_id"] });
							keyRow.ParentTable = (string)xmlPrimaryIndexRow["name"];
							keyRow.ParentColumn = (string)xmlIndexRow["secondary_type_desc"];
						}
						
					}
				}
			}

			//	Full text indexes
			DataRow fullTextIndexRow = schemaNative.Tables["fulltext_indexes"].Rows.Find(object_id);
			if (fullTextIndexRow != null)
			{
				int unique_index_id = (int)fullTextIndexRow["unique_index_id"];
				int fulltext_catalog_id = (int)fullTextIndexRow["fulltext_catalog_id"];

				rows = schemaNative.Tables["indexes"].Select("object_id = " + object_id + " AND index_id = " + unique_index_id);
				if (rows.Length != 1)
					throw new ApplicationException("DAL SchemaKeys; text index unique_index_id row not found");
				string uniqueIndexName = (string)rows[0]["name"];

				DataRow catalogRow = schemaNative.Tables["fulltext_catalogs"].Rows.Find(fulltext_catalog_id);
				if (catalogRow == null)
					throw new ApplicationException("DAL SchemaKeys; fulltext_catalogs row not found fulltext_catalog_id:" + fulltext_catalog_id);
				string fulltext_catalogName = (string)catalogRow["name"];

				string indexName = "TX_" + fullTableName.Replace(".", "_");

				rows = schemaNative.Tables["fulltext_index_columns"].Select("object_id = " + object_id);
				if (rows.Length == 0)
					throw new ApplicationException("DAL SchemaKeys; No columsn for full text index object_id:" + object_id);
				
				for (int ordinal = 0; ordinal < rows.Length; ++ordinal)
				{
					DataRow fulltextIndexColumnRow = rows[ordinal];
					int column_id = (int)fulltextIndexColumnRow["column_id"];
					DataRow columnRow;
					columnRow = FindColumnsRow(schemaNative, object_id, column_id);
					string columnName = (string)columnRow["name"];

					string typeColumnName = null;
					if (fulltextIndexColumnRow.IsNull("type_column_id") == false)
					{
						columnRow = FindColumnsRow(schemaNative, object_id, (int)fulltextIndexColumnRow["type_column_id"]);
						typeColumnName = (string)columnRow["name"];
					}

					keysTable.Rows.Add(new object[] { indexName, "T", ordinal, false, columnName, fulltext_catalogName + "@" + uniqueIndexName, typeColumnName });
				}
			}

			//	Foriegn Keys
			foreach (DataRow keyRow in schemaNative.Tables["foreign_keys"].Select("parent_object_id = " + object_id))
			{
				string keyName = (string)keyRow["name"];
				int constraint_object_id = (int)keyRow["object_id"];

				foreach (DataRow keyColumnRow in schemaNative.Tables["foreign_key_columns"].Select("parent_object_id = " + object_id + " AND constraint_object_id = " + constraint_object_id))
				{
					int parent_object_id = (int)keyColumnRow["parent_object_id"];
					int parent_column_id = (int)keyColumnRow["parent_column_id"];

					rows = schemaNative.Tables["columns"].Select("object_id = " + parent_object_id + " AND column_id = " + parent_column_id);
					if (rows.Length != 1)
						throw new ApplicationException("DAL SchemaKeys; columns row not found");
					string columnName = (string)rows[0]["name"];

					int referenced_object_id = (int)keyColumnRow["referenced_object_id"];
					int referenced_column_id = (int)keyColumnRow["referenced_column_id"];

					rows = schemaNative.Tables["tables"].Select("object_id = " + referenced_object_id);
					if (rows.Length != 1)
						throw new ApplicationException("DAL SchemaKeys; tables row not found");
					string parentTable = (string)rows[0]["name"];
					int parentTableSchemaId = (int)rows[0]["schema_id"];
					string parentTableSchemaName = this.LookupSchemaName(schemaNative.Tables["schemas"], parentTableSchemaId);
					if (parentTableSchemaName != "dbo")
						parentTable = parentTableSchemaName + "." + parentTable;

					rows = schemaNative.Tables["columns"].Select("object_id = " + referenced_object_id + " AND column_id = " + referenced_column_id);
					if (rows.Length != 1)
						throw new ApplicationException("DAL SchemaKeys; columns row not found");
					string parentColumn = (string)rows[0]["name"];

					keysTable.Rows.Add(new object[] { keyName, "F", 0, false, columnName, parentTable, parentColumn });
				}
			}

			schema.Tables.Add(keysTable);
		}

		private DataRow FindColumnsRow(DataSet schemaNative, int object_id, int column_id)
		{
			DataRow[] rows = schemaNative.Tables["columns"].Select("object_id = " + object_id + " AND column_id = " + column_id);
			if (rows.Length != 1)
				throw new ApplicationException("DAL SchemaKeys, ColumnsRow; Columns row not found object_id:" + object_id + ", column_id:" + column_id + ", row count:" + rows.Length);

			return rows[0];
		}

		private string LookupSchemaName(DataTable schemasTable, int schema_id)
		{
			DataRow[] rows = schemasTable.Select("schema_id = " + schema_id);
			if (rows.Length != 1)
				throw new ApplicationException("DAL LookupSchemaName; Did not find 1 row for schema_id: " + schema_id + ", row count:" + rows.Length);

			return (string)rows[0]["name"];
		}

		public DataSet SchemaNative()       //	Only on current, automatically from db
		{
			string[] systemViews = new string[] { "databases", "schemas", "types", "tables", "columns", "indexes", "index_columns", "xml_indexes", "foreign_keys", "foreign_key_columns", "fulltext_indexes", "fulltext_index_columns", "fulltext_catalogs" };

			DataSet dataSet = new DataSet();

			foreach (string systemView in systemViews)
			{
				this.Fill(dataSet, "SELECT * FROM sys." + systemView);
				Spludlow.Data.ADO.NameLastTable(dataSet, systemView);
			}

			DataTable table;	//	Provide reverse lookups

			table = dataSet.Tables["databases"];
			table.PrimaryKey = new DataColumn[] { table.Columns["name"] };

			table = dataSet.Tables["schemas"];
			table.PrimaryKey = new DataColumn[] { table.Columns["name"] };

			table = dataSet.Tables["types"];
			table.PrimaryKey = new DataColumn[] { table.Columns["system_type_id"], table.Columns["user_type_id"] };

			table = dataSet.Tables["tables"];
			table.PrimaryKey = new DataColumn[] { table.Columns["name"], table.Columns["schema_id"] };

			table = dataSet.Tables["columns"];
			table.PrimaryKey = new DataColumn[] { table.Columns["object_id"], table.Columns["name"] };

			table = dataSet.Tables["xml_indexes"];
			table.PrimaryKey = new DataColumn[] { table.Columns["object_id"], table.Columns["index_id"] };

			table = dataSet.Tables["fulltext_indexes"];
			table.PrimaryKey = new DataColumn[] { table.Columns["object_id"] };

			table = dataSet.Tables["fulltext_catalogs"];
			table.PrimaryKey = new DataColumn[] { table.Columns["fulltext_catalog_id"] };
						

			return dataSet;
		}

		public DataSet SchemaConnection()
		{
			DataSet schema;

			if (this.Transaction == null)
				this.Open();
			try
			{
				schema = Spludlow.Data.DALCommon.SchemaConnection(this.Connection);
			}
			finally
			{
				if (this.Transaction == null)
					this.Close();
			}

			DataTable table;

			table = schema.Tables["Columns"];
			table.PrimaryKey = new DataColumn[] { table.Columns["TABLE_NAME"], table.Columns["COLUMN_NAME"] };  //	Same in all

			return schema;
		}

		public DataSet SchemaReader()		//	dont work in transaction
		{
			string[] tableNames = this.TableList();

			if (this.Transaction == null)
				this.Open();
			try
			{
				return Spludlow.Data.DALCommon.SchemaReader(this.Connection, this.Transaction, tableNames, "[", "]");       //	something going on ?????
			}
			finally
			{
				if (this.Transaction == null)
					this.Close();
			}
		}

		public DataSet SchemaANSI()
		{
			string[] viewNames = new string[]
			{
				"CHECK_CONSTRAINTS",
				"COLUMN_DOMAIN_USAGE",
				"COLUMN_PRIVILEGES",
				"COLUMNS",
				"CONSTRAINT_COLUMN_USAGE",
				"CONSTRAINT_TABLE_USAGE",
				"DOMAIN_CONSTRAINTS",
				"DOMAINS",
				"KEY_COLUMN_USAGE",
				"PARAMETERS",
				"REFERENTIAL_CONSTRAINTS",
				"ROUTINES",
				"ROUTINE_COLUMNS",
				"SCHEMATA",
				"TABLE_CONSTRAINTS",
				"TABLE_PRIVILEGES",
				"TABLES",
				"VIEW_COLUMN_USAGE",
				"VIEW_TABLE_USAGE",
				"VIEWS",
			};

			DataSet dataSet = new DataSet();

			foreach (string viewName in viewNames)
			{
				DataTable table = this.Select("SELECT * FROM INFORMATION_SCHEMA." + viewName);
				table.TableName = viewName;

				dataSet.Tables.Add(table);
			}

			return dataSet; 
		}



		//
		//	Database
		//

		public string[] DatabaseList()
		{
			DataTable table = this.Select("select name from sys.databases");

			List<string> databases = new List<string>();
			foreach (DataRow row in table.Rows)
			{
				string name = (string)row[0];

				if (SystemDatabaseNames.Contains(name) == false)
					databases.Add(name);
			}
			return databases.ToArray();
		}


		public void DatabaseCreate(string databaseName)
		{
			this.ExecuteNonQuery("CREATE DATABASE[" + databaseName + "];");
		}

		public void DatabaseCreate(string databaseName, DataSet schema)
		{
			Spludlow.Data.DALCommon.DatabaseCreate(this, databaseName, schema);
		}

		public void DatabaseDelete(string databaseName)
		{
			this.ExecuteNonQuery("ALTER DATABASE [" + databaseName + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;");
			this.ExecuteNonQuery("DROP DATABASE [" + databaseName + "];");
		}

		public bool DatabaseExists(string databaseName)
		{
			object obj = this.ExecuteScalar("SELECT name FROM sys.databases WHERE name = '" + databaseName + "'");

			if (obj == null || obj is DBNull)
				return false;

			return true;
		}

		//
		//	Tables
		//

		public string[] TableList()
		{
			string database = this.CurrentDatabase();
			if (database == null)
				throw new ApplicationException("DALSql; To list tables you must have a current database");

			//DataTable table = this.Select("select name from sys.tables");
			DataTable table = this.Select("SELECT sys.tables.name AS TableName, sys.schemas.name as SchemaName FROM sys.tables INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id");

			List<string> tableNames = new List<string>();
			foreach (DataRow row in table.Rows)
			{
				string name = (string)row[0];
				
				if (name == "sysdiagrams")
					continue;

				string schema = (string)row[1];

				if (schema != "dbo")
					name = schema + "." + name;

				tableNames.Add(name);
			}
			return tableNames.ToArray();
		}

		public void TableCreate(string tableName, DataSet schema)
		{
			this.TableCreate(tableName, schema.Tables[tableName + "_Columns"], schema.Tables[tableName + "_Keys"]);
		}
		public void TableCreate(string tableName, DataTable schemaColumns, DataTable schemaKeys)
		{
			//	CREATE TABLE [schemaname].[tableName]
			//	CREATE SCHEMA schema_name [ AUTHORIZATION owner_name ] [;]  

			string[] schemaTableName = DALCommon.SplitSchemaTableName(tableName);
			if (schemaTableName[0] != null)
			{
				this.CreateSchema(schemaTableName[0]);
				tableName = schemaTableName[0] + "]" + "." + "[" + schemaTableName[1];
			}

			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL SQL; To create a table you must have a current database");

			Spludlow.Data.DALCommon.CreateTableInfo info = new DALCommon.CreateTableInfo();

			info.DatabaseType = "SQL";

			info.QuoteOpen = this.QuoteOpen;
			info.QuoteClose = this.QuoteClose;

			info.AllowNull = "NULL";
			info.DontAllowNull = "NOT NULL";

			info.Identity = "IDENTITY";

			info.ColumnLine = "@ColumnName @DataType @Identity @Null";
			info.PrimaryLine = "CONSTRAINT @KeyName PRIMARY KEY (@Columns)";
			info.UniqueLine = null;		//	"CONSTRAINT @KeyName UNIQUE (@Columns)";
			info.ForeignLine = null;    //	"CONSTRAINT @KeyName FOREIGN KEY (@Columns) REFERENCES @ParentTable(@ParentColumns)";
			info.IndexLine = null;		//	"INDEX @KeyName NONCLUSTERED (@Columns)";

			string commandText = Spludlow.Data.DALCommon.MakeCreateTableCommandText(tableName, schemaColumns, schemaKeys, info);

			this.ExecuteNonQuery(commandText);

			//Spludlow.Log.Report("DAL; Created Table: " + tableName, new object[] { schemaColumns, schemaKeys, commandText });
		}

		private void CreateSchema(string schemaName)
		{
			object result = this.ExecuteScalar("SELECT schema_id FROM sys.schemas WHERE name = @SchemaName", new object[] { schemaName });
			if (result != null)
				return;

			this.ExecuteNonQuery("CREATE SCHEMA [" + schemaName + "] AUTHORIZATION dbo;");
		}

		private void CreateCatalog(string catalogName)
		{
			object result = this.ExecuteScalar("SELECT fulltext_catalog_id FROM sys.fulltext_catalogs WHERE name = @SchemaName", new object[] { catalogName });
			if (result != null)
				return;

			this.ExecuteNonQuery("CREATE FULLTEXT CATALOG [" + catalogName + "]");
		}

		public void TableDelete(string tableName)
		{
			//	ALTER DATABASE [SpludlowTempCopy] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
			//	Change to master
			this.ExecuteNonQuery("DROP TABLE [" + tableName + "]");
		}

		public bool TableExists(string tableName)
		{
			object obj = this.ExecuteScalar("SELECT name FROM sys.tables WHERE name = '" + tableName + "'");

			if (obj == null || obj is DBNull)
				return false;

			return true;
		}
		public void TableClear(string tableName)
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL SQL; To clear a table you must have a current database");

			if (Spludlow.Data.Schemas.TableHasForeignReferences(this.Schema(), tableName) == false)
				this.ExecuteNonQuery("TRUNCATE TABLE [" + tableName + "]");
			else
				this.ExecuteNonQuery("DELETE FROM [" + tableName + "]");
		}

		public void TableAddKeys(DataSet schema, string keyTypesPUIF)
		{
			string[] tableNames = Spludlow.Data.Schemas.TableNamesInOrder(schema);

			this.TableAddKeys(tableNames, schema, keyTypesPUIF);
		}

		public void TableAddKeys(string tableName, DataSet schema, string keyTypesPUIF)
		{
			this.TableAddKeys(new string[] { tableName }, schema, keyTypesPUIF);
		}

		public void TableAddKeys(string[] tableNames, DataSet schema, string keyTypesPUIF)
		{
			//	Allways @TableName @KeyName @Columns";

			string commandTextPrimary = "ALTER TABLE @TableName ADD CONSTRAINT [@KeyName] PRIMARY KEY (@Columns)";
			string commandTextUnique = "CREATE UNIQUE INDEX [@KeyName] ON @TableName (@Columns)";
			string commandTextIndex = "CREATE INDEX [@KeyName] ON @TableName (@Columns)";
			string commandTextForeign = "ALTER TABLE @TableName ADD CONSTRAINT [@KeyName] FOREIGN KEY (@Columns) REFERENCES @ParentTable (@ParentColumns)"; //	Add  @ParentTable (@ParentColumns)

			string commandTextXmlPrimary = "CREATE PRIMARY XML INDEX [@KeyName] ON @TableName (@Columns)";
			string commandTextXmlSecondary = "CREATE XML INDEX [@KeyName] ON @TableName (@Columns) USING XML INDEX [@XmlPrimaryIndex] FOR @XmlSecondayType";    //	Add @XmlPrimaryIndex] @XmlSecondayType

			string commandTextFullTextIndex = "CREATE FULLTEXT INDEX ON @TableName (@Columns) KEY INDEX [@KeyIndex] ON [@Catalog]";   //	Add @KeyIndex	@Catalog

			foreach (string fullTableName in tableNames)
			{
				foreach (char keyType in keyTypesPUIF)
				{
					DataTable keysTable = schema.Tables[fullTableName + "_Keys"];

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

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

					foreach (string keyName in keyNames)
					{
						Spludlow.SysTables.SchemaKeysRow[] keyRows = (Spludlow.SysTables.SchemaKeysRow[])keysTable.Select("KeyName = '" + keyName + "'");

						StringBuilder commandText = new StringBuilder();

						switch (keyType)
						{
							case 'P':
								commandText.Append(commandTextPrimary);
								break;

							case 'U':
								commandText.Append(commandTextUnique);
								break;

							case 'I':
								commandText.Append(commandTextIndex);
								break;

							case 'X':
								if (keyRows[0].IsParentTableNull() == true)
									commandText.Append(commandTextXmlPrimary);
								else
									commandText.Append(commandTextXmlSecondary);
								break;

							case 'F':
								commandText.Append(commandTextForeign);
								break;

							case 'T':
								commandText.Append(commandTextFullTextIndex);
								break;
						}

						if (commandText.Length == 0)
						{
							Spludlow.Log.Warning("TableAddKeys: Keytype not supported: " + keyType);
							continue;
						}

						commandText.Replace("@KeyName", keyName);
						commandText.Replace("@TableName", DALCommon.QuoteTableName(fullTableName, this.QuoteOpen, this.QuoteClose));

						if (keyType != 'T')
							commandText.Replace("@Columns", DALCommon.ColumnNames(keyRows, "ColumnName", this.QuoteOpen, this.QuoteClose));
						else
							commandText.Replace("@Columns", this.TextIndexColumnNames(keyRows, this.QuoteOpen, this.QuoteClose));

						if (keyType == 'F')
						{
							commandText.Replace("@ParentTable", DALCommon.QuoteTableName(keyRows[0].ParentTable, this.QuoteOpen, this.QuoteClose));
							commandText.Replace("@ParentColumns", DALCommon.ColumnNames(keyRows, "ParentColumn", this.QuoteOpen, this.QuoteClose));
						}
						
						if (keyType == 'X')
						{
							if (keyRows[0].IsParentTableNull() == false)
							{
								commandText.Replace("@XmlPrimaryIndex", keyRows[0].ParentTable);
								commandText.Replace("@XmlSecondayType", keyRows[0].ParentColumn);
							}
						}

						if (keyType == 'T')
						{
							string[] catalogAndKeyIndex = keyRows[0].ParentTable.Split(new char[] { '@' });
							if (catalogAndKeyIndex.Length != 2)
								throw new ApplicationException("DAL SQL TableAddKeys; bad catalogAndKeyIndex :" + catalogAndKeyIndex);
							string catalogName = catalogAndKeyIndex[0];
							string keyIndex = catalogAndKeyIndex[1];
							this.CreateCatalog(catalogName);
							commandText.Replace("@Catalog", catalogName);
							commandText.Replace("@KeyIndex", keyIndex);

							Spludlow.Log.Warning(commandText.ToString());
						}

						this.ExecuteNonQuery(commandText.ToString());

					}
				}
			}

		}

		private string TextIndexColumnNames(DataRow[] keyColumns, string quoteOpen, string quoteClose)
		{
			StringBuilder tempText = new StringBuilder();

			foreach (DataRow keyColumn in keyColumns)
			{
				if (tempText.Length > 0)
					tempText.Append(", ");

				tempText.Append(quoteOpen);
				tempText.Append((string)keyColumn["ColumnName"]);
				tempText.Append(quoteClose);

				if (keyColumn.IsNull("ParentColumn") == false)
				{
					tempText.Append(" TYPE COLUMN ");
					tempText.Append(quoteOpen);
					tempText.Append((string)keyColumn["ParentColumn"]);
					tempText.Append(quoteClose);
				}
			}

			return tempText.ToString();
		}

		//
		//	Logins
		//

		public string[] LoginList()		//	Will conatin '\' is windows otherwise SQL login
		{
			DataTable table = this.Select("select name from sys.server_principals WHERE (type <> 'R')");       //	or "name" may be problem if renamed ???????

			List<string> loginnames = new List<string>();
			foreach (DataRow row in table.Rows)
			{
				string name = (string)row[0];

				if (name.StartsWith("#") == true)
					continue;

				loginnames.Add(name);
			}
			return loginnames.ToArray();
		}

		public void LoginCreate(string loginName, bool administrator)			//	Can only use paramters on SQL command not data defintion commands!!!
		{
			LoginCreate(loginName, null, administrator);
		}

		public void LoginCreate(string loginName, string password, bool administrator)
		{
			string commandText;

			if (password == null)
			{
				commandText = "CREATE LOGIN [@LoginName] FROM WINDOWS";
			}
			else
			{
				commandText = "CREATE LOGIN [@LoginName] WITH PASSWORD='@Password'";
				commandText = commandText.Replace("@Password", password);
			}
			commandText = commandText.Replace("@LoginName", loginName);

			this.ExecuteNonQuery(commandText);

			if (administrator == true)
			{
				commandText = "ALTER SERVER ROLE sysadmin ADD MEMBER [@LoginName]";
				commandText = commandText.Replace("@LoginName", loginName);
				this.ExecuteNonQuery(commandText);
			}
		}

		public void LoginDelete(string loginName)
		{
			string commandText = "DROP LOGIN [@LoginName]";
			commandText = commandText.Replace("@LoginName", loginName);
			this.ExecuteNonQuery(commandText);
		}

		public bool LoginExists(string loginName)
		{
			object result = this.ExecuteScalar("select name from sys.server_principals WHERE (name = @LoginName)", new string[] { "@LoginName" }, new object[] { loginName });

			if (result == null || result is DBNull)
				return false;

			return true;
		}

		//
		//	Users	latest is sys.database_principals 
		//

		public string[] UserList()
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL SQL; To List Users you must have a current database");

			DataTable table = this.Select("SELECT name FROM sys.database_principals WHERE type <> 'R'");

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

			foreach (DataRow row in table.Rows)
			{
				string name = (string)row[0];
				userNames.Add(name);
			}

			return userNames.ToArray();
		}

		public void UserCreate(string userName, string loginName, bool administrator)
		{
			string commandText;

			commandText = "CREATE USER [@UserName] FOR LOGIN [@LoginName]";

			commandText = commandText.Replace("@UserName", userName);
			commandText = commandText.Replace("@LoginName", loginName);

			this.ExecuteNonQuery(commandText);

			string[] roles;
			if (administrator == true)
				roles = new string[] { "db_owner" };
			else
				roles = new string[] { "db_datareader", "db_datawriter" };

			foreach (string role in roles)
			{
				commandText = "ALTER ROLE [@Role] ADD MEMBER [@UserName]";
				commandText = commandText.Replace("@Role", role);
				commandText = commandText.Replace("@UserName", userName);

				this.ExecuteNonQuery(commandText);
			}
		}

		public void UserDelete(string userName)
		{
			string commandText = "DROP USER [@UserName]";
			commandText = commandText.Replace("@UserName", userName);
			this.ExecuteNonQuery(commandText);
		}

		public bool UserExists(string userName)
		{
			object result = this.ExecuteScalar("select name from sys.database_principals WHERE (name = @UserName)", new string[] { "@UserName" }, new object[] { userName });

			if (result == null || result is DBNull)
				return false;

			return true;
		}

		//
		//	Backup, Restore, Script, Operation
		//

		public void Backup(string serverFilename)
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL SQL; To backup a database you must have a current database");

			string commandText = "BACKUP DATABASE @BackupDatabase TO DISK=@BackupFilename WITH NO_COMPRESSION, INIT, SKIP, NAME=@BackupName";

			string backupName = databaseName + "-Full Database Backup";

			SqlCommand command = (SqlCommand)this.MakeCommand(commandText, new string[] { "@BackupDatabase", "@BackupFilename", "@BackupName" }, new string[] { databaseName, serverFilename, backupName });

			//	The time in seconds to wait for the command to execute. The default is 30 seconds.

			command.CommandTimeout = 30 * 60;	//	30 mins

			this.ExecuteNonQuery(command);
		}

		public void Restore(string serverFilename)		//	Only supports 1 data file !!!!!!!!!!!!!!
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL SQL; To restore a database you must have a current database");

			DataRow[] rows;

			DataTable existingFiles = this.Select("SELECT * FROM sys.database_files");

			rows = existingFiles.Select("type_desc = 'ROWS'");
			if (rows.Length != 1)
				throw new ApplicationException("DAL SQL Restore; Did not find 1 data file in database: " + databaseName + ", " + rows.Length);
			string dataPath = (string)rows[0]["physical_name"];

			rows = existingFiles.Select("type_desc = 'LOG'");
			if (rows.Length != 1)
				throw new ApplicationException("DAL SQL Restore; Did not find 1 log data file in database: " + databaseName + ", " + rows.Length);
			string logPath = (string)rows[0]["physical_name"];

			dataPath = Path.GetDirectoryName(dataPath) + @"\" + databaseName + ".mdf";
			logPath = Path.GetDirectoryName(logPath) + @"\" + databaseName + "_log.ldf";
						
			DataTable backupFileList = this.Select(@"RESTORE FILELISTONLY FROM DISK=@BackupFilename", new string[] { "@BackupFilename" }, new object[] { serverFilename });

			rows = backupFileList.Select("Type = 'D'");
			if (rows.Length != 1)
				throw new ApplicationException("DAL SQL Restore; Did not find 1 data file in backup file: " + serverFilename + ", " + rows.Length);
			string dataName = (string)rows[0]["LogicalName"];

			rows = backupFileList.Select("Type = 'L'");
			if (rows.Length != 1)
				throw new ApplicationException("DAL SQL Restore; Did not find 1 log data file in backup file: " + serverFilename + ", " + rows.Length);
			string logName = (string)rows[0]["LogicalName"];
			

			string commandText = "RESTORE DATABASE @ResotreDatabase FROM DISK=@ResotreFilename WITH REPLACE, MOVE @DataName TO @DataFilename, MOVE @LogName TO @LogFilename";

			string[] parameterNames = new string[] { "@ResotreDatabase", "@ResotreFilename", "@DataName", "@DataFilename", "@LogName", "@LogFilename" };
			object[] parameterValues = new object[] { databaseName, serverFilename, dataName, dataPath, logName, logPath };

			SqlCommand command = (SqlCommand)this.MakeCommand(commandText, parameterNames, parameterValues);

			command.CommandTimeout = 30 * 60;   //	30 mins

			this.ChangeDatabase("");

			this.ExecuteNonQuery(command);

			this.ChangeDatabase(databaseName);
		}

		private string ScriptProgramPath = null;
		public string RunScript(string localFilename, string server, string database)
		{
			return RunScript(localFilename, server, database, null, null);
		}
		public string RunScript(string localFilename, string server, string database, string userName, string password)      //	Utility method not using any object varibles, can use with null constructor
		{
			string configKey = "Spludlow.ScriptProgramPath.Sql";	//	!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! NAME ????? find bin path

			if (this.ScriptProgramPath == null)
				this.ScriptProgramPath = Spludlow.Config.Get(configKey, true);

			if (this.ScriptProgramPath == null)
				this.ScriptProgramPath = Spludlow.SpawnProcess.FindPath("sqlcmd.exe");

			if (this.ScriptProgramPath == null)
				throw new ApplicationException("DAL SQL; Can not find sqlcmd.exe in the path. You can set the full path to it in an application varible: '" + configKey + "'");

			List<string> argumentNames = new List<string>(new string[] { "-S", "-d", "-i" });
			if (userName != null)
				argumentNames.AddRange(new string[] { "-U", "-P" });

			string[] argValues = new string[] { server, database, localFilename, userName, password };

			StringBuilder arguments = new StringBuilder();
			for (int index = 0; index < argumentNames.Count; ++index)
			{
				if (arguments.Length > 0)
					arguments.Append(" ");

				arguments.Append(argumentNames[index]);
				arguments.Append(" \"");
				arguments.Append(argValues[index]);
				arguments.Append("\"");
			}

			string argumentsText = arguments.ToString();
			
			Spludlow.Log.Info("DAL SQL; Running Script: " + this.ScriptProgramPath + " " + argumentsText);

			Spludlow.SpawnProcess.ProcessExitInfo exitInfo = Spludlow.SpawnProcess.Run(this.ScriptProgramPath, argumentsText, 10 * 60); //	10 mins

			if (exitInfo.StandardError.Length > 0)
				Spludlow.Log.Error("DAL SQL; RunScript, Error in output", exitInfo.StandardError);

			if (exitInfo.ExitCode != 0)
				throw new ApplicationException("DAL SQL; RunScript, Bad Exit Code: " + exitInfo.ExitCode);

			return exitInfo.StandardOutput;
		}

		public void Operation(string details)
		{
			if (details.StartsWith("identity insert") == true)
			{
				StringBuilder commandText = new StringBuilder();
				commandText.Append("SET IDENTITY_INSERT [");
				commandText.Append(details.Substring(16));
				commandText.Append("] ON");

				this.ExecuteNonQuery(commandText.ToString());

				return;
			}

			if (details.StartsWith("shrink") == true)
			{
				string databaseName = this.CurrentDatabase();
				if (databaseName == null)
					throw new ApplicationException("DAL SQL; To perform a shrink Operation you must have a current database");

				if (details == "shrink data")
					this.ShrinkFile(databaseName, "ROWS", 1);

				if (details == "shrink log")
					this.ShrinkFile(databaseName, "LOG", 1);

				return;
			}

			throw new ApplicationException("DAL SQL Operation; Unknown: " + details);
		}

		private void ShrinkFile(string databaseName, string fileType, int targetMegabytes)
		{
			DataTable existingFiles = this.Select("SELECT * FROM sys.database_files");

			Spludlow.Log.Info("DAL SQL ShrinkFile; Stating database:" + databaseName + ", type:" + fileType, new object[] { existingFiles });

			DataRow[] rows;	//	Can do more than 1 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

			rows = existingFiles.Select("type_desc = '" + fileType + "'");
			if (rows.Length != 1)
				throw new ApplicationException("DAL SQL Restore; Did not find 1 data file in database: " + databaseName + ", " + fileType + ", " + rows.Length);
			string logicalName = (string)rows[0]["name"];

			string commandText = "DBCC SHRINKFILE (" + logicalName + ", " + targetMegabytes +  ")";
			
			DataTable result;

			this.CommandTimeout(60);

			this.ExecuteNonQuery("ALTER DATABASE [" + databaseName + "] SET RECOVERY SIMPLE");

			try
			{
				result = this.Select(commandText);
			}
			finally
			{
				this.ExecuteNonQuery("ALTER DATABASE [" + databaseName + "] SET RECOVERY FULL");

				this.CommandTimeout(-1);
			}

			Spludlow.Log.Report("DAL SQL ShrinkFile; Finished database:" + databaseName, new object[] { result });
		}

		public string PageCommandText(string commandText, int pageIndex, int pageSize)
		{
			int offset = pageIndex * pageSize;
			StringBuilder text = new StringBuilder(commandText);
			text.Append(" OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY");
			text.Replace("@Offset", offset.ToString());
			text.Replace("@PageSize", pageSize.ToString());
			return text.ToString();
		}

		//
		//	Make Commands
		//
		private void SetDataType(DAL.CommandInfo info, DataRow columnRow, int index)
		{
			string dataTypeId = (string)columnRow["DataTypeId"];
			string nativeType = Spludlow.Data.Schemas.CommonToNative(dataTypeId, "SqlDbType");

			int removeIndex = nativeType.IndexOf("(");
			if (removeIndex != -1)
				nativeType = nativeType.Substring(0, removeIndex);

			SqlDbType sqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), nativeType);

			info.ParameterTypes[index] = (int)sqlDbType;
			info.ParameterMaxLengths[index] = (int)columnRow["MaxLength"];
		}

		private SqlCommand MakeCommandNative(string commandText)
		{
			SqlCommand command = new SqlCommand(commandText, this.Connection, this.Transaction);

			if (this.CommandTimeoutSeconds >= 0)
				command.CommandTimeout = this.CommandTimeoutSeconds;

			return command;
		}

		public DAL.CommandInfo MakeCommandInfo(string commandText)
		{
			return MakeCommandInfo(commandText, DALCommon.ExtractParameterNames(commandText));
		}
		public DAL.CommandInfo MakeCommandInfo(string commandText, string[] parameterNames)
		{
			return Spludlow.Data.DALCommon.MakeCommandInfo(commandText, parameterNames, this.Schema(), this.SetDataType);
		}

		public DbCommand MakeCommand(string commandText)
		{
			SqlCommand command = this.MakeCommandNative(commandText);
			return command;
		}

		public DbCommand MakeCommand(string commandText, object[] parameterValues)
		{
			DAL.CommandInfo commandInfo = this.MakeCommandInfo(commandText);
			return this.MakeCommand(commandInfo, parameterValues);
		}

		public DbCommand MakeCommand(string commandText, string[] parameterNames, object[] parameterValues)
		{
			DAL.CommandInfo commandInfo = this.MakeCommandInfo(commandText, parameterNames);
			return this.MakeCommand(commandInfo, parameterValues);
		}

		public DbCommand MakeCommand(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			if (commandInfo.ParameterNames.Length != parameterValues.Length)
				throw new ApplicationException("Making Command with different parameterNames and parameterValues counts: " + commandInfo.ParameterNames.Length + ", " + parameterValues.Length);

			SqlCommand command = this.MakeCommandNative(commandInfo.CommandText);

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

				SqlDbType dbType = (SqlDbType)commandInfo.ParameterTypes[index];
				int maxLength = commandInfo.ParameterMaxLengths[index];

				SqlParameter parameter = command.CreateParameter();

				parameter.ParameterName = paramterName;

				if ((int)dbType != -1)
					parameter.SqlDbType = dbType;   //	seems to make slightly slower ?????????? try differnt DALs

				parameter.Value = parameterValues[index];

				command.Parameters.Add(parameter);
			}

			return command;
		}


		//
		//	ExecuteScalar
		//
		public object ExecuteScalar(string commandText)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(string commandText, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandInfo, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(DbCommand command)
		{
			return this.ExecuteScalar((SqlCommand)command);
		}
		
		private object ExecuteScalar(SqlCommand command)
		{
			if (this.Transaction != null)
				return command.ExecuteScalar();

			this.Open();
			try
			{
				return command.ExecuteScalar();
			}
			finally
			{
				this.Close();
			}
		}

		//
		//	ExecuteNonQuery
		//
		public int ExecuteNonQuery(string commandText)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText))
				return this.ExecuteNonQuery(command);
		}
		public int ExecuteNonQuery(string commandText, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText, parameterValues))
				return this.ExecuteNonQuery(command);
		}
		public int ExecuteNonQuery(string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				return this.ExecuteNonQuery(command);
		}
		public int ExecuteNonQuery(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandInfo, parameterValues))
				return this.ExecuteNonQuery(command);
		}
		public int ExecuteNonQuery(DbCommand command)
		{
			return this.ExecuteNonQuery((SqlCommand)command);
		}

		private int ExecuteNonQuery(SqlCommand command)
		{
			if (this.Transaction != null)
				return command.ExecuteNonQuery();

			this.Open();
			try
			{
				return command.ExecuteNonQuery();
			}
			finally
			{
				this.Close();
			}
		}

		//
		//	Fill
		//

		public void Fill(DataSet dataSet, string commandText)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, string commandText, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText, parameterValues))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandInfo, parameterValues))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, DbCommand command)
		{
			this.Fill(dataSet, (SqlCommand)command);
		}

		private void Fill(DataSet dataSet, SqlCommand command)
		{
			this.ExecuteAdapter(command, dataSet, null);
		}

		public void Fill(DataTable table, string commandText)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText))
				this.Fill(table, command);
		}
		public void Fill(DataTable table, string commandText, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText, parameterValues))
				this.Fill(table, command);
		}
		public void Fill(DataTable table, string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				this.Fill(table, command);
		}
		public void Fill(DataTable table, DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandInfo, parameterValues))
				this.Fill(table, command);
		}
		public void Fill(DataTable table, DbCommand command)
		{
			this.Fill(table, (SqlCommand)command);
		}

		private void Fill(DataTable table, SqlCommand command)
		{
			this.ExecuteAdapter(command, null, table);
		}



		//
		//	Select
		//

		public DataTable Select(string commandText)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText))
				return this.Select(command);
		}
		public DataTable Select(string commandText, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText, parameterValues))
				return this.Select(command);
		}
		public DataTable Select(string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				return this.Select(command);
		}
		public DataTable Select(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandInfo, parameterValues))
				return this.Select(command);
		}
		public DataTable Select(DbCommand command)
		{
			return this.Select((SqlCommand)command);
		}

		private DataTable Select(SqlCommand command)
		{
			DataSet dataSet = this.SelectDS(command);
			DataTable table = dataSet.Tables[0];
			dataSet.Tables.Remove(table);
			return table;
		}


		public DataSet SelectDS(string commandText)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText))
				return this.SelectDS(command);
		}
		public DataSet SelectDS(string commandText, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText, parameterValues))
				return this.SelectDS(command);
		}
		public DataSet SelectDS(string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				return this.SelectDS(command);
		}
		public DataSet SelectDS(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(commandInfo, parameterValues))
				return this.SelectDS(command);
		}
		public DataSet SelectDS(DbCommand command)
		{
			return this.SelectDS((SqlCommand)command);
		}
		private DataSet SelectDS(SqlCommand command)
		{
			DataSet dataSet = new DataSet();
			this.ExecuteAdapter(command, dataSet, null);
			return dataSet;
		}

		private void ExecuteAdapter(SqlCommand command, DataSet dataSet, DataTable table)
		{
			if (this.Transaction == null)
				this.Open();

			try
			{
				using (SqlDataAdapter adapter = new SqlDataAdapter(command))
				{
					if (dataSet != null)
						adapter.Fill(dataSet);

					if (table != null)
						adapter.Fill(table);
				}
			}
			finally
			{
				if (this.Transaction == null)
					this.Close();
			}
		}

		private int NativeIntDataType(DataRow columnRow)
		{
			string dataTypeId = (string)columnRow["DataTypeId"];
			string nativeType = Spludlow.Data.Schemas.CommonToNative(dataTypeId, "SqlDbType");

			int index = nativeType.IndexOf("(");		//	Fix (max) types
			if (index != -1)
				nativeType = nativeType.Substring(0, index);
			
			SqlDbType sqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), nativeType);
			return (int)sqlDbType;
		}



		//
		//	Inserter
		//

		public DAL.CommandInfo MakeInserter(string tableName)
		{
			return DALCommon.MakeInserter(this, tableName, "; SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]");
		}

		public long Insert(DataRow row)
		{
			return Insert(row.Table.TableName, row);
		}

		public long Insert(string tableName, DataRow row)
		{
			DAL.CommandInfo insertInfo = this.MakeInserter(tableName);
			return Insert(insertInfo, row);
		}

		public long Insert(DAL.CommandInfo insertInfo, DataRow row)
		{
			object[] objectRow = DALCommon.ObjectsFromRow(row, insertInfo);
			return Insert(insertInfo, objectRow);
		}
		
		public long Insert(string tableName, object[] row)
		{
			DAL.CommandInfo insertInfo = this.MakeInserter(tableName);
			return Insert(insertInfo, row);
		}

		public long Insert(DAL.CommandInfo insertInfo, object[] row)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(insertInfo, row))
			{
				if (insertInfo.Identity == false)
				{
					this.ExecuteNonQuery(command);
					return 0;
				}
				else
				{
					object obj = this.ExecuteScalar(command);

					if (obj == null)
						throw new ApplicationException("DAL Insert; Identity object is null.");

					if (obj is Decimal)
						return (long)((Decimal)obj);

					throw new ApplicationException("DAL Insert; Identity object bad type:\t" + obj.GetType().Name);
				}
			}
		}


		//
		//	Update
		//
		
		public DAL.CommandInfo MakeUpdater(string tableName)
		{
			return MakeUpdater(tableName, new string[0]);
		}

		public DAL.CommandInfo MakeUpdater(string tableName, string[] updateColumns)
		{
			return DALCommon.MakeUpdater(this, this.Schema(), tableName, updateColumns);
		}


		public int Update(DataRow row)
		{
			return this.Update(row.Table.TableName, row);
		}

		public int Update(string tableName, DataRow row)
		{
			DAL.CommandInfo updateInfo = this.MakeUpdater(tableName);
			return this.Update(updateInfo, row);
		}

		public int Update(string tableName, string[] columns, DataRow row)
		{
			DAL.CommandInfo updateInfo = this.MakeUpdater(tableName, columns);
			return this.Update(updateInfo, row);
		}

		public int Update(DAL.CommandInfo info, DataRow row)
		{
			object[] objectRow = DALCommon.ObjectsFromRow(row, info);
			return this.Update(info, objectRow);
		}

		public int Update(string tableName, object[] row)
		{
			DAL.CommandInfo updateInfo = this.MakeUpdater(tableName);
			return this.Update(updateInfo, row);
		}

		public int Update(string tableName, string[] columns, object[] row)
		{
			DAL.CommandInfo updateInfo = this.MakeUpdater(tableName, columns);
			return this.Update(updateInfo, row);
		}

		public int Update(DAL.CommandInfo updateInfo, object[] row)
		{
			using (SqlCommand command = (SqlCommand)this.MakeCommand(updateInfo, row))
			{
				return this.ExecuteNonQuery(command);
			}
		}



		public long InsertOrUpdate(DataRow row)
		{
			return this.InsertOrUpdate(row.Table.TableName, row);
		}

		public long InsertOrUpdate(string tableName, DataRow row)
		{
			DataSet schema = this.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 = this.ExecuteScalar(commandText.ToString(), primaryKeyParams.ToArray(), keyData);

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

			this.Update(tableName, row);

			return 0;
		}

		public void BulkInsert(DataTable table, string tableName, string tempDirectory)
		{
			this.Open();

			try
			{
				SqlBulkCopy bulkCopy = new SqlBulkCopy(this.Connection, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, null);

				bulkCopy.ColumnMappings.Clear();
				foreach (DataColumn column in table.Columns)
					bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);

				try
				{
					bulkCopy.BulkCopyTimeout = 1 * 60 * 60;

					bulkCopy.DestinationTableName = "[" + tableName + "]";
					bulkCopy.WriteToServer(table);
				}
				finally
				{
					bulkCopy.Close();
				}
			}
			finally
			{
				this.Close();
			}
		}


	}
}