n-tier approach - Saving sql connection

[motil]

Hi all, what is the most correct way to save your connection string when using the n-tier approach ? right now i have two windows app and one web app using my DLL library, what i'm currently doing is to create a static
method inside my dal "GLOBALDAL"
inside i'm making this check :

if (System.Web.HttpContext.Current != null)
 ...

if httpContext.Current is null that's mean that one of my windows appliactions doing the request, if so i get the CN from a file otherwise it's mean its the website knocking on my door so i get the CN from:

 System.Web.HttpContext.Current.Application["ConnectionString"]

i thinking of changing it all and just set propery IN my DA layer that expose the CN as a string.

but I fear of security issues .... so what is the correct way?

thanks!

[techgnome]

Why would anything other than you DAL care about the connection string? If the DAL is properly isolated, only it should have access to the ConnectionString, and only it should care. So your app (be it the Web or desktop) makes a request to the Biz object... which makes a request to the next layer, which then requests the data from the DAL... which makes the DB connection, gets the data and passes it back.

your layers and tiers (which are not the same thing btw) shouldn't care about the workings of the next or previous layer/tier.

-tg

[motil]

Hi tg, thanks for the answer

i declare my DAL classes as internal and only the BLL has access to the DAL so it safe to put the connection string as simple string in it and expose it with property?

[techgnome]

the BLL still should not care what the ConnectionString is... or where it came from or where it points to... ONLY the DAL cares. My point plain and simple is that there is no need to make it a property since no one else but the DAL will care about the connectionstring.

-tg

[gep13]

Hey,

Just my two cents worth, but here goes....

I think where motil is coming from is that there might be different applications, let's say a windows application with an app.config file, and say a web application with a web.config, and within these config files, you might want to store the connection string in there. These applications, might be hitting the same database, or they might be hitting different databases, but regardless, they are still using the same DAL, which might reside on a different server from where the UI code is running.

As a result, how do you pass the connection string from the application through to the DAL, since it isn't clear at this point, what/where that connection string comes from.

Gary

[motil]

the property is made for the DAL use, my BLL never see the connection string in any way
for example in the DAL

using(var connection = new SqlConnection(GlobaDALClass.GetConnectionString))
{
 ....
}

the BLL don't know nothing about data pulling it does has reference to the DAL for calling it's methods

using MyLogic.DAL

got it?

[gep13]

But that wouldn't make sense though, are you saying you are opening a new connection in the BLL?

Gary

[motil]

nop.. what doesn't make sense?

inside the DAL
i need to get the connection string somehow correct ?

so i set internal property inside the DAL that gives the connection string to use.

[gep13]

Ah, ok, so what you are saying is you have a public property in your DAL, which you set from the BLL, and then use this property within the DAL, when creating your connection object.

Is that correct?

Gary

[motil]

ok Gary i'll give you example of how things works, tell me if this is the correct direction:


A) a global class in the DAL

internal class GlobalDAL
{

// property for getting my CN from the DAL (all DAL classes will get the CN from here)
 internal static  string ConnectionString
  {
      get {return "My Connection String"; }
  }
}

B) another class inside the DAL this time the class returns data and using the above property to get
the CN

internal class CompanyDAL
{

    internal static  List<Company> GetListOfCompaniesDAL(int CompanyID) {
           
       using(var connection = new sqlConnection(GlobalDAL.ConnectionString))
        {
            // the rest of the code goes here....
        }  

    }
  return // some list of companies 
}

C) now the next method is inside the BLL getting the list of company (using the GetListOfCompanies)

public class Company {
public static List<Company> GetListOfCompaniesBLL(int CompanyID) {
   return  CompanyDAL.GetListOfCompaniesDAL(CompanyID);
}
}

and from the presentation layer i will call the above BLL method to get the list

protected void Page_Load(object sender,EventsArgs e)
{
   var lstCompany = Company .GetListOfCompaniesBLL(214);
   
   // do what ever i wish the list...
}

