[falexge]

Hi All,
I am currently writing a class in vb.net that accesses databases when given the datasource and query. I however have ran into a couple of problems.

I would like to have my class generate a connection string text depending on the data provider the user chooses from a list. How do I have my class determine the right string to place for a provider in the connection string?

For instance, if a computer has Office 2007, it should generate a provider string different from what will be generated by a computer running office 2003.

Just in case you do not get what I mean, When creating a data source with Visual Studio, you typically select a provider from a list in the wizard, choose a database to connect to, and you have a connection string generated for you. I want my class to do the first part; generating valid provider text for the connection string.

Is there any way to do that?

I would prefer the code to be in VB.NET though VB6 is OK with me. I do not know any language other than Visual Basic.

Attached to this post is the class in its most updated state.

The portion I would like to implement the structure I'm talking about is the class ConnectionString; located in the attached file.

Attached File(s)

•  DbDataSet.zip (4.84K)
  Number of downloads: 58

[PsychoCoder]

[rules][/rules]

[falexge]

I attached the file to the original post but if you insist, here are the classes that I intend to use. The first class accepts a connection string or data source, and an SQL query and then retrieves the table that matches the query.

The second accepts a connection string as a System.String, breaks it down into its individual components and stores it.

[code]
Imports System.ComponentModel
Imports System.Diagnostics
Imports FileIO = Microsoft.VisualBasic.FileIO
Imports cs = System.IO.Compression
Imports sr = System.Runtime.Serialization
Imports System.Collections.Generic
Imports System.Data.DBDataSet.ExceptionEncounteredArgs.ExceptionHandlingStyle

Namespace System.Data
''' <summary>
''' A stand-alone OLEDB-based class with Adapter and DataTable.
''' </summary>

<Serializable(), DebuggerNonUserCode()> _
Public Class DBDataSet
Implements System.IDisposable, sr.ISerializable


Private WithEvents _Table As DataTable
Private _connStr As New ConnectionString
Private _SQLQuery As String
Private _exceptions As New List(Of Exception)
Private _AutoClrEx As Boolean = True

#Region "Events"
''' <summary>Occurs when the table is about to be updated</summary>
<EditorBrowsable(EditorBrowsableState.Advanced)> _
Public Event UpdateStarted As CancelEventHandler ' (ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs)
''' <summary>Occurs after the table has been updated.</summary>
<EditorBrowsable(EditorBrowsableState.Advanced)> _
Public Event Updated As EventHandler
<EditorBrowsable(EditorBrowsableState.Advanced)> _
Public Event ColumnChanged As DataColumnChangeEventHandler
<EditorBrowsable(EditorBrowsableState.Advanced)> _
Public Event ColumnChanging As DataColumnChangeEventHandler
<EditorBrowsable(EditorBrowsableState.Advanced)> _
Public Event RowChanged As DataRowChangeEventHandler
<EditorBrowsable(EditorBrowsableState.Advanced)> _
Public Event RowChanging As DataRowChangeEventHandler
<EditorBrowsable(EditorBrowsableState.Advanced)> _
Public Event RowDeleted As DataRowChangeEventHandler
<EditorBrowsable(EditorBrowsableState.Advanced)> _
Public Event RowDeleting As DataRowChangeEventHandler
Public Event TableNewRow As DataTableNewRowEventHandler
''' <summary>Occurs when an exception is encountered by the DBDataSet class.</summary>
<EditorBrowsable(EditorBrowsableState.Advanced)> _
Public Event ExceptionEncounterd(ByVal sender As Object, ByVal e As ExceptionEncounteredArgs)
#End Region

'------------------------------------------------------------------------------
' Subroutine : New
' Date/Time : 26-Jun-08 11:05:36
' Purpose : Creates a new instance of DBDataSet.
'------------------------------------------------------------------------------
' CODE MODIFICATIONS
' ----------- ---------------------------------------------------------------
' Date/Time Programmer/Description
' ----------- ---------------------------------------------------------------
'------------------------------------------------------------------------------
''' <summary>
''' Creates a new instance of DBDataSet.
''' </summary>
Public Sub New()
End Sub

'------------------------------------------------------------------------------
' Subroutine : New
' Date/Time : 26-Jun-08 11:07:51
' Parameters : SQLQuery (String)
' DBPath (String)
' Purpose : Creates a new instance of DataSet.
'------------------------------------------------------------------------------
' CODE MODIFICATIONS
' ----------- ---------------------------------------------------------------
' Date/Time Programmer/Description
' ----------- ---------------------------------------------------------------
'------------------------------------------------------------------------------
''' <summary>
''' Creates a new instance of DBDataSet.
''' </summary>
''' <param name="Query">Query used to retrieve data from the datatabase.</param>
''' <param name="DataSource">Path to the database.</param>
Public Sub New(ByVal Query As String, ByVal DataSource As String)
Me.Query = Query
Me.DataSource = DataSource
End Sub

