123

using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
namespace SamplesExplorer
{
		
public class SpaceFlightsData
{
	private const string ODC_CONNECTIONSTRING1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=";
	private const string ODC_CONNECTIONSTRING2 = @";Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False";
	private const string ODC_MDB_NAME = "\\InterplanetaryTravels.mdb";

	private DataSet mvarDataSet = new DataSet();
    private OleDbConnection mvarConnection = new OleDbConnection();

    public SpaceFlightsData()
	{
		string dbName = clsSamplesPath.AccessDBPath + ODC_MDB_NAME;
		string ConnectionString = ODC_CONNECTIONSTRING1 + dbName + ODC_CONNECTIONSTRING2;
        OleDbConnection connection = new OleDbConnection(ConnectionString);
		this.Connection = connection;
	}


	public OleDbConnection Connection
	{
		get{ return mvarConnection;}
        set{ mvarConnection = value;GetTables();}
	}

    private void GetTables()
	{
        OleDbCommand sc = new OleDbCommand();
        DataTable dt = new DataTable();

        System.Data.DataSet mvarDataSet = new System.Data.DataSet("SpaceFlights");

        sc.Connection = mvarConnection;

        try
		{
				if ( mvarConnection.State != ConnectionState.Open )
					mvarConnection.Open();
		}
        catch(OleDbException e)
		{
			MessageBox.Show(e.Message, "");
		}

        sc.CommandType = CommandType.TableDirect;

        GetTable(sc, "SpaceFlights");
        GetTable(sc, "SpaceFlightStatuses");
        GetTable(sc, "SpaceFlightTravelers");
        GetTable(sc, "SpaceLines");
        GetTable(sc, "SpacePorts");
        GetTable(sc, "Travelers");

	}

   private void GetTable(OleDbCommand sc, string TableName)
   {
        OleDbDataAdapter da = new OleDbDataAdapter();
        sc.CommandText = TableName;
        da.SelectCommand = sc;
        da.Fill(mvarDataSet, TableName);
   }

	public DataSet SpaceFlightStatusesHierarchy
	{
		get{return mvarDataSet;}
	}
	public DataTable Colonies
	{
		get{return mvarDataSet.Tables["Colonies"];}
	}
	public DataTable SpaceFlights
	{
		get{return mvarDataSet.Tables["SpaceFlights"];}
	}
	public DataTable SpaceFlightStatuses
	{
		get{return mvarDataSet.Tables["SpaceFlightStatuses"];}
	}
	public DataTable SpaceFlightTravelers
	{
		get{return mvarDataSet.Tables["SpaceFlightTravelers"];}
	}
	public DataTable SpaceLines
	{
		get{return mvarDataSet.Tables["SpaceLines"];}
	}
	public DataTable SpacePorts
	{
		get{return mvarDataSet.Tables["SpacePorts"];}
	}
	public DataTable Travelers
	{
		get{return mvarDataSet.Tables["Travelers"];}
	}
	public DataTable InvoiceDetails
	{
		get{return mvarDataSet.Tables["InvoiceDetails"];}
	}
	public DataTable Invoices
	{
		get{return mvarDataSet.Tables["Invoices"];}
	}
	public DataTable Products
	{
		get{return mvarDataSet.Tables["Products"];}
	}

    public DataSet GetTravelers(int numberToReturn)
	{
		return this.GetTravelers( numberToReturn, true );
	}

    public DataSet GetTravelers(int numberToReturn, bool bOrderBy)
	{
        OleDbDataAdapter da = new OleDbDataAdapter();
        OleDbCommand sc = new OleDbCommand();
        DataSet ds;
        ds = new DataSet();

        sc.Connection = mvarConnection;
        if ( mvarConnection.State == ConnectionState.Closed )
            mvarConnection.Open();

		sc.CommandType = CommandType.Text;

        string strSQL;
        strSQL = "SELECT TOP " + numberToReturn + " Travelers.FirstName, Travelers.LastName, Travelers.Address, Travelers.City, Travelers.State, Travelers.Zip FROM Travelers";
        if( bOrderBy )
			strSQL = strSQL + "\n\r" + "ORDER BY Travelers.LastName";

        sc.CommandText = strSQL;
        da.SelectCommand = sc;

        //try
		{
            da.Fill(ds);
		}
        //catch
		//{
		//}

        return ds;
	}


    public DataSet GetSpaceTravelers( int nFlightID )
	{
        OleDbDataAdapter da = new OleDbDataAdapter();
        OleDbCommand sc = new OleDbCommand();
        DataSet ds = new DataSet();
        sc.Connection = mvarConnection;
        if ( mvarConnection.State == ConnectionState.Closed )
            mvarConnection.Open();

		sc.CommandType = CommandType.Text;

        string strSQL = "";

        //   Note: use INNER JOIN, not JOIN (Access-specific?)
        strSQL = "SELECT TOP 10 SpaceFlightTravelers.SpaceFlightID, SpaceFlightTravelers.TravelerFlightID, Travelers.FirstName, Travelers.LastName, Travelers.Address, Travelers.City, Travelers.State, Travelers.Zip" + "\n\r";
        strSQL = strSQL + "FROM SpaceFlightTravelers " + "\n\r";
        strSQL = strSQL + "INNER JOIN Travelers ON Travelers.CustomerID = SpaceFlightTravelers.TravelerFlightID " + "\n\r";
        strSQL = strSQL + "WHERE SpaceFlightTravelers.SpaceFlightID = " + nFlightID.ToString();

        sc.CommandText = strSQL;
        da.SelectCommand = sc;

        //try
		{
            da.Fill(ds);
		}
        //catch 
		//{
		//}

        return ds;

    }

}	//	end class

}	//	end namespace