using System;
using System.Data;
using System.Data.SqlClient;
using ByteFX.Data.MySqlClient;

namespace TitaniumSoft.Data
{
	public class TSDatabaseConnection
	{
//--------------------------------------------------------------------------------------------
		ADODB.Connection			adodbconnection;
		MySqlConnection				mysqlconnection;
		TSDatabaseConnectionType	databaseconnectiontype;
		string						connectionstring;
		string						username;
		string						password;
//--------------------------------------------------------------------------------------------
		public void Connect()
		{
			switch (DatabaseConnectionType)
			{
				case TSDatabaseConnectionType.MySQL:
					mysqlconnection                    = new MySqlConnection();
					mysqlconnection.ConnectionString   = ConnectionString;
					mysqlconnection.Open();
					break;

				case TSDatabaseConnectionType.ADODB:
					DatabaseConnectionType	          = TSDatabaseConnectionType.ADODB;
					adodbconnection			     = new ADODB.ConnectionClass();						adodbconnection.Open(ConnectionString, "", "", -1);
					break;
			}
		}

//--------------------------------------------------------------------------------------------		
		public void Disconnect()
		{
			switch (DatabaseConnectionType)
			{
				case TSDatabaseConnectionType.MySQL:
					mysqlconnection.Close();
					break;

				case TSDatabaseConnectionType.ADODB:
					try
					{
						adodbconnection.Close();
						adodbconnection	= null;
					}
					catch
					{
					}
					break;
			}
		}
//--------------------------------------------------------------------------------------------
		public bool Connected
		{
			get
			{
				switch (DatabaseConnectionType)
				{
					case TSDatabaseConnectionType.ADODB:
						return (adodbconnection.State != 0);
					
					case TSDatabaseConnectionType.MSSQL:
						return true;

                         case TSDatabaseConnectionType.MySQL:
                              return (mysqlconnection.State == ConnectionState.Open);

					default:
						return false;
				}
			}
		}
//--------------------------------------------------------------------------------------------
		public TSDatabaseConnectionType DatabaseConnectionType
		{
			get
			{
				return databaseconnectiontype;
			}
			set
			{
				databaseconnectiontype = value;
			}
		}
//--------------------------------------------------------------------------------------------
		public string ConnectionString
		{
			get
			{
				return connectionstring;
			}
			set
			{
				connectionstring = value;
			}
		}
//--------------------------------------------------------------------------------------------
		public TSDataTable Query(string query, string tablename)
		{
			if (!Connected)
			{
				throw new Exception("Database is not connected.");
			}

			switch (DatabaseConnectionType)
			{
				case TSDatabaseConnectionType.ADODB:
					object ob;
					ADODB.Recordset adodbrecordset	     = adodbconnection.Execute(query, out ob, -1);
					ob								= null;
					return RecordsetToDataTable(adodbrecordset, tablename);

				case TSDatabaseConnectionType.MySQL:
					MySqlCommand		mysqlcommand		= new MySqlCommand(query, mysqlconnection);
					MySqlDataAdapter	mysqldataadapter	= new MySqlDataAdapter(mysqlcommand);
					TSDataTable		mysqldatatable		= new TSDataTable();
					mysqldataadapter.Fill(mysqldatatable);
					return mysqldatatable;

				case TSDatabaseConnectionType.MSSQL:
					SqlConnection	connection			= new SqlConnection(ConnectionString);
					SqlCommand	command				= new SqlCommand(query, connection);
					command.CommandType					= CommandType.Text;
					SqlDataAdapter	sda					= new SqlDataAdapter(command);
					TSDataTable		datatable			= new TSDataTable(tablename);
					sda.Fill(datatable);
					connection.Close();
					return datatable;

				default:
					return null;
			}
		}
//--------------------------------------------------------------------------------------------
		private TSDataTable RecordsetToDataTable(ADODB.Recordset recordset, string tablename)
		{
			TSDataTable datatable = new TSDataTable(tablename);
			if (recordset.Fields.Count > 0)
			{
				for (int i = 0; i < recordset.Fields.Count; i++) 
				{
					string		columnname	= recordset.Fields[i].Name;
					Type		type		= null;
					string		typestr		= "System.DBNull";
					try
					{
						type				= recordset.Fields[i].Value.GetType();
					}
					catch {}
					if (typestr == "System.DBNull")
						datatable.Columns.Add(columnname);
					else
						datatable.Columns.Add(columnname, type);
				}
			}
			
			if (recordset.State != 0 && !recordset.EOF)
			{
				recordset.MoveFirst();
				while (!recordset.EOF)
				{
					DataRow datarow		= datatable.NewRow();
					for (int i = 0; i < recordset.Fields.Count; i++)
					{
						datarow[i]			= recordset.Fields[i].Value;
					}
					datatable.Rows.Add(datarow);
					recordset.MoveNext();
				}
			}
			return datatable;
		}
//--------------------------------------------------------------------------------------------
		public static TSDatabaseConnectionType GetConnectionTypeFromString(string type)
		{
			switch (type.ToLower())
			{
				case "access":
					return TSDatabaseConnectionType.Access;
				
				case "adodb":
					return TSDatabaseConnectionType.ADODB;

				case "mysql":
					return TSDatabaseConnectionType.MySQL;
				
				case "mssql":
					return TSDatabaseConnectionType.MSSQL;

				default:
					return TSDatabaseConnectionType.ADODB;
			}
		}
//--------------------------------------------------------------------------------------------
	}
}