DataLoaders - Unified Data to Object Binding

using System;
using System.Data;
using System.Data.OleDb;
using System.Xml;
using System.Collections;

namespace Bttlxe.Data
{
	/// <summary>
	/// Data loader implementing <see cref="IDataLoader"/> to access data in a MySQL database.
	/// <seealso cref="IDataLoader"/>
	/// </summary>
	/// <remarks>
	/// Uses SQL as the query language.
	/// </remarks>
	/// <example>
	/// <p>The example below demonstrates using a <see cref="MySqlDataLoader"/> to load data into a <see cref="Bttlxe.Controls.NewsList"/>
	/// collection which implements <see cref="IDataItem"/>.  (Note that the definition of the <see cref="DataItemDictionary"/> is
	/// only needed where name transformation needs to occur, but is shown here for completeness.)</p>
	/// <p class="lang">[C#]</p>
	/// <code>
	/// Bttlxe.Controls.NewsList oNewsList = new Bttlxe.Controls.NewsList();
	/// 
	/// MySqlDataLoader oLoader = new MySqlDataLoader();
	/// oLoader.Server = "(local)";
	/// oLoader.Database = "tempdb";
	/// 
	/// DataItemDictionary keys = new DataItemDictionary();
	/// keys.Add("ID", "n_id");
	/// keys.Add("Title", "n_title");
	/// keys.Add("Description", "n_description");
	/// keys.Add("Owner", "n_owner");
	/// keys.Add("Created", "n_created");
	/// 
	/// IDataItem oItem = (IDataItem)oNewsList;
	///	oLoader.Execute(ref oItem, ref keys, DataOperation.Read);
	/// </code>
	/// <p>The example below accomplishes the same result as above but without using an <see cref="IDataItem"/> object.</p>
	/// <p class="lang">[C#]</p>
	/// <code>
	/// MySqlDataLoader oLoader = new MySqlDataLoader();
	/// oLoader.Server = "(local)";
	/// oLoader.Database = "tempdb";
	/// 
	/// Bttlxe.Controls.NewsList oNewsList = new Bttlxe.Controls.NewsList();
	/// oNewsList.DataSource = oLoader.ExecuteDataTable("SELECT * FROM [News]");
	/// oNewsList.DataBind();
	/// </code>
	/// <p>The example below demonstrates using a <see cref="DataItem"/> object to write data to your data source.</p>
	/// <p class="lang">[C#]</p>
	/// <code>
	/// MySqlDataLoader oLoader = new MySqlDataLoader();
	/// oLoader.Server = "(local)";
	/// oLoader.Database = "tempdb";
	/// 
	/// DataTable dt = new DataTable("News");
	/// dt.Columns.Add("ID", System.Type.GetType("System.Int32"));
	/// dt.Columns.Add("Title", System.Type.GetType("System.String"));
	/// dt.Columns.Add("Description", System.Type.GetType("System.String"));
	/// dt.Columns.Add("Owner", System.Type.GetType("System.String"));
	/// dt.Columns.Add("Created", System.Type.GetType("System.DateTime"));
	/// 
	/// dt.Columns["ID"].AllowDBNull = false;
	/// dt.Columns["ID"].AutoIncrement = true;
	/// dt.Columns["ID"].AutoIncrementSeed = 1;
	/// dt.Columns["ID"].AutoIncrementStep = 1;
	/// dt.Columns["ID"].ReadOnly = true;
	/// dt.PrimaryKey = new DataColumn[]{dt.Columns["ID"]};
	/// 
	/// dt.Columns["Title"].AllowDBNull = false;
	/// dt.Columns["Title"].MaxLength = 50;
	/// dt.Columns["Owner"].AllowDBNull = false;
	/// dt.Columns["Owner"].MaxLength = 50;
	/// dt.Columns["Owner"].DefaultValue = "Anonymous";
	/// dt.Columns["Created"].AllowDBNull = false;
	/// 
	/// DataItemDictionary keys = new DataItemDictionary();
	/// keys.Add("ID", "n_id");
	/// keys.Add("Title", "n_title");
	/// keys.Add("Description", "n_description");
	/// keys.Add("Owner", "n_owner");
	/// keys.Add("Created", "n_created");
	/// 
	/// DataItem oDataItem = new DataItem();
	/// oDataItem.Data = dt;
	/// oDataItem.Keys = keys;
	/// 
	/// // perform some operation that adds data to the DataItem.
	/// ...
	/// 
	/// oLoader.Execute(ref oDataItem, DataOperation.Write);
	/// </code>
	/// </example>
	public class MySqlDataLoader : IDataLoader, IDisposable
	{
		#region Attributes

		// The database connection.
		private OleDbConnection m_oConnection = null;

		// Show trace helper information.
		private bool m_bTrace = false;

		// Keep connection alive.
		private bool m_bKeepAlive = true;

		// The connection string for the database.
		private string m_strConnection = string.Empty;

		// The server name.
		private string m_strServer = "(local)";

		// The database name.
		private string m_strDatabase = "tempdb";

		// The User ID field.
		private string m_strUserID = "admin";

		// The Password field.
		private string m_strPassword = "";

		#endregion

		#region Properties

