Connecting to multiple databases using N-TIER architecture (asp.net VB)

[pod]

There are multiple ways to connect to databases of any types in ASP.NET and VB.NET

This is the way I do it at the moment. I try to develop in a multi-tier (n-tier) architecture keeping my DATA layer separate from the other layers; Business Logic and Presentation

1. first step: add the desired connectionStrin gs entries in my web.config within my main project
   

   <connectionStrings>
   <add name="Districts" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|DistrictsMSAccessDBFile.mdb" providerName="System.Data.OleDb"/>
   <add name="CDB1" connectionString="Data Source=CDB.CORPORATE.DBNAME;User Id=USRNAME;Password=PSSWRD;Persist Security Info=True" providerName="System.Data.OracleClient"/>
   </connectionStrings>

   where |DataDirectory| is the App_Data folder in my Presentation layer (main project in the Solution)
   ...
2. Create a database helper Class in the DATA layer (IMP.Data) (other project in my Solution), I connect to multiple data sources; Oracle and MS Access
   

   Imports System.Data.OleDb
   Imports System.Data.OracleClient
   Public Class DatabaseHelper
   #Region "Helpers"
       Friend Shared Function GetOleDbConnectionSkills() As OleDbConnection
           'returns the connection string
           Return New OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings("Districts").ConnectionString)
       End Function
       Friend Shared Function GetOracleConnectionCDB1() As OracleConnection
           'returns the connection string
           Return New OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings("CDB1").ConnectionString)
       End Function
   #End Region
   End Class

3. Create another class for the data functions, to keep things clean and separated. Although this portion of the code could be within the same class as step 2
   ...
   

   Imports System.Data.OleDb
   Imports System.Data.OracleClient
   
   Public Class DataAdapter
   
       Public Function DataSetOfDistricts() As DataSet
           Try
               Dim myDataSet As New DataSet
               Dim sqlString As String
               sqlString = " SELECT ID, CODE, NAMEE FROM [DISTRICT] ORDER BY CODE"
   
               Using connection As OleDbConnection = DatabaseHelper.GetOleDbConnectionSkills()
                   Using command As New OleDbCommand(sqlString, connection)                    
                       connection.Open()
                       Dim adapter As New OleDbDataAdapter(command)
                       adapter.Fill(myDataSet)
                   End Using
               End Using
               Return myDataSet
           Catch ex As Exception
               MsgBox("DataSetOfDistricts" & ex.Message)
               Return Nothing
   
           End Try
       End Function

4. In my presentation layer, where I need to access data, for example in the codebehind of one of my WebForms;
   
   

   Imports IMP.Data 'my Data layer project
   
   Partial Public Class WebForm1
       Inherits System.Web.UI.Page
   		
       Dim dataAdp As New DataAdapter
   
       Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
   
       End Sub
   
       'this function is used for a DropDownList ObjectDatasource
       Public Function DataSetOfDistricts() As DataTable
           Try
               Dim dt As New DataTable
               dt = dataAdp.DataSetOfDistricts()
               Return dt
           Catch ex As Exception
               Session("errorMessage") = "DataSetOfDistricts: " & ex.Message
               Return Nothing
           End Try
       End Function

Hope that helps

P:oD

[pod]

If you need this code (or other code) converted from VB to C# or vice versa, check out
http://www.developerfusion.com/tools/ ... very useful tool