'------------------------------------------------------------------------------
' Subroutine : New
' Date/Time : 26-Jun-08 11:10:19
' Parameters : DBPath (String)
' Purpose : Creates a new instance of DBDataSet.
'------------------------------------------------------------------------------
' CODE MODIFICATIONS
' ----------- ---------------------------------------------------------------
' Date/Time Programmer/Description
' ----------- ---------------------------------------------------------------
'------------------------------------------------------------------------------
''' <summary>
''' Creates a new instance of DBDataSet.
''' </summary>
''' <param name=" DataSource">Path to the database.</param>
Public Sub New(ByVal DataSource As String)
Me.DataSource = DataSource
End Sub

''' <summary>
''' Initializes a new instance of DBDataSet class
''' using the specified serialization information and context.
''' </summary>
''' <param name="info">A System.Runtime.Serialization.SerializationInfo
''' containing the data to deserialize the class.</param>
''' <param name="context">The System.Runtime.Serialization.StreamingContext
''' containing the source and destination of the serialized stream.</param>
Protected Sub New(ByVal info As sr.SerializationInfo, ByVal context As sr.StreamingContext)
Try
_SQLQuery = info.GetString("Query")
_connStr = CType(info.GetString("ConnStr"), Data.ConnectionString)
_exceptions = CType(info.GetValue("Exceptions", GetType(Exception)), List(Of Global.System.Exception))
_AutoClrEx = info.GetBoolean("AutoClrEx")
'_Table = DeserializeHelper.Deserialize(info, context)

'decompress the table
Dim bytes() As Byte = DirectCast(info.GetValue("bytes", GetType(Byte())), Byte())
Using inStream As New IO.MemoryStream(bytes)
' Wrap a DeflateStream object around the compressed buffer.
Using zipStream As New cs.GZipStream(inStream, cs.CompressionMode.Decompress)
Dim bf As New sr.Formatters.Binary.BinaryFormatter
_Table = DirectCast(bf.Deserialize(zipStream), DataTable)
End Using
End Using

Catch ex As Exception
If Not TypeOf ex Is sr.SerializationException Then
Throw ex
_exceptions.Add(ex)
RaiseEvent ExceptionEncounterd(Me, New ExceptionEncounteredArgs(ex, SentToCaller))
Else
_exceptions.Add(ex)
RaiseEvent ExceptionEncounterd(Me, New ExceptionEncounteredArgs(ex, Suppressed))
End If
End Try
End Sub

'------------------------------------------------------------------------------
' Property : Query (R/W)
' Date/Time : 26-Jun-08 10:45:40
' Returns : String
' Purpose : Set the query used to retrieve data from the database
'------------------------------------------------------------------------------
' CODE MODIFICATIONS
' ----------- ---------------------------------------------------------------
' Date/Time Programmer/Description
' ----------- ---------------------------------------------------------------
'------------------------------------------------------------------------------
''' <summary>
''' The SQL query used to retrieve data from the database.
''' </summary>
''' <value>SQL string used to query the database.</value>
''' <param name="FormatSQL">Automatically format string passed as SQL queries.
''' If true, characters like % and _ will be replaced with [%] and [_] respectively.</param>
''' <returns></returns>
Public Property Query(Optional ByVal FormatSQL As Boolean = False) As String
Get
Return _SQLQuery
End Get
Set(ByVal value As String)
_SQLQuery = value.Trim
If _connStr.DataSource <> "" Then FillDB()
End Set
End Property

'------------------------------------------------------------------------------
' Property : DataSource (R/W)
' Date/Time : 26-Jun-08 10:50:40
' Returns : String
' Purpose : Set or return the path to the database.
'------------------------------------------------------------------------------
' CODE MODIFICATIONS
' ----------- ---------------------------------------------------------------
' Date/Time Programmer/Description
' ----------- ---------------------------------------------------------------
'------------------------------------------------------------------------------
''' <summary>
''' Set or return the path to the database.
''' </summary>
''' <value>Path to the database.</value>
''' <returns></returns>
Public Property DataSource() As String
Get
Return _connStr.DataSource
End Get
Set(ByVal value As String)
If FileIO.FileSystem.FileExists(value) Then
_connStr.DataSource = value
If _SQLQuery <> "" Then FillDB()
Else
Dim ex As New Exception("The file you specified does not exist.")
Throw ex
_exceptions.Add(ex)
RaiseEvent ExceptionEncounterd(Me, New ExceptionEncounteredArgs(ex, SentToCaller))
End If
End Set
End Property

