mheonyae February 2016

C# bulk insert to sql, but table is empty

I want to insert bulk data from my data table, but after an execution of the table in my database it's still empty when I test query to see if the data were written.

Below is my code for inserting from data table to sql:

private void btnToSql_Click(object sender, EventArgs e)
    {
        try
        {
            //First create a connection string to destination database
            string connectionString;
            connectionString = @"MY DATA STRING - I REPLACED IT JUST TO POST HERE";

            //Open a connection with destination database;
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                connection.Open();

                //Open bulkcopy connection.
                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
                {
                    //Set destination table name
                    //to table previously created.
                    bulkcopy.DestinationTableName = "dbo.Table_1";

                    try
                    {
                        bulkcopy.WriteToServer(dtExcelRecords);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }

                    connection.Close();
                }
            }
        }
        catch (Exception ex) {
            MessageBox.Show(ex.Message);
        }
        MessageBox.Show("Finished!");
    }

Here is how I create/populate my data table (data table is declared globally):

 public void importExcel(string path, string sheetName)
    {
        var excel = new LinqToExcel.ExcelQueryFactory(path);
        excel.ReadOnly = true;
        var companies = from a in excel.Worksheet(sheetName) select a;
        var columnNames = excel.GetColumnNames(sheetName);


        foreach (var columnName in columnNames)
        {
            dtExcelRe        

Answers


Alexei February 2016

Based on my question and received answer in the comments area the problem seems to be related to exception silently caught in the block:

catch (Exception ex) 
{ 
    Console.WriteLine(ex.Message); 
}

My suggestion is to separate DB access logic (DAL) from your UI, by putting this logic in another class (usually it is put in another assembly) and have a pattern like this one:

Data layer

try
{
    // DB logic fetch
}
catch (Exception exc)
{
    // log somewhere - NLog is one way to easily log to anything you want - console, DB, file - and also store important information such as timestamp, user, stack trace etc.
    // put error in some output variable or property
}

UI layer

  • calls data layer
  • if something fails, print a friendly answer for the user
  • look in the logs for the actual error

This is an important separation of concerns and you can also have a good exception management.

Post Status

Asked in February 2016
Viewed 2,072 times
Voted 6
Answered 1 times

Search




Leave an answer