Application Note for QuNect ODBC for QuickBase

VB.Net Console Application for Copying Rows from a SQL Server Table to a QuickBase Table

This console application takes five command line arguments.

The SQL Server table should have as the last word in its name the DBID of the corresponding QuickBase table. You can find the DBID of your QuickBase table by reading the How to Find the DBID of a QuickBase Table application note. All columns in the SQL Server table will be copied to the columns of the same name in the QuickBase table. This piece of code relies on a properly configured ODBC Data Source called QuickBase via QuNect.

Imports System.Data.Odbc
Imports System.Data.SqlClient

Module SQLQuNectQuickBase

   Dim quNectConn As OdbcConnection
   Dim quNectCmd As OdbcCommand
   Dim quNectCacheWritesCmd as OdbcCommand
   Dim quNectFlushCmd As OdbcCommand
   Dim quNectParam As OdbcParameter
   Dim sqlConn As SqlConnection
   Dim sqlComm As SqlCommand
   Dim sqlDr As SqlDataReader

   Sub Main()

       If My.Application.CommandLineArgs.Count <> 5 Then
           Console.WriteLine("Please supply: SQLserver SQLuser SQLpassword SQLDatabase SQLTable")
           Console.WriteLine("The SQL table should have the QuickBase DBID as the last or only word in the table name.")
           Exit Sub
       End If
       Dim argvServer As String = My.Application.CommandLineArgs.Item(0)
       Dim argvUID As String = My.Application.CommandLineArgs.Item(1)
       Dim argvPassword As String = My.Application.CommandLineArgs.Item(2)
       Dim argvDatabase As String = My.Application.CommandLineArgs.Item(3)
       Dim argvTable As String = My.Application.CommandLineArgs.Item(4)
       quNectConn = New OdbcConnection("DSN=QuickBase via QuNect;")
       Try
           quNectConn.Open()
       Catch excpt As Exception
           Console.WriteLine(excpt.Message)
           Exit Sub
       End Try

       'First let's open up a connection to SQL Server
       Dim sqlConnectString As String = "server=" & argvServer & ";uid=" & argvUID & ";pwd=" & argvPassword & ";database=" & argvDatabase
       sqlConn = New SqlConnection(sqlConnectString)
       sqlConn.Open()
       sqlComm = New SqlCommand("Select * from """ & argvTable & """", sqlConn)
       Try
           sqlDr = sqlComm.ExecuteReader()
       Catch excpt As Exception
           Console.WriteLine("SQL Server complained: " & excpt.Message)
           Exit Sub
       End Try

       Dim fieldCount As Integer = sqlDr.FieldCount
       Dim fieldCounter As Integer
       Dim quickBaseSQLFieldList As String = "("
       Dim quickBaseSQLParameterList As String = "("
       Dim quickBaseSQL As String

       For fieldCounter = 0 To fieldCount - 1
           quickBaseSQLFieldList &= """" & sqlDr.GetName(fieldCounter).ToString() & ""","
           quickBaseSQLParameterList &= "?,"
       Next
       quickBaseSQLFieldList = quickBaseSQLFieldList.Substring(0, quickBaseSQLFieldList.Length - 1) & ")"
       quickBaseSQLParameterList = quickBaseSQLParameterList.Substring(0, quickBaseSQLParameterList.Length - 1) & ")"
       quickBaseSQL = "INSERT INTO """ & argvTable & """ " & quickBaseSQLFieldList & " VALUES " & quickBaseSQLParameterList
       
       quNectCmd = New OdbcCommand(quickBaseSQL, quNectConn)
       For fieldCounter = 0 To fieldCount - 1
           Dim thisODBCType As Odbc.OdbcType
           If sqlDr.GetFieldType(fieldCounter).Name = "String" Then
               thisODBCType = OdbcType.NVarChar
           ElseIf sqlDr.GetFieldType(fieldCounter).Name = "Double" Then
               thisODBCType = OdbcType.Double
           ElseIf sqlDr.GetFieldType(fieldCounter).Name = "DateTime" Then
               thisODBCType = OdbcType.DateTime
           ElseIf sqlDr.GetFieldType(fieldCounter).Name = "Date" Then
               thisODBCType = OdbcType.Date
           ElseIf sqlDr.GetFieldType(fieldCounter).Name = "Money" Then
               thisODBCType = OdbcType.Numeric
           ElseIf sqlDr.GetFieldType(fieldCounter).Name = "Bit" Then
               thisODBCType = OdbcType.Bit
           ElseIf sqlDr.GetFieldType(fieldCounter).Name.Contains("Char") Then
               thisODBCType = OdbcType.VarChar
           ElseIf sqlDr.GetFieldType(fieldCounter).Name = "Decimal" Then
               thisODBCType = OdbcType.Decimal
           End If
           quNectParam = New OdbcParameter("@qdb" & fieldCounter, thisODBCType)
           quNectCmd.Parameters.Add(quNectParam)
       Next
       
       While sqlDr.Read()
           For fieldCounter = 0 To fieldCount - 1
               quNectCmd.Parameters(fieldCounter).Value = sqlDr(fieldCounter)
           Next fieldCounter
           Try
               quNectCmd.ExecuteNonQuery()
           Catch excpt As Exception
               Console.WriteLine(excpt.Message)
           End Try
      
       End While
       sqlDr.Close()
       sqlConn.Close()
       quNectConn.Close()
   End Sub

End Module