gistfile1.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;
using System.Threading;
using System.Threading.Tasks;

namespace Frazer.CSV
{
    /// <summary>
    /// Helper class used to populate the progress bar.
    /// </summary>
    public class DataTableProgressEventArgs : EventArgs
    {

        public int CurrentRow { get; set; }

        public int TotalRows { get; set; }
    }


    public class Database
    {
        public event EventHandler<DataTableProgressEventArgs> UpdateDataTableProgress;

        DataTableProgressEventArgs dataTableProgressBar = new DataTableProgressEventArgs();

        CancellationToken cancelToken;


        /// <summary>
        /// Uploads the populated data tables into sql server via SQLBULKCOPY. Allows the user to track the progress based on the batch sizing
        /// and datatable sizes they selected. Support cancellation via a cancellation token.
        /// </summary>
        /// <param name="fullDataTable">Data table containing the data.</param>
        /// <param name="dataTableName">Name of data table in database toupload the data to.</param>
        /// <param name="numberOfColumns">Number of columns in the file/database table.</param>
        /// <param name="batchSize">Number of rows to upload per batch.</param>
        /// <param name="initial">initial row.</param>
        /// <param name="final">final row in this data table</param>
        /// <param name="errorLogFilePath">Error log file path, used to log any errors.</param>
        /// <param name="cancelToken">Cancellation token.</param>
        public void UploadDataTable(DataTable fullDataTable, string dataTableName, int numberOfColumns, int batchSize, int initial, int final, string errorLogFilePath, CancellationToken cancelToken)
        {
            this.cancelToken = cancelToken;
            dataTableProgressBar.TotalRows = (final-initial) + 1;
            dataTableProgressBar.CurrentRow = initial;

            decimal initialValue = (decimal)initial;
            decimal finalValue = (decimal)final;

            decimal notifyIncrement = Math.Round(((finalValue - initialValue) + 1) / 100);

            if (notifyIncrement == 0)
            {
                notifyIncrement = 1;
            }
            
            string status = null;
            bool alreadyCaught = false;

            //Read connection string from config file.
            System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            string connectionString = config.ConnectionStrings.ConnectionStrings["ConnectionString"].ConnectionString;

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    {        
                        try
                        {
                            using (SqlBulkCopy copy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity |SqlBulkCopyOptions.UseInternalTransaction))
                            {
                                //Column mapping for the required columns.
                                for (int count = 0; count < numberOfColumns; count++)
                                {
                                    copy.ColumnMappings.Add(count, count);
                                }

                                //SQLBulkCopy parameters.
                                copy.DestinationTableName = dataTableName;
                                copy.BatchSize = batchSize;

                                copy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                                copy.NotifyAfter = (int)notifyIncrement;

                                copy.WriteToServer(fullDataTable);
                            }
                        }
                        //Error(s) occured while trying to commit the transaction.
                        catch (InvalidOperationException transactionEx)
                        {
                            status = "The current transaction has been rolled back due to an error." + transactionEx.Message;
                            createLog(errorLogFilePath, status);
                            alreadyCaught = true;
                            throw;
                        }
                    }

                }
            }

            //SQL server returns a warning(s) or error(s).
            catch (SqlException SQLEx)
            {
                status = "The following connection string failed:" + Environment.NewLine + connectionString;
                foreach (SqlError error in SQLEx.Errors)
                {
                    status += error.Message + Environment.NewLine;
                }
                createLog(errorLogFilePath, status);
                throw;
            }

            //Catch general error(s).
            catch (Exception Ex)
            {
                //If TransactionException is thrown, a 2nd MessageBox will not be displayed.
                if (alreadyCaught != true)
                {
                    status = "A general error has been encountered:" + Environment.NewLine + Ex.Message;
                    createLog(errorLogFilePath, status);
                    throw;
                }
            }
        }


        /// <summary>
        /// SQLBULKCOPY event handler, used to assign the current row to the helper class and raise the UpdateDataTableProgress event.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            cancelToken.ThrowIfCancellationRequested();
            dataTableProgressBar.CurrentRow = (int)e.RowsCopied;
            OnUpdateDataTableProgress(this, dataTableProgressBar);
            
        }


        /// <summary>
        /// Raises the event used to update the progress bar via the GUI thread.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected virtual void OnUpdateDataTableProgress(object sender, DataTableProgressEventArgs e)
        {
            EventHandler<DataTableProgressEventArgs> TempHandler = UpdateDataTableProgress;

            //Avoid possible race condition.
            if (TempHandler != null)
            {
                TempHandler(this, e);
            }
        }


        /// <summary>
        /// Appends any error messages to the error log file.
        /// </summary>
        /// <param name="errorLogFilePath"></param>
        /// <param name="errorMessage"></param>
        private void createLog(string errorLogFilePath, string errorMessage)
        {

            using (StreamWriter fileWriter = new StreamWriter(errorLogFilePath, true))
            {
                StringBuilder errorLogBuilder = new StringBuilder();
                errorLogBuilder.Append(errorMessage);
                fileWriter.Write(errorLogBuilder.ToString());
            }
        }
    }
}