design pattern of "Business Layer"

[Hasan O. Zavalsiz]

Hi , i am trying to figure out which approach is better to use .
let me explain the scenario.
i am using the "Nortwind" database . in this database i have "Customers "
table .The following is the two different ways to handle this table.

CASE 1 :
create a struct that encaplusates table "Customers" columns
public struct structCustomers
{
public string CustomerID;
public string CompanyName;
public string ContactName;
public string ContactTitle;
public string Address;
public string City;
public string Region;
public string PostalCode;
public string Country;
public string Phone;
public string Fax;
public Customers(int i) {
// assign variables to their default whatever they are
CustomerID = -1 ; CompanyName ="";
}
}
And write a class that encaplusates table
public class Customers {
// all methods related to stored procedures about table
public SqlDataReader GetAllCustomers()
{
.....
return sqlcommand.executereader("GetCustomers",.....)
}
}

and if a need to pass parameters to a method i am using the following ,
lets say as an illustration
public SqlDataReader InsertCustomerDetails(structCustomers sc )
{
sqlparameter customerid = new sqlparameter ("@custid",
Sqldbtype.varchar)
customerid.Value = sc.CustomerID ;
.....
return sqlcommand.executereader("GetCustomers",.....)
}

and finally from calling UI i have the following
void button1_click()
{
structCustomers sc = new structCustomers(0);
sc.CustomerID = textbox1.text ;
Customers c = new Customers ();
dataset ds = c.GetCustomerDetail (sc);
}
CASE 2 :

write a class that encaplusates all table related info
public class Customers {

public string m_CustomerID;
public string m_CompanyName;
public string m_ContactName;
public string m_ContactTitle;
public string m_Address;
public string m_City;
public string m_Region;
public string m_PostalCode;
public string m_Country;
public string m_Phone;
public string m_Fax;

// write all set and get
public object CustomerID{
set
{
m_CustomerID= value;
}
get
{
return m_CustomerID;
}
}

// all methods related to stored procedures about table
public SqlDataReader GetAllCustomers()
{
.....
return sqlcommand.executereader("GetCustomers",.....)
}
}

and if a need to pass parameters to a method i am using the following ,
lets say as an illustration
public SqlDataReader InsertCustomerDetails()
{
sqlparameter customerid = new sqlparameter ("@custid",
Sqldbtype.varchar)
customerid.Value = this.m_CustomerID;
.....
return sqlcommand.executereader("GetCustomers",.....)
}

and finally from calling UI i have the following
void button1_click()
{
Customers c = new Customers ();
c.CustomerID = textbox1.text ;
dataset ds = c.GetCustomerDetail ();
}

these are the different ways i know. Which one is better to use ? Comments
are welcome , and valuable.

not : the different approaches has their disadvantages, dont care about so
much .as an example " get the customers who live either country "Canada" or
"Turkey" , anyway maybe keep this situation for further discussions

[Kevin Spencer]

Good question, Hassan.

There are a couple of principles involved here. One is that a business class
should "mind its own business." That is, a business class should contain
whatever functionality is necessary to work with it, and expose whatever may
be necessary to any client class that may need it. the first model
unnecessarily puts a structure into a class that contains the processes
needed to work on the structure. The "outer" class isn't doing anything but
working with Customer classes. You call it "Customers," but in fact, the
code is for woking with a single "Customer." A class encapsulates data and
process, and there is no need for one class to contain the data, and another
to contain the process. Obviously, we're looking at a thin slice of what a
"Customer" might do, and in fact, there would be much more process involved.
For example, the structure members could be private fields exposed using
public properties. These public properties could then contain process for
doing things like validation. After all, the length of the database field
for "CompanyName," for example, is limited in length. A public Setter could
throw an exception if the value input was too long. And so on. This way, the
Customer class would manage itself.

So, in a sense, your second model is better. However, I would take it a step
further, with a Data Layer. Consider your database function:

// all methods related to stored procedures about table
public SqlDataReader GetAllCustomers()
{
.....
return sqlcommand.executereader("GetCustomers",.....)
}
}
and your UI code:
void button1_click()
{
structCustomers sc = new structCustomers(0);
sc.CustomerID = textbox1.text ;
Customers c = new Customers ();
dataset ds = c.GetCustomerDetail (sc);
}
First, the Customer class has members which expose database data in an
object-oriented way. The class represents a "Customer." It is an object
which represents an entity, not an object which represents anything to do
with a database. So, why would it make sense for it to expose a DataReader,
especially since a DataReader must be closed, and the Connection with it?

In fact, the UI layer shouldn't even be aware that it is working with a
database. It should be aware that it is working with a Customer object. If
it needs the CompanyName, it gets it from the CompanyName property of the
class. Etc. the UI's job is to present a "Customer" entity to a user in a
user-friendly manner. And the business class's job is to present Customer
data to the UI and other business objects in a programmer-friendly manner.