is that fine??

btw - i wrote it by hand since i don't have VS next to me so sorry if there is some mistakes there.

[techgnome]

as-is... that's probably fine... as long as the the connection string isn't available to the BL...

Now... that being said... I wouldn't hard-code the connection string. I'd keep it in the config file and load it from there. your static connectionstring property could do that... OR, each DAL class can do it on its own (thereby negating the need to have the static property.)

-tg

[motil]

Hi tg, when you're saying "config file" do you mean *any* file for example "config.txt" or you mean something that built in the .NET framework?

because this is what i am doing right now, loading the cn from a file, but isn't that just a lot of work for the server to keep open and close the file (if there is 1000's of people using the application + the website) and also, what will happen if two or more users will try to open the file at the same time? wouldn't then one of the users will get error since the file is already in use ?

thanks again.

[motil]

can i ask how you guys get the connection string when using the n-tier approach in more then 1 application (win/web)

[gep13]

In the application that I wrote most recently, a web application, where the BLL and DAL code were running under the same process within IIS, I just put the connection string directly in the web.config file of the application, and the DAL read directly from there.

In order to do this, I just used the WebConfigurationManager class.

In terms of concurrency, you could "lock" the reads from this file, so that only one thread can do it at a time.

Gary

[motil]

Ok, what would you do you'll need that win application will user ur DAL? it can't read it from the web config, correct?

and how do i lock the file ?

[gep13]

Hey,

This is the point that I was trying to make in post #5. In my situation, I couldn't, because I essentially hard coded the connection string into the web.config file for the web application. Arguably, this isn't the best place for it, it perhaps should have been put into an isolated config file, which could be used just by the DAL, and not have any other information in it.

Or, from another stand point, you could argue that there should be a web service between the DAL and the applications, and both applications talk to the Web Service, and the Web Service configuration file have the connection string in it.

However, this doesn't cover the fact that the DAL code might be running on a machine other than the one that the web service is on.

Bottom line, I am not sure what the best course of action is. I have never settled in my own bed what is the best approach.

In terms of locking access to the file, this is what I was referring to:

http://msdn.microsoft.com/en-us/libr...cz(VS.80).aspx

i.e. put a lock around the code that is actually accessing the config file.

Gary

[motil]

Thanks Gary, so if i want to lock the file access i should surround the:

using(var sw = new StreamWriter()) {
} ...

?

one more thing in this code:

 lock(thisLock)
        {
   }

"thisLock" is actually an object that created when an instance of the class was created, why its done like that ? i mean every new instance will have new "thisLock" object

Thanks again.

[techgnome]

that's why our connectionstrings are in the machine.config file on the remote servers.... then it doesn't matter if if it's a web project or a win project...

-tg

[motil]

hi tg,
what exactly is "machine.config" ? how do i create it and how i call it from web/win app ?

thanks

[gep13]

Ah, tg, interesting I hadn't considered that. This still doesn't help though is you have your tiers separated over various computers.

motil, the machine.config file is stored in your .Net Framework directory, and it is used to combine machine settings with the settings in either your app.config file or your web.config file.

The web.config file that your web application uses is actually a merge of the machine.config file, and any web.config files it finds in your application.

Have a look here:

http://msdn.microsoft.com/en-us/library/aa719558.aspx

Gary

[MarMan]

Cool discussion. I can show you what I've done so maybe you can get ideas from anything I've done well and point out any weaknesses. This is a windows app.

Connection information stored in a ini file. We use SQL Server 2005 and windows authentication.

[Connection]
Server=SERVERNAME
Database=DATABASENAME

Then I have a MainDataAccess in its own DLL which sounds like it is the same as GloabalDAL;

Imports System.Data.SqlClient

