Table Creation in ADO.NET

[Robert C. Cain]

Hi All,

OK, here's what I need to do:

I have an Oracle database. I am going to write a VB.Net application that on
a regular basis will grab a table, and copy it down to Access. I'd like to
maximize reuse by creating a generic routine that will:

Open the Oracle Table

Cycle through each field in the table, getting it's name, datatype, size,
and adding them to a string. This string will become an Create Table sql
statement for Access. (Naturally I'll have to do some translation, VarChar
to Text, etc).

Create the table in Access, then copy each row over.

I know I can do this with traditional ADO, I've done it before. My question
is, can I do this with ADO.NET? I'd like to use the newer ADO.NET just for
the learning experience, but so far every book I've picked up only shows you
how to open a table and get the rows, and maybe update. Nothing on getting
field types, etc.

So before I spend a lot of time on this, is it possible? And if so, has
anyone seen any good articles out there on viewing table structures under
ADO.NET?

Thanks,

Robert

 

[Joe Fallon]

I wrote this to get schema for any table using SQL Server, Oracle, or OLEDB
(Access, Excel).
Also, check out David Sceppa's book on ADO.Net (it is first rate.)

Public Sub GetSchema(ByRef dt As DataTable, ByVal strSQL As String, ByVal
DBtype As String)
'retrieve structure information into a datatable using the FillSchema
method of a DataAdapter object
If DBtype = "SQL Server" Then
Dim da As SqlDataAdapter
Dim cnn As New SqlConnection(mConnStr)
Dim cmd As New SqlCommand(strSQL, cnn)
Try
cmd.CommandType = CommandType.Text
cnn.Open()
da = New SqlClient.SqlDataAdapter(cmd)
da.FillSchema(dt, SchemaType.Source)

Finally
cnn.Close()
End Try
ElseIf DBtype = "Oracle" Then
Dim da As OracleDataAdapter
Dim cnn As New OracleConnection(mConnStr)
Dim cmd As New OracleCommand(strSQL, cnn)
Try
cmd.CommandType = CommandType.Text
cnn.Open()
da = New OracleClient.OracleDataAdapter(cmd)
da.FillSchema(dt, SchemaType.Source)
Finally
cnn.Close()
End Try
ElseIf DBtype = "OLEDB" Then
Dim cnn As New OleDbConnection(mConnStr)
Try
cnn.Open()
'KB309488 - the Object array is for filtering the returned data
(only TABLEs are returned not Views, etc.)
dt = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object()
{Nothing, Nothing, Nothing, "TABLE"})
Finally
cnn.Close()
End Try
End If
End Sub



Private Function GetSchemaInfoFromDataReader(ByVal strSQL As String,
ByVal DBtype As String) As DataTable
' retrieve structure information into a datatable using the
GetSchemaTable method of a datareader object
' this does NOT give an empty table structure identical to the one on
the Server!!!
' it gives a table of all the columns and lots of info about each
column!!

If DBtype = "SQL Server" Then
Dim dr As SqlDataReader
Dim cnn As New SqlConnection(mConnStr)
Dim cmd As New SqlCommand(strSQL, cnn)
Dim tblSchema As DataTable

Try
cmd.CommandType = CommandType.Text
cnn.Open()
dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly)
tblSchema = dr.GetSchemaTable

Return tblSchema
End Try
ElseIf DBtype = "Oracle" Then
Dim dr As OracleDataReader
Dim cnn As New OracleConnection(mConnStr)
Dim cmd As New OracleCommand(strSQL, cnn)
Dim tblSchema As DataTable

Try
cmd.CommandType = CommandType.Text
cnn.Open()
dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly)
tblSchema = dr.GetSchemaTable
Return tblSchema
End Try
End If
End Function