Click here to Skip to main content
15,914,943 members
Home / Discussions / Visual Basic
   

Visual Basic

 
Generaldata caching Pin
brieg10004-Dec-02 10:02
brieg10004-Dec-02 10:02 
GeneralRe: data caching Pin
Nick Seng4-Dec-02 15:05
Nick Seng4-Dec-02 15:05 
GeneralRe: data caching Pin
brieg10005-Dec-02 2:41
brieg10005-Dec-02 2:41 
GeneralODBC with Access 2000 Pin
-- NA --3-Dec-02 8:14
-- NA --3-Dec-02 8:14 
GeneralRe: ODBC with Access 2000 Pin
Daniel Turini3-Dec-02 8:27
Daniel Turini3-Dec-02 8:27 
GeneralVB.Net decreased speed Pin
Supermaniac3-Dec-02 2:45
Supermaniac3-Dec-02 2:45 
GeneralRe: VB.Net decreased speed Pin
mikasa10-Dec-02 4:49
mikasa10-Dec-02 4:49 
GeneralRe: VB.Net decreased speed Pin
mikasa10-Dec-02 4:50
mikasa10-Dec-02 4:50 
Here's the DataSource Class:

Option Strict On
Option Explicit On
'<<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>>
'Imports
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports Microsoft.Data.Odbc
'<<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>>





'Class to encapsulate Data Access
'<<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>>
Public NotInheritable Class DataSource
'<<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>>
#Region "Declarations"

'Implementations
Implements IDisposable

'Enumerations
Public Enum DatabaseTypes
dbAccess
dbExcel
dbODBC
dbFileDSN
dbSQLRemote
dbSQLServer
dbSybase5
dbSybase6
dbSybase7
End Enum
Public Enum SQLProtocols
sqlNamedPipes
sqlTCPIP
sqlSPXIPX
sqlBanyanVines
sqlRPC
End Enum 'When connecting through the SQLOLEDB provider use the syntax Network Library=dbmssocn and when connecting through MSDASQL provider use the syntax Network=dbmssocn
Public Enum SecuirtyProtocols
StandardSecurity = 0
TrustedSecurity = 1
End Enum

'Property Variables
Private _Server, _RemoteServer, _Database, _DSN, _Address, _ConnectString As String
Private _User, _Password As String
Private _TimeOut, _Port As Integer
Private _UseIntegrated, _UsePrompt As Boolean
Private _Type As ActiveDataObjects.DataSource.DatabaseTypes
Private _Protocol As ActiveDataObjects.DataSource.SQLProtocols
Private _Security As ActiveDataObjects.DataSource.SecuirtyProtocols

'Variables
Private odbcCon As Microsoft.Data.Odbc.OdbcConnection
Private odbcAdapter As Microsoft.Data.Odbc.OdbcDataAdapter
Private oleCon As System.Data.OleDb.OleDbConnection
Private oleAdapter As OleDb.OleDbDataAdapter
Private sqlCon As System.Data.SqlClient.SqlConnection
Private sqlAdapter As SqlClient.SqlDataAdapter
Private bConnected, bDisposed As Boolean

#End Region
'<<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>>

'<<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>>
#Region "Properties"

'Property to Return the Connection Object
Public ReadOnly Property Connection() As System.Data.IDbConnection
Get
If (IsNothing(_ConnectString)) OrElse (_ConnectString.Length = 0) Then Exit Property
If (Not bConnected) Then Call Me.Connect() 'Determine if Connected

Select Case _Type
Case DatabaseTypes.dbAccess : If (Not oleCon Is Nothing) Then Connection = oleCon Else Return Nothing
Case DatabaseTypes.dbExcel : Return Nothing
Case DatabaseTypes.dbODBC : If (Not odbcCon Is Nothing) Then Connection = odbcCon Else Return Nothing
Case DatabaseTypes.dbSQLServer : If (Not sqlCon Is Nothing) Then Return sqlCon Else Return Nothing
Case DatabaseTypes.dbSybase5 : If (Not oleCon Is Nothing) Then Connection = oleCon Else Return Nothing
Case DatabaseTypes.dbSybase6 : If (Not oleCon Is Nothing) Then Connection = oleCon Else Return Nothing
Case DatabaseTypes.dbSybase7 : If (Not oleCon Is Nothing) Then Connection = oleCon Else Return Nothing
End Select
End Get
End Property

