Provider independent DataSet

[J L]

A few days ago, I started a thread on abstracting connections and
commands. Based on the input and further study, I think I have learned
a way to do some of what I need based on using Interfaces. Below is
untested code (I am still awaiting my copy of VS to begin actually
coding) that is meant to provide a way to get a filled data set in my
UI layre without worring about the provider interface.

My quesions are:
1. Does this look like it will work (I will actually test it as soon
as I get VS).
2. Any performance issues with creating types, etc on the fly?
3. Any better way to do this?

I do understand the lack of security in this approach but (as I posted
recently) all of my SQL statements are code created based on
validated, non-databound user input and not susceptible to the SQL
injections etc.

Thanks to anyone who takes the time to look at this and comment:

The user creates a DataSet object and an SQL Selecct string which are
passed into the function. If successful, the DataSet will be filled
and the function returns True. If not, the function returns False.

To work, I have to reference and import System.Reflection.Activator

Function GetDataSet( ByRef ds as DataSet, strSQL as String) As Boolean
Dim cnnType, cmdType, daType as Type
Dim cnn as IDbConnection
Dim cmd as IdbCommand
Dim d As IdataAdapter
Dim da As IDbDataAdapter

Try
' dbType set globally from application configuration file
Select Case dbType
Case "SQL"
cnnType = GetType(SQLConnection)
cmdType = GetType(SQLCommand)
daType = GetType(SQLDataAdapter)
Case "OleDb"
cnnType = GetType(OleDbConnection)
cmdType = GetType(OleDbCommand)
daType = GetType(OleDbDataAdapter)
Case Else
GetDataSet = False
Exit Function
End Select

' create the Connection
' strConnect global and built based on dbType
cnn = Ctype(Activator.CreateInstance(cnnType, False), Idbconnection)
cnn.ConnectionSTring = strConnect

' create the Command
cmd = Ctype(Activator.CreateInstance(cmdType, False), IdbCommand)
cmd.CommandText = strSql
cmd.Connection = cnn

' create the DataAdapter
d = Ctype(Activator.CreateInstance(daType, False), IdataAdapter)
da = Ctype(d, IdbDataAdapter)

' fill the DataSet
da.Fill(ds, cmd)

' return success
GetDataSet = True

Catch e As Exception
' failed
GetDataSet = False
End Try

End Function

 

[Bishoy Ghaly]

I did that 4 months ago on an enterprise application my comapany started and
it works great.

 

[J L]

Thanks for the reply Bishoy. I am glad and encouraged to hear that it
worked great for you. What functionality did you implement? Mine just
returns fills a DataSet. Did you create a DataReader, Command only,
etc? If you did the either of these, could you share some code (or
pseudo code if your code is proprietary)?

Thanks,
John