''' <summary>Set or get the connection string for accessing the database.</summary>
Public Property ConnectionString() As ConnectionString
Get
Return _connStr
End Get
Set(ByVal value As ConnectionString)
_connStr = value
End Set
End Property

'------------------------------------------------------------------------------
' Property : Table (Default)(R/W)
' Date/Time : 26-Jun-08 11:28:13
' Parameters : index (Integer)
' Purpose : Return a table
'------------------------------------------------------------------------------
' CODE MODIFICATIONS
' ----------- ---------------------------------------------------------------
' Date/Time Programmer/Description
' ----------- ---------------------------------------------------------------
'------------------------------------------------------------------------------
''' <summary>
''' Set or retrieve a table from the database.
''' </summary>
''' <value>Table.</value>
''' <returns></returns>
Public Property Table() As DataTable
Get
Try
Return _Table
Catch ex As Exception
Throw ex
End Try
End Get
Set(ByVal value As DataTable)
Try
_Table = value
Catch ex As Exception
Throw ex
_exceptions.Add(ex)
RaiseEvent ExceptionEncounterd(Me, New ExceptionEncounteredArgs(ex, SentToCaller))
End Try
End Set
End Property

'------------------------------------------------------------------------------
' Property : Exceptions(R)
' Date/Time : 28-Dec-08 11:28:13
' Purpose : Rettrieves a list of exceptions this instance has smothered
'------------------------------------------------------------------------------
' CODE MODIFICATIONS
' ----------- ---------------------------------------------------------------
' Date/Time Programmer/Description
' ----------- ---------------------------------------------------------------
'------------------------------------------------------------------------------
''' <summary>
''' Retrieves a list of exceptions that have been encountered.
''' </summary>
''' <value></value>
''' <returns></returns>
<EditorBrowsable(EditorBrowsableState.Advanced)> _
Public ReadOnly Property Exceptions() As List(Of Exception)
Get
Return _exceptions
End Get
End Property

''' <summary>
''' <para>Determine whether or not an exception has been encountered.</para><para/>
''' <para>A list of exceptions that have been handled internally can be retrieved using the Exceptions property.</para>
''' </summary>
''' <value></value>
''' <returns></returns>
Public ReadOnly Property ExceptionEncountered() As Boolean
Get
If _exceptions.Count > 0 Then Return True Else Return False
End Get
End Property

''' <summary>
''' Retrieves the last exception that was encountered.
''' </summary>
''' <value></value>
''' <returns></returns>
Public ReadOnly Property LastException() As Exception
Get
Dim x As Integer = _exceptions.Count
If x > 0 Then Return _exceptions(x - 1) Else Return Nothing
End Get
End Property

''' <summary>
''' Set this property if you want the list of exceptions to be reset when a new query is made.
''' </summary>
''' <value></value>
''' <returns></returns>
Public Property AutoClearExceptions() As Boolean
Get
Return _AutoClrEx
End Get
Set(ByVal value As Boolean)
_AutoClrEx = value
End Set
End Property

'------------------------------------------------------------------------------
' Subroutine : FillDB
' Date/Time : 26-Jun-08 11:26:24
' Purpose : Fill tables.
'------------------------------------------------------------------------------
' CODE MODIFICATIONS
' ----------- ---------------------------------------------------------------
' Date/Time Programmer/Description
' ----------- ---------------------------------------------------------------
'------------------------------------------------------------------------------
''' <summary>
''' Fills the table with data from the database.
''' </summary>
Private Sub FillDB()
'clear exceptions that have been raised
If _AutoClrEx Then ClearExceptions()

Try
Dim adapter As New OleDb.OleDbDataAdapter(_SQLQuery, _connStr.ToString)
_Table = New DataTable
_Table.Clear()

adapter.Fill(_Table)
adapter.Dispose()
Catch ex As Exception
_exceptions.Add(ex)
RaiseEvent ExceptionEncounterd(Me, New ExceptionEncounteredArgs(ex, Suppressed))
End Try
End Sub