'PropertytoReturn the Connection String
Public ReadOnly Property ConnectionString() As String
Get
Return _ConnectString
End Get
End Property

'Property for the Database
Public Property Database() As String
Get
Return _Database
End Get
Set(ByVal Value As String)
If (Not IsNothing(_Database)) Then If (_Database.Equals(Value)) Then Exit Property
If (bConnected) Then Exit Property

_Database = Value
Call Me.RefreshConnectionString()
End Set
End Property

'Property for the Database Type
Public Property DatabaseType() As DatabaseTypes
Get
Return _Type
End Get
Set(ByVal Value As DatabaseTypes)
If (_Type.Equals(Value)) Then Exit Property
If (bConnected) Then Exit Property

_Type = Value
Call Me.RefreshConnectionString()
End Set
End Property

'Property to Return whether the Connection is Open
Public ReadOnly Property IsConnected() As Boolean
Get
Return bConnected
End Get
End Property

'Property for the Password
Public Property Password() As String
Get
Return _Password
End Get
Set(ByVal Value As String)
If (Not IsNothing(_Password)) Then If (_Password.Equals(Value)) Then Exit Property
If (bConnected) Then Exit Property

_Password = Value
Call Me.RefreshConnectionString()
End Set
End Property

'Property for the Server
Public Property Server() As String
Get
Return _Server
End Get
Set(ByVal Value As String)
If (Not IsNothing(_Server)) Then If (_Server.Equals(Value)) Then Exit Property
If (bConnected) Then Exit Property

_Server = Value
Call Me.RefreshConnectionString()
End Set
End Property

'Property for the SQL Server Port
Public Property ServerPort() As Integer
Get
Return _Port
End Get
Set(ByVal Value As Integer)
If (_Port.Equals(Value)) Then Exit Property
If (bConnected) Then Exit Property
_Port = Value
Call Me.RefreshConnectionString()
End Set
End Property

'Property for the Security Protocol
Public Property SecurityProtocol() As SecuirtyProtocols
Get
Return _Security
End Get
Set(ByVal Value As SecuirtyProtocols)
If (_Security.Equals(Value)) Then Exit Property
If (bConnected) Then Exit Property
_Security = Value
Call Me.RefreshConnectionString()
End Set
End Property

'Property for User Prompting
Public Property UsePrompt() As Boolean
Get
Return _UsePrompt
End Get
Set(ByVal Value As Boolean)
If (_UsePrompt.Equals(Value)) Then Exit Property
If (bConnected) Then Exit Property

_UsePrompt = Value
Call Me.RefreshConnectionString()
End Set
End Property

'Property for the User ID
Public Property UserID() As String
Get
Return _User
End Get
Set(ByVal Value As String)
If (Not IsNothing(_User)) Then If (_User.Equals(Value)) Then Exit Property
If (bConnected) Then Exit Property

_User = Value
Call Me.RefreshConnectionString()
End Set
End Property

#End Region
'<<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>>

'<<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>>
#Region "Methods"

'Function to Connect
Public Function Connect() As Boolean
Dim mServer As ActiveSystemObjects.Network.Server
Dim sMsg As String
Dim bServerExists As Boolean
Dim i As Integer

'Verify that there is enough info to Connect
If (IsNothing(_ConnectString)) OrElse (_ConnectString.Length = 0) Then Return False
If (bConnected) Then Return True 'Determine if the Connection already Open