		/// <summary>
		/// The database connection.
		/// </summary>
		/// <value>
		/// An <see cref="OleDbConnection"/> for the database.
		/// </value>
		public OleDbConnection Connection
		{
			get
			{
				OpenConnection();

				return m_oConnection;
			}
		}

		/// <summary>
		/// The server name.
		/// </summary>
		/// <remarks>
		/// <p>If you want To connect with MySql running on the same computer, you must to specify the keyword <c>(local)</c>.</p>
		/// </remarks>
		public string Server
		{
			get
			{
				return m_strServer;
			}
			set
			{
				m_strServer = value;
			}
		}

		/// <summary>
		/// The database name.
		/// </summary>
		public string Database
		{
			get
			{
				return m_strDatabase;
			}
			set
			{
				m_strDatabase = value;
			}
		}

		/// <summary>
		/// The User ID field.
		/// </summary>
		public string UserID
		{
			get
			{
				return m_strUserID;
			}
			set
			{
				m_strUserID = value;
			}
		}

		/// <summary>
		/// The Password field.
		/// </summary>
		public string Password
		{
			get
			{
				return m_strPassword;
			}
			set
			{
				m_strPassword = value;
			}
		}

		/// <summary>
		/// Show trace helper information.
		/// </summary>
		/// <value>
		/// Whether the data loader will output helper information to the application's trace log.
		/// </value>
		public bool Trace
		{
			get
			{
				return m_bTrace;
			}
			set
			{
				m_bTrace = value;
			}
		}

		/// <summary>
		/// Keep connection alive.
		/// </summary>
		/// <remarks>
		/// It is not recommended that you change this value once the data loader has been initialised as
		/// results may be unpredictable.
		/// </remarks>
		/// <value>
		/// Whether the data loader will manage the database connection and ensure it is always open.
		/// </value>
		public bool KeepAlive
		{
			get
			{
				return m_bKeepAlive;
			}
			set
			{
				m_bKeepAlive = value;
			}
		}

		/// <summary>
		/// The connection string for the database.
		/// </summary>
		/// <remarks>
		/// You should not include the Provider attribute in the connection string.
		/// </remarks>
		public string ConnectionString
		{
			get
			{
				if (m_strDatabase.Length > 0 && m_strServer.Length > 0)
				{
					string strConnection = "Provider=MySQLProv; DB=" + m_strDatabase + "; Server=" + m_strServer + ";";
					strConnection += " User ID=" + m_strUserID + "; Password=" + m_strPassword + ";";

					return strConnection;
				}

				return m_strConnection;
			}
			set
			{
				m_strConnection = value;
			}
		}

		#endregion

		/// <summary>
		/// Create the data loader.
		/// </summary>
		public MySqlDataLoader()
		{
		}

		/// <summary>
		/// Create the data loader.
		/// </summary>
		/// <param name="bKeepAlive">Keep connection alive.</param>
		public MySqlDataLoader(bool bKeepAlive)
		{
			m_bKeepAlive = bKeepAlive;
		}

		/// <summary>
		/// Create the data loader.
		/// </summary>
		/// <param name="bKeepAlive">Keep connection alive.</param>
		/// <param name="bTrace">Show trace helper information.</param>
		public MySqlDataLoader(bool bKeepAlive, bool bTrace)
		{
			m_bKeepAlive = bKeepAlive;
			m_bTrace = bTrace;
		}

		/// <summary>
		/// Opens the database connection.
		/// </summary>
		/// <remarks>
		/// Used internally.
		/// </remarks>
		protected void OpenConnection()
		{
			if (m_bTrace)
				DoTrace("MySqlDataLoader.OpenConnection", "Opening database connection.\n" + ConnectionString);

			if (m_oConnection == null || m_oConnection.State == ConnectionState.Closed)
			{
				m_oConnection = new OleDbConnection(ConnectionString);
				m_oConnection.Open();
			}
		}

		/// <summary>
		/// Closes the database connection
		/// </summary>
		/// <remarks>
		/// Used internally.
		/// </remarks>
		protected void CloseConnection()
		{
			if (m_bTrace)
				DoTrace("MySqlDataLoader.CloseConnection", "Closing database connection");

			if (m_oConnection != null)
			{
				m_oConnection.Close();
				m_oConnection = null;
			}
		}

		#region IDataLoader Members

		/// <summary>
		/// Initialise the data loader.
		/// </summary>
		/// <remarks>
		/// Initialises the connection that is present for the lifetime of the data loader if
		/// <see cref="KeepAlive"/> is true.
		/// </remarks>
		public void Initialise()
		{
			if (m_bKeepAlive)
				OpenConnection();
		}

		/// <summary>
		/// Terminate the data loader.
		/// </summary>
		/// <remarks>
		/// Terminates the connection that is present for the lifetime of the data loader if
		/// <see cref="KeepAlive"/> is true.
		/// This method should not be called directly, use the <see cref="Dispose"/> method instead.
		/// </remarks>
		public void Terminate()
		{
			if (m_bKeepAlive)
				CloseConnection();
		}

