Solved

3 Tier application Connection Object/NT authentication

Posted on 2004-08-10
9
273 Views
Last Modified: 2012-05-05
hi,

I want to implement 3 tier MULTI USER application in visual basic(Client/Dll/Db) . I want to implement windows Authentication
with sqlserver2000 as Database

Connection string is :

dim strsql as string="Provider=sqloledb;database=Test;server=computer1;Trusted_connection=True"

How should the connection object be written in the middleLayer.

Lets say I have several functions in my DLL's which interact with the database.
Should I design in such a way that in each and every function open database serve data to the client
and close it Or should i go for global connection .......

Could it be explained in detail which technique to follow and why with a simple example

Thanks in advance
0
Comment
Question by:sainavya1215
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 22

Expert Comment

by:DarkoLord
ID: 11767273
Hi,
I would make a class for database connection/manipulation/etc and call .Open and .Close methods in every function so there would be as little code as possible because for a global connection you would always have to check if a connection is still "on" (you never know when you might get disconnected) and if not you would have to estabilish it again and so on...

Just a thought :))

Darko
0
 
LVL 3

Expert Comment

by:TILL
ID: 11767489
Business Logic usually performes multiple operations on a database. If you use SQL Server, I would recomend you to create a dynamic connection pool manager and create a stored procedure on the server for each business rules that you have in your application. The advantage of this approach is that whenever a field is added tio your database, you can anly update the stored procedures without rewriting important parts of the application, and regarding the pool manager is that you minimize the overload and the roundtrips to the server.

If you need more details, just ask.
Best of luck, TILL.
0
 

Author Comment

by:sainavya1215
ID: 11767717
1)

ok lets say I have  Db.open and db.close(finally) in all my functions in my DLL's . Lets say 4 users have connected to the application. Will it contain 4 connections or? How can we analyze this. MoreOver if we are going to implement NTauthentication will this technique work.. Isnt it that connection open and close each and every time effect the performance. If opening and closing will the UserContext be mainatianed(NTauthentication) as sqlserver is on different box.  


2) If its global conn and we just pass connObj to functions instead of opening and closing it again and again . How would this work lets say 4 users are connected. How many connections would be created. Will the connection be opened only once Or 4 times.

I need to understand how it works in a multiUser environement. If u say about connection pooling how can it be implemented in the 1st scenario as i heard Pooling doesnot work in NTauthentication (though we are doing it right using open and close connection in all our functions)

In some of the Doc's it says "OPEN CONN late close early"  in some it says open/close is performance loss at DB level..........I really dont understand instead confused which to use to IMPLEMENT authentication / Pooling  etc and how it works in both scenarios(how many connections are created etc) are multi User environment
0
 
LVL 22

Expert Comment

by:DarkoLord
ID: 11767907
Connecting and disconnecting every time is not a performance issue as these things work very fast...

Darko
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 2

Accepted Solution

by:
born4code earned 500 total points
ID: 11769235
Here is some possible help for you.

"I want to implement 3 tier MULTI USER application in visual basic(Client/Dll/Db) . I want to implement windows Authentication with sqlserver2000 as Database..."

Connection string is :
dim strsql as string="Provider=sqloledb;database=Test;server=computer1;Trusted_connection=True"

-- Okay, go out to http://www.able-consulting.com and doublecheck your syntax.


How should the connection object be written in the middleLayer.

-- You are on the right track.  Basically, it is going to depend upon what functionality you want.  Let's say for example, that your use only wants to get a recordset or something and use that data in the application, as read-only data.  Or... for example, the user may want to grab a recordset and play with it, and then send it back to sql server for updating.  Either way, you can return a disconnected recordset from your dll.  If you want to use it for updating, then use a batch recordset.  Inside of your dll, call a stored procedure.  That makes it even faster.  Does that help?

Lets say I have several functions in my DLL's which interact with the database.
Should I design in such a way that in each and every function open database serve data to the client
and close it Or should i go for global connection .......

-- I have done both... and it depends on a number of things.  If your database and server can handle it, then you can create a global ado connection and just leave it hanging around during the time the app is being used.  When you are ready to get a recordset or update something in the database, then use that connection.  Obviously, if your users are not connected to a hard network (remote) then that would not be the thing to do.  The key is... get in there, get your data, get out.

Could it be explained in detail which technique to follow and why with a simple example

-- okay try this in your dll.  If you are using visual basic, then

Dim m_oCon As ADODB.Connection
Set m_oCon = New ADODB.Connection
Set m_oCon = OpenSQLIP()

Here is the OpenSQLIP function:


Public Function OpenSQLIP() As Connection
     Dim oCon As ADODB.Connection    
        Set oCon = New ADODB.Connection
        oCon.CursorLocation = adUseClient
        oCon.ConnectionTimeout = 180        

'*** you can see I have some variables in here, you need to set those up

        oCon.Open "Provider=sqloledb;" & _
            "Data Source=" & g_sIPAddress & ",1433;" & _
            "Network Library=dbmssocn;" & _
            "Initial Catalog=" & g_sDatabase & ";" & _
            "User ID=sa;" & _
            "Password=sa;"
           
        Set OpenSQLIP = oCon
        Set oCon = Nothing
End Function


Now, when you are first testing this, don't do it in a dll.  Do it in the vb project until you get it right.  Then send it out to a dll if you have to.  Now you can use this connection when you need it (if you keep it global).

Good luck
 
0
 

Author Comment

by:sainavya1215
ID: 11769965
ok fine.. Actually I am implementing Remoting(Dcom) u can say where i have my DLL's hosted in IIS... IIS and sqlserver2000 reside on different box. ie all the dll's are in central location. Now clients connect  to that box.

In this type of scenario which is best to use to achieve NT authentication. currently I am having the following structure


i have a connection class (function which retuns connection) Lets say

public function OpenConn() as connection

return connection
end function

public function connClose()
close connection
end function

In all my dlls I am using stored proc's..........I open the conn in each and every function and close it ....So every function call opens and closes connection....

My doubts are :1)  In distributed com environment is this right approach..........How can the user context be maintained when i am open and closing functions in each and every function.I heard that open conn in every function is not good (But doc's say open conn late and close it early ie what i am followng now right)

2) In some other doc's it says when ur having a remote DB better u use a global connection........

Hope its clear what I want to achieve. Considering its multiUserapp/implementation of NT authentication etc Pls advice me what would be the best and WHY?

Thanks in advance

0
 

Author Comment

by:sainavya1215
ID: 11769970
infact its .NET remoting scenario
0
 
LVL 2

Expert Comment

by:born4code
ID: 11779957
I have the understanding that your application will complete authentication once when the connection is established.  After the connection has been established, you do not have to keep "repeating" authentication.  Is this correct?
0
 

Author Comment

by:sainavya1215
ID: 11791255
My question is


In my current model i  am serveral functions in my Dlls which interact with the database. Now.. I have a connection class which  has 2 functins 1) Opens connection
2) closes connection


So in each and every function in my Dll i   open and close function after data is served. Now..I am using Windows authentication ......... Lets say a user connects Now user is authenticated sucessfully and connection is CLOSED........1) Will the user context be maintained or not???

  how would u maintain userContext when u open a connection and Close it ...... So i wanted to make sure that what i had done is right or not.... I know that A token  is sent first when connecting To db to authenticate the user but when we close connection wil the UserContext be identified OR SHOULD WE USE A GLOBAL CONNECTION and remain always connection to maintain userContext
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now