'Determine if the Server is available
Try
If (Not IsNothing(_Server)) AndAlso (_Server.Length > 0) Then
mServer = New ActiveSystemObjects.Network.Server(_Server)
If (mServer.Exists) Then _Address = mServer.IPAddress : bServerExists = True
End If
Catch
Call Globals.ErrorMessage("DataSource.Connect")
Finally
mServer = Nothing
End Try
If (Not bServerExists) Then Return False

Try
Select Case _Type
Case DatabaseTypes.dbAccess, DatabaseTypes.dbExcel, DatabaseTypes.dbSQLRemote, DatabaseTypes.dbSybase6, DatabaseTypes.dbSybase7
Try
oleCon = New OleDb.OleDbConnection(_ConnectString)
oleCon.Open()
bConnected = (oleCon.State = ConnectionState.Open)
Catch
Call Globals.ErrorMessage("DataSource.Connect")
oleCon.Dispose() : oleCon = Nothing
bConnected = False
End Try

Case DatabaseTypes.dbODBC
Try
odbcCon = New OdbcConnection(_ConnectString)
odbcCon.Open()
bConnected = (odbcCon.State = ConnectionState.Open)
Catch
Call Globals.ErrorMessage("DataSource.Connect")
odbcCon.Dispose() : odbcCon = Nothing
bConnected = False
End Try

Case DatabaseTypes.dbSQLServer
Try
'Open the SQL Connection
sqlCon = New SqlClient.SqlConnection(_ConnectString)
sqlCon.Open()
bConnected = (sqlCon.State = ConnectionState.Open)
Catch
Call Globals.ErrorMessage("DataSource.Connect")
sqlCon.Dispose() : sqlCon = Nothing
bConnected = False
End Try

End Select

Catch Excp As SystemException
Call Globals.ErrorMessage("DataSource.Connect")
End Try

Return bConnected
End Function

'Routine to DisConnect
Public Sub DisConnect()
'Determine if there is a Connection String
If (IsNothing(_ConnectString) OrElse (_ConnectString.Length = 0)) Then Exit Sub
If (Not bConnected) Then Exit Sub 'Determine if Connected

'Destroy any SQL Connections
If (Not sqlCon Is Nothing) Then sqlCon.Close() : sqlCon.Dispose()
sqlCon = Nothing

'Destroy any OLEDB Connections
If (Not oleCon Is Nothing) Then oleCon.Close() : oleCon.Dispose() : oleCon.ReleaseObjectPool()
oleCon = Nothing

'Destroy any OLEDB Connections
If (Not odbcCon Is Nothing) Then odbcCon.Close() : odbcCon.Dispose() : odbcCon.ReleaseObjectPool()
oleCon = Nothing

bConnected = False
End Sub

'Function to Load an Adapter from a Configuration
Public Function LoadAdapter(ByVal ConfigPath As String, ByVal AdapterName As String, Optional ByVal Version As String = "1.0") As Data.Common.DataAdapter
Dim IAdapter As Data.IDbDataAdapter
Dim xDoc As New Xml.XmlDocument()
Dim xAdapter, xCommand, xParam As Xml.XmlNode

'Determine if the File Exists
If (Not IO.File.Exists(ConfigPath)) Then Return Nothing

'Open the BusinessObject Configuration
xDoc.Load(ConfigPath)
xAdapter = xDoc.SelectSingleNode("//DataAdapter[@Name = '" & AdapterName & "'][@Version = '" & Version & "']")
For Each xCommand In xAdapter.ChildNodes

'Determine the Type of Command
Select Case CType(xCommand.Attributes("StatementType").Value, Data.StatementType)
Case StatementType.Select
With IAdapter.SelectCommand
.CommandType = CType(CInt(xCommand.Attributes("CommandType").Value), Data.CommandType)
.CommandText = xCommand.Attributes("Text").Value
.CommandTimeout = CInt(xCommand.Attributes("TimeOut").Value)
End With

Case StatementType.Insert
Case StatementType.Update
Case StatementType.Delete
End Select
Next
End Function