		/// <summary>
		/// Executes a scalar SELECT query.
		/// </summary>
		/// <param name="strQuery">The SQL query to evaluate.</param>
		/// <returns>The first record or the first row returned.</returns>
		public object ExecuteScaler(string strQuery)
		{
			DataItem dataItem = new DataItem();
			DataItemDictionary keys = new DataItemDictionary();
			string strTableName = GetTableFromQuery(strQuery);

			// get the mapping
			if (NameMappingCallback != null)
				NameMappingCallback(strTableName, ref keys);

			if (keys.Count > 0)
			{
				// translate names
				foreach (DictionaryEntry oItem in keys)
					strQuery = strQuery.Replace("[" + (string)oItem.Key + "]", "[" + (string)oItem.Value + "]");
			}

			if (m_bTrace)
				DoTrace("MySqlDataLoader.ExecuteScalar", strQuery);

			object oRetVal = null;

			// two scenarios - if the connection is already open use it, else open our
			// .. own connection, query it, then close it again
			if (m_oConnection != null)
			{
				OleDbCommand cmd = new OleDbCommand(strQuery, m_oConnection);
				oRetVal = cmd.ExecuteScalar();
			}
			else
			{
				OleDbConnection oConn = new OleDbConnection(ConnectionString);
				oConn.Open();
				OleDbCommand cmd = new OleDbCommand(strQuery, oConn);
				oRetVal = cmd.ExecuteScalar();
				oConn.Close();
			}

			return oRetVal;
		}

		/// <summary>
		/// Executes a SELECT query.
		/// </summary>
		/// <param name="strQuery">The SQL query to evaluate.</param>
		/// <returns>A <see cref="DataTable"/> containing any result.</returns>
		public DataTable ExecuteDataTable(string strQuery)
		{
			DataItem dataItem = new DataItem();
			DataItemDictionary keys = new DataItemDictionary();
			string strTableName = GetTableFromQuery(strQuery);

			// get the mapping
			if (NameMappingCallback != null)
				NameMappingCallback(strTableName, ref keys);

			if (keys.Count > 0)
			{
				// translate names
				foreach (DictionaryEntry oItem in keys)
					strQuery = strQuery.Replace("[" + (string)oItem.Key + "]", "[" + (string)oItem.Value + "]");
			}

			if (m_bTrace)
				DoTrace("MySqlDataLoader.ExecuteDataTable", strQuery);

			if (m_oConnection == null || m_oConnection.State == ConnectionState.Closed)
				OpenConnection();

			OleDbDataAdapter oAdaptor = new OleDbDataAdapter();
			oAdaptor.SelectCommand = new OleDbCommand(strQuery, m_oConnection);

			DataSet oDataSet = new DataSet();
			oAdaptor.Fill(oDataSet);
			oDataSet.Tables[0].TableName = strTableName;

			if (keys.Count > 0)
			{
				// revert back to our proxy names
				DataItemDictionary values = new DataItemDictionary();

				foreach (DictionaryEntry oItem in keys)
					values.Add((string)oItem.Value, (string)oItem.Key);

				foreach (DataColumn oColumn in oDataSet.Tables[0].Columns)
					oDataSet.Tables[0].Columns[oColumn.ColumnName].ColumnName = values[oColumn.ColumnName];
			}

			return oDataSet.Tables[0];
		}

		/// <summary>
		/// Executes a SELECT query.
		/// </summary>
		/// <param name="strQuery">The SQL query to evaluate.</param>
		/// <returns>A <see cref="IDataReader"/> containing any result.</returns>
		public IDataReader ExecuteReader(string strQuery)
		{
			if (m_bTrace)
				DoTrace("MySqlDataLoader.ExecuteReader", strQuery);

			OleDbDataReader oReader = null;

			if (m_oConnection == null || m_oConnection.State == ConnectionState.Closed)
				OpenConnection();

			OleDbCommand cmd = new OleDbCommand(strQuery, m_oConnection);
			oReader = cmd.ExecuteReader();

			return oReader;
		}

		/// <summary>
		/// <b>[Unsupported by this data loader].</b>
		/// </summary>
		/// <param name="strQuery">The SQL query to evaluate.</param>
		/// <returns>Null.</returns>
		/// <remarks>
		/// This method is unsupported by this data loader.  Use <see cref="ExecuteReader"/> instead.
		/// </remarks>
		public XmlReader ExecuteXmlReader(string strQuery)
		{
			return null;
		}

		/// <summary>
		/// Executes a non query (UPDATE, INSERT, or DELETE).
		/// </summary>
		/// <param name="strQuery">The SQL query to evaluate.</param>
		/// <returns>The number of records affected.</returns>
		public int ExecuteNonQuery(string strQuery)
		{
			DataItem dataItem = new DataItem();
			DataItemDictionary keys = new DataItemDictionary();
			string strTableName = GetTableFromQuery(strQuery);

			// get the mapping
			if (NameMappingCallback != null)
				NameMappingCallback(strTableName, ref keys);

			if (keys.Count > 0)
			{
				// translate names
				foreach (DictionaryEntry oItem in keys)
					strQuery = strQuery.Replace("[" + (string)oItem.Key + "]", "[" + (string)oItem.Value + "]");
			}

			if (m_bTrace)
				DoTrace("MySqlDataLoader.ExecuteNonQuery", strQuery);

			int nRetVal = 0;

			// two scenarios - if the connection is already open use it, else open our
			// .. own connection, query it, then close it again
			if (m_oConnection != null)
			{
				OleDbCommand cmd = new OleDbCommand(strQuery, m_oConnection);
				nRetVal = cmd.ExecuteNonQuery();
			}
			else
			{
				OleDbConnection oConn = new OleDbConnection(ConnectionString);
				oConn.Open();
				OleDbCommand cmd = new OleDbCommand(strQuery, oConn);
				nRetVal = cmd.ExecuteNonQuery();
				oConn.Close();
			}

			return nRetVal;
		}

