Example #1
0
    /// <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);
      }
    }
Example #2
0
    /// <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();
      //}
    }