QuNect ODBC for QuickBase

網站
--
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 first argument is the name of your SQL Server.
The second argument is your SQL Server username.
The third argument is your SQL Server password.
The fourth argument is the SQL Server database where the table in questions resides.
The fifth argument is the SQL Server table.
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