From: Jordan Sparks
Date: October 6 2004 3:14pm
Subject: RE: Better way to get all columns in the DataReader
List-Archive: http://lists.mysql.com/dotnet/17
Message-Id: <02a801c4abb7$2e715280$6401a8c0@jordans>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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;
 		}


}

       
Jordan Sparks
 

-----Original Message-----
From: Guy Platt [mailto:guy@stripped] 
Sent: Wednesday, October 06, 2004 1:53 AM
To: dotnet@stripped
Subject: Better way to get all columns in the DataReader


There must a be a better way to get all the columns in a DataReader and 
map them to a class than the way I am doing it. Can anyone help me 
rewrite the following?

Thanks
   Guy
P.S. The code has been edited (including removing a check to see whether

the Dr returned any rows).


    public class MemberDetail
    {
        public int MemberNrr;
        public string FirstName;
        public string LastName;
        public string Password;
        public string TelephoneNr;
        public string MobileNr;
        public string CancelDate;
    }

public static MemberDetail GetMemberDetails(int memberNr)
        {
            MySqlConnection myConn;
            MySqlCommand myCmd = new MySqlCommand();
            MySqlDataReader Dr = null;
            myConn = new 
MySqlConnection(ConfigurationSettings.AppSettings["MySqlConnectionString
"]); 

            myConn.Open();
            myCmd.Connection = myConn;
            myCmd.CommandText = "SELECT * FROM ppmr_members WHERE 
ppmr_members.MembersNumber = " + memberid  ;
            Dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
            Dr.Read();

            MemberDetail myMemberDetail = new MemberDetail();

            myMemberDetail.MembersNumber = 
Convert.ToInt32(Dr[Dr.GetOrdinal("MemberNr")]);
            myMemberDetail.FirstName = 
Dr[Dr.GetOrdinal("FirstName")].ToString();
            myMemberDetail.LastName = 
Dr[Dr.GetOrdinal("LastName")].ToString();
            myMemberDetail.TelephoneNr = 
Dr[Dr.GetOrdinal("Telephone")].ToString();
            myMemberDetail.MobileNr = 
Dr[Dr.GetOrdinal("Mobile")].ToString();
            if (Dr[Dr.GetOrdinal("CancelDate")] != DBNull.Value)
                myMemberDetails.CancelDate = 
Dr[Dr.GetOrdinal("CancelDate")].ToString();
            else
                 myMemberDetail.CancelDate = String.Empty;

           
            Dr.Close();
            myConn.Close();
            return myMemberDetail;
        }