473,472 Members | 2,174 Online
Bytes | Software Development & Data Engineering Community
Create Post

Home Posts Topics Members FAQ

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

pod
298 Contributor
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
1 5689
pod
298 Contributor
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

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Heather | last post by:
Hello, I'm writing to see if anyone is familiar with a tool that can update SQL Server code (i.e. stored procedures, views or table modifications) throughout multiple databases. Currently we...
3
by: DKode | last post by:
I am preparing to build an app that will pull data from multiple databases on the same sql server. For performance sake, is there a more "effecient" way to grab data from multiple databases at...
9
by: TC | last post by:
I need to design a system which represents multiple "projects" in SQL Server. Each project has the same data model, but is independent of all others. My inclination is to use one database to store...
0
by: Tom | last post by:
I'm using the DB_DataObject script createTables.php to auto-generate the necessary database schema on two databases. Using the .ini approach (not the in-line PHP approach) to configure...
0
by: Dinkar Sh | last post by:
hi All, i m facing a big problem. i want to Insert Multiple Items at a time Using Grid In Asp.Net. is it possible. if yes then which grid is suitable for it. thanks DInkar
1
by: foneman | last post by:
I'm running Sambar 7.0, PHP 5.2 and PostgreSQL 8.2 with WinXP. I built a database that's fed with form data, and created summaries with views and PHP scripts. I cloned the database two different...
1
by: richard.crosh | last post by:
What is the IBM recommendation for the number of DB2-LUW databases per instance on AIX? With Oracle, it is one-to-one. In DB2 multiple databases can co-exist in an instance but is this...
4
by: JoyceBabu | last post by:
Can anyone plz tell me the advantages and disadvantages of using multiple databases / single database for all tables. I have a site with more than a 100 tables. Current all the tables are in a...
2
by: krishna kumari | last post by:
Can we use multiple databases in one project? I am working as a QA TESTER. One of my interviwer asked me like how you use multiple databases in one project? EX: DB2 database, oracle and MySQL
6
by: cj2 | last post by:
If from withing one program I want to access multiple databases on a sql server do I need to make multiple connections? The connection string says initial catalog. I'd assume I could denote the...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
1
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
1
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...
0
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
0
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
0
muto222
php
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.