		/// <summary>
		/// Executes a SELECT query.
		/// </summary>
		/// <param name="strQuery">The SQL query to evaluate.</param>
		/// <returns>A <see cref="IDataItem"/> containing any result.</returns>
		public IDataItem Execute(string strQuery)
		{
			DataItem dataItem = new DataItem();
			DataItemDictionary keys = new DataItemDictionary();
			string strTableName = GetTableFromQuery(strQuery);

			// get the mapping
			if (NameMappingCallback != null)
				NameMappingCallback(strTableName, ref keys);

			// translate names
			foreach (DictionaryEntry oItem in keys)
				strQuery = strQuery.Replace("[" + (string)oItem.Key + "]", "[" + (string)oItem.Value + "]");

			// execute
			if (m_bTrace)
				DoTrace("MySqlDataLoader.Execute", strQuery);

			DataSet oDataSet = new DataSet();

			// two scenarios - if the connection is already open use it, else open our
			// .. own connection, query it, then close it again
			if (m_oConnection != null)
			{
				OleDbDataAdapter oAdaptor = new OleDbDataAdapter();
				oAdaptor.SelectCommand = new OleDbCommand(strQuery, m_oConnection);
				oAdaptor.Fill(oDataSet);

				oDataSet.Tables[0].TableName = strTableName;

				// revert back to our proxy names
				if (keys != null && keys.Count > 0)
				{
					DataItemDictionary values = new DataItemDictionary();

					foreach (DictionaryEntry oItem in keys)
						values.Add((string)oItem.Value, (string)oItem.Key);

					foreach (DataColumn oColumn in oDataSet.Tables[0].Columns)
						oDataSet.Tables[0].Columns[oColumn.ColumnName].ColumnName = values[oColumn.ColumnName];
				}
			}
			else
			{
				OleDbConnection oConn = new OleDbConnection(ConnectionString);
				oConn.Open();

				OleDbDataAdapter oAdaptor = new OleDbDataAdapter();
				oAdaptor.SelectCommand = new OleDbCommand(strQuery, oConn);

				oAdaptor.Fill(oDataSet);

				oDataSet.Tables[0].TableName = strTableName;

				// revert back to our proxy names
				if (keys != null && keys.Count > 0)
				{
					DataItemDictionary values = new DataItemDictionary();

					foreach (DictionaryEntry oItem in keys)
						values.Add((string)oItem.Value, (string)oItem.Key);

					foreach (DataColumn oColumn in oDataSet.Tables[0].Columns)
						oDataSet.Tables[0].Columns[oColumn.ColumnName].ColumnName = values[oColumn.ColumnName];
				}

				oConn.Close();
			}

			// apply the schema to the data
			/*DataTable dtNew = dataItem.Schema.Tables[0].Clone();

			foreach (DataRow oRow in oDataSet.Tables[0].Rows)
				dtNew.ImportRow(oRow);

			oDataSet.Tables.Clear();
			oDataSet.Tables.Add(dtNew);*/

			dataItem.Data = oDataSet.Copy();

			return dataItem;
		}