So, first, the Customer class, in order to "mind its own business," should
only expose fields and properties pertaining to a Customer to client
classes.

But there's more. In that database function, what I see abstractly is a
function that executes a query or stored procedure, and returns a
SqlDataReader. Sounds like a potentially reusable function to me, with a
little tweaking. How about a Data class with a static function like the
following?

public static SqlDataReader GetDataReader(SqlConnection conn,
SqlCommand command, string ProcedureName,
ParamArray params)
{
OpenConn(conn, command);
.....
return lcommand.ExecuteReader(ProcedureName, params);
}

Just a rough idea. You would have to flesh out the details yourself, of
course, including how to handle the ConnectionString, etc.

Now, the Customer class is the only class that will probably using this
Stored Procedure, so it will know the Procedure name and the parameters
needed. So, you have a method that populates the class from the Stored
Procedure, such as a Constructor method. Example:

public Customer(string CustomerID)
{
try
{
SqlCommand command;
SqlConnection conn;
SqlDataReader dr;
ParamArray params = ...;
dr = DataClass.GetDataReader(conn, command, "GetCustomer", params);
...
_CustomerID = dr.GetString(dr.GetOrdinal("CustomerID"));
...
}
catch (Exception ex)
{...}
finally
{
dr.Close();
DataClass.CloseConn(conn, command);
}
}

Now the DataClass class encapsulates database functionality, and the
Customer class encapsulates all the process necessary to create a Customer
with a simple API. Any UI class can simply create an instance of a Customer
by passing the ID

That's the power of object-orientation!

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Ambiguity has a certain quality to it.

"Hasan O. Zavalsiz" <ho********@gmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl... Hi , i am trying to figure out which approach is better to use .
let me explain the scenario.
i am using the "Nortwind" database . in this database i have "Customers "
table .The following is the two different ways to handle this table.

CASE 1 :
create a struct that encaplusates table "Customers" columns
public struct structCustomers
{
public string CustomerID;
public string CompanyName;
public string ContactName;
public string ContactTitle;
public string Address;
public string City;
public string Region;
public string PostalCode;
public string Country;
public string Phone;
public string Fax;
public Customers(int i) {
// assign variables to their default whatever they are
CustomerID = -1 ; CompanyName ="";
}
}
And write a class that encaplusates table
public class Customers {
// all methods related to stored procedures about table
public SqlDataReader GetAllCustomers()
{
.....
return sqlcommand.executereader("GetCustomers",.....)
}
}

and if a need to pass parameters to a method i am using the following ,
lets say as an illustration
public SqlDataReader InsertCustomerDetails(structCustomers sc )
{
sqlparameter customerid = new sqlparameter ("@custid",
Sqldbtype.varchar)
customerid.Value = sc.CustomerID ;
.....
return sqlcommand.executereader("GetCustomers",.....)
}

and finally from calling UI i have the following
void button1_click()
{
structCustomers sc = new structCustomers(0);
sc.CustomerID = textbox1.text ;
Customers c = new Customers ();
dataset ds = c.GetCustomerDetail (sc);
}
CASE 2 :

write a class that encaplusates all table related info
public class Customers {

public string m_CustomerID;
public string m_CompanyName;
public string m_ContactName;
public string m_ContactTitle;
public string m_Address;
public string m_City;
public string m_Region;
public string m_PostalCode;
public string m_Country;
public string m_Phone;
public string m_Fax;

// write all set and get
public object CustomerID{
set
{
m_CustomerID= value;
}
get
{
return m_CustomerID;
}
}

// all methods related to stored procedures about table
public SqlDataReader GetAllCustomers()
{
.....
return sqlcommand.executereader("GetCustomers",.....)
}
}

and if a need to pass parameters to a method i am using the following ,
lets say as an illustration
public SqlDataReader InsertCustomerDetails()
{
sqlparameter customerid = new sqlparameter ("@custid",
Sqldbtype.varchar)
customerid.Value = this.m_CustomerID;
.....
return sqlcommand.executereader("GetCustomers",.....)
}

and finally from calling UI i have the following
void button1_click()
{
Customers c = new Customers ();
c.CustomerID = textbox1.text ;
dataset ds = c.GetCustomerDetail ();
}

these are the different ways i know. Which one is better to use ?
Comments
are welcome , and valuable.

not : the different approaches has their disadvantages, dont care about so
much .as an example " get the customers who live either country "Canada"
or
"Turkey" , anyway maybe keep this situation for further discussions

[shiv_koirala]

Hi Hasan,

I will prefer the second approach. As the first one is is structure and
they have there limitation. But the best approach would be the hybrid
approach encapsulate the structure inside the class.

Thats mean
class x
{
structure customer
{
string customer;
}

}
And use the properties to set the structure.
-------
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Questions Blog
http://spaces.msn.com/members/dotnetinterviews/

