Il Blog di Nicolò Carandini

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

        //}

    }
}