		/// <summary>
		/// Executes a query.
		/// </summary>
		/// <param name="dataItem">The <see cref="IDataItem"/> either containing the data passed to this method or that will receive any records returned.</param>
		/// <param name="operation">The <see cref="DataOperation"/> to perform.</param>
		public void Execute(ref IDataItem dataItem, DataOperation operation)
		{
			if (dataItem == null)
				throw(new ArgumentNullException("dataItem"));

			string strQuery = string.Empty;
			DataTable dtThis = null;

			// drop and create operations use the data item schema not data
			if (operation == DataOperation.Create || operation == DataOperation.Drop)
			{
				// if we have multiple data tables
				if (dataItem.Schema.Tables.Count > 1)
				{
					DataSet dsResults = new DataSet(dataItem.Schema.DataSetName);

					foreach (DataTable dt in dataItem.Schema.Tables)
					{
						DataSet ds = new DataSet();
						ds.Tables.Add(dt.Copy());

						DataItem di = new DataItem(ds);
						IDataItem idi = (IDataItem)di;

						Execute(ref idi, operation);

						dsResults.Tables.Add(ds.Tables[0].Copy());
					}

					return;
				}

				dtThis = dataItem.Schema.Tables[0];
			}
			else
			{
				// if we have multiple data tables
				if (dataItem.Data.Tables.Count > 1)
				{
					DataSet dsResults = new DataSet(dataItem.Data.DataSetName);

					foreach (DataTable dt in dataItem.Data.Tables)
					{
						DataSet ds = new DataSet();
						ds.Tables.Add(dt.Copy());

						DataItem di = new DataItem(ds);
						IDataItem idi = (IDataItem)di;

						Execute(ref idi, operation);

						dsResults.Tables.Add(ds.Tables[0].Copy());
					}

					dataItem.Data = dsResults;

					return;
				}

				dtThis = dataItem.Data.Tables[0];
			}

			// determine the type of query (get or set)
			if (operation == DataOperation.Read)
			{
				// SELECT
				strQuery = "SELECT ";

				foreach (DataColumn oColumn in dtThis.Columns)
					strQuery += "[" + oColumn.ColumnName + "]";

				strQuery = strQuery.Replace("][", "], [");

				// FROM
				strQuery += " FROM [" + dtThis.TableName + "]";

				// WHERE
				if (dataItem.Condition != null && dataItem.Condition.Length > 0)
					strQuery += " WHERE (" + dataItem.Condition + ")";
				else
				{
					int nReadOnlyColumnCount = 0;

					foreach (DataColumn oColumn in dtThis.Columns)
					{
						bool bPrimaryKey = false;

						foreach (DataColumn oColumnKey in dtThis.PrimaryKey)
						{
							if (oColumnKey.ColumnName == oColumn.ColumnName)
								bPrimaryKey = true;
						}

						if (oColumn.ReadOnly || bPrimaryKey)
						{
							nReadOnlyColumnCount++;
							continue;
						}
					}

					if (nReadOnlyColumnCount > 0 && dtThis.Rows.Count > 0)
					{
						strQuery += " WHERE (";
						int nColumnCount = 0;

						foreach (DataColumn oColumn in dtThis.Columns)
						{
							nColumnCount++;

							bool bPrimaryKey = false;

							foreach (DataColumn oColumnKey in dtThis.PrimaryKey)
							{
								if (oColumnKey.ColumnName == oColumn.ColumnName)
									bPrimaryKey = true;
							}

							if (oColumn.ReadOnly || bPrimaryKey)
								strQuery += "[" + oColumn.ColumnName + "]=" + GetValueFromType(oColumn.DataType, dtThis.Rows[0][oColumn.ColumnName]);

							if (nColumnCount < nReadOnlyColumnCount)
								strQuery += " AND ";
						}

						strQuery += ")";
					}
				}

				// ORDER BY
				if (dataItem.Sort.Length > 0)
					strQuery += " ORDER BY " + dataItem.Sort;

				DataSet oDataSet = new DataSet();
				oDataSet.Tables.Add(ExecuteDataTable(strQuery).Copy());

				// apply the schema to the data
				DataTable dtNew = dataItem.Schema.Tables[0].Clone();

				foreach (DataRow oRow in oDataSet.Tables[0].Rows)
					dtNew.ImportRow(oRow);

				oDataSet.Tables.Clear();
				oDataSet.Tables.Add(dtNew);

				dataItem.Data = oDataSet.Copy();
			}
			else if (operation == DataOperation.Delete)
			{
				// DELETE
				strQuery = "DELETE FROM [" + dtThis.TableName + "]";

				// now execute for each row we are inserting
				foreach (DataRow oRow in dtThis.Rows)
				{
					string strQueryThis = strQuery;
					int nColumnCount = 0;
					int nReadOnlyColumnCount = 0;

					foreach (DataColumn oColumn in dtThis.Columns)
					{
						nColumnCount++;

						bool bPrimaryKey = false;

						foreach (DataColumn oColumnKey in dtThis.PrimaryKey)
						{
							if (oColumnKey.ColumnName == oColumn.ColumnName)
								bPrimaryKey = true;
						}

						if (oColumn.ReadOnly || bPrimaryKey)
						{
							nReadOnlyColumnCount++;
							continue;
						}
					}

					// check we are updating at least one column or that at least one column is a key!
					if (nReadOnlyColumnCount == nColumnCount || nReadOnlyColumnCount == 0)
						return;

					// WHERE
					if (dataItem.Condition != null && dataItem.Condition.Length > 0)
						strQueryThis += " WHERE (" + dataItem.Condition + ")";
					else if (nReadOnlyColumnCount > 0)
					{
						strQueryThis += " WHERE (";
						nColumnCount = 0;

						foreach (DataColumn oColumn in dtThis.Columns)
						{
							nColumnCount++;

							bool bPrimaryKey = false;

							foreach (DataColumn oColumnKey in dtThis.PrimaryKey)
							{
								if (oColumnKey.ColumnName == oColumn.ColumnName)
									bPrimaryKey = true;
							}

							if (oColumn.ReadOnly || bPrimaryKey)
								strQueryThis += "[" + oColumn.ColumnName + "]=" + GetValueFromType(oColumn.DataType, oRow[oColumn.ColumnName]);

							if (nColumnCount < nReadOnlyColumnCount)
								strQueryThis += " AND ";
						}

						strQueryThis += ")";
					}

					// execute
					this.ExecuteNonQuery(strQueryThis);
				}
			}
			else if (operation == DataOperation.Drop)
			{
				// DROP TABLE
				strQuery = "DROP TABLE [" + dtThis.TableName + "]";

				// WHERE
				if (dataItem.Condition != null && dataItem.Condition.Length > 0)
					strQuery += " WHERE (" + dataItem.Condition + ")";

				// execute
				this.ExecuteNonQuery(strQuery);
			}
			else if (operation == DataOperation.Write)
			{
				// check we dont exist (based on our keys) else INSERT
				bool bHasIdentity = false;
				int nReadOnlyColumnCount = 0;

				foreach (DataColumn oColumn in dtThis.Columns)
				{
					bool bPrimaryKey = false;

					foreach (DataColumn oColumnKey in dtThis.PrimaryKey)
					{
						if (oColumnKey.ColumnName == oColumn.ColumnName)
							bPrimaryKey = true;
					}

					if (oColumn.AutoIncrement)
						bHasIdentity = true;

					if (oColumn.ReadOnly || bPrimaryKey)
					{
						nReadOnlyColumnCount++;
						continue;
					}
				}

				strQuery = "INSERT INTO [" + dtThis.TableName + "] (";

				foreach (DataColumn oColumn in dtThis.Columns)
				{
					bool bPrimaryKey = false;

					foreach (DataColumn oColumnKey in dtThis.PrimaryKey)
					{
						if (oColumnKey.ColumnName == oColumn.ColumnName)
							bPrimaryKey = true;
					}

					//if (oColumn.ReadOnly)
					//	continue;

					strQuery += "[" + oColumn.ColumnName + "]";
				}

				strQuery = strQuery.Replace("][", "], [");

				strQuery += ") VALUES (@VALUES@)";

				// WHERE
				if (dataItem.Condition != null && dataItem.Condition.Length > 0)
					strQuery += " WHERE (" + dataItem.Condition + ")";

				// now execute for each row we are inserting
				foreach (DataRow oRow in dtThis.Rows)
				{
					// check we dont exist (based on our keys) else INSERT
					string strCheck = "SELECT Count([" + dtThis.Columns[0].ColumnName + "]) FROM [" + dtThis.TableName + "]";

					if (nReadOnlyColumnCount > 0)
					{
						strCheck += " WHERE (";
						int nColumnCount1 = 0;

						foreach (DataColumn oColumn in dtThis.Columns)
						{
							nColumnCount1++;

							bool bPrimaryKey = false;

							foreach (DataColumn oColumnKey in dtThis.PrimaryKey)
							{
								if (oColumnKey.ColumnName == oColumn.ColumnName)
									bPrimaryKey = true;
							}

							if (oColumn.ReadOnly || bPrimaryKey)
								strCheck += "[" + oColumn.ColumnName + "]=" + GetValueFromType(oColumn.DataType, oRow[oColumn.ColumnName]);

							if (nColumnCount1 < nReadOnlyColumnCount)
								strCheck += " AND ";
						}

						strCheck += ")";
					}

					// if we exist try an update instead
					if ((int)ExecuteScaler(strCheck) > 0)
					{
						DataSet dsThis = new DataSet(dtThis.TableName);
						dsThis.Tables.Add(dtThis.Clone());
						dsThis.Tables[0].ImportRow(oRow);

						DataItem diThis = new DataItem(dsThis, dataItem.Condition, dataItem.Sort);
						IDataItem di = (IDataItem)diThis;

						Execute(ref di, DataOperation.Update);

						continue;
					}

					string strQueryThis = string.Empty;
					int nColumnCount = 0;

					foreach (DataColumn oColumn in dtThis.Columns)
					{
						nColumnCount++;

						bool bPrimaryKey = false;

						foreach (DataColumn oColumnKey in dtThis.PrimaryKey)
						{
							if (oColumnKey.ColumnName == oColumn.ColumnName)
								bPrimaryKey = true;
						}

						//if (oColumn.ReadOnly)
						//	continue;

						strQueryThis += GetValueFromType(oColumn.DataType, oRow[oColumn.ColumnName]);

						if (nColumnCount < dtThis.Columns.Count)
							strQueryThis += ", ";
					}

					strQueryThis = strQuery.Replace("@VALUES@", strQueryThis);

					// execute
					/*if (bHasIdentity)
						ExecuteNonQuery("SET IDENTITY_INSERT [" + dtThis.TableName + "] ON");*/

					this.ExecuteNonQuery(strQueryThis);

					/*if (bHasIdentity)
						ExecuteNonQuery("SET IDENTITY_INSERT [" + dtThis.TableName + "] OFF");*/
				}
			}
			else if (operation == DataOperation.Update)
			{
				// UPDATE
				strQuery = "UPDATE [" + dtThis.TableName + "] SET ";

				// now execute for each row we are inserting
				foreach (DataRow oRow in dtThis.Rows)
				{
					string strQueryThis = strQuery;
					int nColumnCount = 0;
					int nReadOnlyColumnCount = 0;

					foreach (DataColumn oColumn in dtThis.Columns)
					{
						nColumnCount++;

						bool bPrimaryKey = false;

						foreach (DataColumn oColumnKey in dtThis.PrimaryKey)
						{
							if (oColumnKey.ColumnName == oColumn.ColumnName)
								bPrimaryKey = true;
						}

						if (oColumn.ReadOnly || bPrimaryKey)
						{
							nReadOnlyColumnCount++;
							continue;
						}

						strQueryThis += "[" + oColumn.ColumnName + "]=" + GetValueFromType(oColumn.DataType, oRow[oColumn.ColumnName]);

						if (nColumnCount < dtThis.Columns.Count)
							strQueryThis += ", ";
					}

					// check we are updating at least one column or that at least one column is a key!
					if (nReadOnlyColumnCount == nColumnCount || nReadOnlyColumnCount == 0)
						return;

					// WHERE
					if (dataItem.Condition != null && dataItem.Condition.Length > 0)
						strQueryThis += " WHERE (" + dataItem.Condition + ")";
					else if (nReadOnlyColumnCount > 0)
					{
						strQueryThis += " WHERE (";
						nColumnCount = 0;

						foreach (DataColumn oColumn in dtThis.Columns)
						{
							nColumnCount++;

							bool bPrimaryKey = false;

							foreach (DataColumn oColumnKey in dtThis.PrimaryKey)
							{
								if (oColumnKey.ColumnName == oColumn.ColumnName)
									bPrimaryKey = true;
							}

							if (oColumn.ReadOnly || bPrimaryKey)
								strQueryThis += "[" + oColumn.ColumnName + "]=" + GetValueFromType(oColumn.DataType, oRow[oColumn.ColumnName]);

							if (nColumnCount < nReadOnlyColumnCount)
								strQueryThis += " AND ";
						}

						strQueryThis += ")";
					}

					// execute
					this.ExecuteNonQuery(strQueryThis);
				}
			}
			else if (operation == DataOperation.Create)
			{
				// CREATE TABLE
				// N.B.: the DataTable wrapped in the DataItem only contains DataColumn definitions.
				strQuery = "CREATE TABLE [" + dtThis.TableName + "] (";
				int nColumnCount = 0;

				foreach (DataColumn oColumn in dtThis.Columns)
				{
					strQuery += "\n[" + oColumn.ColumnName + "] ";

					if (oColumn.MaxLength == -1)
						strQuery += GetTypeFromType(oColumn.DataType);
					else
						strQuery += GetTypeFromTypeAndSize(oColumn.DataType, oColumn.MaxLength);

					if (oColumn.AutoIncrement)
						strQuery += " IDENTITY(" + oColumn.AutoIncrementSeed + "," + oColumn.AutoIncrementStep + ")";

					if (oColumn.AllowDBNull)
						strQuery += " NULL";
					else
						strQuery += " NOT NULL";

					// set the default value
					if (oColumn.DefaultValue != DBNull.Value)
						strQuery += " DEFAULT(" + GetDefaultValueFromType(oColumn.DataType, oColumn.DefaultValue) + ")";

					bool bPrimaryKey = false;

					foreach (DataColumn oColumnKey in dtThis.PrimaryKey)
					{
						if (oColumnKey.ColumnName == oColumn.ColumnName)
						{
							bPrimaryKey = true;
							strQuery += " PRIMARY KEY";
						}
					}

					if (!bPrimaryKey && oColumn.Unique)
						strQuery += " UNIQUE";

					if (++nColumnCount < dtThis.Columns.Count)
						strQuery += ", ";
				}

				strQuery += ")";

				ExecuteNonQuery(strQuery);
			}
			else
				throw(new ArgumentException("The DataOperation is not valid", "operation"));
		}

