using System;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;


namespace DarkStrideToolbox
{
	//This class holds a recordset in it.  It can minipulate the recordset in several ways.
	public class DSRSet
	{
		#region Properties
		private DataSet m_oDataSet = null;
		private DataRowCollection m_oDataRows = null;
		private DB m_oDB = null;
		private int m_nCurrentRow = -1;
		#endregion
		

		public DSRSet(){}


		//Move the cursor around between records
		public void MoveFirst()
		{
			const string sRoutineName = "DarkStrideToolbox.RSet.MoveFirst";

			try
			{
				m_nCurrentRow = 0;
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
		}

		public bool MoveNext()
		{
			const string sRoutineName = "DarkStrideToolbox.RSet.MoveNext";
			bool bEOF = false;

			try
			{
				m_nCurrentRow++;
				bEOF = EOF();
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return( bEOF );
		}

		public bool EOF()
		{
			const string sRoutineName = "DarkStrideToolbox.RSet.EOF";
			bool bRetVal = false;

			try
			{
				bRetVal = ( m_nCurrentRow >= m_oDataRows.Count || m_nCurrentRow < 0 );
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return( bRetVal );
		}


		//Some functions want the data row of the recordset object.  This returns it.
		public DataRow GetDataRow( int nIndex )
		{
			const string sRoutineName = "DarkStrideToolbox.RSet.GetDataRow";
			DataRow oRetVal = null;

			try
			{
				if( nIndex < Count() && nIndex >= 0 )
				{
					oRetVal = m_oDataRows[ nIndex ];
				}
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return( oRetVal );
		}


		//Get the data from the database.
		public bool GetBool( string sColumn )
		{
			const string sRoutineName = "DarkStrideToolbox.RSet.GetBool";
			DataRow oRow = null;
			bool bRetVal = false;

			try
			{
				if( EOF() == false )
				{
					oRow = m_oDataRows[ m_nCurrentRow ];
					bRetVal = (bool)oRow[sColumn];
				}
				else 
				{
					throw new System.Exception( "Current location is EOF." );
				}
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return( bRetVal );
		}

		public string GetString( string sColumn )
		{
			const string sRoutineName = "DarkStrideToolbox.RSet.GetString";
			DataRow oRow = null;
			string sRetVal = "";

			try
			{
				if( EOF() == false )
				{
					oRow = m_oDataRows[ m_nCurrentRow ];
					sRetVal = (string)oRow[sColumn];
				}
				else 
				{
					throw new System.Exception( "Current location is EOF." );
				}
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return( sRetVal );
		}

		public int GetInt( string sColumn )
		{
			const string sRoutineName = "DarkStrideToolbox.RSet.GetInt";
			DataRow oRow = null;
			int nRetVal = 0;

			try
			{
				if( EOF() == false )
				{
					oRow = m_oDataRows[ m_nCurrentRow ];
					nRetVal = (int)oRow[sColumn];
				}
				else 
				{
					throw new System.Exception( "Current location is EOF." );
				}
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return( nRetVal );
		}


		public int Count()
		{
			const string sRoutineName = "DarkStrideToolbox.RSet.MoveFirst";

			try
			{
				return( m_oDataRows.Count );
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
		}

		public bool OpenRSet( string sSQL, DB oDB )
		{
			const string sRoutineName = "DarkStrideToolbox.RSet.MoveFirst";

			try
			{
				DataTable oTable;

				//Save the DB, incase we had a different DB
				this.DB = oDB;

				m_oDataSet = oDB.OpenDataSet( sSQL,"MyRSet" );
				oTable = m_oDataSet.Tables[ "MyRSet" ];
				m_oDataRows = oTable.Rows;
			
				m_nCurrentRow = -1;
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return( true );
		}

		public bool OpenRSet( string sSQL )
		{
			const string sRoutineName = "DarkStrideToolbox.RSet.MoveFirst";

			try
			{
				bool bRetVal;

				if( this.DB == null )
				{
					return( false );
				}
				else
				{
					bRetVal = OpenRSet( sSQL,m_oDB );
					return( bRetVal );
				}			
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
		}



		#region Properties
		public object DB
		{
			get
			{
				return( m_oDB );
			}
			set
			{
				m_oDB = (DB)value;
			}
		}
		#endregion
	}


	//This class holds a DB connection and manages it.  The most commonly used function is OpenRSet.
	public class DB
	{
		#region Properties
		private OleDbConnection m_oCon = null;
		#endregion

		
		public DB(){}


		public bool OpenDB( string sFileName )
		{
			const string sRoutineName = "DarkStrideToolbox.DB.OpenDB";
			string sConnStr351 = "Provider=Microsoft.JET.OLEDB.3.51;Data Source=" + sFileName;
			string sConnStr40  = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + sFileName;
			bool bRetVal = false;

			try
			{
				if( OpenWithProvider( sConnStr351 ) == false )
				{
					if( OpenWithProvider( sConnStr40 ) == true )
					{
						bRetVal = true;
					}
				}
				else
				{
					bRetVal = true;
				}
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return( bRetVal );
		}
		
		//Open up the DB with a specific provider
		private bool OpenWithProvider( string sProvider )
		{
			const string sRoutineName = "DarkStrideToolbox.DB.OpenWithProvider";
			string sEndProvider = "";
			bool bRetVal = false;

			try
			{
				sEndProvider = sProvider;

				//Try to open it
				m_oCon = new OleDbConnection( sEndProvider );
				m_oCon.Open();
				m_oCon.Close();

				//We only get here if the open command worked
				bRetVal = true;			
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return( bRetVal );
		}

		public bool CloseConnection( OleDbConnection oCon )
		{
			const string sRoutineName = "DarkStrideToolbox.DB.CloseConnection";
			bool blnRetVal = false;
			
			try
			{
				if(oCon.State.Equals("Open"))
				{
					oCon.Close();
				}
				oCon.Close();
				oCon.Dispose();
				blnRetVal = true;
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return(blnRetVal);
		}


		//These are some common system types that other functions might need.
		public DataSet OpenDataSet( string sSQL,string sDataSetName )
		{
			const string sRoutineName = "DarkStrideToolbox.DB.OpenDataSet";
			DataSet oDSet = null;
		
			try
			{
				oDSet = new DataSet();

				m_oCon.Open();
				OleDbDataAdapter sAdapter = new OleDbDataAdapter(sSQL,m_oCon);
				sAdapter.Fill(oDSet,sDataSetName);
				m_oCon.Close();
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return( oDSet );
		}

		public DataTable OpenDataTable( string sSQL,string sDataTableName )
		{
			const string sRoutineName = "DarkStrideToolbox.DB.OpenDataTable";
			DataSet oDataSet = null;
			DataTable oDataTable = null;

			try
			{
				oDataSet = OpenDataSet( sSQL,sDataTableName );
				oDataTable = oDataSet.Tables[ sDataTableName ];
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return( oDataTable );
		}

		public DataRowCollection OpenDataRows( string sSQL )
		{
			const string sRoutineName = "DarkStrideToolbox.DB.OpenDataRows";
			DataRowCollection oDataRows = null;
			DataTable oDataTable = null;

			try
			{
				oDataTable = OpenDataTable( sSQL,"MyNewDataRows" );
				oDataRows = oDataTable.Rows;
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return( oDataRows );
		}


		//Execute some SQL... probabely an insert statement
		public int Execute( string sSQL )
		{
			const string sRoutineName = "DarkStrideToolbox.DB.Execute";
			int iReturn = 0;

			try
			{
				m_oCon.Open();
				OleDbCommand oCommand = new OleDbCommand(sSQL,m_oCon);
				iReturn = oCommand.ExecuteNonQuery();
				m_oCon.Close();
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return (iReturn);
		}

		//Return a recordset
		public DSRSet OpenRSet( string sSQL )
		{
			const string sRoutineName = "DarkStrideToolbox.DB.OpenRSet";
			DSRSet oRSet = null;

			try
			{
				oRSet = new DSRSet();
				if( oRSet.OpenRSet( sSQL,this ) == false )
				{
					oRSet = null;
				}
			}
			catch( System.Exception oEx )
			{
				throw new System.Exception( sRoutineName + " Failed.",oEx );
			}			
			return( oRSet );
		}

		

		#region Properties
		public OleDbConnection Connection
		{
			get
			{
				return( m_oCon );
			}
			set
			{
				m_oCon = value;
			}
		}
		#endregion
	}
}
