All,
I am still new to SQLite and C#. I am wondering if I have
the correct order of 'using' statements in the code below.
In particular, I am wondering if 'using (transaction...)'
should come before 'using (SQLCommand...)'
Any other comments are appreciated.
Thanks,
-Bill
========================================
using System;
using System.IO;
using System.Data.SQLite;
using System.Diagnostics;
namespace Spurs
{
class Program
{
static void Main(string[] args)
{
/*
* Spur database has the following structure:
*
* CREATE TABLE [spurs] (
* [FundamentalFreq] FLOAT,
* [SpurFreq] FLOAT,
* [SpurdBm] FLOAT);
*
*
* .CSV file is 12.3 million entries like this:
* 1075.100000,2150.449990,-49.192
* 1075.100000,2150.449990,-49.278
* 1886.900000,1887.197613,-54.701
*
* */
// Timer stuff
Stopwatch stopWatch = new Stopwatch();
TimeSpan insertTime, commitTime;
const string timePattern =
"{0:00}:{1:00}:{2:00}.{3:00}";
string elapsedTime;
// File names
string fileName = "raw_data.csv"; // Input
file name.
string dbFileName = "raw_data.db"; // Database
file (output file) name.
// Input file read-loop variables
StreamReader myReader; // A reader
to read the file.
int lineCount = 0; // Total
number of lines in input file.
int reportInterval = 100000; // Update
progress every reportInterval lines.
int intervalCount = 0; // Number of
lines since last update report.
string line = ""; // Holds 1
line from file.
string[] lineCSV = new string[3]; // Holds the
values from each line.
// Database variables
string connectionString; // Database
connection string.
string commandString; // Database
SQL command string.
connectionString = String.Format("Data
Source={0}", dbFileName);
commandString = "INSERT INTO spurs VALUES (?,
?, ?)";
SQLiteConnection connection; // Active
database connection.
SQLiteTransaction transaction; // Active
database transaction.
SQLiteCommand SQLCommand; // Active
database command.
SQLiteParameter @FundamentalFreq; // UUT Test
frequency (MHz).
SQLiteParameter @SpurFreq; // Frequency
of largest spur (MHz).
SQLiteParameter @SpurAmp; // Amplitude
of largest spur (dBm).
@FundamentalFreq = new SQLiteParameter();
@SpurFreq = new SQLiteParameter();
@SpurAmp = new SQLiteParameter();
// Process .csv file.
System.Console.WriteLine("Reading file: " +
fileName);
using (connection = new
SQLiteConnection(connectionString))
{
using (SQLCommand = new
SQLiteCommand(connection))
{
SQLCommand.CommandText = commandString;
SQLCommand.Parameters.Add(@FundamentalFreq);
SQLCommand.Parameters.Add(@SpurFreq);
SQLCommand.Parameters.Add(@SpurAmp);
connection.Open();
using (transaction =
connection.BeginTransaction())
{
using (myReader = new
StreamReader(fileName))
{
stopWatch.Start();
while ((line =
myReader.ReadLine()) != null)
{
// Get values from one line
in the .csv file.
lineCSV = line.Split(new
char[] { ',' });
@FundamentalFreq.Value =
double.Parse(lineCSV[0]);
@SpurFreq.Value =
double.Parse(lineCSV[1]);
@SpurAmp.Value =
double.Parse(lineCSV[2]);
// Insert them into the
database.
SQLCommand.ExecuteNonQuery();
// Print progress every
reportInterval lines.
lineCount++;
intervalCount++;
if (intervalCount ==
reportInterval)
{
System.Console.Write("Processing line " + lineCount + '\r');
intervalCount = 0;
}
}// End while.
stopWatch.Stop();
insertTime = stopWatch.Elapsed;
stopWatch.Reset();
System.Console.Write("Processed
" + lineCount + " lines.\r\n");
}// End myReader.
System.Console.WriteLine("\nCommiting data...");
stopWatch.Start();
transaction.Commit();
stopWatch.Stop();
commitTime = stopWatch.Elapsed;
}// End transaction.
}// End SQLCommand.
}// End connection.
elapsedTime = String.Format(timePattern,
insertTime.Hours, insertTime.Minutes,
insertTime.Seconds,
insertTime.Milliseconds / 10);
System.Console.WriteLine("Insert time: " +
elapsedTime);
elapsedTime = String.Format(timePattern,
commitTime.Hours, commitTime.Minutes,
commitTime.Seconds,
commitTime.Milliseconds / 10);
System.Console.WriteLine("Commit time: " +
elapsedTime);
}
}
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