'------------------------------------------------------------------------------
' Function : Update
' Date/Time : 26-Jun-08 11:52:40
' Returns : Boolean
' Purpose : Update the database.
'------------------------------------------------------------------------------
' CODE MODIFICATIONS
' ----------- ---------------------------------------------------------------
' Date/Time Programmer/Description
' ----------- ---------------------------------------------------------------
' 05-Jul-08 Franz Alex Gaisie-Essilfie
' 23:15 Corrected a major error which caused 'INSERT INTO ...' SQL
' Command to fail.
'------------------------------------------------------------------------------
''' <summary>
''' Updates the database.
''' </summary>
''' <returns>True if successful; false if otherwise.</returns>
Public Function Update() As Boolean
Dim returnVal As Integer = 0
Dim e As New System.ComponentModel.CancelEventArgs
RaiseEvent UpdateStarted(Me, e)

Try

If Not e.Cancel Then
'todo: check if OleDbConnection is compatible with other providers.
Dim Connection As New OleDb.OleDbConnection(_connStr.ToString) ' "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _DBPath)
Dim Adapter As New OleDb.OleDbDataAdapter(_SQLQuery, Connection)
Dim CmdBuilder As New OleDb.OleDbCommandBuilder(Adapter)
CmdBuilder.QuotePrefix = "["
CmdBuilder.QuoteSuffix = "]"

Connection.Open()

Adapter.InsertCommand = CmdBuilder.GetInsertCommand
Adapter.DeleteCommand = CmdBuilder.GetDeleteCommand
Adapter.UpdateCommand = CmdBuilder.GetUpdateCommand

returnVal += Adapter.Update(_Table.Select(Nothing, Nothing, DataViewRowState.Deleted))
returnVal += Adapter.Update(_Table.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))
returnVal += Adapter.Update(_Table.Select(Nothing, Nothing, DataViewRowState.Added))
Adapter.Dispose()

If returnVal > 0 Then
Return True
Else
Return False
End If
End If

Catch ex As Exception
_exceptions.Add(ex)
RaiseEvent ExceptionEncounterd(Me, New ExceptionEncounteredArgs(ex, Suppressed))
Finally
If returnVal > 0 Then RaiseEvent Updated(Me, New System.EventArgs)
End Try

End Function

'------------------------------------------------------------------------------
' Function : Update
' Date/Time : 26-Jun-08 11:56:27
' Parameters : Table (DataTable)
' Returns : Boolean
' Purpose : Update the database with the table specified.
'------------------------------------------------------------------------------
' CODE MODIFICATIONS
' ----------- ---------------------------------------------------------------
' Date/Time Programmer/Description
' ----------- ---------------------------------------------------------------
' 05-Jul-08 Franz Alex Gaisie-Essilfie
' 23:16 Corrected a major error which caused 'INSERT INTO ...' SQL
' Command to fail.
'------------------------------------------------------------------------------
''' <summary>
''' Updates the database with the table specified.
''' </summary>
''' <param name="Table">Table to use to update the database.</param>
''' <returns></returns>
Public Function Update(ByVal Table As DataTable) As Boolean
Dim returnval As Integer = 0
Dim e As New System.ComponentModel.CancelEventArgs
RaiseEvent UpdateStarted(Me, e)

Try
If Not e.Cancel Then
'todo: remove if code still functions properly
'Dim Connection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _DBPath
Dim Adapter As New OleDb.OleDbDataAdapter(_SQLQuery, _connStr.ToString)
Dim CmdBuilder As New OleDb.OleDbCommandBuilder(Adapter)
CmdBuilder.QuotePrefix = "["
CmdBuilder.QuoteSuffix = "]"

Adapter.InsertCommand = CmdBuilder.GetInsertCommand
Adapter.DeleteCommand = CmdBuilder.GetDeleteCommand
Adapter.UpdateCommand = CmdBuilder.GetUpdateCommand

returnval += Adapter.Update(Table.Select(Nothing, Nothing, DataViewRowState.Deleted))
returnval += Adapter.Update(Table.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))
returnval += Adapter.Update(Table.Select(Nothing, Nothing, DataViewRowState.Added))
returnval += Adapter.Update(Table.Select(Nothing, Nothing, DataViewRowState.Unchanged))
Adapter.Dispose()

If returnval > 0 Then
Return True
Else
Return False
End If
End If

Catch ex As Exception
_exceptions.Add(ex)
RaiseEvent ExceptionEncounterd(Me, New ExceptionEncounteredArgs(ex, Suppressed))
Finally
If returnval > 0 Then RaiseEvent Updated(Me, New System.EventArgs)
End Try

End Function

''' <summary>Updates the database with the rows passed.</summary>
''' <param name="Rows">DataRows to be used in updating the database.</param>
''' <returns>Boolean. True if successful; false if otherwise.</returns>
Public Function Update(ByVal Rows() As DataRow) As Boolean
Dim returnval As Integer = 0
Dim e As New System.ComponentModel.CancelEventArgs
RaiseEvent UpdateStarted(Me, e)

