Object Design Best Practice

[Steve]

I am building an object library for tables in a database. What is the
best practice for creating objects like this?

For example, say I have the following tables in my database:
User:
- Id
- FirstName
- LastName
- CompanyId (many-to-one [Users to Company])

Company:
- Id
- Name
- Address
- NumberOfEmployees

1. Is it correct to create two objects (User, Company) using table
columns as object properties, have a Save method for each object to
insert and update the table, and pass the identifier (Id) and database
connection string into the constructor to load the object using data
from the table?

2. Should I load ALL of the object's properties with the matching
data from the table in the constructor, therefore causing 1 database
query when instantiating the object [i.e. User u = new
User(connectionString, userId)]?

3. Should the User object have a property that returns a Company
object instead of just the CompanyId? If so, should I load the
Company object in the User object's constructor, therefore causing 2
database queries?
For example:
public User(string connectionString, System.Guid userId)
{
// Query User table (select * from User where Id = userId) into a
DataTable (dt)
// ...

this._connectionString = connectionString;
this._id = userId; // Defined as: System.Guid _id
this._firstName = dt.Rows[0]["FirstName"]; // Defined as: string
_firstName
this._lastName = dt.Rows[0]["LastName"]; // Defined as: string
_lastName
this._company = new Company(connectionString, dt.Rows[0]
["CompanyId"]); // Defined as: Company _company
}

4. Would it be better to instantiate and fill the Company object when
the User object is created, or when the Company object is first used?
For example:
private Company _company = null;
public Company MyCompany
{
get
{
if (this._company == null)
{
// Company object's constructor is similar to the User
object's from (3) above
this._company = new Company(this._connectionString,
this._companyId);
}
return this._company;
}
set
{
this._company = value;
}
}

