/// <summary>
/// Takes the passed DataTable <c>dt</c> and creates a table in the currently
/// open SQLite database with equivilant schama and values. If the currently
/// open database has a table of the same name, attempts to append the rows
/// in the DataTable to it.
/// </summary>
/// <remarks>
/// <para>This qualifies as one of the greatest dirty hacks that just works.
/// I don't scrub parameters, and it will probably only work with DataTables
/// from Microsoft Access Databases. I am not all emcompasing with
/// data types. I simple kept feeding it the data I needed to feed it until
/// it worked. If it encounters a datatype it doesn't know how to deal with it
/// panics and throws an exception.</para>
/// <para>In terms of datatype considerations, dates are mapped as strings
/// because I read somewhere that there are date manipulation functions
/// in SQLite that operate on strings and assigning a DateTime to a string
/// produced a sensible value. If storing dates in integers as unix
/// timestamps makes more sense in the future I might do that. Be warned.
/// I never told you to depend on this function.</para>
/// <para>All INSERT statements are done as one transaction. The reason for this
/// is that no writes are performed to the database until a transaction is
/// committed and therefore the difference in execution time between
/// inserting a thousand rows in one transaction and inserting a thousand rows
/// without transactions is greater than a thousand fold. This has been tested on
/// a table containing 179442 rows with 5 numeric column and one DateTime column
/// that was inserted as a text column. Tests much be performed on even larger
/// datasets to see if there is a point of noticeable performance degradation.
/// </para>
/// </remarks>
/// <param name="dt">The DataTable to place in a new SQLite database.</param>
public void DataTable2SQLiteTable(DataTable dt)
{
/* Check to make sure that the DataTable has a name */
string TableNameError =
"DataTable passed to DataTable2SQLiteTable() must have the TableName Property " +
"set to a non null, non empty string (\"\") value.";
if (dt.TableName == null)
{
throw new ArgumentNullException(TableNameError);
}
else if (dt.TableName == "")
{
throw new ArgumentException(TableNameError);
}
/* Create the table */
using (SQLiteCommand cmd = (SQLiteCommand)Cn.CreateCommand()) {
StringBuilder DDL = new StringBuilder();
//TODO: I wonder if I can pass parameters to CREATE TABLE statements.
DDL.AppendFormat("CREATE TABLE [{0}] (", dt);
List <string> Cols = new List <string>();
/* Figure out what datatypes to assign to the columns */
foreach (DataColumn col in dt.Columns)
{
if (col.DataType == typeof(string) || col.DataType == typeof(DateTime))
{
Cols.Add(String.Format("[{0}] TEXT", col.ColumnName));
}
else if (col.DataType == typeof(long) || col.DataType == typeof(ulong) || col.DataType == typeof(Single) || col.DataType == typeof(Int32) || col.DataType == typeof(bool) || col.DataType == typeof(Guid))
{
Cols.Add(String.Format("[{0}] INTEGER", col.ColumnName));
}
else if (col.DataType == typeof(byte[]))
{
Cols.Add(String.Format("[{0}] BLOB", col.ColumnName));
}
else
{
throw new DataException
(String.Concat("DataTable2SQLiteTable() doesn't know how to map columns of type ", col.DataType.ToString()));
}
}
DDL.Append(String.Join(", ", Cols.ToArray()));
DDL.Append(")");
cmd.CommandText = DDL.ToString();
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = (SQLiteCommand)Cn.CreateCommand()) {
/* Create the INSERT INTO statement. */
StringBuilder DML = new StringBuilder();
DML.AppendFormat("INSERT INTO [{0}] ([", dt.TableName);
List <string> ColumnName = new List <string>();
List <string> ParameterName = new List <string>();
foreach (DataColumn col in dt.Columns)
{
ColumnName.Add(col.ColumnName);
ParameterName.Add(col.ColumnName.Replace(' ', '_'));
}
DML.Append(String.Join("], [", ColumnName.ToArray()));
DML.Append("]) VALUES (@");
DML.Append(String.Join(", @", ParameterName.ToArray()));
DML.Append(")");
cmd.CommandText = DML.ToString();
/* Populate the parameters for the INSERT INTO statement and execute. */
foreach (DataRow Row in dt.Rows)
{
foreach (DataColumn col in dt.Columns)
{
cmd.Parameters.Add(new SQLiteParameter(String.Concat("@", col.ColumnName.Replace(' ', '_')), Row[col.ColumnName]));
}
}
/* It is much faster if this is done as one transaction. */
cmd.Transaction = (SQLiteTransaction)Cn.BeginTransaction();
DataTableReader rdr = dt.CreateDataReader();
/* Populate the parameters for the INSERT INTO statement and execute. */
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
cmd.Parameters[i].Value = rdr[i];
}
cmd.ExecuteNonQuery();
}
cmd.Transaction.Commit();
cmd.Transaction.Dispose();
}
}
