By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,659 Members | 1,939 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

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

pod
100+
P: 298
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 connectionStrings entries in my web.config within my main project
    Expand|Select|Wrap|Line Numbers
    1. <connectionStrings>
    2. <add name="Districts" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|DistrictsMSAccessDBFile.mdb" providerName="System.Data.OleDb"/>
    3. <add name="CDB1" connectionString="Data Source=CDB.CORPORATE.DBNAME;User Id=USRNAME;Password=PSSWRD;Persist Security Info=True" providerName="System.Data.OracleClient"/>
    4. </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
    Expand|Select|Wrap|Line Numbers
    1. Imports System.Data.OleDb
    2. Imports System.Data.OracleClient
    3. Public Class DatabaseHelper
    4. #Region "Helpers"
    5.     Friend Shared Function GetOleDbConnectionSkills() As OleDbConnection
    6.         'returns the connection string
    7.         Return New OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings("Districts").ConnectionString)
    8.     End Function
    9.     Friend Shared Function GetOracleConnectionCDB1() As OracleConnection
    10.         'returns the connection string
    11.         Return New OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings("CDB1").ConnectionString)
    12.     End Function
    13. #End Region
    14. 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
    ...
    Expand|Select|Wrap|Line Numbers
    1. Imports System.Data.OleDb
    2. Imports System.Data.OracleClient
    3.  
    4. Public Class DataAdapter
    5.  
    6.     Public Function DataSetOfDistricts() As DataSet
    7.         Try
    8.             Dim myDataSet As New DataSet
    9.             Dim sqlString As String
    10.             sqlString = " SELECT ID, CODE, NAMEE FROM [DISTRICT] ORDER BY CODE"
    11.  
    12.             Using connection As OleDbConnection = DatabaseHelper.GetOleDbConnectionSkills()
    13.                 Using command As New OleDbCommand(sqlString, connection)                    
    14.                     connection.Open()
    15.                     Dim adapter As New OleDbDataAdapter(command)
    16.                     adapter.Fill(myDataSet)
    17.                 End Using
    18.             End Using
    19.             Return myDataSet
    20.         Catch ex As Exception
    21.             MsgBox("DataSetOfDistricts" & ex.Message)
    22.             Return Nothing
    23.  
    24.         End Try
    25.     End Function
  4. In my presentation layer, where I need to access data, for example in the codebehind of one of my WebForms;

    Expand|Select|Wrap|Line Numbers
    1. Imports IMP.Data 'my Data layer project
    2.  
    3. Partial Public Class WebForm1
    4.     Inherits System.Web.UI.Page
    5.  
    6.     Dim dataAdp As New DataAdapter
    7.  
    8.     Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    9.  
    10.     End Sub
    11.  
    12.     'this function is used for a DropDownList ObjectDatasource
    13.     Public Function DataSetOfDistricts() As DataTable
    14.         Try
    15.             Dim dt As New DataTable
    16.             dt = dataAdp.DataSetOfDistricts()
    17.             Return dt
    18.         Catch ex As Exception
    19.             Session("errorMessage") = "DataSetOfDistricts: " & ex.Message
    20.             Return Nothing
    21.         End Try
    22.     End Function

Hope that helps

P:oD
Feb 27 '13 #1
Share this Article
Share on Google+
1 Comment


pod
100+
P: 298
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
Mar 20 '13 #2