[Joanna Carter [TeamB]]

"Hasan O. Zavalsiz" <ho********@gmail.com> a écrit dans le message de news:
%2****************@TK2MSFTNGP09.phx.gbl...

| Hi , i am trying to figure out which approach is better to use .
| let me explain the scenario.
| i am using the "Nortwind" database . in this database i have "Customers "
| table .The following is the two different ways to handle this table.

Modelling a class based on a table is never as good an idea as modelling
your class and then providing a table to persist the data of instances of
that class.

In the example Customer table, the Contact details are mixed in with the
Customer(Company) details; this is the first indicator that badly normalised
tables are not a good starting point for well designed classes (a Company
can have more than one Contact)

Good OO practice suggests that you separate your business logic completely
from your data layer, so you could start by having a Contact class :

public interface IIdentifiable
{
int ID { get, set };
}

Start off by separating the concept of identity from anything else, after
all, you should not either use anything other than an integer for a primary
key in a database table nor should that key have any business meaning. Now a
Customer may well have a reference code that could be alphanumeric but that
would be in addition to any unique integer used for database keys.

public class Contact : IIdentifiable
{
private int id;
private string name;
private string title;

int IIdentifiable.ID
{
get { return id; }
set { id = value; }
}

public string Name
{
get { return name; }
set { name = value; }
}

public string Title
{
get { return title; }
set { title = value; }
}
}

Note that the id field in the Contact class is an integer and that it is not
surfaced as a public property, instead it can only be accessed from outside
the class through the IIdentifiable interface. This is a deterrent to using
the ID in everyday code as it will only really be wanted by the persistence
mechanism that stores and retrieves instances of your Contact class. It is
unlikely that you would need a readable reference code for each Contact, so
that has been omitted from the class.

The Customer class will be similar, but there are one or two aspects that
will differ, both from the Contact class and also a "normal" database table
:

public class Customer : IIdentifiable
{
private int id;
private string ref;
private string name;
private string address;
private string city;
private string region;
private string postalCode;
private string country;
private string phone;
private string fax;

... // public properties

private List<Contact> contacts;

public Enumerator<Contact> Contacts
{
get { return contacts.GetEnumerator(); }
}

public Contact AddContact()
{
contacts.Add(new Contact());
}

public void RemoveContact(Contact contact)
{
contacts.Remove(contact);
}
}

It would possibly be necessary to include a Customer Ref property so a
string field and property would be added for this.

Notice that we are keeping the Contacts list hidden and only allowing adding
or removing of Contacts via methods; if you need the list of Contacts, then
you can only get an enumerator to the internal list.

Also note that we don't have a "foreign key" to the Customer in the Contact
class as would be normal for a 1-n relationship in a relational database.
Instead the Customer has a list of Contacts.

It would be the job of a data access layer to translate this object
relationship to the RDB relationship, but the business classes need know
nothing of this matter.

You would then look at a data access layer whose job it is to take any
object that supports IIdentifiable and use reflection to persist/retrieve
the data for that particular object, using the "hidden" ID as the database
table primary or foreign keys.

public class ObjectStore
{
public bool StoreObject(IIdentifiable obj)
{
// extract ID from the interface, then cast to Object and use reflection
// to extract the property values
// Then create a SQL Insert or Update statement using the values
}

...
}

Just as you would only ever expect a set of records to be returned from a
database, so you should never expect a retrieval of objects from an Object
Store to only return one object. A Retrieval method on the ObjectStore class
should return a List which you can then traverse in the normal manner. .NET
datagrids are just as capable of displaying properties of objects as they
are of displaying fields of tables.

Using this technique gives you the flexibility to change your database
vendor without having to change any of your business coding; the only
changes wouldf be to the SQL generated and the query component created to
handle that SQL.

For further reading on OPFs (Object Persistence Frameworks) go to my website
www.carterconsulting.org.uk; the articles are written for a Delphi audience,
but the principles are the same.

Joanna

--
Joanna Carter [TeamB]
Consultant Software Engineer

[Hasan O. Zavalsiz]

Hi Kevin ,
thanks for the answer. I have made some comments about your ideas , inline .

So, in a sense, your second model is better. However, I would take it a step further, with a Data Layer. Consider your database function:

// all methods related to stored procedures about table
public SqlDataReader GetAllCustomers()
{
.....
return sqlcommand.executereader("GetCustomers",.....)
}
}
and your UI code:

void button1_click()
{
structCustomers sc = new structCustomers(0);
sc.CustomerID = textbox1.text ;
Customers c = new Customers ();
dataset ds = c.GetCustomerDetail (sc);
}

First, the Customer class has members which expose database data in an
object-oriented way. The class represents a "Customer." It is an object
which represents an entity, not an object which represents anything to do
with a database. So, why would it make sense for it to expose a

