How to translate database table design to C# Class?

Question

I create two C# Classes Contacts and Customers based on SQL diagram (see the image) just I want see if I am doing it right and I need some advice ? down database diagram and Classes Customer and Contacts

Customer Class


 public class Customer
    {
        public int CustomerID { get; set; }
        public string Title { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Postion { get; set; }
        public Char Gender { get; set; }
        public DateTime BecomeCustomer { get; set; }     
        public DateTime ModifiedDate { get; set; }



        public Customer() { }


        public static bool AddNewCustomer_Contact(Customer cust,Contacts cont)
        {
            try
            {
                // get a configured DbCommand object
                DbCommand comm = GenericDataAccess.CreateCommand();


                //Set the store Proc name 
                comm.CommandText = "AddNewCustomer_Contact";

                //create new parameter @Title 
                DbParameter param = comm.CreateParameter();
                param = comm.CreateParameter();
                param.ParameterName = "@Title";
                param.Value = cust.Title;
                param.DbType = DbType.StringFixedLength;
                comm.Parameters.Add(param);

                //create new parameter @FirstName
                param = comm.CreateParameter();
                param.ParameterName = "@FirstName ";
                param.Value = cust.FirstName;
                param.DbType = DbType.StringFixedLength;
                comm.Parameters.Add(param);

                //create new parameter @LastName
                param = comm.CreateParameter();
                param.ParameterName = "@LastName";
                param.Value = cust.LastName;
                param.DbType = DbType.StringFixedLength;
                comm.Parameters.Add(param);

                //create new parameter @Postion 
                param = comm.CreateParameter();
                param.ParameterName = "@Postion ";
                param.Value = cust.Postion;
                param.DbType = DbType.StringFixedLength;
                comm.Parameters.Add(param);

                //create new parameter @BecomeCustomer
                param = comm.CreateParameter();
                param.ParameterName = "@BecomeCustomer";
                param.Value = DateTime.Now;
                param.DbType = DbType.DateTime;
                comm.Parameters.Add(param);

                //create new parameter @Gender 
                param = comm.CreateParameter();
                param.ParameterName = "@Gender";
                param.Value = cust.Gender;
                param.DbType = DbType.String;
                comm.Parameters.Add(param);

                //create new parameter @ModifiedDate  
                param = comm.CreateParameter();
                param.ParameterName = "@ModifiedDate";
                param.Value = DateTime.Now;
                param.DbType = DbType.StringFixedLength;
                comm.Parameters.Add(param);

                //create new parameter @LabelContactTypeID
                param = comm.CreateParameter();
                param.ParameterName = "@LabelContactTypeID";
                param.Value = cont.LabelContactTypeID;
                param.DbType = DbType.StringFixedLength;
                comm.Parameters.Add(param);

                //create new parameter @ContactDetails
                param = comm.CreateParameter();
                param.ParameterName = "@ContactDetails";
                param.Value = cont.ContactDetail;
                param.DbType = DbType.StringFixedLength;
                comm.Parameters.Add(param);

                //create new parameter @Status
                param = comm.CreateParameter();
                param.ParameterName = "@Status";
                param.Value = cont.Status;
                param.DbType = DbType.StringFixedLength;
                comm.Parameters.Add(param);

                //create new parameter @Notes
                param = comm.CreateParameter();
                param.ParameterName = "@Notes";
                param.Value = cont.Notes;
                param.DbType = DbType.StringFixedLength;
                comm.Parameters.Add(param);


                return (GenericDataAccess.ExecuteNonQuery(comm) != -1);

            }
            catch
            {
                return false;
            }
        }
    }
Contact Class

public class Contacts
{
    public int ContactsID { get; set; }
    public int CustomerID { get;set; }
    public string ContactDetail { get; set; }
    public bool Status { get; set; }
    public int LabelContactTypeID { get; set; }
    public string Notes { get; set; }


}

Answer 1

While designing a class, the first step would be to identify what classes are needed and what are their attributes/responsibilities. From what I understand, the only high level class that you want to represent is a Customer. By Customer being your high level class, I mean that some attributes of Customer can be constituted by other objects (like Name, Contacts, etc.).

In addition, some fields like Id are not of any significance outside the database (I assume that the Id in this case is the primary key of your Customer table). If you are bent on basing your class model on the database design (normalized one), then I would suggest that you 'unnormalize' your design before proceeding on with the class model. The optimization offered by normalization is only within the scope of a relationship model and usually should not extend to a class which is an in memory representation.

Furthermore, as suggested by @Yatrix, it would be better to abstract the data access layer from the actual data model.

Here are some suggestions,

public class Customer
{
    // public int CustomerID { get; set; } // Database specific primary key
    public string Title { get; set; } 
    public string FirstName { get; set; } // Is the setter required to be publicly exposed? 
                                          // Or can it be private to the class?
    public string LastName { get; set; }  // FirstName and LastName can be part of a nested class 
                                          // Name, so that the access is more natural... 
                                          // Customer.Name.FirstName 
    public string Postion { get; set; }
    public Char Gender { get; set; } // Can be an enumeration
    public DateTime BecomeCustomer { get; set; }     
    public DateTime ModifiedDate { get; set; }
    public IList<Contact> Contacts { get; private set; } // The getter can be private and you can 
                                                         // expose only some methods like 
                                                         // Customer.GetContactOfType(type)


    public bool AddContact(Contact contact)
    {
         // ...
    }

    public bool RemoveContact(Contact contact)
    {
         // ...
    }       
}

public class Contact
{
    public string ContactDetail { get; set; } // Can be named as Detail or something more specific
    public bool Status { get; set; } // Can be enumeration
    public ContactType Type { get; set; } // ContactType is an enumeration
    public string Notes { get; set; }
}

Answer 2

Most architecture questions / "am I doing it right?" boil down to "It depends".

Couple ways you can approach this. In a scenario that is not "high performance" and you want your objects to map directly to database tables, then using an ORM like Entity Framework is perfectly fine.

If you want to decouple your domain layer from the logical design of your data store then you should look into something like the repository pattern and use DTOs / straight ADO.NET. It's a lot more code though, more effort overall.

From the little you've shown here though it looks like a straightforward non-high performance concerned app, so I would reach for the simplest thing that works well, which in this case would be entity framework or another ORM.

If you want to use ado .net and plan old data transfer objects for code cleanliness I recommend putting the database access code into separate classes than the data transfer objects (DTOs, in this case, Customer is a DTO, a property bag if you will)

Answer 3

It looks fine, but I would put the data access layer in a different static class as it's not really manipulating the model. A class should be encapsulated to it's data and methods that interact with that data on the model itself.

Also, look into AddWithValue(): http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx

May be easier for you to use.