'Routine to Refresh the Connection String
Private Sub RefreshConnectionString()
Dim sProvider As String
Dim sProperties As New Text.StringBuilder()

Select Case _Type
Case DatabaseTypes.dbAccess 'Access Databases
If (Not IsNothing(_Database)) AndAlso (_Database.Length > 0) Then
sProperties.Append("Provider=Microsoft.Jet.OLEDB.4.0")
sProperties.Append(";Data Source=" & _Database)
If (Not IsNothing(_User)) AndAlso (_User.Length > 0) Then sProperties.Append(";User ID=" & _User)
If (Not IsNothing(_Password)) AndAlso (_Password.Length > 0) Then sProperties.Append(";Password=" & _Password)
End If

Case DatabaseTypes.dbExcel 'Excel Spread Sheets
If (Not IsNothing(_Database)) AndAlso (_Database.Length > 0) Then
sProperties.Append("Provider=Microsoft.Jet.OLEDB.4.0")
sProperties.Append(";Data Source=" & _Database)
sProperties.Append(";Extended Properties=Excel 8.0")
'sProperties.Append(";HDR=Yes") 'First Row has Column Names
End If

Case DatabaseTypes.dbODBC 'ODBC Data Sources
If (Not IsNothing(_DSN)) AndAlso (_DSN.Length > 0) Then
sProperties.Append("DSN=" & _DSN)
If (Not IsNothing(_User)) AndAlso (_User.Length > 0) Then sProperties.Append(";UID=" & _User)
If (Not IsNothing(_Password)) AndAlso (_Password.Length > 0) Then sProperties.Append(";Password=" & _Password)
End If

Case DatabaseTypes.dbSQLServer 'SQL Server
If ((Not IsNothing(_Server)) AndAlso (_Server.Length > 0)) AndAlso ((Not IsNothing(_Database)) AndAlso (_Database.Length > 0)) Then
sProperties.Append("Data Source=" & _Server)
sProperties.Append(";Initial Catalog=" & _Database)

'Determine the Protocol to use
'Select Case _Protocol
' Case SQLProtocols.sqlBanyanVines : sProperties.Append(";Network Library=DBMSVINN")
' Case SQLProtocols.sqlNamedPipes : sProperties.Append(";Network Library=DBNMPNTW")
' Case SQLProtocols.sqlRPC : sProperties.Append(";Network Library=DBMSRPCN")
' Case SQLProtocols.sqlSPXIPX : sProperties.Append(";Network Library=DBMSSPXN")
' Case SQLProtocols.sqlTCPIP : sProperties.Append(";Network Library=DBMSSOCN")
'End Select
'sProperties.Append(";Connection Timeout=" & _TimeOut)

'Determine the Security to Use
If (_Security = SecuirtyProtocols.TrustedSecurity) Then
sProperties.Append("Integrated Security=SSPI")
Else
If (Not IsNothing(_User)) AndAlso (_User.Length > 0) Then sProperties.Append(";User ID=" & _User)
If (Not IsNothing(_Password)) AndAlso (_Password.Length > 0) Then sProperties.Append(";Password=" & _Password)
End If
End If

Case DatabaseTypes.dbSybase5
Case DatabaseTypes.dbSybase6
Case DatabaseTypes.dbSybase7
End Select

_ConnectString = sProperties.ToString
sProperties = Nothing
End Sub

#End Region
'<<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>>

'<<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>>
#Region "Class Methods"

'Initialize Class
Public Sub New()
_Type = DatabaseTypes.dbSQLServer
_Port = 1433
End Sub

'Initialize for Access Database
Public Sub New(ByVal Database As String, ByVal UserID As String, ByVal Password As String)
_Type = DatabaseTypes.dbAccess
_Database = Database
_User = UserID
_Password = Password
Call Me.RefreshConnectionString()
End Sub