DataReader, especially since a DataReader must be closed, and the Connection with it?

it is just a psoude code , i generally use facade modeling ( using ms
enterprise library , data access app. block )

In fact, the UI layer shouldn't even be aware that it is working with a
database. It should be aware that it is working with a Customer object. If
it needs the CompanyName, it gets it from the CompanyName property of the
class. Etc. the UI's job is to present a "Customer" entity to a user in a
user-friendly manner. And the business class's job is to present Customer
data to the UI and other business objects in a programmer-friendly manner.

So, first, the Customer class, in order to "mind its own business," should
only expose fields and properties pertaining to a Customer to client
classes.

I got the idea. if i need the all customers , i cant get it xx proterty , it
a dataset/datatable or something. Do I handle this situation and similar
situations using an private DataSet ? and return like

void button1_click()
{
structCustomers sc = new structCustomers(0);
sc.CustomerID = textbox1.text ;
Customers c = new Customers ();
c.GetCustomers();
dataset ds = c.dsCustomersAll;
}

But there's more. In that database function, what I see abstractly is a
function that executes a query or stored procedure, and returns a
SqlDataReader. Sounds like a potentially reusable function to me, with a
little tweaking. How about a Data class with a static function like the
following?

public static SqlDataReader GetDataReader(SqlConnection conn,
SqlCommand command, string ProcedureName,
ParamArray params)
{
OpenConn(conn, command);
.....
return lcommand.ExecuteReader(ProcedureName, params);
}

Just a rough idea. You would have to flesh out the details yourself, of
course, including how to handle the ConnectionString, etc.

The idea is telling me that i have been on the right way already , i am
using facade pattern and ms entirprise library . I dont create commands,
connection strings .
every class or only one connection is open for general use .
Now, the Customer class is the only class that will probably using this
Stored Procedure, so it will know the Procedure name and the parameters
needed. So, you have a method that populates the class from the Stored
Procedure, such as a Constructor method. Example:

public Customer(string CustomerID)
{
try
{
SqlCommand command;
SqlConnection conn;
SqlDataReader dr;
ParamArray params = ...;
dr = DataClass.GetDataReader(conn, command, "GetCustomer", params); ...
_CustomerID = dr.GetString(dr.GetOrdinal("CustomerID"));
...
}
catch (Exception ex)
{...}
finally
{
dr.Close();
DataClass.CloseConn(conn, command);
}
}

Now the DataClass class encapsulates database functionality, and the
Customer class encapsulates all the process necessary to create a Customer
with a simple API. Any UI class can simply create an instance of a Customer by passing the ID

That's the power of object-orientation!

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Ambiguity has a certain quality to it.

Again thanks for the answer Kevin ,
have a nice day

[Hasan O. Zavalsiz]

Hi shiv ,
do i need a struct if already handled with private objects ?

<sh**********@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Hi Hasan,

I will prefer the second approach. As the first one is is structure and
they have there limitation. But the best approach would be the hybrid
approach encapsulate the structure inside the class.

Thats mean
class x
{
structure customer
{
string customer;
}

}
And use the properties to set the structure.
-------
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Questions Blog
http://spaces.msn.com/members/dotnetinterviews/

[Hasan O. Zavalsiz]

Hi Joanna ,

"Joanna Carter [TeamB]" <jo****@not.for.spam> wrote in message
news:uR**************@TK2MSFTNGP09.phx.gbl...

"Hasan O. Zavalsiz" <ho********@gmail.com> a écrit dans le message de news: %2****************@TK2MSFTNGP09.phx.gbl...

| Hi , i am trying to figure out which approach is better to use .
| let me explain the scenario.
| i am using the "Nortwind" database . in this database i have "Customers " | table .The following is the two different ways to handle this table.

Modelling a class based on a table is never as good an idea as modelling
your class and then providing a table to persist the data of instances of
that class.
modelling that i use is based on the principles of " how is " , "has it " ,
"owns it" . I mean the customer table in on the top of related table .
Customers has orders , has contact and so on .
Joanna
Joanna Carter [TeamB]
Consultant Software Engineer

thanks for the answer , let me think about what you have written
have a nice day

[Kevin Spencer]

Glad to be of service, Hasan.

I got the idea. if i need the all customers , i cant get it xx proterty ,
it
a dataset/datatable or something. Do I handle this situation and similar
situations using an private DataSet ? and return like
I figured my example might be a little confusing, due to its handling only
one Customer. but I was trying to keep things short, sweet, and to the
point. The same principle would apply when dealing with, for example,
getting a subset of Customers from the Customers table in the database. What
you would need would be 2 things:

1. A CustomerCollection class. This class would be a Collection, with a
method or methods for populating it with Customer objects. Since each
Customer is a class unto itself, you would want a Collection for handling
groups of Customers for various purposes, such as populating them all at
once from a single query, applying a business rule across the entire subset,
etc.

