What's the best way to open a Connection to DB in the WEB envoironment

[Ahmed Jewahar]

Hi All,

I'm looking for an Ideal and best logic towards open a database
connection to SQL server 2000 from an ASPX pages.

I have desinged an Intranet application. But I don't know that I have
applied an ideal logic to open a connection from my application.

Below is the details of my applicaiton:

Application : ASP.NET Intranet Application
WebServer: IIS Server
Backend Database : SQL Server 2000. Configured as "Use SQL Server
Authenticaiton"


I have about 60+ users who use my Inreanet application extensivley daily
basis. We have many remote users (using leased line) within our Network
(VAN).

I have created "Login" for each users in SQL Server 2000 and all of them
added (user) to all the Database. In my application, there is a "Login"
form where all the usres must enter their "Login" to enter the
applicaiton.

I'm using the above login to open a "Connection" to my SQL server. For
example if there are 50 user accessing my Intranet Application, there
would be 50 Connection will opened at Database !. I'm afraid this might
cause my database performance.

My questions are :

a) Can I use a Common "Login" for all the userd to open a "Connection"
to my SQL Server 2k. ? Will it incerase the performance of my
application as conneciton string can be utilized from "Pooling" ?.

b) If I user One 'Login" for all the users to open a connection, I won't
be able to monitor each users activity (from SQL profiler). Isn't it ?.

c) What's is normal method people are following or MS suggesting towards
this type of situaitons ?

d) Also In my application there are server ASPX pages which are
basically for various reporting and analysis purpose. In every page, I
OPEN the "Connection" and CLOSE at the end of the process. But not using
"cn1.Dispose" in order to utilise the conneciton from "Pooling" .

Kindly share all of your expertise in this area. I would be more than
happy to follow best logic of yours.

Expecting quick reply and really appreciate for it.

Hope my quiery make sense. Otherwise pls let me know so I can give more
details. I'm not good at writing.

Many thanks in advance..

 

[Scott Allen]

Ahmed:

a) Yes, use a common login for your web application and take advantage
of connection pooling.

b) You could pass the username or login along as a parameter to any
stored procedure to record activity (that's just one possible
solution).

c) Here are some resources:

..NET Data Access Architecture Guide
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag.asp

Implement a Data Access Layer for Your App with ADO.NET
http://msdn.microsoft.com/msdnmag/issues/03/04/DataAccessLayer/default.aspx

Data Access Application Block Overview
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/cmab.asp

 

[Michael Hampel]

Hi All,

I'm looking for an Ideal and best logic towards open a database
connection to SQL server 2000 from an ASPX pages.

I have desinged an Intranet application. But I don't know that I have
applied an ideal logic to open a connection from my application.

Below is the details of my applicaiton:

Application : ASP.NET Intranet Application
WebServer: IIS Server
Backend Database : SQL Server 2000. Configured as "Use SQL Server
Authenticaiton"


I have about 60+ users who use my Inreanet application extensivley daily
basis. We have many remote users (using leased line) within our Network
(VAN).

I have created "Login" for each users in SQL Server 2000 and all of them
added (user) to all the Database. In my application, there is a "Login"
form where all the usres must enter their "Login" to enter the
applicaiton.

I'm using the above login to open a "Connection" to my SQL server. For
example if there are 50 user accessing my Intranet Application, there
would be 50 Connection will opened at Database !. I'm afraid this might
cause my database performance.

My questions are :

a) Can I use a Common "Login" for all the userd to open a "Connection"
to my SQL Server 2k. ? Will it incerase the performance of my
application as conneciton string can be utilized from "Pooling" ?.

For your iis application change the anonymous user to a suitable nt
account.
Add this account to sql server then use trusted security to connect to
your database. As connection pooling works by matching connection
strings you will now use this and that should increase performance
slightly.

b) If I user One 'Login" for all the users to open a connection, I won't
be able to monitor each users activity (from SQL profiler). Isn't it ?.

That is true though you will still see what application is making the
call

c) What's is normal method people are following or MS suggesting towards
this type of situaitons ?

I would use trusted security and MS recommend that you should use that
rather than SQL security.

d) Also In my application there are server ASPX pages which are
basically for various reporting and analysis purpose. In every page, I
OPEN the "Connection" and CLOSE at the end of the process. But not using
"cn1.Dispose" in order to utilise the conneciton from "Pooling" .

Thats fine though but I think its down to .net when the actual
connection object is destroyed as it is manged code.

 

[Ahmed Jewahar]

scott,

Thanks for your reply and kind advise it's really helped me !.

Regards,

Ahmed

 

[Ahmed Jewahar]

Michael,

Thanks for your advise and it's really helped me.

I will be using a "Common Login" to open a connection to a Datasource
for my application.

Having One more question. I normally specify the "Database" name along
with connection string. Using the same connection, to read from another
database which is avaliable in the same data source (SQL server). My
question is, (a) can we use One Connection (Cn1) to read from various
database within same server. will it make any perforamnce issue ? (b)
Should I open separate "Connections" for each databases sitting on same
"data source" ? Kindly advise..

Below is the example most of time I used.

Cn1.ConnectionSring = "data source=myServer; initial Catalog=myDB1; user
id=sa; password=pwd"
cn1.open
cmd1.Connection = cn1

cmd1.CommandText = "select *into #Tmp from Table1"
cmd1.ExecuteNonQuery

cmd1.CommandText = "select *from [MyDB2].[dbo].OtherTable"
dr1 = cmd1.ExecuteReader
myDataGrid1.Datasource = dr1
myDataGrida.bindData.

dr1.close
cn1.close

Many thanks in advance..

 

[Michael Hampel]

If you wanted only one connection you could use the ChangeDatabase
method of the connection component. Using this would still provide
connection pooling and I dont think there is any performance penalty.

But if you required to use 2 seperate databases frequently you may
prefer to open 2 seperate connections though this would make it more
difficult to manage transactions if you updates needed to be
transactional.

 

[Ahmed Jewahar]

Michael,

Thanks for your reply.

But, the below point your mention is not clealy undertood.
prefer to open 2 seperate connections though this would make it more
difficult to manage transactions if you updates needed to be
transactional.>>

you mean, I use one "Conn" for updating different database, I won't be
able to apply "begin trans", "rollback" etc.... this is what do you mean
?. Kindly advise..

should I open diferent connection for each databse if the updates
required ?. Pls advise..


Many thanks in advance for your help..