Thank you for this information. I am working on my senior project and used a slightly different connection, but following the pattern that you used I was able to make mine work.
My code is somewhat like this...(some information changed to make it more generic)
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Odbc;
namespace AccessPackage
{
public class Packages : IEnumerable
{
//This class was created with the help of
//
http://www.tek-tips.com/faqs.cfm?fid=3363
//provided by (c) 2003 Chip Holland and
//Tek-Tips.com (Thanks, guys)
public Packages()
{
packageCollection = new ArrayList();
}
public void CreatePackages()
{
packageCollection = new ArrayList();
DataTable rt = new DataTable();
DataSet ds = new DataSet();
OdbcDataAdapter da = new OdbcDataAdapter();
OdbcConnection con = new OdbcConnection(GetConnection());
String sql = "SELECT * FROM yourTable";
OdbcCommand cmd = new OdbcCommand(sql, con);
da.SelectCommand = cmd;
da.Fill(ds);
try
{
rt = ds.Tables[0];
}
catch
{
rt = null;
}
//datareader for reading values from table
con.Open();
OdbcDataReader dr = cmd.ExecuteReader();
//this cycle should list all tables in mysql default database
while (dr.Read())
{
String Column1 = dr.GetValue(1).ToString();
String Column2 = dr.GetValue(2).ToString();
String Column3 = dr.GetValue(3).ToString();
String Column4 = dr.GetValue(4).ToString();
pkg = new Package(Column1, Column2, Column3, Column4);
packageCollection.Add(pkg);
//Console.WriteLine(dr.GetValue(0)); //for debugging
//Console.WriteLine(dr.GetValue(1)); //for debugging
}
//Close up connection and datareader
dr.Close();
con.Close();
}
private static string GetConnection()
{
//localhost = 127.0.0.1
return "DRIVER={MySQL ODBC 3.51 Driver};Server=127.0.0.1;Database=yourDatabaseName;User=root;Password=yourMySQLPassword;Option=3;";
}
public void Add(Package pkg)
{
// Ignore return value
packageCollection.Add(pkg);
}
public void Remove(Package pkg)
{
packageCollection.Remove(pkg);
}
public bool Count(int index)
{
bool isValidIndex = (index < (packageCollection.Count - 1));
return isValidIndex;
}
public Package this[int index]
{
// invalid index will throw an exception so added
// code so if it is not valid force index of 0
get
{
if (index < 0 || index >= packageCollection.Count)
index = 0;
return (Package)packageCollection[index];
}
set
{
if (index < 0 || index > packageCollection.Count)
{ //System.Windows.Forms.MessageBox.Show(
// "This Package cannot be created");
}
else
{
packageCollection[index] = value;
}
}
}
#region Implementation of IEnumerable
public System.Collections.IEnumerator GetEnumerator()
{
return new PackageEnumerator(this);
}
#endregion
private class PackageEnumerator : IEnumerator
{
private Packages packageReference;
private int location;
public PackageEnumerator(Packages PackageRef)
{
this.packageReference = PackageRef;
//location = -1;
}
#region Implementation of IEnumerator
public void Reset()
{
location = -1;
}
public bool MoveNext()
{
location++;
return (location <= (packageReference.packageCollection.Count - 1));
}
public object Current
{
get
{
if ((location < 0) || (location >
packageReference.packageCollection.Count))
{
return null;
}
else
{
return packageReference.packageCollection[location];
}
}
}
#endregion
}
private ArrayList packageCollection;
//Package is the class of objects that is held by the packageCollection
private Package pkg;
}
}