interfacing with data layer

[headware]

I'm relatively new to ASP.NET and ADO.NET, but I have a basic design
question regarding the use of web services and APS.NET applications.
Right now we have an application that uses web services to access the
database layer. However, the code works in a pretty cumbersome and
ungeneric way. Basically every query, update, and insert has its own
[WebMethod] function. So you see a lot of functions like

webService.InsertCustomer(name, age, phone);
or
DataSet custDS = webService.GetAllCustomers();
or
webService.UpdateCustomer(custId, name, age, phone);

I have my doubts about whether this is the best way to be interfacing
with the data layer. Couldn't you store the customer currently being
worked on (assuming you have a page in which you can work on the data
for a single customer at a time) in a DataSet stored in the Session
object and send that DataSet back to the data later so the DataAdapter
can call the Update() method on it? Unless I'm missing something, that
should allow you to have only one web service function for inserting,
deleting, and updating any table in the database. For example, you
could update the database like this

[WebMethod]
public void UpdateDatabase(DataSet ds)
{
OleDbConnection connection = new OleDbConnection("");
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " +
ds.Tables[0].TableName, connection);
adapter.Update(ds);
}

As long as the DataSet rows have the proper rowstate values, this one
function should do all your inserting, updating, and deleting for you,
right?

Of course, you'd have to be careful about storing large DataSets in
the Session object for the sake of scalability. In our particular
application, there would never be all that many users logged on at any
given point so I don't think that should be an issue.

What I'm really getting at here is that I would like
ideas/experience/articles/books about how people interface with the
data layer in ASP.NET applications.

Thanks,
Dave

[Cowboy \(Gregory A. Beamer\)]

Let's look at the purpose of web services, whether ASMX or Remoting, which
is a transport mechanism. This mechanism can sit between the Business layer
and the data layer or the data layer and the UI. Ultimately, the core of the
data layer is more generic, like the Microsoft Data Access Application
Block. There are times you will have more specific data elements on the data
layer, like strongly typed DataSet definitions with their data access
methods (which use the generic data tier) and other times, the business
rules will be more of a driver (indicating the sproc used to access data,
for example).

In your model, you can have the web method stay as is, but add something
more generic on another data tier. The Data Access Application Block is not
a bad choice, as it is free and the code is already written for you. There
is an error in the FillDataSet() method where the table mapping names are
linked to the generic names created when the DataSet is filled, but it is
not too hard to edit.

Now, to where the web service should be.

If you have an "app server" that also hosts your data access code, you will
have web services as the transport between web server (UI) and app (business
layer). If you are dealing with web server, app server and data server, you
may end up with web services at both spots, although the Remoting type
perform better. NOTE that it is unusual to have two web services layers in
your app model. More common, you will see database server with business
rules and data on the same server, as filtered data (data that has passed
through some form of business rules component(s)) is more common, esp. since
you often see web services used for Extranet situations.

Another option is setting up your assemblies to go inside COM+ and
distributing your application that way.

I realize this is more theory than anything else, but I hope it gives you
some ideas.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************
"headware" <he******@aol.com> wrote in message
news:e3**************************@posting.google.c om...

I'm relatively new to ASP.NET and ADO.NET, but I have a basic design
question regarding the use of web services and APS.NET applications.
Right now we have an application that uses web services to access the
database layer. However, the code works in a pretty cumbersome and
ungeneric way. Basically every query, update, and insert has its own
[WebMethod] function. So you see a lot of functions like

webService.InsertCustomer(name, age, phone);
or
DataSet custDS = webService.GetAllCustomers();
or
webService.UpdateCustomer(custId, name, age, phone);

I have my doubts about whether this is the best way to be interfacing
with the data layer. Couldn't you store the customer currently being
worked on (assuming you have a page in which you can work on the data
for a single customer at a time) in a DataSet stored in the Session
object and send that DataSet back to the data later so the DataAdapter
can call the Update() method on it? Unless I'm missing something, that
should allow you to have only one web service function for inserting,
deleting, and updating any table in the database. For example, you
could update the database like this

[WebMethod]
public void UpdateDatabase(DataSet ds)
{
OleDbConnection connection = new OleDbConnection("");
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " +
ds.Tables[0].TableName, connection);
adapter.Update(ds);
}

As long as the DataSet rows have the proper rowstate values, this one
function should do all your inserting, updating, and deleting for you,
right?

