/// <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(); } }