namespace ConsoleLoadToSQL
{
class TextToSqlCe
{
private Stopwatch stopWatch;
public Stopwatch StopWatch
{
get
{
return stopWatch;
}
}
public String TimeElapsed
{
get
{
if (stopWatch != null)
{
return (new DateTime(stopWatch.ElapsedTicks)).ToString("HH.mm.ss.ffff");
}
else
{
return "-.--.-- (null reference)";
}
}
}
public void LoadDataSimpleCommand()
{
string sourceConnectionString = Properties.Settings.Default.CBDataLogConnectionString;
string destConnectionString = Properties.Settings.Default.WeightsConnectionString;
using (OdbcConnection sourceConnection = new OdbcConnection(sourceConnectionString))
{
sourceConnection.Open();
OdbcCommand commandSourceData = new OdbcCommand("SELECT * FROM CBDataLog.txt;", sourceConnection);
OdbcDataReader textFileDataReader = commandSourceData.ExecuteReader();
if (textFileDataReader.HasRows)
{
// Open the destination connection and insert directly to the SQLCe table
using (SqlCeConnection destinationConnection = new SqlCeConnection(destConnectionString))
{
destinationConnection.Open();
try
{
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = destinationConnection;
stopWatch = Stopwatch.StartNew();
while (textFileDataReader.Read())
{
cmd.CommandText = "INSERT INTO DataLog (LogLine) VALUES ('" +
textFileDataReader.GetValue(0).ToString() + "')";
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
stopWatch.Stop();
textFileDataReader.Close();
// The connection objects are automatically closed at the end
// of the using blocks.
}
}
}
}
}
public void LoadData()
{
string sourceConnectionString = Properties.Settings.Default.CBDataLogConnectionString;
string destConnectionString = Properties.Settings.Default.WeightsConnectionString;
using (OdbcConnection sourceConnection = new OdbcConnection(sourceConnectionString))
{
sourceConnection.Open();
OdbcCommand commandSourceData = new OdbcCommand("SELECT * FROM CBDataLog.txt;", sourceConnection);
OdbcDataReader textFileDataReader = commandSourceData.ExecuteReader();
if (textFileDataReader.HasRows)
{
// Open the destination connection and insert directly to the SQLCe table
using (SqlCeConnection destinationConnection = new SqlCeConnection(destConnectionString))
{
destinationConnection.Open();
try
{
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = destinationConnection;
cmd.CommandText = "INSERT INTO DataLog (LogLine) VALUES (?)";
cmd.Parameters.Add("@logLine", System.Data.SqlDbType.NVarChar);
cmd.Prepare();
stopWatch = Stopwatch.StartNew();
while (textFileDataReader.Read())
{
cmd.Parameters[0].Value = textFileDataReader.GetValue(0).ToString();
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
stopWatch.Stop();
textFileDataReader.Close();
// The connection objects are automatically closed at the end
// of the using blocks.
}
}
}
}
}
public void EmptyData()
{
string destConnectionString = Properties.Settings.Default.WeightsConnectionString;
using (SqlCeConnection destinationConnection = new SqlCeConnection(destConnectionString))
{
destinationConnection.Open();
try
{
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = destinationConnection;
cmd.CommandText = "DELETE DataLog";
cmd.Prepare();
stopWatch = Stopwatch.StartNew(); ;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
stopWatch.Stop();
// The connection objects are automatically closed at the end
// of the using blocks.
}
}
}
public void LoadDataWithTransaction()
{
string sourceConnectionString = Properties.Settings.Default.CBDataLogConnectionString;
string destConnectionString = Properties.Settings.Default.WeightsConnectionString;
using (OdbcConnection sourceConnection = new OdbcConnection(sourceConnectionString))
{
sourceConnection.Open();
OdbcCommand commandSourceData = new OdbcCommand("SELECT * FROM CBDataLog.txt;", sourceConnection);
OdbcDataReader textFileDataReader = commandSourceData.ExecuteReader();
if (textFileDataReader.HasRows)
{
// Open the destination connection and insert directly to the SQLCe table
using (SqlCeConnection destinationConnection = new SqlCeConnection(destConnectionString))
{
destinationConnection.Open();
SqlCeTransaction destTransaction = destinationConnection.BeginTransaction();
try
{
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = destinationConnection;
cmd.Transaction = destTransaction;
cmd.CommandText = "INSERT INTO DataLog (LogLine) VALUES (?)";
cmd.Parameters.Add("@logLine", System.Data.SqlDbType.NVarChar);
cmd.Prepare();
stopWatch = Stopwatch.StartNew();
while (textFileDataReader.Read())
{
cmd.Parameters[0].Value = textFileDataReader.GetValue(0).ToString();
cmd.ExecuteNonQuery();
}
destTransaction.Commit();
}
catch (Exception ex)
{
destTransaction.Rollback();
Console.WriteLine(ex.Message);
}
finally
{
stopWatch.Stop();
textFileDataReader.Close();
// The connection objects are automatically closed at the end
// of the using blocks.
}
}
}
}
}
public void EmptyDataWithTransaction()
{
string destConnectionString = Properties.Settings.Default.WeightsConnectionString;
using (SqlCeConnection destinationConnection = new SqlCeConnection(destConnectionString))
{
destinationConnection.Open();
SqlCeTransaction destTransaction = destinationConnection.BeginTransaction();
try
{
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = destinationConnection;
cmd.Transaction = destTransaction;
cmd.CommandText = "DELETE DataLog";
cmd.Prepare();
stopWatch = Stopwatch.StartNew();
cmd.ExecuteNonQuery();
destTransaction.Commit();
}
catch (Exception ex)
{
destTransaction.Rollback();
Console.WriteLine(ex.Message);
}
finally
{
stopWatch.Stop();
// The connection objects are automatically closed at the end
// of the using blocks.
}
}
}
public void LoadDataWithResultSet()
{
string sourceConnectionString = Properties.Settings.Default.CBDataLogConnectionString;
string destConnectionString = Properties.Settings.Default.WeightsConnectionString;
using (OdbcConnection sourceConnection = new OdbcConnection(sourceConnectionString))
{
sourceConnection.Open();
OdbcCommand commandSourceData = new OdbcCommand("SELECT * FROM CBDataLog.txt;", sourceConnection);
OdbcDataReader textFileDataReader = commandSourceData.ExecuteReader();
if (textFileDataReader.HasRows)
{
// Open the destination connection and insert directly to the SQLCe table
using (SqlCeConnection destinationConnection = new SqlCeConnection(destConnectionString))
{
destinationConnection.Open();
try
{
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = destinationConnection;
cmd.CommandText = "DemoInsert";
cmd.CommandType = System.Data.CommandType.TableDirect;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
SqlCeUpdatableRecord rec = rs.CreateRecord();
cmd.CommandText = "INSERT INTO DataLog (LogLine) VALUES (?)";
cmd.Parameters.Add("@logLine", System.Data.SqlDbType.NVarChar);
cmd.Prepare();
stopWatch = Stopwatch.StartNew();
while (textFileDataReader.Read())
{
rec.SetString(1, textFileDataReader.GetValue(0).ToString());
rs.Insert(rec);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
stopWatch.Stop();
textFileDataReader.Close();
// The connection objects are automatically closed at the end
// of the using blocks.
}
}
}
}
}
}
}
namespace ConsoleLoadToSQL
{
class TextToSqlExpress
{
private DataTable sourceDataTable;
private String sourceConnectionString;
private String destConnectionString;
private Stopwatch stopWatch;
public Stopwatch StopWatch
{
get
{
return stopWatch;
}
}
public String TimeElapsed
{
get
{
if (stopWatch != null)
{
return (new DateTime(stopWatch.ElapsedTicks)).ToString("HH.mm.ss.ffff");
}
else
{
return "-.--.-- (null reference)";
}
}
}
public TextToSqlExpress()
{
sourceDataTable = new DataTable();
sourceDataTable.Columns.Add("LogLine", typeof(String));
sourceConnectionString = Properties.Settings.Default.CBDataLogConnectionString;
destConnectionString = Properties.Settings.Default.WeightsSqlExpressConnectionString;
}
public void LoadDataSimpleCommand()
{
using (OdbcConnection sourceConnection = new OdbcConnection(sourceConnectionString))
{
sourceConnection.Open();
OdbcCommand commandSourceData = new OdbcCommand("SELECT * FROM CBDataLog.txt", sourceConnection);
OdbcDataReader textFileDataReader = commandSourceData.ExecuteReader();
if (textFileDataReader.HasRows)
{
using (SqlConnection destinationConnection = new SqlConnection(destConnectionString))
{
destinationConnection.Open();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = destinationConnection;
stopWatch = Stopwatch.StartNew();
while (textFileDataReader.Read())
{
cmd.CommandText = "INSERT INTO dbo.DataLog (LogLine) VALUES ('" +
textFileDataReader.GetValue(0).ToString() + "')";
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
stopWatch.Stop();
textFileDataReader.Close();
// The connection objects are automatically closed at the end
// of the using blocks.
}
}
}
}
}
public void LoadData()
{
using (OdbcConnection sourceConnection = new OdbcConnection(sourceConnectionString))
{
sourceConnection.Open();
OdbcCommand commandSourceData = new OdbcCommand("SELECT * FROM CBDataLog.txt", sourceConnection);
OdbcDataReader textFileDataReader = commandSourceData.ExecuteReader();
if (textFileDataReader.HasRows)
{
using (SqlConnection destinationConnection = new SqlConnection(destConnectionString))
{
destinationConnection.Open();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = destinationConnection;
cmd.CommandText = "INSERT INTO dbo.DataLog (LogLine) VALUES (@logLine)";
cmd.Parameters.Add("@logLine", System.Data.SqlDbType.NVarChar, 200);
cmd.Prepare();
stopWatch = Stopwatch.StartNew();
while (textFileDataReader.Read())
{
cmd.Parameters[0].Value = textFileDataReader.GetValue(0).ToString();
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
stopWatch.Stop();
textFileDataReader.Close();
// The connection objects are automatically closed at the end
// of the using blocks.
}
}
}
}
}
public void EmptyData()
{
using (SqlConnection destinationConnection = new SqlConnection(destConnectionString))
{
destinationConnection.Open();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = destinationConnection;
cmd.CommandText = "DELETE FROM dbo.DataLog";
cmd.CommandTimeout = 240;
cmd.Prepare();
stopWatch = Stopwatch.StartNew(); ;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
stopWatch.Stop();
// The connection objects are automatically closed at the end
// of the using blocks.
}
}
}
public void LoadDataWithTransaction()
{
using (OdbcConnection sourceConnection = new OdbcConnection(sourceConnectionString))
{
sourceConnection.Open();
OdbcCommand commandSourceData = new OdbcCommand("SELECT * FROM CBDataLog.txt", sourceConnection);
OdbcDataReader textFileDataReader = commandSourceData.ExecuteReader();
if (textFileDataReader.HasRows)
{
using (SqlConnection destinationConnection = new SqlConnection(destConnectionString))
{
destinationConnection.Open();
SqlTransaction destTransaction = destinationConnection.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = destinationConnection;
cmd.Transaction = destTransaction;
cmd.CommandText = "INSERT INTO dbo.DataLog (LogLine) VALUES (@logLine)";
cmd.Parameters.Add("@logLine", System.Data.SqlDbType.NVarChar, 200);
cmd.Prepare();
stopWatch = Stopwatch.StartNew();
while (textFileDataReader.Read())
{
cmd.Parameters[0].Value = textFileDataReader.GetValue(0).ToString();
cmd.ExecuteNonQuery();
}
destTransaction.Commit();
}
catch (Exception ex)
{
destTransaction.Rollback();
Console.WriteLine(ex.Message);
}
finally
{
stopWatch.Stop();
textFileDataReader.Close();
// The connection objects are automatically closed at the end
// of the using blocks.
}
}
}
}
}
public void EmptyDataWithTransaction()
{
using (SqlConnection destinationConnection = new SqlConnection(destConnectionString))
{
destinationConnection.Open();
SqlTransaction destTransaction = destinationConnection.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = destinationConnection;
cmd.Transaction = destTransaction;
cmd.CommandText = "DELETE FROM dbo.DataLog";
cmd.Prepare();
stopWatch = Stopwatch.StartNew();
cmd.ExecuteNonQuery();
destTransaction.Commit();
}
catch (Exception ex)
{
destTransaction.Rollback();
Console.WriteLine(ex.Message);
}
finally
{
stopWatch.Stop();
// The connection objects are automatically closed at the end
// of the using blocks.
}
}
}
public void LoadSourceDataTable()
{
using (OdbcConnection sourceConnection = new OdbcConnection(sourceConnectionString))
{
sourceConnection.Open();
OdbcCommand commandSourceData = new OdbcCommand("SELECT LogLine FROM CBDataLog.txt", sourceConnection);
OdbcDataReader textFileDataReader = commandSourceData.ExecuteReader();
try
{
stopWatch = Stopwatch.StartNew();
sourceDataTable.Load(textFileDataReader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
stopWatch.Stop();
textFileDataReader.Close();
// The connection object is automatically closed at the end
// of the using block.
}
}
}
public void BulkCopy()
{
using (SqlConnection destinationConnection = new SqlConnection(destConnectionString))
{
destinationConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.TableLock, null))
{
try
{
SqlBulkCopyColumnMapping colMapping1 = new SqlBulkCopyColumnMapping("LogLine", "LogLine");
// bulkCopy.BatchSize = 100;
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.ColumnMappings.Add(colMapping1);
bulkCopy.DestinationTableName = "dbo.DataLog";
// bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
// bulkCopy.NotifyAfter = 1000;
stopWatch = Stopwatch.StartNew();
bulkCopy.WriteToServer(sourceDataTable);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
stopWatch.Stop();
// The SqlBulkCopy object is automatically closed at the end
// of the using block.
// The destination connection object is automatically closed at the end
// of the using block.
}
}
}
}
public void BulkCopyFromIDataReader(int batchSize)
{
using (OdbcConnection sourceConnection = new OdbcConnection(sourceConnectionString))
{
sourceConnection.Open();
OdbcCommand commandSourceData = new OdbcCommand("SELECT LogLine FROM CBDataLog.txt", sourceConnection);
OdbcDataReader textFileDataReader = commandSourceData.ExecuteReader();
using (SqlConnection destinationConnection = new SqlConnection(destConnectionString))
{
destinationConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.TableLock, null))
{
try
{
bulkCopy.BatchSize = batchSize;
bulkCopy.BulkCopyTimeout = 600; // 10 min should be enougth even with batch size = 0 ...
SqlBulkCopyColumnMapping colMapping1 = new SqlBulkCopyColumnMapping("LogLine", "LogLine");
bulkCopy.ColumnMappings.Add(colMapping1);
bulkCopy.DestinationTableName = "dbo.DataLog";
// bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
// bulkCopy.NotifyAfter = bulkCopy.BatchSize;
stopWatch = Stopwatch.StartNew();
bulkCopy.WriteToServer(textFileDataReader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
textFileDataReader.Close();
stopWatch.Stop();
// The SqlBulkCopy object is automatically closed at the end
// of the using block.
// The source connection object is automatically closed at the end
// of the using block.
}
}
}
}
}
//void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
//{
// Console.WriteLine();
// Console.WriteLine(String.Format("{0} Rows have been copied.", e.RowsCopied.ToString()));
//}
}
}