Public Module MainDataAccess

    Public Interface IDataAccess 'Used in all objects that will represent a row in the DB (not the collections; i.e. datasets)

        ReadOnly Property Dirty() As Boolean

        ReadOnly Property ExistsInDB() As Boolean

        ReadOnly Property LastDBError() As String

        ReadOnly Property [Readonly]() As Boolean

        Sub Instantiate()

        Function Load(ByVal strKey() As String) As eLoadResultType

        Function Save() As Boolean

        Function Delete() As Boolean

    End Interface

    Private mstrServer As String
    Private mstrDatabase As String
    Private mstrLastError As String
    Private mConn As System.Data.SqlClient.SqlConnection
    'Set only when a record is locked.
    Private mstrLockKey As String
    Private mstrLockedBy As String
    Private mstrLockedOnHost As String


    Public ReadOnly Property Connected() As Boolean

        Get
            If mConn Is Nothing Then
                Return False
            Else
                Return mConn.ConnectionString <> vbNullString
            End If
        End Get

    End Property

    Public ReadOnly Property Connection() As String

        Get
            If mConn Is Nothing Then
                Return mconNotConnected
            Else
                Return mConn.ConnectionString
            End If
        End Get

    End Property

    Public ReadOnly Property LastDBError() As String

        Get
            Return mstrLastError
        End Get

    End Property


    Public Property Server() As String
        Get
            Return mstrServer
        End Get
        Set(ByVal value As String)
            mstrServer = value
        End Set

    End Property


    Public Property Database() As String
        Get
            Return mstrDatabase
        End Get
        Set(ByVal value As String)
            mstrDatabase = value
        End Set

    End Property

    Public Property LockedBy() As String
        Get
            Return mstrLockedBy
        End Get
        Set(ByVal value As String)
            mstrLockedBy = value
        End Set

    End Property

    Public Property LockedOnHost() As String
        Get
            Return mstrLockedOnHost
        End Get
        Set(ByVal value As String)
            mstrLockedOnHost = value
        End Set

    End Property


    Public Function AddLock(ByVal strTable As String, ByVal strKey As String) As Boolean?

        Dim strComputerName As String = vbNullString
        Dim strSQL As String
        Dim strUserName As String = vbNullString
        Dim cmd As SqlCommand


        Try

            If ConnectionOK() IsNot Nothing Then
                ScalarFunctions.CurrentUserLoginName(strUserName)
                ScalarFunctions.ComputerName(strComputerName)
                strSQL = "INSERT INTO usysLogicalLocks" & _
                " (LockType, LockKey, LockedBy, LockedOnHost)" & _
                " VALUES ('" & strTable & "', '" & strKey & "', '" & strUserName & "', '" & strComputerName & "')"
                cmd = New SqlCommand(strSQL, mConn)
                cmd.ExecuteNonQuery()
                Return True
            End If


        Catch ex As Exception

            mstrLastError = ex.Message
            Return Nothing

        End Try

    End Function

    Public Function IsLocked(ByVal strTable As String, ByVal strPKey As String) As Boolean?

        Dim strSQL As String
        Dim cmd As SqlCommand
        Dim Reader As SqlDataReader = Nothing


        Try

            If ConnectionOK() IsNot Nothing Then
                strSQL = "SELECT LockedBy, LockedOnHost" & _
                " FROM usysLogicalLocks" & _
                " WHERE LockType='" & strTable & "' AND LockKey='" & strPKey & "'"
                cmd = New SqlCommand(strSQL, mConn)
                Reader = cmd.ExecuteReader()
                If Reader.Read() Then
                    mstrLockedBy = Reader("LockedBy").ToString
                    mstrLockedOnHost = Reader("LockedOnHost").ToString
                    Return True
                Else
                    Return False
                End If
            End If


        Catch ex As Exception

            mstrLastError = ex.Message
            Return Nothing

        Finally

            Reader.Close()

        End Try

    End Function

    Public Sub RemoveLock(ByVal strTable As String, ByVal strPKey As String)

        Dim strSQL As String
        Dim cmd As SqlCommand


        Try

            If ConnectionOK() IsNot Nothing Then
                strSQL = "DELETE " & _
                    " FROM usysLogicalLocks" & _
                    " WHERE LockType='" & strTable & "' AND LockKey='" & strPKey & "'"
                cmd = New SqlCommand(strSQL, mConn)
                cmd.ExecuteNonQuery()
            End If


        Catch ex As Exception

            mstrLastError = ex.Message

        End Try

    End Sub

    Public Function UnlockIfOwned(ByVal strTable As String, ByVal strPKey As String) As Boolean

        Dim strComputerName As String = vbNullString
        Dim bLocked As Boolean = True 'We only get here if the SQL fails.
        Dim strSQL As String
        Dim strUserName As String = vbNullString
        Dim cmd As SqlCommand


        Try

            ScalarFunctions.CurrentUserLoginName(strUserName)
            ScalarFunctions.ComputerName(strComputerName)
            If mstrLockedBy = strUserName And _
            mstrLockedOnHost = strComputerName Then
                strSQL = "DELETE " & _
                " FROM usysLogicalLocks" & _
                " WHERE LockType='" & strTable & "' AND LockKey='" & strPKey & "'"
                cmd = New SqlCommand(strSQL, mConn)
                cmd.ExecuteNonQuery()
                bLocked = False
            End If

        Catch ex As Exception

            mstrLastError = ex.Message
            Return bLocked

        End Try

        Return bLocked
    End Function


    Public Function Connect() As Boolean

        mConn = New System.Data.SqlClient.SqlConnection
        With mConn
            .ConnectionString = [String].Format(mconConnectString, mstrServer, mstrDatabase, mstrDatabase)
            .Open()
        End With

    End Function

    Public Function ConnectionOK() As System.Data.SqlClient.SqlConnection

        If Not mConn Is Nothing Then
            Return mConn
        Else
            Initialize()
            Connect()
            If Not mConn Is Nothing Then
                Return mConn
            Else
                Return Nothing
            End If
        End If

    End Function

    Public Sub Disconnect()

        If Not mConn Is Nothing Then
            mConn.Dispose()
            mConn = Nothing
        End If

    End Sub

    Public Sub Initialize()

        mstrDatabase = GetValueForKey(mconConnectionSection, mconDataBaseKey, mconDatabase)
        mstrServer = GetValueForKey(mconConnectionSection, mconServerKey, mconServer)

    End Sub


    Public Function GetDate(ByVal Reader As System.Data.SqlClient.SqlDataReader, ByVal intColumn As Integer) As DateTime?

        If Not Reader.IsDBNull(intColumn) Then
            Return CType(Reader(intColumn), DateTime?)
        Else
            Return Nothing
        End If

    End Function


    Public Function GetNewGUID() As String

        Dim strSQL As String
        Dim strNewGUIID As String
        Dim cmd As System.Data.SqlClient.SqlCommand
        Dim Reader As System.Data.SqlClient.SqlDataReader = Nothing


        Try

            strSQL = "SELECT newid()"

            cmd = New System.Data.SqlClient.SqlCommand(strSQL, mConn)
            Reader = cmd.ExecuteReader()
            If Reader.Read() Then
                strNewGUIID = Reader(0).ToString
            Else
                strNewGUIID = vbNullString
            End If
            Return strNewGUIID

        Catch ex As Exception

            mstrLastError = ex.Message
            Return Nothing

        Finally

            Reader.Close()

        End Try

    End Function

End Module

There are some generic functions and I know one thing I am doing wrong is not using parameters. I didn't learn about them until after I started this.
It does use a very simple logical locking that will attempt to clean up after itself. If a record is to be opened that is locked AND it is locked by the same user on the same machine who tried to open it then that lock is removed and a new one is added.
The interface is just for simple checking on the form closing; Dirty? Want to save changes. Same routine is used for all.

[SeanGrebey]

hi tg,
what exactly is "machine.config" ? how do i create it and how i call it from web/win app ?

thanks

Think web.config except that every web server has exactly one and everything in it applies to all web sites on the box.