/// <summary>Write the specified number of rows.</summary>
/// <param name="connection">The SQLite connection to write to</param>
/// <param name="simulationIDs">A dictionary of simulation IDs</param>
public void WriteRows(SQLite connection, Dictionary <string, int> simulationIDs)
{
IntPtr preparedInsertQuery = IntPtr.Zero;
try
{
// If the table exists, make sure it has the required columns, otherwise create the table
if (TableExists(connection, Name))
{
AlterTable(connection);
}
else
{
CreateTable(connection);
}
// Create an insert query
preparedInsertQuery = CreateInsertQuery(connection);
for (int rowIndex = 0; rowIndex < RowsToWrite.Count; rowIndex++)
{
connection.BindParametersAndRunQuery(preparedInsertQuery, RowsToWrite[rowIndex]);
}
}
finally
{
if (preparedInsertQuery != IntPtr.Zero)
{
connection.Finalize(preparedInsertQuery);
}
}
}
/// <summary>Write the specified number of rows.</summary>
/// <param name="connection">The SQLite connection to write to</param>
/// <param name="simulationIDs">A dictionary of simulation IDs</param>
public void WriteRows(SQLite connection, Dictionary <string, int> simulationIDs)
{
IntPtr preparedInsertQuery = IntPtr.Zero;
try
{
List <string> columnNames = new List <string>();
List <object[]> values = new List <object[]>();
// If the table exists, make sure it has the required columns, otherwise create the table
lock (lockObject)
{
int numRows = RowsToWrite.Count;
if (TableExists(connection, Name))
{
AlterTable(connection);
}
else
{
CreateTable(connection);
}
columnNames.AddRange(Columns.Select(column => column.Name));
values.AddRange(RowsToWrite.GetRange(0, numRows));
RowsToWrite.RemoveRange(0, numRows);
}
// Create an insert query
preparedInsertQuery = CreateInsertQuery(connection, columnNames);
for (int rowIndex = 0; rowIndex < values.Count; rowIndex++)
{
connection.BindParametersAndRunQuery(preparedInsertQuery, values[rowIndex]);
}
}
finally
{
if (preparedInsertQuery != IntPtr.Zero)
{
connection.Finalize(preparedInsertQuery);
}
}
}
/// <summary>
/// Write the specified tables to a single table in the DB. i.e. merge
/// all columns and rows in all specified tables into a single table.
/// </summary>
/// <param name="tables">The tables.</param>
private void WriteTable(TableToWrite[] tables)
{
// Open the .db for writing.
Open(forWriting: true);
// What table are we writing?
string tableName = tables[0].TableName;
// Get a list of all names and datatypes for each field in this table.
List <string> names = new List <string>();
List <Type> types = new List <Type>();
names.Add("SimulationID");
types.Add(typeof(int));
foreach (TableToWrite table in tables)
{
if (table.Data != null)
{
// If the table has a simulationname then go find its ID for later
if (table.Data.Columns.Contains("SimulationID"))
{
// do nothing.
}
else if (table.SimulationName != null)
{
table.SimulationID = GetSimulationID(table.SimulationName);
}
else
{
AddSimulationIDColumnToTable(table.Data);
}
// Go through all columns for this table and add to 'names' and 'types'
foreach (DataColumn column in table.Data.Columns)
{
if (!names.Contains(column.ColumnName) && column.ColumnName != "SimulationName")
{
names.Add(column.ColumnName);
types.Add(column.DataType);
}
}
}
}
// Create the table.
CreateTable(tableName, names.ToArray(), types.ToArray());
// Prepare the insert query sql
IntPtr query = PrepareInsertIntoTable(Connection, tableName, names.ToArray());
// Tell SQLite that we're beginning a transaction.
Connection.ExecuteNonQuery("BEGIN");
// Go through all tables and write the data.
foreach (TableToWrite table in tables)
{
// Write each row to the .db
if (table.Data != null)
{
object[] values = new object[names.Count];
foreach (DataRow row in table.Data.Rows)
{
for (int i = 0; i < names.Count; i++)
{
if (names[i] == "SimulationID" && table.SimulationID != int.MaxValue)
{
values[i] = table.SimulationID;
}
else if (table.Data.Columns.Contains(names[i]))
{
values[i] = row[names[i]];
}
}
// Write the row to the .db
Connection.BindParametersAndRunQuery(query, values);
}
}
}
// tell SQLite we're ending our transaction.
Connection.ExecuteNonQuery("END");
// finalise our query.
Connection.Finalize(query);
}
/// <summary>Create a table in the database based on the specified one.</summary>
/// <param name="table">The table.</param>
public void WriteTableRaw(DataTable table)
{
if (table.Columns.Count > 0)
{
// Open the .db for writing.
Open(readOnly: false);
if (table.Columns.Contains("SimulationName"))
{
AddSimulationIDColumnToTable(table);
}
// Get a list of all names and datatypes for each field in this table.
List <string> names = new List <string>();
List <Type> types = new List <Type>();
// Go through all columns for this table and add to 'names' and 'types'
foreach (DataColumn column in table.Columns)
{
names.Add(column.ColumnName);
types.Add(column.DataType);
}
// Create the table.
CreateTable(table.TableName, names, types);
// Prepare the insert query sql
IntPtr query = PrepareInsertIntoTable(connection, table.TableName, names);
// Tell SQLite that we're beginning a transaction.
connection.ExecuteNonQuery("BEGIN");
try
{
// Write each row to the .db
if (table != null)
{
object[] values = new object[names.Count];
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < names.Count; i++)
{
if (table.Columns.Contains(names[i]))
{
values[i] = row[names[i]];
}
}
// Write the row to the .db
connection.BindParametersAndRunQuery(query, values);
}
}
}
finally
{
// tell SQLite we're ending our transaction.
connection.ExecuteNonQuery("END");
// finalise our query.
connection.Finalize(query);
}
}
}