		/// <summary>
		/// Event for mapping data item column names to those on the data source.
		/// </summary>
		/// <remarks>
		/// This event is typically consumed by your Global class and provides a way for to
		/// specify the column names on the data source that map to those in the data item.
		/// </remarks>
		/// <example>
		/// <p>The following example shows how the name mapping can be used.</p>
		/// <p class="lang">[C#]</p>
		/// <code>
		/// Loader.NameMappingCallback += new NameMappingCallbackHandler(Loader_NameMappingCallback);
		/// 
		/// ...
		/// 
		/// public void Loader_NameMappingCallback(string table, ref DataItemDictionary keys)
		/// {
		/// 	keys.Clear();
		/// 
		/// 	switch (table)
		/// 	{
		/// 		case "News":
		/// 			keys.Add("ID", "n_id");
		/// 			keys.Add("Title", "n_title");
		/// 			keys.Add("Description", "n_description");
		/// 			keys.Add("Owner", "n_owner");
		/// 			keys.Add("Created", "n_created");
		/// 			break;
		/// 		case "Product":
		/// 			keys.Add("ID", "pr_id");
		/// 			keys.Add("Name", "pr_name");
		/// 			keys.Add("Description", "pr_description");
		/// 			keys.Add("Price", "pr_price");
		/// 			break;
		/// 	}
		/// }
		/// </code>
		/// </example>
		public event NameMappingCallbackHandler NameMappingCallback;