2. An overload for the Customer constructor. In fact, it might not be
necessary to have an overload for the Customer constructor that takes a
Customer ID and performs a query, but the nice thing about overloads is that
you can overload them! ;-) For example, keeping in mind that business
objects should "mind their own business," you could create an overload for a
Customer constructor that takes an ID and a dataset:

public Customer(string customerID, DataSet ds)
{
foreach (DataRow dr in ds.Tables["Customer"].Rows)
if ((string)dr["CustomerID"] == customerID)
{
CustomerID = customerID;
...
}
break;
}

This way the Customer populates itself from a data source passed to it by
the CustomerCollection. This way the CustomerCollection can populate itself
by looping through the rows of a table in a DataSet (for example), getting
the CustomerID value from the row, and adding a new Customer class to its
List, passing the CustomerID and DataSet to the Customer constructor.

I used a DataSet as an example, because the DataSet can house multiple
tables, and there might be a relationship between the Customer table and
another table, such that the Customer might need access to the other table
when being populated. Another advantage to the DataSet is that the
CustomerCollection could keep a cached copy of the data for doing updates.

But one of the beauties of the CLR is its immense flexibility. You can
customize your app to use whatever combination of available classes and
methodology suits its requirements. In other words, this is a rough idea,
for the purpose of illustrating the concept, not a recommended methodology
for a specific purpose.
The idea is telling me that i have been on the right way already , i am
using facade pattern and ms entirprise library . I dont create commands,
connection strings .
every class or only one connection is open for general use .
One caveat here: It is not a good idea to keep Connections opened. The .Net
platform makes excellent use of Connection Pooling. When you use the same
Connection String to open a Connection, it is re-used from the pool, if it
has been used before. Keeping Connections opened in .Net defeats the purpose
of Connection Pooling, which is there for a number of good reasons. Think of
Connections like File streams. Open them and close them as quickly as
possible. Cache your Connection String, and re-use it instead.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Ambiguity has a certain quality to it.

"Hasan O. Zavalsiz" <ho********@gmail.com> wrote in message
news:O9**************@TK2MSFTNGP14.phx.gbl... Hi Kevin ,
thanks for the answer. I have made some comments about your ideas , inline
.

So, in a sense, your second model is better. However, I would take it a

step

further, with a Data Layer. Consider your database function:

> // all methods related to stored procedures about table
> public SqlDataReader GetAllCustomers()
> {
> .....
> return sqlcommand.executereader("GetCustomers",.....)
> }
> }

and your UI code:

> void button1_click()
> {
> structCustomers sc = new structCustomers(0);
> sc.CustomerID = textbox1.text ;
> Customers c = new Customers ();
> dataset ds = c.GetCustomerDetail (sc);
> }

First, the Customer class has members which expose database data in an
object-oriented way. The class represents a "Customer." It is an object
which represents an entity, not an object which represents anything to do
with a database. So, why would it make sense for it to expose a

DataReader,

especially since a DataReader must be closed, and the Connection with it?

it is just a psoude code , i generally use facade modeling ( using ms
enterprise library , data access app. block )

In fact, the UI layer shouldn't even be aware that it is working with a
database. It should be aware that it is working with a Customer object.
If
it needs the CompanyName, it gets it from the CompanyName property of the
class. Etc. the UI's job is to present a "Customer" entity to a user in a
user-friendly manner. And the business class's job is to present Customer
data to the UI and other business objects in a programmer-friendly
manner.

So, first, the Customer class, in order to "mind its own business,"
should
only expose fields and properties pertaining to a Customer to client
classes.

I got the idea. if i need the all customers , i cant get it xx proterty ,
it
a dataset/datatable or something. Do I handle this situation and similar
situations using an private DataSet ? and return like

void button1_click()
{
structCustomers sc = new structCustomers(0);
sc.CustomerID = textbox1.text ;
Customers c = new Customers ();
c.GetCustomers();
dataset ds = c.dsCustomersAll;
}

But there's more. In that database function, what I see abstractly is a
function that executes a query or stored procedure, and returns a
SqlDataReader. Sounds like a potentially reusable function to me, with a
little tweaking. How about a Data class with a static function like the
following?

public static SqlDataReader GetDataReader(SqlConnection conn,
SqlCommand command, string ProcedureName,
ParamArray params)
{
OpenConn(conn, command);
.....
return lcommand.ExecuteReader(ProcedureName, params);
}

Just a rough idea. You would have to flesh out the details yourself, of
course, including how to handle the ConnectionString, etc.

The idea is telling me that i have been on the right way already , i am
using facade pattern and ms entirprise library . I dont create commands,
connection strings .
every class or only one connection is open for general use .

