How To

Re: Need How to Dynamically Create Parameters [SOLVED]

Robert Simpson — Fri, 26 Jun 2009 22:57:02 GMT

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.

Re: Need How to Dynamically Create Parameters [SOLVED]

rkulp — Fri, 03 Apr 2009 21:41:53 GMT

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

Re: Need How to Dynamically Create Parameters

rkulp — Fri, 03 Apr 2009 20:07:22 GMT

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?

Re: Need How to Dynamically Create Parameters

rkulp — Wed, 01 Apr 2009 14:13:38 GMT

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******************

With dlgSaveFile .InitialDirectory = My.Computer.FileSystem.GetParentPath(txtExcelFileName.Text)

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

.FilterIndex = 1

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

strSQLiteConn = "DataSource = " & .FileName

End With

SQLiteConn = New SQLite.SQLiteConnection(strSQLiteConn)

SQLiteConn.Open()

gMsg = "Data Types: " & vbCrLf

CreateTableString = CreateTableBaseString & cboSheetName.SelectedText

Dim nbrParm As Short = dtExcel.Columns.Count

Dim parmList As SQLite.SQLiteParameter()

ReDim parmList(nbrParm - 1)

Dim I As Integer = 0

For Each myDataColumn In dtExcel.ColumnsgMsg += "Column " & myDataColumn.ColumnName & ": data type = " & myDataColumn.DataType.ToString & vbCrLf

myDataType = myDataColumn.DataType.ToString

Select Case myDataType

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 & ", "

Dim holdParm As New SQLiteParameter

With holdParm

.ParameterName = "@" & myDataColumn.ColumnName

End With

parmList(I) = holdParm

Next myDataColumn

CreateColumnString = CreateColumnString + ")"

CreateColumnString = Replace(CreateColumnString, ", )", ")")

CreateTableString = CreateTableString & CreateColumnString

Using cmdCreateTable As SQLiteCommand = SQLiteConn.CreateCommandWith cmdCreateTable

.CommandType = CommandType.Text

.CommandText = CreateTableString

.Parameters.AddRange(parmList)

.ExecuteNonQuery()

End With

Dim trans As SQLiteTransaction = cmdCreateTable.Transaction

End Using

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

Need How to Dynamically Create Parameters

rkulp — Tue, 31 Mar 2009 22:11:54 GMT

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.