This is a simple example of what I am trying to do, but hopefully you
get the gist of it. Basically, I need objects linked to the database,
but want to minimize database queries and unnecessary data in memory
(I may never need to know the number of employees in the company, but
I'd want the address often). Any input is appreciated.

Thanks,
Steve

 

[PS]

I am building an object library for tables in a database. What is the
best practice for creating objects like this?

For example, say I have the following tables in my database:
User:
- Id
- FirstName
- LastName
- CompanyId (many-to-one [Users to Company])

Company:
- Id
- Name
- Address
- NumberOfEmployees

1. Is it correct to create two objects (User, Company) using table
columns as object properties, have a Save method for each object to
insert and update the table, and pass the identifier (Id) and database
connection string into the constructor to load the object using data
from the table?

Passing the connection string into the object is getting the object too
involved in creating itself and too involved with the database. Start with a
Builder class with methods like
public static BuildUser(string ID)
{...}
and let that class deal with creating objects and talking to the database.

2. Should I load ALL of the object's properties with the matching
data from the table in the constructor, therefore causing 1 database
query when instantiating the object [i.e. User u = new
User(connectionString, userId)]?

I always do. Otherwise you will need to use flags as to know was loaded and
then get more information later. Can get complicated.

3. Should the User object have a property that returns a Company
object instead of just the CompanyId? If so, should I load the
Company object in the User object's constructor, therefore causing 2
database queries?

You can use a collection of companies as a lookup table., e.g

public Company Company {
get {
return companyCollection[this.CompanyID];
}

or build the company as needed like

public Company Company
{
get {
if(this.company == null)
this.company = Builder.BuildCompany(this.CompanyID);
return this.company;

The second method will get you a more current version of the Company without
constantly hitting the database every time you use the Company property.

I just see that you did something like this below!!!

For example:
public User(string connectionString, System.Guid userId)
{
// Query User table (select * from User where Id = userId) into a
DataTable (dt)
// ...

this._connectionString = connectionString;
this._id = userId; // Defined as: System.Guid _id
this._firstName = dt.Rows[0]["FirstName"]; // Defined as: string
_firstName
this._lastName = dt.Rows[0]["LastName"]; // Defined as: string
_lastName
this._company = new Company(connectionString, dt.Rows[0]
["CompanyId"]); // Defined as: Company _company
}

Don't use data tables. Use collections like KeyedCollection is good for ID
based objects (.Net 2.0 feature)

4. Would it be better to instantiate and fill the Company object when
the User object is created, or when the Company object is first used?
For example:
private Company _company = null;
public Company MyCompany
{
get
{
if (this._company == null)
{
// Company object's constructor is similar to the User
object's from (3) above
this._company = new Company(this._connectionString,
this._companyId);
}
return this._company;
}
set
{
this._company = value;

here you can also set the companyID value as this is what needs to be sent
back to the database
this.companyID = value.CompanyID;

}
}

This is a simple example of what I am trying to do, but hopefully you
get the gist of it. Basically, I need objects linked to the database,
but want to minimize database queries and unnecessary data in memory
(I may never need to know the number of employees in the company, but
I'd want the address often). Any input is appreciated.

Thanks,
Steve

Seems like you got most of it worked out. Just don't make your User and
Company objects do everything and get cluttered with database calls etc.
Even the saving could be passed back to another object like

public class User
{..
public void Save()
{
DatabaseClass.Save(this);

You will need to track if an object is new or dirty too. I use a pattern
called Unit Of Work for this but this might be overkill for now.

PS

 

[sloan]

Personally ( and some others agree) that mixing the objects with the code
that creates them is a no-no.
Its cluttered, and assumes 1 kind of creation method (usually a database).

I think a good suggestion is

User

and then a

UserManager class

which creates User objects as needed.


If your User has a company, then you can create a User.CurrentCompany
property.


see
6/5/2006
Custom Objects and Tiered Development II // 2.0
http://sholliday.spaces.live.com/blog/


Personally, I use IDataReaders to load up my objects from the database.
I also include a RowVersion timestamp (in sql server) to implement a
optmistic concurrency. a timestamp in sql server is a value that gets auto
incremented whenever a row is updated.

I am building an object library for tables in a database. What is the
best practice for creating objects like this?

For example, say I have the following tables in my database:
User:
- Id
- FirstName
- LastName
- CompanyId (many-to-one [Users to Company])

Company:
- Id
- Name
- Address
- NumberOfEmployees

1. Is it correct to create two objects (User, Company) using table
columns as object properties, have a Save method for each object to
insert and update the table, and pass the identifier (Id) and database
connection string into the constructor to load the object using data
from the table?

2. Should I load ALL of the object's properties with the matching
data from the table in the constructor, therefore causing 1 database
query when instantiating the object [i.e. User u = new
User(connectionString, userId)]?

3. Should the User object have a property that returns a Company
object instead of just the CompanyId? If so, should I load the
Company object in the User object's constructor, therefore causing 2
database queries?
For example:
public User(string connectionString, System.Guid userId)
{
// Query User table (select * from User where Id = userId) into a
DataTable (dt)
// ...

this._connectionString = connectionString;
this._id = userId; // Defined as: System.Guid _id
this._firstName = dt.Rows[0]["FirstName"]; // Defined as: string
_firstName
this._lastName = dt.Rows[0]["LastName"]; // Defined as: string
_lastName
this._company = new Company(connectionString, dt.Rows[0]
["CompanyId"]); // Defined as: Company _company
}

4. Would it be better to instantiate and fill the Company object when
the User object is created, or when the Company object is first used?
For example:
private Company _company = null;
public Company MyCompany
{
get
{
if (this._company == null)
{
// Company object's constructor is similar to the User
object's from (3) above
this._company = new Company(this._connectionString,
this._companyId);
}
return this._company;
}
set
{
this._company = value;
}
}

This is a simple example of what I am trying to do, but hopefully you
get the gist of it. Basically, I need objects linked to the database,
but want to minimize database queries and unnecessary data in memory
(I may never need to know the number of employees in the company, but
I'd want the address often). Any input is appreciated.

Thanks,
Steve

 

[Rick]

Beware! The path you are heading down has no end. Save yourself some time
and look at nHibernate:

http://www.hibernate.org/343.html

 

[RobinS]

Comments below.

I am building an object library for tables in a database. What is the
best practice for creating objects like this?

For example, say I have the following tables in my database:
User:
- Id
- FirstName
- LastName
- CompanyId (many-to-one [Users to Company])

Company:
- Id
- Name
- Address
- NumberOfEmployees

1. Is it correct to create two objects (User, Company) using table
columns as object properties, have a Save method for each object to
insert and update the table, and pass the identifier (Id) and database
connection string into the constructor to load the object using data
from the table?

Yes to the first part, no to the second. You should have a separate class
for each object, and in the class you can put your Create and Save methods
as well as your properties.

You should have your business layer call your data access layer, which will
pass back a data table or dataset of information for your business layer to
handle. Then only the data access layer holds the connectionstring and
connects to the database, etc. (example below)

2. Should I load ALL of the object's properties with the matching
data from the table in the constructor, therefore causing 1 database
query when instantiating the object [i.e. User u = new
User(connectionString, userId)]?

Yes, although like I said, you need to extract the data access part of it.
I make my default constructor private, and use a factory method like this.
Mine's in VB, but I'm going to try to translate it to C#; it should give
you the general idea.

public static Company Create(int compID)
{
Company comp;
if (compID = 0)
{
comp = New Company();
comp.Name = String.Empty;
comp.Address = String.Empty;
comp.NumberOfEmployees = 0;
}
else
{
DataTable dt;
//call the data access layer to return a datatable with one row
// containing the company you are looking for
dt = DAC.ExecuteDataTable("CompanyRetrieveByID_sp",
DAC.Parameter("ID", compID);
comp = New Company();
comp.Name = dt.Rows[0]("Name").ToString;
//load rest of properties
}
return comp;
}

In my DAC, this runs a procedure that takes the stored procedure name and a
parameter array as arguments. It opens the connection, adds the parameters
to the command object, executes the query, and returns the data table.

3. Should the User object have a property that returns a Company
object instead of just the CompanyId? If so, should I load the
Company object in the User object's constructor, therefore causing 2
database queries?
For example:
public User(string connectionString, System.Guid userId)
{
// Query User table (select * from User where Id = userId) into a
DataTable (dt)
// ...

this._connectionString = connectionString;
this._id = userId; // Defined as: System.Guid _id
this._firstName = dt.Rows[0]["FirstName"]; // Defined as: string
_firstName
this._lastName = dt.Rows[0]["LastName"]; // Defined as: string
_lastName
this._company = new Company(connectionString, dt.Rows[0]
["CompanyId"]); // Defined as: Company _company
}

If you want to have a company instance in the User class, add a company
instance. Then load it when you load the user class by calling the Create
function (or constructor) of the Company class.

this._company = company.Create(this._companyId);

4. Would it be better to instantiate and fill the Company object when
the User object is created, or when the Company object is first used?
For example:
private Company _company = null;
public Company MyCompany
{
get
{
if (this._company == null)
{
// Company object's constructor is similar to the User
object's from (3) above
this._company = new Company(this._connectionString,
this._companyId);
}
return this._company;
}
set
{
this._company = value;
}
}

It depends on how much you are going to use this. You can always go get it
later. I would probably load it, or at least the parts you care about, like
company name, if you think you are going to display it anywhere.

This is a simple example of what I am trying to do, but hopefully you
get the gist of it. Basically, I need objects linked to the database,
but want to minimize database queries and unnecessary data in memory
(I may never need to know the number of employees in the company, but
I'd want the address often). Any input is appreciated.

Thanks,
Steve

You might want to check out "Doing Objects in VB2005" by Deborah Kurata. I
know it's in VB, but you can probably figure it out. It explains how to
separate your layers, and you end up building a small application doing
that, which is the best way to learn. She talks about it from soup to nuts,
from designing your classes to adding the data access layer to doing your
forms. I found it really useful and very clarifying and easy to understand.

BTW, if you want to do a list of your objects, that should be another
class, and if you are going to bind them to a datagridview or something,
use a BindingList(Of T).

Good luck.
Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.

 

[Steve]

Yes to the first part, no to the second. You should have a separate class
for each object, and in the class you can put your Create and Save methods
as well as your properties.

You should have your business layer call your data access layer, which will
pass back a data table or dataset of information for your business layer to
handle. Then only the data access layer holds the connectionstring and
connects to the database, etc. (example below)

2. Should I load ALL of the object's properties with the matching
data from the table in the constructor, therefore causing 1 database
query when instantiating the object [i.e. User u = new
User(connectionString, userId)]?

Yes, although like I said, you need to extract the data access part of it.
I make my default constructor private, and use a factory method like this.
Mine's in VB, but I'm going to try to translate it to C#; it should give
you the general idea.

public static Company Create(int compID)
{
Company comp;
if (compID = 0)
{
comp = New Company();
comp.Name = String.Empty;
comp.Address = String.Empty;
comp.NumberOfEmployees = 0;
}
else
{
DataTable dt;
//call the data access layer to return a datatable with one row
// containing the company you are looking for
dt = DAC.ExecuteDataTable("CompanyRetrieveByID_sp",
DAC.Parameter("ID", compID);
comp = New Company();
comp.Name = dt.Rows[0]("Name").ToString;
//load rest of properties
}
return comp;

}

In my DAC, this runs a procedure that takes the stored procedure name and a
parameter array as arguments. It opens the connection, adds the parameters
to the command object, executes the query, and returns the data table.

This is basically what I am doing now. I have a DataLayer object that
does all of the DB-specific functions (GetDataSet/Table, ExecNonQuery,
Open/CloseConnection, etc). In the User example, it would look like
this:
public User(string connectionString, System.Guid userId)
{
// Query User table (select * from User where Id = userId) into a
DataTable (dt)
DataLayer.DBClient db = new DataLayer.DBClient(connectionString);
db.CommandType = System.Data.CommandType.StoredProcedure;
db.CommandText = "MY_GetUser_QRY";
db.AddParameter("@UserId", userId);
System.Data.DataTable dt = db.GetDataTable();

// ...
}

Is that more in-line with what you're doing? I see your point about
having a seperate "factory" method that allows you to create a new
object or load it from the database, but is there a disadvantage of
allowing the constructor to take care of that?

@sloan:
Can you explain the UserManager class a bit more? Is that essentially
the "factory" that handles User objects? i.e. UserManager would
include GetUserById(System.Guid userId) and SaveUser(User u)? What's
the advantage to this, other than providing another layer between the
database and the business objects (if I already have a data layer that
handles the low-level data functions)?

Thanks again,
Steve

 

[Peter Bradley]

I don't know whether this is helpful or not. Ignore it if it isn't.

1. Is it correct to create two objects...?

Generally speaking, your data model comes last; after you've decided what
objects your application needs. However, I do recognise that very often
people are developing with existing databases in mind and this is bound to
influence your object model. I don't think it should determine it, though.
Having decided that you need User and Company objects for your application,
you should supply them with whatever methods your analysis says your
application requires - without paying any attention to what there is in the
database. So you might want things like:

public User()
public User(string uid)
public User(string uid, string username)
** and any other constructors you may need**
public DataSet GetUserById(string uid)
public DataSet GetUserByName(string username)
public void StoreUser()
public void DeleteUser(string uid)

Your application should determine these (e.g. you found them using UML
Sequence diagrams or Collaboration diagrams): not the existing database.

2. Should I load ALL of the object's properties ...?

What if it's a new object? Does your application create new Users/Companies
that are then inserted? Does it delete them - if so you may only need to
know the id.

I guess some of your constructors will want to check whether a user with the
passed-in data already exists. If so, this will cause a read on the
database, from which you can get property values if you want to: but I don't
see why you should want to pass the connection string. Things like
connection strings should be in config files. That way you have complete
flexibility over how you implement your business and data access objects.
They could be in-process dlls or remote objects using TCP/IP or SOAP. It
wouldn't matter. They'd still get the connection string from their config
file. And if the database server changed its name, for example, all you
would have to do would be to alter the config file entry.

3. Should the User object have a property that returns a Company object
instead of just the CompanyId?

What does your analysis say? One purpose of analysis is to determine what
methods the objects making up the application should have. If your analysis
says that this is required, then it is. If it doesn't, it isn't.

HTH


Peter

I am building an object library for tables in a database. What is the
best practice for creating objects like this?

For example, say I have the following tables in my database:
User:
- Id
- FirstName
- LastName
- CompanyId (many-to-one [Users to Company])

Company:
- Id
- Name
- Address
- NumberOfEmployees

1. Is it correct to create two objects (User, Company) using table
columns as object properties, have a Save method for each object to
insert and update the table, and pass the identifier (Id) and database
connection string into the constructor to load the object using data
from the table?

2. Should I load ALL of the object's properties with the matching
data from the table in the constructor, therefore causing 1 database
query when instantiating the object [i.e. User u = new
User(connectionString, userId)]?

3. Should the User object have a property that returns a Company
object instead of just the CompanyId? If so, should I load the
Company object in the User object's constructor, therefore causing 2
database queries?
For example:
public User(string connectionString, System.Guid userId)
{
// Query User table (select * from User where Id = userId) into a
DataTable (dt)
// ...

this._connectionString = connectionString;
this._id = userId; // Defined as: System.Guid _id
this._firstName = dt.Rows[0]["FirstName"]; // Defined as: string
_firstName
this._lastName = dt.Rows[0]["LastName"]; // Defined as: string
_lastName
this._company = new Company(connectionString, dt.Rows[0]
["CompanyId"]); // Defined as: Company _company
}

4. Would it be better to instantiate and fill the Company object when
the User object is created, or when the Company object is first used?
For example:
private Company _company = null;
public Company MyCompany
{
get
{
if (this._company == null)
{
// Company object's constructor is similar to the User
object's from (3) above
this._company = new Company(this._connectionString,
this._companyId);
}
return this._company;
}
set
{
this._company = value;
}
}

This is a simple example of what I am trying to do, but hopefully you
get the gist of it. Basically, I need objects linked to the database,
but want to minimize database queries and unnecessary data in memory
(I may never need to know the number of employees in the company, but
I'd want the address often). Any input is appreciated.

Thanks,
Steve

 

[RobinS]

Comments below...

Yes to the first part, no to the second. You should have a separate
class
for each object, and in the class you can put your Create and Save
methods
as well as your properties.

You should have your business layer call your data access layer, which
will
pass back a data table or dataset of information for your business layer
to
handle. Then only the data access layer holds the connectionstring and
connects to the database, etc. (example below)

2. Should I load ALL of the object's properties with the matching
data from the table in the constructor, therefore causing 1 database
query when instantiating the object [i.e. User u = new
User(connectionString, userId)]?

Yes, although like I said, you need to extract the data access part of
it.
I make my default constructor private, and use a factory method like
this.
Mine's in VB, but I'm going to try to translate it to C#; it should give
you the general idea.

public static Company Create(int compID)
{
Company comp;
if (compID = 0)
{
comp = New Company();
comp.Name = String.Empty;
comp.Address = String.Empty;
comp.NumberOfEmployees = 0;
}
else
{
DataTable dt;
//call the data access layer to return a datatable with one row
// containing the company you are looking for
dt = DAC.ExecuteDataTable("CompanyRetrieveByID_sp",
DAC.Parameter("ID", compID);
comp = New Company();
comp.Name = dt.Rows[0]("Name").ToString;
//load rest of properties
}
return comp;

}

In my DAC, this runs a procedure that takes the stored procedure name
and a
parameter array as arguments. It opens the connection, adds the
parameters
to the command object, executes the query, and returns the data table.

This is basically what I am doing now. I have a DataLayer object that
does all of the DB-specific functions (GetDataSet/Table, ExecNonQuery,
Open/CloseConnection, etc). In the User example, it would look like
this:
public User(string connectionString, System.Guid userId)
{
// Query User table (select * from User where Id = userId) into a
DataTable (dt)
DataLayer.DBClient db = new DataLayer.DBClient(connectionString);
db.CommandType = System.Data.CommandType.StoredProcedure;
db.CommandText = "MY_GetUser_QRY";
db.AddParameter("@UserId", userId);
System.Data.DataTable dt = db.GetDataTable();

// ...
}

Is that more in-line with what you're doing?

Sort of. My data layer is actually more abstracted than that. I don't want
to have a routine for updating every table; that's what the SP's are for.
If I have 50 tables, I don't want 50 update routines in my DAC. I pass in
the stored procedure name and parameters and have the DAC handle the
connections, command objects, etc.

Here's my code, in VB (sorry, haven't rewritten it in C# yet). If you can't
figure it out, post back and I'll translate it.

ExecuteDataTable is called in my code above; look for
"DAC.ExecuteDataTable" to see how I'm calling it.

My connection string is stored in the Settings.Settings. In C#, this would
be Properties.Settings.Default.PTConnectionString.

storedProcedureName is passed in from the Business Layer, as is the
parameter array.

Public Shared Function ExecuteDataTable(ByVal storedProcedureName _
As String, ByVal ParamArray arrParam() As SqlParameter) _
As DataTable

Dim dt As DataTable
'open the connection
using cnn As New SqlConnection(My.Settings.PTConnectionString)
cnn.Open()

'define the command
Dim cmd As New SqlCommand
cmd.Connection = cnn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = storedProcedureName

'handle the parameters
If arrParam IsNot Nothing Then
For Each param As SqlParameter In arrParam
cmd.Parameters.Add(param)
Next
End If

'define the data adapter and fill the data table
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
dt = New DataTable
da.Fill(dt)
End Using
Return dt
End Function

And here's my DAC.Parameter routine that I call from my business object to
add parameters to the passed-in parameter array (this is in my example
above, called DAC.Parameter(...) in the call to DAC.ExecuteDataTable).

Public Shared Function Parameter(ByVal parameterName As String, _
ByVal parameterValue As Object) As SqlParameter
Dim param As New SqlParameter
param.ParameterName = parameterName
param.Value = parameterValue
Return param
End Function

The only place that needs to know the stored procedure name is the business
layer. Although, I don't actually store them there; I put them in the
Settings, so I can easily change them if I need to, and get the name from
the Settings to pass in to the DAC routine. So I might have a setting
called "SP_GetCustomer" that has a value of "GetCustomerByID_sp".

I see your point about
having a seperate "factory" method that allows you to create a new
object or load it from the database, but is there a disadvantage of
allowing the constructor to take care of that?

I always kind of think the default constructor should create an initialized
object, and then you can fill in the properties willy-nilly. In the case of
creating my business objects, I don't *want* to use a default constructor,
I want it to be really specific -- do they want a new instance, or one from
the database?

I have control issues, and I hate to leave a default constructor out there
that can be called from anywhere when it matters to me how the object gets
created; in some cases, I just want more control over the creation of my
objects. Aside from other people using my classes, this keeps *me* from
accidentally using the default constructor when I shouldn't.

@sloan:
Can you explain the UserManager class a bit more? Is that essentially
the "factory" that handles User objects? i.e. UserManager would
include GetUserById(System.Guid userId) and SaveUser(User u)? What's
the advantage to this, other than providing another layer between the
database and the business objects (if I already have a data layer that
handles the low-level data functions)?

I can't speak for @sloan, but I would not place the functions that belong
with my business object in a separate class. That's one level of separation
too much. Just my 2 cents' worth.

Good luck.
Robin S.