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
- first step: add the desired connectionStrings entries in my web.config within my main project
Expand|Select|Wrap|Line Numbers- <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>
... - 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- 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
- 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- 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
- 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- 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