/// <summary>
/// Writes fixation data table into MDF File database with bulk statement.
/// </summary>
/// <remarks>The bulk statement reduces the time consumption for large amount
/// of data.</remarks>
/// <param name="sampleType">A <see cref="SampleType"/> which indicates
/// the sampling data source, gaze or mouse.</param>
private void WriteToMDF(SampleType sampleType)
{
try
{
string fixTableName = string.Empty;
DataTable fixTable = null;
if (sampleType == (sampleType | SampleType.Gaze))
{
fixTableName = "GazeFixations";
fixTable = Document.ActiveDocument.DocDataSet.GazeFixations;
}
else if (sampleType == (sampleType | SampleType.Mouse))
{
fixTableName = "MouseFixations";
fixTable = Document.ActiveDocument.DocDataSet.MouseFixations;
}
// Delete Entrys in current tables because BulkInsert will insert all rows again
string queryString = "DELETE FROM " + fixTableName + ";";
var command = new SQLiteCommand(queryString, Document.ActiveDocument.DocDataSet.DatabaseConnection);
SQLiteDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
//Console.WriteLine(string.Format("{0}, {1}", reader[0], reader[1]));
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
var conn = Document.ActiveDocument.DocDataSet.DatabaseConnection;
var sbi = new SQLiteBulkInsert(conn, fixTableName);
sbi.AddParameter("ID", DbType.Int64);
sbi.AddParameter("SubjectName", DbType.String);
sbi.AddParameter("TrialSequence", DbType.Int32);
sbi.AddParameter("TrialId", DbType.Int32);
sbi.AddParameter("CountInTrial", DbType.Int32);
sbi.AddParameter("StartTime", DbType.Int64);
sbi.AddParameter("Length", DbType.Int32);
sbi.AddParameter("PosX", DbType.Single);
sbi.AddParameter("PosY", DbType.Single);
foreach (var row in fixTable.Rows)
{
if (row is SQLiteOgamaDataSet.GazeFixationsRow)
{
var rawRow = row as SQLiteOgamaDataSet.GazeFixationsRow;
sbi.Insert(new object[] { rawRow.ID, rawRow.SubjectName, rawRow.TrialSequence, rawRow.TrialID, rawRow.CountInTrial, rawRow.StartTime, rawRow.Length, rawRow.PosX, rawRow.PosY });
}
else if (row is SQLiteOgamaDataSet.MouseFixationsRow)
{
var rawRow = row as SQLiteOgamaDataSet.MouseFixationsRow;
sbi.Insert(new object[] { rawRow.ID, rawRow.SubjectName, rawRow.TrialSequence, rawRow.TrialID, rawRow.CountInTrial, rawRow.StartTime, rawRow.Length, rawRow.PosX, rawRow.PosY });
}
}
sbi.Flush();
//// Write new Entrys
//using (SqlBulkCopy bcp = new SqlBulkCopy(Document.ActiveDocument.DocDataSet.DatabaseConnection))
//{
// bcp.BulkCopyTimeout = 6000;
// // Write from the source to the destination.
// bcp.DestinationTableName = fixTableName;
// bcp.WriteToServer(fixTable);
// bcp.Close();
//}
}
catch (Exception ex)
{
ExceptionMethods.HandleException(ex);
}
}
/// <summary>
/// This method uses the <see cref="SqlBulkCopy"/> for fast
/// copying the rawdata of the given subjects raw data table
/// to the database file (.mdf).
/// </summary>
/// <param name="subjectName">A <see cref="string"/> with the subject name.</param>
/// <param name="rawDataTable">A <see cref="DataTable"/> with the subjects sampled data.</param>
public static void WriteRawDataWithBulkStatement(string subjectName, DataTable rawDataTable)
{
var conn = Document.ActiveDocument.DocDataSet.DatabaseConnection;
var sbi = new SQLiteBulkInsert(conn, rawDataTable.TableName);
sbi.AddParameter("ID", DbType.Int64);
sbi.AddParameter("SubjectName", DbType.String);
sbi.AddParameter("TrialSequence", DbType.Int32);
sbi.AddParameter("Time", DbType.Int64);
sbi.AddParameter("PupilDiaX", DbType.Single);
sbi.AddParameter("PupilDiaY", DbType.Single);
sbi.AddParameter("GazePosX", DbType.Single);
sbi.AddParameter("GazePosY", DbType.Single);
sbi.AddParameter("MousePosX", DbType.Single);
sbi.AddParameter("MousePosY", DbType.Single);
sbi.AddParameter("EventID", DbType.Int32);
foreach (var row in rawDataTable.Rows)
{
var rawRow = row as SQLiteOgamaDataSet.RawdataRow;
sbi.Insert(new object[] { rawRow.ID, rawRow.SubjectName, rawRow.TrialSequence, rawRow.Time, rawRow.PupilDiaX, rawRow.PupilDiaY, rawRow.GazePosX, rawRow.GazePosY, rawRow.MousePosX, rawRow.MousePosY, rawRow.EventID });
}
sbi.Flush();
//using (SqlBulkCopy bcp = new SqlBulkCopy(Document.ActiveDocument.DocDataSet.DatabaseConnection))
//{
// bcp.BulkCopyTimeout = 300;
// //// Write from the source to the destination.
// string tableName = subjectName + "Rawdata";
// bcp.DestinationTableName = "dbo." + tableName;
// bcp.WriteToServer(rawDataTable);
// bcp.Close();
//}
}