Now, the Customer class is the only class that will probably using this
Stored Procedure, so it will know the Procedure name and the parameters
needed. So, you have a method that populates the class from the Stored
Procedure, such as a Constructor method. Example:

public Customer(string CustomerID)
{
try
{
SqlCommand command;
SqlConnection conn;
SqlDataReader dr;
ParamArray params = ...;
dr = DataClass.GetDataReader(conn, command, "GetCustomer",

params);

...
_CustomerID = dr.GetString(dr.GetOrdinal("CustomerID"));
...
}
catch (Exception ex)
{...}
finally
{
dr.Close();
DataClass.CloseConn(conn, command);
}
}

Now the DataClass class encapsulates database functionality, and the
Customer class encapsulates all the process necessary to create a
Customer
with a simple API. Any UI class can simply create an instance of a

Customer

by passing the ID

That's the power of object-orientation!

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Ambiguity has a certain quality to it.

Again thanks for the answer Kevin ,
have a nice day

[Hasan O. Zavalsiz]

Hi Kevin ,

why ms dont give such an example :)
thanks a lot

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:eR**************@TK2MSFTNGP15.phx.gbl...

Glad to be of service, Hasan.

I got the idea. if i need the all customers , i cant get it xx proterty , it
a dataset/datatable or something. Do I handle this situation and similar
situations using an private DataSet ? and return like
I figured my example might be a little confusing, due to its handling only
one Customer. but I was trying to keep things short, sweet, and to the
point. The same principle would apply when dealing with, for example,
getting a subset of Customers from the Customers table in the database.

What you would need would be 2 things:

1. A CustomerCollection class. This class would be a Collection, with a
method or methods for populating it with Customer objects. Since each
Customer is a class unto itself, you would want a Collection for handling
groups of Customers for various purposes, such as populating them all at
once from a single query, applying a business rule across the entire subset, etc.

2. An overload for the Customer constructor. In fact, it might not be
necessary to have an overload for the Customer constructor that takes a
Customer ID and performs a query, but the nice thing about overloads is that you can overload them! ;-) For example, keeping in mind that business
objects should "mind their own business," you could create an overload for a Customer constructor that takes an ID and a dataset:

public Customer(string customerID, DataSet ds)
{
foreach (DataRow dr in ds.Tables["Customer"].Rows)
if ((string)dr["CustomerID"] == customerID)
{
CustomerID = customerID;
...
}
break;
}

This way the Customer populates itself from a data source passed to it by
the CustomerCollection. This way the CustomerCollection can populate itself by looping through the rows of a table in a DataSet (for example), getting
the CustomerID value from the row, and adding a new Customer class to its
List, passing the CustomerID and DataSet to the Customer constructor.

I used a DataSet as an example, because the DataSet can house multiple
tables, and there might be a relationship between the Customer table and
another table, such that the Customer might need access to the other table
when being populated. Another advantage to the DataSet is that the
CustomerCollection could keep a cached copy of the data for doing updates.

But one of the beauties of the CLR is its immense flexibility. You can
customize your app to use whatever combination of available classes and
methodology suits its requirements. In other words, this is a rough idea,
for the purpose of illustrating the concept, not a recommended methodology
for a specific purpose.

The idea is telling me that i have been on the right way already , i am
using facade pattern and ms entirprise library . I dont create commands, connection strings .
every class or only one connection is open for general use .
One caveat here: It is not a good idea to keep Connections opened. The

..Net platform makes excellent use of Connection Pooling. When you use the same
Connection String to open a Connection, it is re-used from the pool, if it
has been used before. Keeping Connections opened in .Net defeats the purpose of Connection Pooling, which is there for a number of good reasons. Think of Connections like File streams. Open them and close them as quickly as
possible. Cache your Connection String, and re-use it instead.

HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Ambiguity has a certain quality to it.

"Hasan O. Zavalsiz" <ho********@gmail.com> wrote in message
news:O9**************@TK2MSFTNGP14.phx.gbl...

Hi Kevin ,
thanks for the answer. I have made some comments about your ideas , inline .

So, in a sense, your second model is better. However, I would take it a

step

further, with a Data Layer. Consider your database function:

> // all methods related to stored procedures about table
> public SqlDataReader GetAllCustomers()
> {
> .....
> return sqlcommand.executereader("GetCustomers",.....)
> }
> }

and your UI code:

> void button1_click()
> {
> structCustomers sc = new structCustomers(0);
> sc.CustomerID = textbox1.text ;
> Customers c = new Customers ();
> dataset ds = c.GetCustomerDetail (sc);
> }

First, the Customer class has members which expose database data in an
object-oriented way. The class represents a "Customer." It is an object
which represents an entity, not an object which represents anything to do with a database. So, why would it make sense for it to expose a

DataReader,

especially since a DataReader must be closed, and the Connection with it?

it is just a psoude code , i generally use facade modeling ( using ms
enterprise library , data access app. block )

