Separate your data connection into a separate object. All classes in your program then use this one object. So you have only one place in the entire program that is concerned with data retreival. I did the following code in a hurry, so it is messy and buggy. Also, it sort of shows how to get an array of members rather than just one, but the idea is the same. For functions that act on multiple Members, I would actually use a separate class called Members (plural). I also have special classes to convert back and forth between mysql strings and .NET data types. Details of those are not shown and I am sure there is a better way to do that with TypeConverters. There are also a lot of other improvements you could make to my example, but it gives you an idea of how I do it.

public class MemberDetail//I would recommend renaming this to Member and renaming your db table to member as well for consistency { public int MemberNr; public string FirstName; public string LastName; public string Password; public string TelephoneNr; public string MobileNr; public string CancelDate;

public static bool GetMemberDetails(int memberNr) { //you will have already created an instance of MemberDetail. This function then populates it. string command="SELECT * FROM ppmr_members WHERE ppmr_members.MembersNumber = " + memberid.ToString() ; DataConnection dcon=new DataConnection(); DataTable table=dcon.GetTable(command); //MemberDetail[] List=new MemberDetail[table.Rows.Count]; //for(int i=0;i<List.Length;i++){ //List[i]=new MemberDetail(); MemberNr= FromMysql.Int (table.Rows[0][0].ToString());//List[i].MemberNr=...[i][0].ToString()); FirstName= FromMysql.String(table.Rows[0][1].ToString()); LastName= FromMysql.String(table.Rows[0][2].ToString()); TelephoneNr= etc. //return List } }

public class DataConnection{ ///<summary>This data adapter is used for all queries to the database.</summary> private MySqlDataAdapter da; ///<summary>This is the connection that is used by the data adapter for all queries.</summary> private MySqlConnection con; ///<summary>Used to get very small bits of data from the db when the data adapter would be overkill. For instance retrieving the response after a command is sent.</summary> private MySqlDataReader dr; ///<summary>Stores the string of the command that will be sent to the database.</summary> private MySqlCommand cmd; ///<summary>After inserting a row, this variable will contain the primary key for the newly inserted row. This can frequently save an additional query to the database.</summary> public int InsertID;

///<summary>Constructor sets the connection values.</summary> public DataConnection(){ con=new MySqlConnection( "Server="+FormConfig.ComputerName +";Database="+FormConfig.Database +";User ID="+FormConfig.User +";Password="+FormConfig.Password); //dr = null; cmd = new MySqlCommand(); cmd.Connection=con; //table=new DataTable(); }

///<summary>Fills table with data from the database.</summary> public DataTable GetTable(string command){ cmd.CommandText=command; DataTable table=new DataTable(); try{ da=new MySqlDataAdapter(cmd); da.Fill(table); } catch(MySqlException e){ MessageBox.Show("MySQL Error: "+e.Message); } catch{ MessageBox.Show("Error: "+cmd.CommandText); } finally{ con.Close(); } return table; }

///<summary>Sends a non query command to the database and returns the number of rows affected. If true, then InsertID will be set to the value of the primary key of the newly inserted row.</summary>summary> public int NonQ(string command){ return NonQ(command,false); }

public int NonQ(string command,bool getInsertID){ cmd.CommandText=command; int rowsChanged=0; try{ con.Open(); rowsChanged=cmd.ExecuteNonQuery(); if(getInsertID){ cmd.CommandText="SELECT LAST_INSERT_ID()";

dr=(MySqlDataReader)cmd.ExecuteReader(); if(dr.Read())

InsertID=PIn.PInt(dr[0].ToString()); } } catch(MySqlException e){ MessageBox.Show("Error: "+e.Message+","+cmd.CommandText); } //catch{ // MessageBox.Show("Error: "+); //} finally{ con.Close(); } return rowsChanged; }

}