Comments below.
"Steve" <madrogue@gmail.comwrote in message
news:1173481098.883586.96830@t69g2000cwt.googlegro ups.com...
Quote:
>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)
Quote:
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.
Quote:
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);
Quote:
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.
Quote:
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.
