none
How to load objects from database (OOP) ? RRS feed

  • Question

  • Hi,

    We have some objects (for example Customer and Address). The customer object contains a address object and the address object contains a customer object.

    Now iam lost because how can i fill this objects from the database?

    For eaxample when u got a class database with function loadCustomer and function loadAddress.

    public void loadCustomer() {
     // .................
    
     Customer customer = new Customer()
     customer.ID = dr("CustomerID")
     customer.Address = loadAddress(dr("CustomerID"))
    
     // .................
    }
    
    public Address loadAddress(Guid CustomerID) {
    
     // .................
    
     Address address = new Address()
     address.ID = dr("AddressID")
     Address.Customer = // Iam lost here
    
     // ................. 
    }
    
    

    How can i load a customer object in a address object and vice versa?

    Kind regards,

    Flaminio

    Wednesday, August 18, 2010 9:03 AM

Answers

  • You are doing a very clear OOP mistake:

    you cannot have customer.Address and then Address.Customer

    You should choose either for a customer to have an address "customer.Address" (which seems the right solution to me) or you should have that the address have a customer (this depends on what business requirements you have).

    Because if you have the address of a customer (customer.Address) it is done - you have the information - no need to place it in another attribute.

    So I suggest that you remove address.Customer

    Thanks,


    Ali Hamdar (alihamdar.com)
    • Marked as answer by Flaminio Thursday, August 26, 2010 8:31 AM
    Wednesday, August 18, 2010 9:14 AM
  • Hi,

    There is a mistake in the database conception because your a re making a circular depency between the customer and the address.

    Enven in the OOP, there is this circular dependency.

    Problemes involved :

    Circular dependencies can cause a domino effect when a small local change in one module spreads into other modules and has unwanted global effects (program errors, compile errors). Circular dependencies can also result in infinite recursions or other unexpected failures.

    Circular dependencies may also cause memory leaks by preventing certain very primitive automatic garbage collector from deallocating unused objects.

    Solution :

    I guess the customer could have many addresses.

    So, here is the database schema :

    Customer

    CustomerID (PK)

    Name

    Address

    AdressID (PK)

    Customer (FK reference CustomerID)

    Name

    In the OOP model, you will have :

    Customer

    int CustomerID;

    string Name;

    List<Adress> Adresses; // get -> select * from Adress where Customer = CustomerID

    Address

    int AdressID;

    string Name;

    int CustomerID;

     

    then in your datalayer, you will have something like this :

    using System.Collections.Generic;
    using System.Data.SqlClient;
    
    namespace Test.DbHelper
    {
      public class Address
      {
        public int AddressId { get; set; }
        public int CustomerID { get; set; }
        public string Name { get; set; }
      }
    
      public class Customer
      {
        public int CustomerID { get; set; }
        public string Name { get; set; }
        public List<Address> Addresses
        {
          get
          {
            return dbHelper.GetAddressesByCustomer(this.CustomerID);
          }
        }
    
      }
    
      public class dbHelper
      {
    
        public dbHelper()
        {
          // Nothing in this sample.
        }
    
        public static void CreateCustomer(Customer customer)
        {
          // Save customer.
          using (SqlConnection connection = new SqlConnection(
     "connectionString"))
          {
    
            SqlCommand myCommand = new SqlCommand(); // define the command ...
            SqlParameter paramCustomerID = new SqlParameter();
            paramCustomerID.TypeName = "";//... Name of the parametre in youur procedure
            paramCustomerID.Value = customer.CustomerID;
            paramCustomerID.SqlDbType = System.Data.SqlDbType.Int;
            myCommand.Parameters.Add(paramCustomerID);
    
            SqlParameter paramCustomerName = new SqlParameter();
            paramCustomerName.TypeName = "";//... Name of the parametre in youur procedure
            paramCustomerName.Value = customer.Name;
            paramCustomerName.SqlDbType = System.Data.SqlDbType.VarChar;
            myCommand.Parameters.Add(paramCustomerName);
    
            // That's all for the customer
    
            myCommand.ExecuteNonQuery();
          }
        }
    
        public static void CreateAddress(Address address)
        {
          // Save address.
          using (SqlConnection connection = new SqlConnection(
     "connectionString"))
          {
            SqlCommand myCommand = new SqlCommand(); // define the command ...
            SqlParameter paramAddressID = new SqlParameter();
            paramAddressID.TypeName = "";//... Name of the parametre in youur procedure
            paramAddressID.Value = address.AddressId;
            paramAddressID.SqlDbType = System.Data.SqlDbType.Int;
            myCommand.Parameters.Add(paramAddressID);
    
            SqlParameter paramAddressName = new SqlParameter();
            paramAddressName.TypeName = "";//... Name of the parametre in youur procedure
            paramAddressName.Value = address.Name;
            paramAddressName.SqlDbType = System.Data.SqlDbType.VarChar;
            myCommand.Parameters.Add(paramAddressName);
    
            // Add the refernce of the customer
            SqlParameter paramCustomerID = new SqlParameter();
            paramCustomerID.TypeName = "";//... Name of the parametre in youur procedure
            paramCustomerID.Value = address.CustomerID;
            paramCustomerID.SqlDbType = System.Data.SqlDbType.Int;
            myCommand.Parameters.Add(paramCustomerID);
    
            // That's all for the Adredss
    
            myCommand.ExecuteNonQuery();
          }
        }
    
        public static List<Address> GetAddressesByCustomer(int customerId)
        {
          List<Address> adresses = new List<Address>();
    
          using (SqlConnection connection = new SqlConnection(
          "connectionString"))
          {
    
            SqlCommand myCommand = new SqlCommand(
              string.Format("select * from Address where Customer = {0} ", customerId));
    
            SqlParameter paramCustomerID = new SqlParameter();
            paramCustomerID.TypeName = "";//... Name of the parametre in youur procedure
            paramCustomerID.Value = customerId;
            paramCustomerID.SqlDbType = System.Data.SqlDbType.Int;
            myCommand.Parameters.Add(paramCustomerID);
    
            SqlDataReader reader = myCommand.ExecuteReader();
            try
            {
              while (reader.Read())
              {
                adresses.Add(new Address
                {
                  Name = reader["Name"] as string,
                  CustomerID = int.Parse(reader["Customer"] as string),
                  AddressId = int.Parse(reader["AdressId"] as string)
    
                });
    
              }
            }
            finally
            {
              // Always call Close when done reading.
              reader.Close();
            }
          }
    
    
          return adresses;
    
        }
      }
    
    }
    
    

    If the customer have only one address you should make Customer field of Address as PK.

     

    Regards,

    • Marked as answer by Flaminio Thursday, August 26, 2010 8:31 AM
    Friday, August 20, 2010 10:33 PM