Of course, you'd have to be careful about storing large DataSets in
the Session object for the sake of scalability. In our particular
application, there would never be all that many users logged on at any
given point so I don't think that should be an issue.

What I'm really getting at here is that I would like
ideas/experience/articles/books about how people interface with the
data layer in ASP.NET applications.

Thanks,
Dave

[headware]

Thanks for the response. The more I think about the situation the more
I think that the correct solution is to build a business layer between
the UI and the database. Doing that would make the issues I brought up
in the first post mostly moot.

I checked out the ASP.NET Alliance start kits (found at
http://www.asp.net/Default.aspx?tabindex=9&tabid=47). The two that I
have downloaded and looked at are set up as 3-tiered systems with
ASP.NET as the UI, a set of classes to perform the business logic, and
SQL Server Stored Procs as the data access tier. Seems like a pretty
good separation except for the fact that the business tier is
dependent on ADO.NET since it instantiates classes like SqlDataAdapter
to call the Stored Procs. The UI layer is also dependent on ADO.NET
since it uses the DataSets and DataReaders the business layer returns.
Is that considered good practice? I realize that it would probably be
difficult to totally separate the technologies used from the different
layers of the application. I suppose you'd have to come up with your
own classes for sharing the data between layers. Is that done often?

Dave

"Cowboy \(Gregory A. Beamer\)" <No************@comcast.netNoSpamM> wrote in message news:<#K**************@tk2msftngp13.phx.gbl>...

Let's look at the purpose of web services, whether ASMX or Remoting, which
is a transport mechanism. This mechanism can sit between the Business layer
and the data layer or the data layer and the UI. Ultimately, the core of the
data layer is more generic, like the Microsoft Data Access Application
Block. There are times you will have more specific data elements on the data
layer, like strongly typed DataSet definitions with their data access
methods (which use the generic data tier) and other times, the business
rules will be more of a driver (indicating the sproc used to access data,
for example).

In your model, you can have the web method stay as is, but add something
more generic on another data tier. The Data Access Application Block is not
a bad choice, as it is free and the code is already written for you. There
is an error in the FillDataSet() method where the table mapping names are
linked to the generic names created when the DataSet is filled, but it is
not too hard to edit.

Now, to where the web service should be.

If you have an "app server" that also hosts your data access code, you will
have web services as the transport between web server (UI) and app (business
layer). If you are dealing with web server, app server and data server, you
may end up with web services at both spots, although the Remoting type
perform better. NOTE that it is unusual to have two web services layers in
your app model. More common, you will see database server with business
rules and data on the same server, as filtered data (data that has passed
through some form of business rules component(s)) is more common, esp. since
you often see web services used for Extranet situations.

Another option is setting up your assemblies to go inside COM+ and
distributing your application that way.

I realize this is more theory than anything else, but I hope it gives you
some ideas.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************
"headware" <he******@aol.com> wrote in message
news:e3**************************@posting.google.c om...

I'm relatively new to ASP.NET and ADO.NET, but I have a basic design
question regarding the use of web services and APS.NET applications.
Right now we have an application that uses web services to access the
database layer. However, the code works in a pretty cumbersome and
ungeneric way. Basically every query, update, and insert has its own
[WebMethod] function. So you see a lot of functions like

webService.InsertCustomer(name, age, phone);
or
DataSet custDS = webService.GetAllCustomers();
or
webService.UpdateCustomer(custId, name, age, phone);

I have my doubts about whether this is the best way to be interfacing
with the data layer. Couldn't you store the customer currently being
worked on (assuming you have a page in which you can work on the data
for a single customer at a time) in a DataSet stored in the Session
object and send that DataSet back to the data later so the DataAdapter
can call the Update() method on it? Unless I'm missing something, that
should allow you to have only one web service function for inserting,
deleting, and updating any table in the database. For example, you
could update the database like this

[WebMethod]
public void UpdateDatabase(DataSet ds)
{
OleDbConnection connection = new OleDbConnection("");
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " +
ds.Tables[0].TableName, connection);
adapter.Update(ds);
}

As long as the DataSet rows have the proper rowstate values, this one
function should do all your inserting, updating, and deleting for you,
right?

Of course, you'd have to be careful about storing large DataSets in
the Session object for the sake of scalability. In our particular
application, there would never be all that many users logged on at any
given point so I don't think that should be an issue.

What I'm really getting at here is that I would like
ideas/experience/articles/books about how people interface with the
data layer in ASP.NET applications.

Thanks,
Dave