Multi threaded app database access

[Macca]

Hi,

I have a multithreaded app which now needs database storage.

I am in the process of designing my Data Access Layer but and was wondering
what issues I should look for for in regards to a multi threaded app.

My app will be need to initiate access to the database from several places
in the Business logic. I'd appreaciate any advice/suggesstions on best
practices to avoid problems such as accessing the same method in the data
access layer form different parts of the business logic at the same time etc,

Thanks In Advance
Andy

[Michael Nemtsev]

RE: Multi threaded app database access

> I have a multithreaded app which now needs database storage.[color=blue]
> I am in the process of designing my Data Access Layer but and was wondering
> what issues I should look for for in regards to a multi threaded app.[/color]

Performance, resources, scalability. Look at connection pools and thread pool
[color=blue]
> My app will be need to initiate access to the database from several places
> in the Business logic. I'd appreaciate any advice/suggesstions on best
> practices to avoid problems such as accessing the same method in the data
> access layer form different parts of the business logic at the same time etc,[/color]

It's not very good solution to block table from the business logic
perspective.
DBs have concept of ACID see there
http://www.service-architecture.com/...roperties.html or
in MSDN that allow to specyfy which level of isolation do u need to apply for
your tables. Scrutinize DB manuals to get more info about it

--
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche

[Ignacio Machin \( .NET/ C# MVP \)]

Re: Multi threaded app database access

Hi,

"Macca" <Macca@discussi ons.microsoft.c om> wrote in message
news:0A4A6F83-56EE-4392-BC54-75F76FBB8631@mi crosoft.com...
[color=blue]
> I am in the process of designing my Data Access Layer but and was
> wondering
> what issues I should look for for in regards to a multi threaded app.[/color]

A web app with DB access faces the very same issues, you could read some
articles about how to implement DB access in the web.
[color=blue]
> My app will be need to initiate access to the database from several places
> in the Business logic. I'd appreaciate any advice/suggesstions on best
> practices to avoid problems such as accessing the same method in the data
> access layer form different parts of the business logic at the same time
> etc,[/color]

I would keep the DB access class as a stateless class, each method receive a
ICommand that just execute using the selected way (
ExecuteReader/NonQuery/Scalar ). Also each method will create its own
connection.



--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Macca]

Re: Multi threaded app database access

Hi Ignacio,

Wouldn't I have to lock the methods in the database access class while in
use to stop them being called from elsewhere in the business logic?

Regards
Macca

"Ignacio Machin ( .NET/ C# MVP )" wrote:
[color=blue]
> Hi,
>
> "Macca" <Macca@discussi ons.microsoft.c om> wrote in message
> news:0A4A6F83-56EE-4392-BC54-75F76FBB8631@mi crosoft.com...
>[color=green]
> > I am in the process of designing my Data Access Layer but and was
> > wondering
> > what issues I should look for for in regards to a multi threaded app.[/color]
>
> A web app with DB access faces the very same issues, you could read some
> articles about how to implement DB access in the web.
>[color=green]
> > My app will be need to initiate access to the database from several places
> > in the Business logic. I'd appreaciate any advice/suggesstions on best
> > practices to avoid problems such as accessing the same method in the data
> > access layer form different parts of the business logic at the same time
> > etc,[/color]
>
> I would keep the DB access class as a stateless class, each method receive a
> ICommand that just execute using the selected way (
> ExecuteReader/NonQuery/Scalar ). Also each method will create its own
> connection.
>
>
>
> --
> Ignacio Machin,
> ignacio.machin AT dot.state.fl.us
> Florida Department Of Transportation
>
>
>[/color]

[Ignacio Machin \( .NET/ C# MVP \)]

Re: Multi threaded app database access

Hi,

"Macca" <Macca@discussi ons.microsoft.c om> wrote in message
news:B9E0DE4D-76EA-41B3-B17B-389B4A59F3B4@mi crosoft.com...[color=blue]
> Hi Ignacio,
>
> Wouldn't I have to lock the methods in the database access class while in
> use to stop them being called from elsewhere in the business logic?[/color]

No, unless the DB class is using shared resources, which you should not.

Here is an example, this class deal with the DB comm, it does receive a
SqlCommand (which is built by the BO ).

As you can see none method use any shared resources, and a connection is
created and closed inside the method.

public class DataProvider
{
static string connString;
public static string ConnString
{
get{ return connString;}
set
{
connString = value;
}
}


static public object ExecuteScalar(S qlCommand command)
{
object returnvalue = null;
if ( connString == "")
throw new Exception("No connection string defined");
SqlConnection conn = new SqlConnection( connString);
try
{
conn.Open();
command.Connect ion = conn;
returnvalue = command.Execute Scalar();

}
catch( Exception e)
{
throw new Exception("Erro r ocurred in ExecuteScalar: commandtext: " +
command.Command Text, e);
}
finally
{
conn.Close();
}
return returnvalue;

}
static public SqlDataReader ExecuteReader(S qlCommand command)
{}

[William Stacey [MVP]]

Re: Multi threaded app database access

Your tsql procs may need special attention. I think a reasonable design is
to have all updates in sprocs and have selects in client code and/or in
sprocs. Most of the sql will automatically handle sync within the context
of the sql statement transaction. Sometimes, for special procs like a "test
and set" you may need to ensure correct atomic behavior with other tsql
symantics. But that is all on the sql end. Your DAL could probably be a
static class with all static methods, so you should not need any special
synchronization in general. Naturally, this may not always be true and you
may need some shared object(s) that will need to be sync'd with a lock, etc.
It all depends on what your doing.

--
William Stacey [MVP]

"Macca" <Macca@discussi ons.microsoft.c om> wrote in message
news:B9E0DE4D-76EA-41B3-B17B-389B4A59F3B4@mi crosoft.com...
| Hi Ignacio,
|
| Wouldn't I have to lock the methods in the database access class while in
| use to stop them being called from elsewhere in the business logic?
|
| Regards
| Macca
|
| "Ignacio Machin ( .NET/ C# MVP )" wrote:
|
| > Hi,
| >
| > "Macca" <Macca@discussi ons.microsoft.c om> wrote in message
| > news:0A4A6F83-56EE-4392-BC54-75F76FBB8631@mi crosoft.com...
| >
| > > I am in the process of designing my Data Access Layer but and was
| > > wondering
| > > what issues I should look for for in regards to a multi threaded app.
| >
| > A web app with DB access faces the very same issues, you could read
some
| > articles about how to implement DB access in the web.
| >
| > > My app will be need to initiate access to the database from several
places
| > > in the Business logic. I'd appreaciate any advice/suggesstions on best
| > > practices to avoid problems such as accessing the same method in the
data
| > > access layer form different parts of the business logic at the same
time
| > > etc,
| >
| > I would keep the DB access class as a stateless class, each method
receive a
| > ICommand that just execute using the selected way (
| > ExecuteReader/NonQuery/Scalar ). Also each method will create its own
| > connection.
| >
| >
| >
| > --
| > Ignacio Machin,
| > ignacio.machin AT dot.state.fl.us
| > Florida Department Of Transportation
| >
| >
| >