		#endregion

		private string GetValueFromType(Type type, object value)
		{
			if (value == null || value == DBNull.Value)
				return "NULL";

			switch (type.Name)
			{
				case "String":
					return "'" + S((string)value) + "'";
				case "Int16":
				case "Int32":
				case "Int64":
					return ((int)value).ToString();
				case "Boolean":
					return ((bool)value) ? "1" : "0";
				case "Double":
					return ((double)value).ToString("G");
				case "Single":
					return ((float)value).ToString("G");
				case "Decimal":
					return ((decimal)value).ToString("G");
				case "DateTime":
					return "'" + ((DateTime)value).ToString("dd MMMM yyyy HH:mm:mm") + "'";
			}

			throw new ArgumentException("The System.Type is not supported as a data item on this loader", "type");
		}

		private string GetDefaultValueFromType(Type type, object value)
		{
			if (value == null || value == DBNull.Value)
				return "NULL";

			switch (type.Name)
			{
				case "String":
					return "'" + S((string)value) + "'";
				case "Int16":
				case "Int32":
				case "Int64":
					return ((int)value).ToString();
				case "Boolean":
					return ((bool)value) ? "1" : "0";
				case "Double":
					return ((double)value).ToString("G");
				case "Single":
					return ((float)value).ToString("G");
				case "Decimal":
					return ((decimal)value).ToString("G");
				case "DateTime":
					return "'" + ((DateTime)value).ToString("dd MMMM yyyy HH:mm:mm") + "'";
			}

			throw new ArgumentException("The System.Type is not supported as a data item on this loader", "type");
		}