In fact, the UI layer shouldn't even be aware that it is working with a
database. It should be aware that it is working with a Customer object.
If
it needs the CompanyName, it gets it from the CompanyName property of the class. Etc. the UI's job is to present a "Customer" entity to a user in a user-friendly manner. And the business class's job is to present Customer data to the UI and other business objects in a programmer-friendly
manner.

So, first, the Customer class, in order to "mind its own business,"
should
only expose fields and properties pertaining to a Customer to client
classes.

I got the idea. if i need the all customers , i cant get it xx proterty , it
a dataset/datatable or something. Do I handle this situation and similar
situations using an private DataSet ? and return like

void button1_click()
{
structCustomers sc = new structCustomers(0);
sc.CustomerID = textbox1.text ;
Customers c = new Customers ();
c.GetCustomers();
dataset ds = c.dsCustomersAll;
}

But there's more. In that database function, what I see abstractly is a
function that executes a query or stored procedure, and returns a
SqlDataReader. Sounds like a potentially reusable function to me, with a little tweaking. How about a Data class with a static function like the
following?

public static SqlDataReader GetDataReader(SqlConnection conn,
SqlCommand command, string ProcedureName,
ParamArray params)
{
OpenConn(conn, command);
.....
return lcommand.ExecuteReader(ProcedureName, params);
}

Just a rough idea. You would have to flesh out the details yourself, of
course, including how to handle the ConnectionString, etc.

The idea is telling me that i have been on the right way already , i am
using facade pattern and ms entirprise library . I dont create commands, connection strings .
every class or only one connection is open for general use .

Now, the Customer class is the only class that will probably using this
Stored Procedure, so it will know the Procedure name and the parameters
needed. So, you have a method that populates the class from the Stored
Procedure, such as a Constructor method. Example:

public Customer(string CustomerID)
{
try
{
SqlCommand command;
SqlConnection conn;
SqlDataReader dr;
ParamArray params = ...;
dr = DataClass.GetDataReader(conn, command, "GetCustomer",

params);

...
_CustomerID = dr.GetString(dr.GetOrdinal("CustomerID"));
...
}
catch (Exception ex)
{...}
finally
{
dr.Close();
DataClass.CloseConn(conn, command);
}
}

Now the DataClass class encapsulates database functionality, and the
Customer class encapsulates all the process necessary to create a
Customer
with a simple API. Any UI class can simply create an instance of a

Customer

by passing the ID

That's the power of object-orientation!

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Ambiguity has a certain quality to it.

Again thanks for the answer Kevin ,
have a nice day

[Ramkumar Thirumangalam]

Hi Kevin
I always had this in my mind abt the distinction between Business Layer
and Data Layer. Your answer to Hassan's post is very informative. I am
new to OO development and been thinking abt the best possible approch to
model database tables as business objects.
I had a look at the TimeTracker Starter kit on the www.ASP.Net page and
started modelling my new app that I am working on based on it.
However, I am not sure if it is truly objecy orieted. After seeing
Jonna's response to Hassan's post, I wonder if I need to remodel it.
Here is a sample business object that I have written in my BLL. Can you
please have a look at it and see if I could improve on it. I have posted
some more questions based on the code below.

Here are my additional questions:
1. Would it make sense to move those Data Access code to a new class and
call it the Data Access Layer?
2. Also, would it moake sense to move the Static GetUsers method which
returns a type safe user's collection to a instance mthod in the
UsersCollection class?
3. Also, before inserting the User into the database, Do i need to
provide for validation of the user attributes to make sure they are not
null?

any help or comments would be greatly appreciated as I want to make my
app OO as much as possible.

================================================== ======