All replies

  • You are doing a very clear OOP mistake:

    you cannot have customer.Address and then Address.Customer

    You should choose either for a customer to have an address "customer.Address" (which seems the right solution to me) or you should have that the address have a customer (this depends on what business requirements you have).

    Because if you have the address of a customer (customer.Address) it is done - you have the information - no need to place it in another attribute.

    So I suggest that you remove address.Customer

    Thanks,


    Ali Hamdar (alihamdar.com)
    • Marked as answer by Flaminio Thursday, August 26, 2010 8:31 AM
    Wednesday, August 18, 2010 9:14 AM
  • Hi Ali Hamdar,

    Thanks for your explanation. The only way i did this is when i need to save a address to the database i need a customerid.

    Database tables (example)

    Customer
    CustomerID
    Name
    ....

    Address
    AddressID
    CustomerID

    The only way to do this (in my opinion) is to create a property CustomerID (Address.CustomerID) on the Address object (what i have now) but it feels so wrong.

    Is this the good way or...?

    Kind regards,

    Flaminio

    Wednesday, August 18, 2010 12:41 PM
  • What is the business requirement here? Do you want to save a Customer with his/her address?

    If this is the case, the you should do as follows:

    The Customer Table should contain the AddressID and you should drop the CustomerId col in the Address table.


    Ali Hamdar (alihamdar.com)
    Wednesday, August 18, 2010 1:44 PM
  • Hi Ali Hamdar,

    The business requirement is: a customer can have one or more addresses.

    Our database is as follows:

    Customer
    CustomerID
    Name
    ....

    Address
    AddressID
    AddressTypeID (TinyInt - enumeration)
    Street
    ....

    Address type can be:
    Primary, invoice or delivery address

    Thanks again.

    Kind regards,

    Flaminio

    Wednesday, August 18, 2010 2:05 PM
  • If your customer can have one or more address then your classes should be like this:

    Address Class containing the attributes (Id, Type, Street,...)

    Customer Class containing a List of addresses (Id, Name, IList<Address>)

    and your database should be like this

    Customer Table

    CustomerID, Name

     

    Address Table

    AddressID, Type, Street,

     

    CustomerAddresses Table

    CustomerID

    AddressId

     

    You need to use a 3rd table to associate the customer with its addresses - the 3rd table will contain only the customerId and the addressId and these 2 columns will constitute the primary key of the table.

    And this table, is reflected in the Customer class with a List of Address class instances IList<Address>


    Ali Hamdar (alihamdar.com)
    Thursday, August 19, 2010 1:09 PM
  • Hi Ali Hamdar,

    Thats so true but i still dont get it. Maybe my problem is my data acces layer. In got a database helper class that contains a save function for each object.

    For our sample:

    public class dbHelper
    {
    
    	public dbHelper()
    	{
    		// Nothing in this sample.
    	}
    
    	public void setCustomer(Customer customer)
    	{
    		// Save customer.
    		
    		// .................
    		
    		SqlParameter paramCustomerID = new SqlParameter();
    		paramCustomerID.value = customer.ID();
    		myCommand.Parameters.Add(paramCustomerID);
    		
    		// .................
    		
    		myCommand.ExecuteNonQuery();
    		
    		// Save customer address.
    		setAddress(customer.PrimaryAddress());
    		
    		// .................
    	}
    
    	public void setAddress(Address address)
    	{
    		// Save address.
    		
    		// .................
    
    		SqlParameter paramAddressID = new SqlParameter();
    		paramAddressID.value = address.ID();
    		myCommand.Parameters.Add(paramAddressID);
    		
    		// Here i need a customerID for the cross table.
    		// I thought something like this but this is the
    		// wrong way right?:
    		SqlParameter paramCustomerID = new SqlParameter();
    		paramCustomerID.value = address.Customer.ID();
    		myCommand.Parameters.Add(paramCustomerID);
    		
    		// .................
    		
    		myCommand.ExecuteNonQuery();
    		
    		// .................
    	}
    }
    

    Do you have some suggestions?

    Kind regards,

    Flaminio

    Friday, August 20, 2010 3:11 PM
  • Hi,

    There is a mistake in the database conception because your a re making a circular depency between the customer and the address.

    Enven in the OOP, there is this circular dependency.

    Problemes involved :

    Circular dependencies can cause a domino effect when a small local change in one module spreads into other modules and has unwanted global effects (program errors, compile errors). Circular dependencies can also result in infinite recursions or other unexpected failures.

    Circular dependencies may also cause memory leaks by preventing certain very primitive automatic garbage collector from deallocating unused objects.

    Solution :

    I guess the customer could have many addresses.

    So, here is the database schema :

    Customer

    CustomerID (PK)

    Name

    Address

    AdressID (PK)

    Customer (FK reference CustomerID)

    Name

    In the OOP model, you will have :

    Customer

    int CustomerID;

    string Name;

    List<Adress> Adresses; // get -> select * from Adress where Customer = CustomerID

    Address

    int AdressID;

    string Name;

    int CustomerID;

     

    then in your datalayer, you will have something like this :

    using System.Collections.Generic;
    using System.Data.SqlClient;
    
    namespace Test.DbHelper
    {
      public class Address
      {
        public int AddressId { get; set; }
        public int CustomerID { get; set; }
        public string Name { get; set; }
      }
    
      public class Customer
      {
        public int CustomerID { get; set; }
        public string Name { get; set; }
        public List<Address> Addresses
        {
          get
          {
            return dbHelper.GetAddressesByCustomer(this.CustomerID);
          }
        }
    
      }
    
      public class dbHelper
      {
    
        public dbHelper()
        {
          // Nothing in this sample.
        }
    
        public static void CreateCustomer(Customer customer)
        {
          // Save customer.
          using (SqlConnection connection = new SqlConnection(
     "connectionString"))
          {
    
            SqlCommand myCommand = new SqlCommand(); // define the command ...
            SqlParameter paramCustomerID = new SqlParameter();
            paramCustomerID.TypeName = "";//... Name of the parametre in youur procedure
            paramCustomerID.Value = customer.CustomerID;
            paramCustomerID.SqlDbType = System.Data.SqlDbType.Int;
            myCommand.Parameters.Add(paramCustomerID);
    
            SqlParameter paramCustomerName = new SqlParameter();
            paramCustomerName.TypeName = "";//... Name of the parametre in youur procedure
            paramCustomerName.Value = customer.Name;
            paramCustomerName.SqlDbType = System.Data.SqlDbType.VarChar;
            myCommand.Parameters.Add(paramCustomerName);
    
            // That's all for the customer
    
            myCommand.ExecuteNonQuery();
          }
        }
    
        public static void CreateAddress(Address address)
        {
          // Save address.
          using (SqlConnection connection = new SqlConnection(
     "connectionString"))
          {
            SqlCommand myCommand = new SqlCommand(); // define the command ...
            SqlParameter paramAddressID = new SqlParameter();
            paramAddressID.TypeName = "";//... Name of the parametre in youur procedure
            paramAddressID.Value = address.AddressId;
            paramAddressID.SqlDbType = System.Data.SqlDbType.Int;
            myCommand.Parameters.Add(paramAddressID);
    
            SqlParameter paramAddressName = new SqlParameter();
            paramAddressName.TypeName = "";//... Name of the parametre in youur procedure
            paramAddressName.Value = address.Name;
            paramAddressName.SqlDbType = System.Data.SqlDbType.VarChar;
            myCommand.Parameters.Add(paramAddressName);
    
            // Add the refernce of the customer
            SqlParameter paramCustomerID = new SqlParameter();
            paramCustomerID.TypeName = "";//... Name of the parametre in youur procedure
            paramCustomerID.Value = address.CustomerID;
            paramCustomerID.SqlDbType = System.Data.SqlDbType.Int;
            myCommand.Parameters.Add(paramCustomerID);
    
            // That's all for the Adredss
    
            myCommand.ExecuteNonQuery();
          }
        }
    
        public static List<Address> GetAddressesByCustomer(int customerId)
        {
          List<Address> adresses = new List<Address>();
    
          using (SqlConnection connection = new SqlConnection(
          "connectionString"))
          {
    
            SqlCommand myCommand = new SqlCommand(
              string.Format("select * from Address where Customer = {0} ", customerId));
    
            SqlParameter paramCustomerID = new SqlParameter();
            paramCustomerID.TypeName = "";//... Name of the parametre in youur procedure
            paramCustomerID.Value = customerId;
            paramCustomerID.SqlDbType = System.Data.SqlDbType.Int;
            myCommand.Parameters.Add(paramCustomerID);
    
            SqlDataReader reader = myCommand.ExecuteReader();
            try
            {
              while (reader.Read())
              {
                adresses.Add(new Address
                {
                  Name = reader["Name"] as string,
                  CustomerID = int.Parse(reader["Customer"] as string),
                  AddressId = int.Parse(reader["AdressId"] as string)
    
                });
    
              }
            }
            finally
            {
              // Always call Close when done reading.
              reader.Close();
            }
          }
    
    
          return adresses;
    
        }
      }
    
    }
    
    

    If the customer have only one address you should make Customer field of Address as PK.

     

    Regards,

    • Marked as answer by Flaminio Thursday, August 26, 2010 8:31 AM
    Friday, August 20, 2010 10:33 PM
  • Hi,

    That's exactly what i have now in my application. The only thing is u need a CustomerID in the class Address. So I thought it would be nice if i can replace the CustomerID with a Customer object. But i know now it is just a stupid idea.

    One more question:
    In my application u got a screen where a employee can create a new address for a customer. When the employee clicks on the add button i add a object to the address list of the customer object (i dont save the object into the database). When the employee clicks on the close button of my customer screen i save the customer object into the database. After that i save every address in the customer object into the database. Is this wrong thinking or... ?

    Kind regards,

    Flaminio

    Monday, August 23, 2010 7:35 AM

  • Hi,

    I guess the employee adds a customer, So when he adds it, you should first store in your database the customer (Id, Name,  Description) without any information about the address.


    Second, When the employee adds an address you should store the address in the database independently from the customer because the customer reference is in the address (store the customerID in the Customer field of address).

    You should not add the object address to the list of addresses of the customer, It has no sense because you already store the reference of the customer in the address.


    Regards,

     

    Monday, August 23, 2010 6:32 PM
  • Hi,

    Ok that's fair enough. The only reason why i store the address first in the list of addresses of the customer is performance. If I have understood you right is better to do the following:

    My collection class Addresses:

    public class Addresses: BindingList<Address>
    {
    	public Addresses()
    	{
    		// Nothing.
    	}
    }
    

    Its better to overide the add function here and create a second thread wich saves the object to the database?

    Kind regards,

    Flaminio

    Is OOP that hard or iam just crazy? :)

    Wednesday, August 25, 2010 7:42 AM
  • Nervermind, It's easy to complicate things when they are simple.

     

    Wednesday, August 25, 2010 8:25 PM