System.Data.SQLite

I am trying to export an Excel Workbook containing an unknown number of sheets, each with an unknown number of fields and field names, to a SQLite db with a table for each sheet and a column for each field. I can get the schema including sheet names and column names with data types. What I want to do is create parameters for the insert command. Robert made the following suggestion in http://sqlite.phxsoftware.com/forums/p/451/3617.aspx#3617:

"I'd recommend a more generic approach, which would be to use an array and call the Command.Parameters.AddRange() command to add the entire array to the parameters for the command."

I would like some help in implementing this idea or a suggestion for a better way.

Here is my first attempt. I am getting an error that the "DataSource cannot be empty" when I try to open the connection and build the table. I have not coded the transaction part yet. I just wanted to see if the database and table were created with the schema. gMsg is for a message box used for debugging.

*******************Begin Code Snippet******************

.Filter = "SQLite3 Database *.s3db|*.s3db"

If .ShowDialog <> Windows.Forms.DialogResult.OK Then Exit Sub

End With

Dim parmList As SQLite.SQLiteParameter()

Dim I As Integer = 0

Case Is = "System.Double"

myDataType = "FLOAT"

Case Is = "System.Decimal"

myDataType = "FLOAT"

Case Is = "System.String"

myDataType = "VARCHAR(1000)"

Case Is = "System.DateTime"

myDataType = "DATETIME"

Case Else

myDataType = "VARCHAR(500)"

End Select

CreateColumnString += myColumnName & " " & myDataType & ", "

With holdParm

End With

CreateColumnString = CreateColumnString + ")"

End With

End Using

*******************End Code Snippet******************

Here is how I created the table:

1. I used a dummy database (DummyDB.s3db) with a dummy table (DummyTable) which I stored as a resource and copied to the appropriate directory if it did not already exist. This allowed me to open a connection.

2. Using the Excel schema, I was able to create a table in the dummy database which had the same schema plus an autoincrement primary key.

3. I dropped the dummy table. When the new table is filled, I'll copy it to the final destination with the final db name.

Now I am trying to fill the new table. Is there a way to attach a DataAdapter to the datatable that was created from the Excel sheet and then use the provider to update the new table without looping through each row of the Excel datatable?

I went ahead and looped through the datatable. I would still like to know of a better way to do it.

Sorry about not getting back to you on this.  Your timing was bad :)  I broke both my legs at the end of March, so you were posting these questions while I was laid up and on pain meds :)

I'm not sure what the necessity was for creating a dummy database ... I mean, the engine will autocreate the databsae if the file wasn't found, so I'm not sure what that was meant to solve.

As for filling the table ... If you want a hands-off approach that's only a little slower, you could fill a datatable with the excel data, then using a SQLiteCommandBuilder and SQLiteDataAdapter (after creating the SQLite table) you could copy all the rows from the excel datatable to a SQLite datatable.  Then just call the adapter's Update method to have the rows inserted.