private void AddParameters(SQLiteBulkInsert target)
{
target.AddParameter("somestring", DbType.String);
target.AddParameter("somereal", DbType.String);
target.AddParameter("someint", DbType.Int32);
target.AddParameter("somedt", DbType.DateTime);
}
/// <summary>
/// Reads the JMdict file.
/// </summary>
private IEnumerable <VocabEntity> ReadJmDict()
{
// Load the file.
XDocument xdoc = XDocument.Load(PathHelper.JmDictPath);
// Load vocab categories.
_log.Info("Loading vocab categories");
using (SQLiteBulkInsert <VocabCategory> categoryInsert
= new SQLiteBulkInsert <VocabCategory>(int.MaxValue))
{
foreach (VocabCategory category in LoadVocabCategories(xdoc))
{
// Store vocab categories in the database.
category.ID = categoryInsert.Insert(category);
VocabCategoryCount++;
// Add them to the dictionary too.
_categoryDictionary.Add(category.Label, category);
}
}
_log.InfoFormat("Loaded {0} vocab categories", VocabCategoryCount);
// Load and return vocab items.
_log.Info("Loading vocab");
foreach (VocabEntity vocab in LoadVocabItems(xdoc))
{
yield return(vocab);
}
}
public void FlushTest()
{
string[] names = new string[] { "metalica", "beatles", "coldplay", "tiesto", "t-pain", "blink 182", "plain white ts", "staind", "pink floyd" };
Random rand = new Random(Environment.TickCount);
SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
AddParameters(target);
target.CommitMax = 1000;
//Insert less records than commitmax
for (int x = 0; x < 50; x++)
{
target.Insert(names[rand.Next(names.Length)], (float)rand.NextDouble(), rand.Next(50), DateTime.Now);
}
//Close connect to verify records were not inserted
m_dbCon.Close();
m_dbCon = new SQLiteConnection(m_connectionString);
m_dbCon.Open();
long count = CountRecords();
Assert.AreEqual(0, count);
//Now actually verify flush worked
target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
AddParameters(target);
target.CommitMax = 1000;
//Insert less records than commitmax
for (int x = 0; x < 50; x++)
{
target.Insert(names[rand.Next(names.Length)], (float)rand.NextDouble(), rand.Next(50), DateTime.Now);
}
target.Flush();
count = CountRecords();
Assert.AreEqual(50, count);
//Close connect to verify flush worked
m_dbCon.Close();
m_dbCon = new SQLiteConnection(m_connectionString);
m_dbCon.Open();
count = CountRecords();
Assert.AreEqual(50, count);
DeleteRecords();
count = CountRecords();
Assert.AreEqual(0, count);
}
public void CommitMaxTest()
{
SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
target.CommitMax = 4;
Assert.AreEqual(4, target.CommitMax);
target.CommitMax = 1000;
Assert.AreEqual(1000, target.CommitMax);
}
public void CommandTextTest()
{
SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
AddParameters(target);
string pd = target.ParamDelimiter;
string expectedStmnt = "INSERT INTO [{0}] ([somestring], [somereal], [someint], [somedt]) VALUES ({1}somestring, {2}somereal, {3}someint, {4}somedt)";
expectedStmnt = string.Format(expectedStmnt, m_testTableName, pd, pd, pd, pd);
Assert.AreEqual(expectedStmnt, target.CommandText);
}
public void InsertTest()
{
SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
bool didThrow = false;
try {
target.Insert("hello"); //object.length must equal the number of parameters added
}
catch (Exception ex) { didThrow = true; }
Assert.IsTrue(didThrow);
AddParameters(target);
target.CommitMax = 4;
DateTime dt1 = DateTime.Now; DateTime dt2 = DateTime.Now; DateTime dt3 = DateTime.Now; DateTime dt4 = DateTime.Now;
target.Insert("john", 3.45f, 10, dt1);
target.Insert("paul", -0.34f, 100, dt2);
target.Insert("ringo", 1000.98f, 1000, dt3);
target.Insert("george", 5.0f, 10000, dt4);
long count = CountRecords();
Assert.AreEqual(4, count);
SQLiteDataReader reader = SelectAllRecords();
Assert.IsTrue(reader.Read());
Assert.AreEqual("john", reader.GetString(1)); Assert.AreEqual(3.45f, reader.GetFloat(2));
Assert.AreEqual(10, reader.GetInt32(3)); Assert.AreEqual(dt1, reader.GetDateTime(4));
Assert.IsTrue(reader.Read());
Assert.AreEqual("paul", reader.GetString(1)); Assert.AreEqual(-0.34f, reader.GetFloat(2));
Assert.AreEqual(100, reader.GetInt32(3)); Assert.AreEqual(dt2, reader.GetDateTime(4));
Assert.IsTrue(reader.Read());
Assert.AreEqual("ringo", reader.GetString(1)); Assert.AreEqual(1000.98f, reader.GetFloat(2));
Assert.AreEqual(1000, reader.GetInt32(3)); Assert.AreEqual(dt3, reader.GetDateTime(4));
Assert.IsTrue(reader.Read());
Assert.AreEqual("george", reader.GetString(1)); Assert.AreEqual(5.0f, reader.GetFloat(2));
Assert.AreEqual(10000, reader.GetInt32(3)); Assert.AreEqual(dt4, reader.GetDateTime(4));
Assert.IsFalse(reader.Read());
DeleteRecords();
count = CountRecords();
Assert.AreEqual(0, count);
}
public void SQLiteBulkInsertConstructorTest()
{
SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
Assert.AreEqual(m_testTableName, target.TableName);
bool wasException = false;
try {
string a = target.CommandText;
}
catch (SQLiteException ex) { wasException = true; }
Assert.IsTrue(wasException);
}
public void AllowBulkInsertTest()
{
string[] names = new string[] { "metalica", "beatles", "coldplay", "tiesto", "t-pain", "blink 182", "plain white ts", "staind", "pink floyd" };
Random rand = new Random(Environment.TickCount);
SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
AddParameters(target);
const int COUNT = 100;
target.CommitMax = COUNT;
DateTime start1 = DateTime.Now;
for (int x = 0; x < COUNT; x++)
{
target.Insert(names[rand.Next(names.Length)], (float)rand.NextDouble(), rand.Next(COUNT), DateTime.Now);
}
DateTime end1 = DateTime.Now;
TimeSpan delta1 = end1 - start1;
DeleteRecords();
target.AllowBulkInsert = false;
DateTime start2 = DateTime.Now;
for (int x = 0; x < COUNT; x++)
{
target.Insert(names[rand.Next(names.Length)], (float)rand.NextDouble(), rand.Next(COUNT), DateTime.Now);
}
DateTime end2 = DateTime.Now;
TimeSpan delta2 = end2 - start2;
//THIS MAY FAIL DEPENDING UPON THE MACHINE THE TEST IS RUNNING ON.
Assert.IsTrue(delta1.TotalSeconds < 0.1); //approx true for 100 recs Assert.IsTrue(delta2.TotalSeconds > 1.0); //approx true for 100 recs;
//UNCOMMENT THIS TO MAKE IT FAIL AND SEE ACTUAL NUMBERS IN FAILED REPORT
//Assert.AreEqual(delta1.TotalSeconds, delta2.TotalSeconds);
DeleteRecords();
}
/// <summary>
/// Gets the radicals and stores them in the database.
/// </summary>
public override void Execute()
{
// Parse the files.
RadicalDictionary = ParseKradFiles();
// Create a new bulk insert object.
using (SQLiteBulkInsert <RadicalEntity> radicalInsert
= new SQLiteBulkInsert <RadicalEntity>(RadicalMaxCommit))
{
Dictionary <string, RadicalEntity> addedRadicals = new Dictionary <string, RadicalEntity>();
foreach (var composition in RadicalDictionary)
{
// For each composition read, browse the radicals.
foreach (RadicalValue radicalValue in composition.Value)
{
if (addedRadicals.ContainsKey(radicalValue.Character))
{
// The radical was already found and added.
// Just set the radical of the RadicalValue.
radicalValue.Radical = addedRadicals[radicalValue.Character];
}
else
{
// Store in the database the radicals that have not already been stored in the
// "already added" dictionary.
RadicalEntity radical = new RadicalEntity()
{
Character = radicalValue.Character
};
radical.ID = radicalInsert.Insert(radical);
RadicalCount++;
// Set the radical of the RadicalValue and add an entry to the "already added" dictionary.
radicalValue.Radical = radical;
addedRadicals.Add(radicalValue.Character, radical);
// Log
_log.InfoFormat("Added radical {0} ({1})", radical.Character, radical.ID);
}
}
}
}
}
/// <summary>
/// Gets the radicals and stores them in the database.
/// </summary>
public override void Execute()
{
// Parse the files.
RadicalDictionary = ParseKradFiles();
// Create a new bulk insert object.
using (SQLiteBulkInsert<RadicalEntity> radicalInsert
= new SQLiteBulkInsert<RadicalEntity>(RadicalMaxCommit))
{
Dictionary<string, RadicalEntity> addedRadicals = new Dictionary<string, RadicalEntity>();
foreach (var composition in RadicalDictionary)
{
// For each composition read, browse the radicals.
foreach (RadicalValue radicalValue in composition.Value)
{
if (addedRadicals.ContainsKey(radicalValue.Character))
{
// The radical was already found and added.
// Just set the radical of the RadicalValue.
radicalValue.Radical = addedRadicals[radicalValue.Character];
}
else
{
// Store in the database the radicals that have not already been stored in the
// "already added" dictionary.
RadicalEntity radical = new RadicalEntity() { Character = radicalValue.Character };
radical.ID = radicalInsert.Insert(radical);
RadicalCount++;
// Set the radical of the RadicalValue and add an entry to the "already added" dictionary.
radicalValue.Radical = radical;
addedRadicals.Add(radicalValue.Character, radical);
// Log
_log.InfoFormat("Added radical {0} ({1})", radical.Character, radical.ID);
}
}
}
}
}
public override void SaveTicks(List <Ticks> ticks, ConfigSettings settings, string tableName)
{
SQLiteBulkInsert sbi = DataAccessFactory.GetBulkDatabase(settings, tableName);
sbi.ClearTable(tableName);
sbi.AddParameter("symbol", DbType.String);
sbi.AddParameter("time", DbType.DateTime);
sbi.AddParameter("open", DbType.Decimal);
sbi.AddParameter("high", DbType.Decimal);
sbi.AddParameter("low", DbType.Decimal);
sbi.AddParameter("close", DbType.Decimal);
sbi.AddParameter("volume", DbType.Int32);
foreach (Ticks tt in ticks)
{
foreach (var t in tt.TickGroup)
{
sbi.Insert(new object[] { tt.Symbol, t.Date, t.Open, t.High, t.Low, t.Close, t.Volume });
}
}
sbi.Flush();
}
/// <summary>
/// Reads kanji and stores them in the database.
/// </summary>
public override void Execute()
{
List <KanjiRadicalJoinEntity> kanjiRadicalList = new List <KanjiRadicalJoinEntity>();
List <KanjiMeaning> kanjiMeaningList = new List <KanjiMeaning>();
List <KanjiStrokes> kanjiStrokes = new List <KanjiStrokes>();
using (SQLiteBulkInsert <KanjiEntity> kanjiInsert
= new SQLiteBulkInsert <KanjiEntity>(KanjiMaxCommit))
{
// Parse the file.
foreach (KanjiEntity kanji in ReadKanjiDic2())
{
// For each kanji read:
string addedRadicalsString = string.Empty; // Log
// Try to find the matching composition.
if (_radicalDictionary.ContainsKey(kanji.Character))
{
RadicalValue[] matchingRadicals = _radicalDictionary[kanji.Character];
// If the composition is found:
foreach (RadicalValue radicalValue in matchingRadicals)
{
// Retrieve each radical from the database and add it in the kanji.
kanji.Radicals.Add(radicalValue.Radical);
addedRadicalsString += radicalValue.Character + " "; // Log
}
}
// Search for a matching SVG.
kanjiStrokes.Add(RetrieveSvg(kanji));
// Add the finalized kanji to the database.
kanji.ID = kanjiInsert.Insert(kanji);
// Add the kanji meaning entities.
kanjiMeaningList.AddRange(kanji.Meanings);
// Add the kanji-radical join entities.
foreach (RadicalEntity radical in kanji.Radicals)
{
kanjiRadicalList.Add(new KanjiRadicalJoinEntity()
{
KanjiId = kanji.ID,
RadicalId = radical.ID
});
}
// Increment counter
KanjiCount++;
// Log
_log.InfoFormat("Inserted kanji {0} ({1}) with radicals {2}", kanji.Character, kanji.ID, addedRadicalsString);
}
}
CloseZipArchive();
// Insert the strokes.
using (SQLiteBulkInsert <KanjiStrokes> kanjiStrokesInsert
= new SQLiteBulkInsert <KanjiStrokes>(KanjiMaxCommit))
{
foreach (KanjiStrokes strokes in kanjiStrokes)
{
kanjiStrokesInsert.Insert(strokes);
}
}
// Insert the kanji meaning entities.
KanjiMeaningCount = kanjiMeaningList.Count;
_log.InfoFormat("Inserting {0} kanji meaning entities", KanjiMeaningCount);
using (SQLiteBulkInsert <KanjiMeaning> kanjiMeaningInsert
= new SQLiteBulkInsert <KanjiMeaning>(int.MaxValue))
{
foreach (KanjiMeaning km in kanjiMeaningList)
{
kanjiMeaningInsert.Insert(km);
}
}
// Insert the kanji-radical join entities
KanjiRadicalCount = kanjiRadicalList.Count;
_log.InfoFormat("Inserting {0} kanji-radical join entities", KanjiRadicalCount);
using (SQLiteBulkInsert <KanjiRadicalJoinEntity> kanjiRadicalInsert
= new SQLiteBulkInsert <KanjiRadicalJoinEntity>(int.MaxValue))
{
foreach (KanjiRadicalJoinEntity kr in kanjiRadicalList)
{
kanjiRadicalInsert.Insert(kr);
}
}
}
/// <summary>
/// Writes the given list of vocab to the database.
/// </summary>
/// <param name="vocabList">Vocab to write to the database.</param>
private void InsertData(List <VocabEntity> vocabList)
{
if (vocabList.Any())
{
_log.InfoFormat("Inserting the entities for the {0} last vocab", vocabList.Count);
// Insert vocab itself.
using (SQLiteBulkInsert <VocabEntity> vocabInsert
= new SQLiteBulkInsert <VocabEntity>(int.MaxValue))
{
foreach (VocabEntity vocab in vocabList)
{
vocab.ID = vocabInsert.Insert(vocab);
}
}
_log.InfoFormat("Inserted {0} vocab entities", vocabList.Count);
VocabCount += vocabList.Count;
// Insert meanings.
VocabMeaning[] newMeanings = vocabList.SelectMany(v => v.Meanings)
.Distinct()
.Where(vm => vm.ID <= 0)
.ToArray();
int vocabMeaningCount = 0;
using (SQLiteBulkInsert <VocabMeaning> vocabMeaningInsert
= new SQLiteBulkInsert <VocabMeaning>(int.MaxValue))
{
foreach (VocabMeaning vocabMeaning in newMeanings)
{
vocabMeaning.ID = vocabMeaningInsert.Insert(vocabMeaning);
vocabMeaningCount++;
}
}
_log.InfoFormat("Inserted {0} vocab meaning entities", vocabMeaningCount);
VocabMeaningCount += vocabMeaningCount;
// Insert kanji-vocab join entities.
int kanjiVocabCount = 0;
using (SQLiteBulkInsert <KanjiVocabJoinEntity> kanjiVocabInsert
= new SQLiteBulkInsert <KanjiVocabJoinEntity>(int.MaxValue))
{
foreach (VocabEntity vocab in vocabList)
{
foreach (KanjiEntity kanji in vocab.Kanji)
{
kanjiVocabInsert.Insert(new KanjiVocabJoinEntity()
{
KanjiId = kanji.ID,
VocabId = vocab.ID
});
kanjiVocabCount++;
}
}
}
_log.InfoFormat("Inserted {0} kanji-vocab join entities", kanjiVocabCount);
KanjiVocabCount += kanjiVocabCount;
// Insert Vocab-VocabCategory join entities.
int vocabVocabCategoryCount = 0;
using (SQLiteBulkInsert <VocabCategoryVocabJoinEntity> bulk
= new SQLiteBulkInsert <VocabCategoryVocabJoinEntity>(int.MaxValue))
{
foreach (VocabEntity vocab in vocabList)
{
foreach (VocabCategory category in vocab.Categories.Distinct())
{
bulk.Insert(new VocabCategoryVocabJoinEntity()
{
CategoryId = category.ID,
VocabId = vocab.ID
});
vocabVocabCategoryCount++;
}
}
}
_log.InfoFormat("Inserted {0} Vocab-VocabCategory join entities", vocabVocabCategoryCount);
VocabVocabCategoryCount += vocabVocabCategoryCount;
// Insert Vocab-VocabMeaning join entities.
int vocabVocabMeaningCount = 0;
using (SQLiteBulkInsert <VocabVocabMeaningJoinEntity> bulk
= new SQLiteBulkInsert <VocabVocabMeaningJoinEntity>(int.MaxValue))
{
foreach (VocabEntity vocab in vocabList)
{
foreach (VocabMeaning meaning in vocab.Meanings)
{
bulk.Insert(new VocabVocabMeaningJoinEntity()
{
MeaningId = meaning.ID,
VocabId = vocab.ID
});
vocabVocabMeaningCount++;
}
}
}
_log.InfoFormat("Inserted {0} Vocab-VocabMeaning join entities", vocabVocabMeaningCount);
VocabVocabMeaningCount += vocabVocabMeaningCount;
// Insert VocabMeaning-VocabCategory join entities.
int vocabMeaningVocabCategoryCount = 0;
using (SQLiteBulkInsert <VocabMeaningVocabCategoryJoinEntity> bulk
= new SQLiteBulkInsert <VocabMeaningVocabCategoryJoinEntity>(int.MaxValue))
{
foreach (VocabMeaning meaning in newMeanings)
{
foreach (VocabCategory category in meaning.Categories)
{
bulk.Insert(new VocabMeaningVocabCategoryJoinEntity()
{
MeaningId = meaning.ID,
CategoryId = category.ID
});
vocabMeaningVocabCategoryCount++;
}
}
}
_log.InfoFormat("Inserted {0} VocabMeaning-VocabCategory join entities", vocabMeaningVocabCategoryCount);
VocabMeaningVocabCategoryCount += vocabMeaningVocabCategoryCount;
}
}
/// <summary>
/// Reads kanji and stores them in the database.
/// </summary>
public override void Execute()
{
List<KanjiRadicalJoinEntity> kanjiRadicalList = new List<KanjiRadicalJoinEntity>();
List<KanjiMeaning> kanjiMeaningList = new List<KanjiMeaning>();
List<KanjiStrokes> kanjiStrokes = new List<KanjiStrokes>();
using (SQLiteBulkInsert<KanjiEntity> kanjiInsert
= new SQLiteBulkInsert<KanjiEntity>(KanjiMaxCommit))
{
// Parse the file.
foreach (KanjiEntity kanji in ReadKanjiDic2())
{
// For each kanji read:
string addedRadicalsString = string.Empty; // Log
// Try to find the matching composition.
if (_radicalDictionary.ContainsKey(kanji.Character))
{
RadicalValue[] matchingRadicals = _radicalDictionary[kanji.Character];
// If the composition is found:
foreach (RadicalValue radicalValue in matchingRadicals)
{
// Retrieve each radical from the database and add it in the kanji.
kanji.Radicals.Add(radicalValue.Radical);
addedRadicalsString += radicalValue.Character + " "; // Log
}
}
// Search for a matching SVG.
kanjiStrokes.Add(RetrieveSvg(kanji));
// Add the finalized kanji to the database.
kanji.ID = kanjiInsert.Insert(kanji);
// Add the kanji meaning entities.
kanjiMeaningList.AddRange(kanji.Meanings);
// Add the kanji-radical join entities.
foreach (RadicalEntity radical in kanji.Radicals)
{
kanjiRadicalList.Add(new KanjiRadicalJoinEntity()
{
KanjiId = kanji.ID,
RadicalId = radical.ID
});
}
// Increment counter
KanjiCount++;
// Log
_log.InfoFormat("Inserted kanji {0} ({1}) with radicals {2}", kanji.Character, kanji.ID, addedRadicalsString);
}
}
CloseZipArchive();
// Insert the strokes.
using (SQLiteBulkInsert<KanjiStrokes> kanjiStrokesInsert
= new SQLiteBulkInsert<KanjiStrokes>(KanjiMaxCommit))
{
foreach (KanjiStrokes strokes in kanjiStrokes)
{
kanjiStrokesInsert.Insert(strokes);
}
}
// Insert the kanji meaning entities.
KanjiMeaningCount = kanjiMeaningList.Count;
_log.InfoFormat("Inserting {0} kanji meaning entities", KanjiMeaningCount);
using (SQLiteBulkInsert<KanjiMeaning> kanjiMeaningInsert
= new SQLiteBulkInsert<KanjiMeaning>(int.MaxValue))
{
foreach (KanjiMeaning km in kanjiMeaningList)
{
kanjiMeaningInsert.Insert(km);
}
}
// Insert the kanji-radical join entities
KanjiRadicalCount = kanjiRadicalList.Count;
_log.InfoFormat("Inserting {0} kanji-radical join entities", KanjiRadicalCount);
using (SQLiteBulkInsert<KanjiRadicalJoinEntity> kanjiRadicalInsert
= new SQLiteBulkInsert<KanjiRadicalJoinEntity>(int.MaxValue))
{
foreach (KanjiRadicalJoinEntity kr in kanjiRadicalList)
{
kanjiRadicalInsert.Insert(kr);
}
}
}
/// <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);
}
}
public void TableNameTest()
{
SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
Assert.AreEqual(m_testTableName, target.TableName);
}
/// <summary>
/// Reads the JMdict file.
/// </summary>
private IEnumerable<VocabEntity> ReadJmDict()
{
// Load the file.
XDocument xdoc = XDocument.Load(PathHelper.JmDictPath);
// Load vocab categories.
_log.Info("Loading vocab categories");
using (SQLiteBulkInsert<VocabCategory> categoryInsert
= new SQLiteBulkInsert<VocabCategory>(int.MaxValue))
{
foreach (VocabCategory category in LoadVocabCategories(xdoc))
{
// Store vocab categories in the database.
category.ID = categoryInsert.Insert(category);
VocabCategoryCount++;
// Add them to the dictionary too.
_categoryDictionary.Add(category.Label, category);
}
}
_log.InfoFormat("Loaded {0} vocab categories", VocabCategoryCount);
// Load and return vocab items.
_log.Info("Loading vocab");
foreach (VocabEntity vocab in LoadVocabItems(xdoc)) { yield return vocab; }
}
/// <summary>
/// Writes the given list of vocab to the database.
/// </summary>
/// <param name="vocabList">Vocab to write to the database.</param>
private void InsertData(List<VocabEntity> vocabList)
{
if (vocabList.Any())
{
_log.InfoFormat("Inserting the entities for the {0} last vocab", vocabList.Count);
// Insert vocab itself.
using (SQLiteBulkInsert<VocabEntity> vocabInsert
= new SQLiteBulkInsert<VocabEntity>(int.MaxValue))
{
foreach (VocabEntity vocab in vocabList)
{
vocab.ID = vocabInsert.Insert(vocab);
}
}
_log.InfoFormat("Inserted {0} vocab entities", vocabList.Count);
VocabCount += vocabList.Count;
// Insert meanings.
VocabMeaning[] newMeanings = vocabList.SelectMany(v => v.Meanings)
.Distinct()
.Where(vm => vm.ID <= 0)
.ToArray();
int vocabMeaningCount = 0;
using (SQLiteBulkInsert<VocabMeaning> vocabMeaningInsert
= new SQLiteBulkInsert<VocabMeaning>(int.MaxValue))
{
foreach (VocabMeaning vocabMeaning in newMeanings)
{
vocabMeaning.ID = vocabMeaningInsert.Insert(vocabMeaning);
vocabMeaningCount++;
}
}
_log.InfoFormat("Inserted {0} vocab meaning entities", vocabMeaningCount);
VocabMeaningCount += vocabMeaningCount;
// Insert kanji-vocab join entities.
int kanjiVocabCount = 0;
using (SQLiteBulkInsert<KanjiVocabJoinEntity> kanjiVocabInsert
= new SQLiteBulkInsert<KanjiVocabJoinEntity>(int.MaxValue))
{
foreach (VocabEntity vocab in vocabList)
{
foreach (KanjiEntity kanji in vocab.Kanji)
{
kanjiVocabInsert.Insert(new KanjiVocabJoinEntity()
{
KanjiId = kanji.ID,
VocabId = vocab.ID
});
kanjiVocabCount++;
}
}
}
_log.InfoFormat("Inserted {0} kanji-vocab join entities", kanjiVocabCount);
KanjiVocabCount += kanjiVocabCount;
// Insert Vocab-VocabCategory join entities.
int vocabVocabCategoryCount = 0;
using (SQLiteBulkInsert<VocabCategoryVocabJoinEntity> bulk
= new SQLiteBulkInsert<VocabCategoryVocabJoinEntity>(int.MaxValue))
{
foreach (VocabEntity vocab in vocabList)
{
foreach (VocabCategory category in vocab.Categories.Distinct())
{
bulk.Insert(new VocabCategoryVocabJoinEntity()
{
CategoryId = category.ID,
VocabId = vocab.ID
});
vocabVocabCategoryCount++;
}
}
}
_log.InfoFormat("Inserted {0} Vocab-VocabCategory join entities", vocabVocabCategoryCount);
VocabVocabCategoryCount += vocabVocabCategoryCount;
// Insert Vocab-VocabMeaning join entities.
int vocabVocabMeaningCount = 0;
using (SQLiteBulkInsert<VocabVocabMeaningJoinEntity> bulk
= new SQLiteBulkInsert<VocabVocabMeaningJoinEntity>(int.MaxValue))
{
foreach (VocabEntity vocab in vocabList)
{
foreach (VocabMeaning meaning in vocab.Meanings)
{
bulk.Insert(new VocabVocabMeaningJoinEntity()
{
MeaningId = meaning.ID,
VocabId = vocab.ID
});
vocabVocabMeaningCount++;
}
}
}
_log.InfoFormat("Inserted {0} Vocab-VocabMeaning join entities", vocabVocabMeaningCount);
VocabVocabMeaningCount += vocabVocabMeaningCount;
// Insert VocabMeaning-VocabCategory join entities.
int vocabMeaningVocabCategoryCount = 0;
using (SQLiteBulkInsert<VocabMeaningVocabCategoryJoinEntity> bulk
= new SQLiteBulkInsert<VocabMeaningVocabCategoryJoinEntity>(int.MaxValue))
{
foreach (VocabMeaning meaning in newMeanings)
{
foreach (VocabCategory category in meaning.Categories)
{
bulk.Insert(new VocabMeaningVocabCategoryJoinEntity()
{
MeaningId = meaning.ID,
CategoryId = category.ID
});
vocabMeaningVocabCategoryCount++;
}
}
}
_log.InfoFormat("Inserted {0} VocabMeaning-VocabCategory join entities", vocabMeaningVocabCategoryCount);
VocabMeaningVocabCategoryCount += vocabMeaningVocabCategoryCount;
}
}