public class User
{
private string _oneWorldID;
private string _lastName;
private string _firstName;
private string _middleInitial;
private string _respDivName;
private string _accessLevel;
private string _activeInd;
private int _respDiv;

public enum Select
{
Active="Y",
InActive="N",
All=""
}
public User(string sOneWorldID)
{
UserID = sOneWorldID;
FirstName = LastName = MiddleInitial = string.Empty;
RespDivName = ActiveInd = string.Empty;
RespDiv = 0;
}

public User()
{
User(string.Empty);
}

public string OneWorldID
{
get {return _oneWorldID;}
set {_oneWorldID = value;}
}
public string FirstName
{
get {return _firstName;}
set {_firstName = value;}
}
public string LastName
{
get {return _lastName;}
set {_lastName = value;}
}
public string MiddleInitial
{
get {return _middleInitial;}
set {_middleInitial = value;}
}
public string RespDivName
{
get {return _respDivName;}
set {_respDivName = value;}
}
public int RespDiv
{
get {return _respDiv;}
set {_respDiv = value;}
}
public string ActiveInd
{
get {return _activeInd;}
set {_activeInd = value;}
}
public string AccessLevel
{
get {return _accessLevel;}
set {_accessLevel = value;}
}

public bool GetUsersDetails()
{
bool bUserFound = false;
Database sqldb = DatabaseFactory.CreateDatabase
("GatewaySQL");
DBCommandWrapper sqlCommandWrapper =
sqldb.GetStoredProcCommandWrapper("sp_Select_User_ List");

// Set up parameters
Utils.BuildParameters( sqlCommandWrapper,
Utils.ParameterCount.SingleParameter,
new
object[]{"@ONW",System.Data.DbType.AnsiStringFixedLength,4 ,ParameterDire
ction.Input,this.OneWorldID}
);

using (IDataReader dr = sqldb.ExecuteReader(sqlCommandWrapper))
{

while (dr.Read())
{
this.LastName = dr.GetValue(1).ToString();
this.FirstName = dr.GetValue(2).ToString();
this.MiddleInitial = dr.GetValue(3).ToString();
this.RespDivName = dr.GetValue(4).ToString();
this.ActiveInd = dr.GetValue(5).ToString();
this.AccessLevel = dr.GetValue(6).ToString();
bUserFound = true;
}
return bUserFound;
}
}
public static UsersCollection GetUsers(int iRespDiv, User.Select select)
{
Database sqldb = DatabaseFactory.CreateDatabase("GatewaySQL");

DBCommandWrapper sqlCommandWrapper =
sqldb.GetStoredProcCommandWrapper("sp_Select_User_ List");

// Set up parameters

string sActiveInactiveInd = "";
switch (select)
{
case User.Select.Active:
ActiveInactiveInd = "Y";
break;
case User.Select.InActive:
ActiveInactiveInd = "N";
break;
}

Utils.BuildParameters( sqlCommandWrapper,
Utils.ParameterCount.MultipleParameters,
new
object[]{"@iRespDiv",System.Data.DbType.Int32,4,ParameterD irection.iResp
Div},
new
object[]{"@ActiveInactiveInd",System.Data.DbType.AnsiStrin gFixedLength,1
,ParameterDirection.Input,sActiveInactiveInd}
);

using (IDataReader dr = sqldb.ExecuteReader(sqlCommandWrapper))
{

UsersCollection users = new UsersCollection();

while (dr.Read())
{
User usr = new User();
usr.OneWorldID = dr.GetValue(0).ToString();
usr.LastName = dr.GetValue(1).ToString();
usr.FirstName = dr.GetValue(2).ToString();
usr.MiddleInitial = dr.GetValue(3).ToString();
usr.AccessLevel = dr.GetValue(4).ToString();
usr.ActiveInd = dr.GetValue(5).ToString();
}
return users;
}
}
public void InsertUser(string sUpdt_User_OneWorldID)
{
// The insert user stored procedure inserts a new user.
try
{
using (new EntServicesProvider())
{
Database sqldb = DatabaseFactory.CreateDatabase("GatewaySQL");

DBCommandWrapper sqlCommandWrapper =
sqldb.GetStoredProcCommandWrapper("sp_Insert_User" );

// Set up parameters
Utils.BuildParameters( sqlCommandWrapper,
Utils.ParameterCount.MultipleParameters,
new
object[]{"@OWN",System.Data.DbType.StringFixedLength,9,Par ameterDirectio
n.Input,this.UserID},
new
object[]{"@firstName",System.Data.DbType.StringFixedLength ,40,ParameterD
irection.Input,this.FirstName},
new
object[]{"@lastName",System.Data.DbType.StringFixedLength, 40,ParameterDi
rection.Input,this.LastName},
new
object[]{"@middleInitial",System.Data.DbType.StringFixedLe ngth,40,Parame
terDirection.Input,this.MiddleInitial},
new
object[]{"@accessLevel",System.Data.DbType.StringFixedLeng th,1,Parameter
Direction.Input,this.AccessLevel},
new
object[]{"@activeInd",System.Data.DbType.StringFixedLength ,1,ParameterDi
rection.Input,this.ActiveInd},
new
object[]{"@respDiv",System.Data.DbType.Int16,2,ParameterDi rection.InputO
utput,this.RespDiv}
);

using (IDbConnection connection = sqldb.GetConnection())
{
connection.Open();
sqldb.ExecuteNonQuery(sqlCommandWrapper);
}
SQLTransactionLog.InsertTransaction( "Insert",
sUpdt_UserID,
"SQL",
"sp_Insert_User",
this.Id,
60,
this.RespDiv.ToString(),
260,
" ",
0,
0,
" ",
0,
"Secondary column contains User's Division ID." );

ContextUtil.SetComplete();
}
}
catch (Exception e)
{
ContextUtil.SetAbort();
throw new Exception (e.Message);
}
}

================================================== =====


*** Sent via Developersdex http://www.developersdex.com ***