Try
If Not e.Cancel Then
'todo: remove if code still functions properly
'Dim Connection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _DBPath
Dim Adapter As New OleDb.OleDbDataAdapter(_SQLQuery, _connStr.ToString)
Dim CmdBuilder As New OleDb.OleDbCommandBuilder(Adapter)
CmdBuilder.QuotePrefix = "["
CmdBuilder.QuoteSuffix = "]"

Adapter.InsertCommand = CmdBuilder.GetInsertCommand
Adapter.DeleteCommand = CmdBuilder.GetDeleteCommand
Adapter.UpdateCommand = CmdBuilder.GetUpdateCommand

returnval = Adapter.Update(Rows)
Adapter.Dispose()

If returnval > 0 Then Return True Else Return False

End If
Catch ex As Exception
_exceptions.Add(ex)
RaiseEvent ExceptionEncounterd(Me, New ExceptionEncounteredArgs(ex, Suppressed))
Finally
If returnval > 0 Then RaiseEvent Updated(Me, New System.EventArgs)
End Try
End Function

''' <summary>
''' Refill the table's columns from the database.
''' </summary>
Public Sub RefreshTables()
If (_SQLQuery.Trim <> "") And (_connStr.DataSource.Trim <> "") Then FillDB()
End Sub

'------------------------------------------------------------------------------
' Subroutine : ClearExceptions
' Date/Time : 28-Dec-08 11:26:24
' Purpose : Clears list of exceptions.
'------------------------------------------------------------------------------
' CODE MODIFICATIONS
' ----------- ---------------------------------------------------------------
' Date/Time Programmer/Description
' ----------- ---------------------------------------------------------------
'------------------------------------------------------------------------------
''' <summary>
''' Clears exceptions that have been raised by this instance of the DBDataSet class.
''' </summary>
Public Sub ClearExceptions()
_exceptions.Clear()
End Sub

''' <summary>
''' Populates a serialization information object with the data
''' needed to serialize the System.Data.DBDataSet.
''' </summary>
''' <param name="info">A System.Runtime.Serialization.SerializationInfo
''' containing the data to serialize the class.</param>
''' <param name="context">The System.Runtime.Serialization.StreamingContext
''' containing the source and destination of the serialized stream.</param>
<EditorBrowsable(EditorBrowsableState.Always)> _
Public Overridable Sub GetObjectData(ByVal info As Runtime.Serialization.SerializationInfo, ByVal context As Runtime.Serialization.StreamingContext) Implements Runtime.Serialization.ISerializable.GetObjectData
With info
Try
.AddValue("Query", _SQLQuery, _SQLQuery.GetType)
.AddValue("ConnStr", _connStr, _connStr.GetType)
.AddValue("Exceptions", _exceptions, _exceptions.GetType)
.AddValue("AutoClrEx", _AutoClrEx, _AutoClrEx.GetType)
'_Table.GetObjectData(info, context)

'compress the table
Using memStream As New IO.MemoryStream()
Using zipStream As New cs.GZipStream(memStream, cs.CompressionMode.Compress)
Dim bf As New sr.Formatters.Binary.BinaryFormatter()
bf.Serialize(zipStream, _Table)
zipStream.Flush()
' Save the contents of the compressed stream.
Dim bytes() As Byte = memStream.GetBuffer()
.AddValue("bytes", bytes)
End Using
End Using

Catch ex As Exception
Throw ex
End Try
End With
End Sub

#Region "Table Event Raisers"
Private Sub OnColumnChanged(ByVal sender As Object, ByVal e As DataColumnChangeEventArgs) Handles _
_Table.ColumnChanged
RaiseEvent ColumnChanged(sender, e)
End Sub

Private Sub OnColumnChanging(ByVal sender As Object, ByVal e As DataColumnChangeEventArgs) Handles _Table.ColumnChanging
RaiseEvent ColumnChanging(sender, e)
End Sub

Private Sub OnRowChang

This post has been edited by falexge: 09 January 2009 - 09:03 AM

[falexge]

There's something wrong, my code can't paste!. parts of it just keep getting clipped off. Can I please attach the files?

Pretty Please???

[falexge]

Here are the files, all put together in one zip file.

The zip file contains the two classes but not the entire project as I'm on VS 2008 and do not want you to have any problems loading it in an older version of VS.

Thanks for your cooperation.


Alex.

Attached File(s)

•  DatabaseConnectivity.zip (6.9K)
  Number of downloads: 49