'Initialize for SQL Server
Public Sub New(ByVal Database As String, ByVal Server As String, ByVal UserID As String, ByVal Password As String)
_Type = DatabaseTypes.dbSQLServer
_Server = Server
_Database = Database
_User = UserID
_Password = Password
_Port = 1433
Call Me.RefreshConnectionString()
End Sub

'Initialize for DSN
Public Sub New(ByVal DSN As String)
_Type = DatabaseTypes.dbODBC
_DSN = DSN
Call Me.RefreshConnectionString()
End Sub

'Routine to Dispose
Public Sub Dispose() Implements IDisposable.Dispose
If (Not bDisposed) Then
bDisposed = True
Call Me.DisConnect()
If (Not IsNothing(odbcAdapter)) Then odbcAdapter.Dispose() : odbcAdapter = Nothing 'Destroy any ODBC Adapters
If (Not IsNothing(oleAdapter)) Then oleAdapter.Dispose() : oleAdapter = Nothing 'Destroy any OLEDB Adapters
If (Not IsNothing(sqlAdapter)) Then sqlAdapter.Dispose() : sqlAdapter = Nothing 'Destroy any SQL Adapters

GC.SuppressFinalize(Me)
End If
End Sub

'Routine to Finalize
Protected Overrides Sub Finalize()
MyBase.Finalize()
Call Dispose()
End Sub

#End Region
'<<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>>
End Class
'<<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>><<o>>
GeneralRe: VB.Net decreased speed Pin
mikasa10-Dec-02 4:57
mikasa10-Dec-02 4:57 
QuestionVB6: Call a java method from VB project?!?!!? Possible or not? Pin
gicio2-Dec-02 7:49
gicio2-Dec-02 7:49 
AnswerRe: VB6: Call a java method from VB project?!?!!? Possible or not? Pin
Daniel Turini2-Dec-02 8:40
Daniel Turini2-Dec-02 8:40 
AnswerRe: VB6: Call a java method from VB project?!?!!? Possible or not? Pin
Nick Parker2-Dec-02 8:48
protectorNick Parker2-Dec-02 8:48 
GeneralRe: VB6: Call a java method from VB project?!?!!? Possible or not? Pin
Daniel Turini3-Dec-02 3:48
Daniel Turini3-Dec-02 3:48 
GeneralRe: VB6: Call a java method from VB project?!?!!? Possible or not? Pin
Nick Parker3-Dec-02 8:03
protectorNick Parker3-Dec-02 8:03 
GeneralGame Pin
Andy H2-Dec-02 6:38
Andy H2-Dec-02 6:38 
GeneralRe: Game Pin
Nick Seng2-Dec-02 14:16
Nick Seng2-Dec-02 14:16 
GeneralRe: Game Pin
David Brooks19-Dec-02 10:28
David Brooks19-Dec-02 10:28 
Questionmenu enable/disable problem....?? Pin
drmzunlimited30-Nov-02 19:51
drmzunlimited30-Nov-02 19:51 
AnswerRe: menu enable/disable problem....?? Pin
mikasa1-Dec-02 2:47
mikasa1-Dec-02 2:47 
GeneralCustom Property Editor in VB.NET Pin
mikasa30-Nov-02 9:44
mikasa30-Nov-02 9:44 
GeneralRe: Custom Property Editor in VB.NET Pin
FruitBatInShades2-Dec-02 12:09
FruitBatInShades2-Dec-02 12:09 
GeneralDispatchWrapper gives error Pin
Uday Bhaskar29-Nov-02 18:34
Uday Bhaskar29-Nov-02 18:34 
GeneralSelecting all the Items in Checked List Box Pin
Sameers (theAngrycodeR )28-Nov-02 11:56
Sameers (theAngrycodeR )28-Nov-02 11:56 
GeneralRe: Selecting all the Items in Checked List Box Pin
Richard Deeming2-Dec-02 5:40
mveRichard Deeming2-Dec-02 5:40 
GeneralRe: Selecting all the Items in Checked List Box Pin
Anonymous2-Dec-02 9:38
Anonymous2-Dec-02 9:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.