		private string GetTypeFromType(Type type)
		{
			switch (type.Name)
			{
				case "String":
					return "TEXT";
				case "Int16":
				case "Int32":
				case "Int64":
					return "INT";
				case "Boolean":
					return "BIT";
				case "Double":
				case "Single":
					return "FLOAT";
				case "Decimal":
					return "DECIMAL(18, 6)";
				case "DateTime":
					return "DATETIME";
			}

			throw new ArgumentException("The System.Type is not supported as a data item on this loader", "type");
		}

		private string GetTypeFromTypeAndSize(Type type, int size)
		{
			switch (type.Name)
			{
				case "String":
					return "VARCHAR(" + size + ")";
				case "Int16":
				case "Int32":
				case "Int64":
					return "INT(" + size + ")";
				case "Boolean":
					return "BIT";
				case "Double":
				case "Single":
					return "DOUBLE(" + size + ")";
				case "Decimal":
					return "DECIMAL(18, " + size + ")";
				case "DateTime":
					return "DATETIME";
			}

			throw new ArgumentException("The System.Type is not supported as a data item on this loader", "type");
		}

		private string GetTableFromQuery(string strQuery)
		{
			string strQueryLC = strQuery.ToLower();
			int nStart = 0, nEnd = strQuery.Length;

			if (strQueryLC.IndexOf("select") == 0)
			{
				nStart = strQueryLC.IndexOf("from [", 0) + 6;
				nEnd = strQueryLC.IndexOf("]", nStart);

				return strQuery.Substring(nStart, nEnd - nStart);
			}
			else if (strQueryLC.IndexOf("insert into") == 0)
			{
				nStart = strQueryLC.IndexOf("[", 0) + 1;
				nEnd = strQueryLC.IndexOf("]", nStart);

				return strQuery.Substring(nStart, nEnd - nStart);
			}
			else if (strQueryLC.IndexOf("create table") == 0)
			{
				nStart = strQueryLC.IndexOf("[", 0) + 1;
				nEnd = strQueryLC.IndexOf("]", nStart);

				return strQuery.Substring(nStart, nEnd - nStart);
			}
			else if (strQueryLC.IndexOf("update") == 0)
			{
				nStart = strQueryLC.IndexOf("[", 0) + 1;
				nEnd = strQueryLC.IndexOf("]", nStart);

				return strQuery.Substring(nStart, nEnd - nStart);
			}
			else if (strQueryLC.IndexOf("drop table") == 0)
			{
				nStart = strQueryLC.IndexOf("[", 0) + 1;
				nEnd = strQueryLC.IndexOf("]", nStart);

				return strQuery.Substring(nStart, nEnd - nStart);
			}
			else if (strQueryLC.IndexOf("delete from") == 0)
			{
				nStart = strQueryLC.IndexOf("[", 0) + 1;
				nEnd = strQueryLC.IndexOf("]", nStart);

				return strQuery.Substring(nStart, nEnd - nStart);
			}

			return string.Empty;
		}

		private string S(string str)
		{
			return str.Replace("'", "''");
		}

		#region IDisposable Members

		/// <summary>
		/// Performs tasks associated with freeing, releasing, or resetting unmanaged resources.
		/// </summary>
		/// <remarks>
		/// Calls <see cref="Terminate"/>.
		/// </remarks>
		public void Dispose()
		{
			Terminate();

			// Call GC.SupressFinalize to take this object off the finalization queue and prevent
			// .. finalization code for this object from executing a second time.
			GC.SuppressFinalize(this);
		}

		#endregion

		private void DoTrace(string strCategory, string strMessage)
		{
			try
			{
				System.Web.HttpContext.Current.Trace.Write(strCategory, strMessage);
			}
			catch (Exception)
			{
				System.Diagnostics.Trace.WriteLine(strMessage, strCategory);
			}
		}
	}
}