Sorgenti del test di importazione file txt in SQL Server

Sorgente delle prove con SQL Server Compact Edition:

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. } } } } } } }

Sorgente delle prove con SQL Server Express:

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())); //} } }

posted @ venerdì 28 dicembre 2007 10:53

Print
Comments have been closed on this topic.
«ottobre»
domlunmarmergiovensab
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456