[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How to create a table in Sql server from a datareader.GetSchemaTable DataTable

Posted on 2008-01-30
4
Medium Priority
?
2,206 Views
Last Modified: 2013-11-24
Can anyone help me with VB.NET code to create a table in Sql server from a datareader.GetSchemaTable DataTable?

I'm having trouble with comparing the datatype column and converting it.

I need a way to convert dBASE IV tables to SQL Server tables on the fly.
0
Comment
Question by:straucha
  • 3
4 Comments
 
LVL 12

Expert Comment

by:vb_jonas
ID: 20784242
Hi!

Could you tell us more about your datatype problems? Are the GetSchemaTable not getting the correct type (DataType should be mapped to the .NET Framework type of the column.)

Are you using T-SQL or SMO to create the datatable in SQL Server?
0
 
LVL 12

Accepted Solution

by:
vb_jonas earned 2000 total points
ID: 20784453
Here's code I wrote to test creating a datatable from datareader.GetSchemaTable:
 Sub CreateDb(ByVal strTableName As String, ByVal dtSchema As DataTable)
 
  ' dtSchema is a schema from 
  ' dtSchema=somekindofdatareader.GetSchemaTable()
 
  ' You will need references to 
  ' Microsoft.SqlServer.ConnectionInfo
  ' Microsoft.SqlServer.Smo()
  ' Microsoft.SqlServer.SmoEnum()
  ' Microsoft.SqlServer.SqlEnum()
 
  Dim server As New Server(".\SQLEXPRESS") ' local pc SQLEXPRESS
  Dim db As Database = server.Databases("TestDataBase")
 
  ' Create new table
  Dim newTable As New Table(db, strTableName)
 
  Dim rw As DataRow
 
  Dim smoColumn As Column
  Dim nColumnSize As Integer
  Dim nColumnNumericPrecision As Decimal
  Dim nColumnNumericScale As Decimal
  Dim strIndexColumnName As String = ""
 
  For Each rw In dtSchema.Rows
   smoColumn = New Column(newTable, rw("ColumnName"))
   Select Case rw("DataType").ToString
    '
    ' Handle datatype conversion - add the types you need
    '
    Case "System.Int32"
     smoColumn.DataType = DataType.Int
    Case "System.String"
     nColumnSize = rw("ColumnSize")
     If nColumnSize = -1 Then
      smoColumn.DataType = DataType.VarCharMax
     Else
      smoColumn.DataType = DataType.VarChar(nColumnSize)
     End If
    Case "System.Decimal"
     nColumnNumericPrecision = rw("NumericPrecision")
     nColumnNumericScale = rw("NumericScale")
     smoColumn.DataType = DataType.Decimal(nColumnNumericScale, nColumnNumericPrecision)
    Case Else
     Err.Raise(555, , "Handle this type please")
   End Select
   smoColumn.Nullable = rw("AllowDBNull")
   
   ' Handle id-column
   If rw("IsAutoIncrement") And rw("IsUnique") Then
    strIndexColumnName = rw("ColumnName")
    smoColumn.Identity = True
    smoColumn.IdentitySeed = 1
    smoColumn.IdentityIncrement = 1
   End If
   newTable.Columns.Add(smoColumn)
  Next
  If strIndexColumnName <> "" Then
   ' Create a PK Index
   Dim index As New Index(newTable, "PK_TestTable")
   index.IndexKeyType = IndexKeyType.DriPrimaryKey
   index.IndexedColumns.Add(New IndexedColumn(index, strIndexColumnName))
   newTable.Indexes.Add(index)
  End If
 
  ' Create the table in the database
  newTable.Create()
 
 
 End Sub

Open in new window

0
 
LVL 1

Author Comment

by:straucha
ID: 20793921
I should have stated that I was using VS 2003, however your post still helped me.

My original problem was with the following Line:
Select Case rw("DataType").ToString

The .ToString doesn't appear as an option for the datarow("ColumnName") in the tip that pops up, only .getType, however as your code showed it works anyway.

Thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question