SQLBulkCopy - WriteToServer method not working

Question

I'm trying to export some data from an Excel file to a MDF database. To do that, I'm using Excel Data Reader and SQL Bulk Copy :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Excel;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;

namespace BulkTest
{
    class Program
    {
        static void Main(string[] args)
        {
            FileStream fs = File.Open(@"C:\Users\maarab\Desktop\List_Personnel 2013.xlsx", FileMode.Open, FileAccess.Read);
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs);

            DataTable dt = CreateDataTable();

            string cx = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DataTest.mdf;Integrated Security=True;User Instance=True";
            SqlBulkCopy bcp = new SqlBulkCopy(cx, SqlBulkCopyOptions.KeepIdentity);


            bool first = true;

            while (excelReader.Read())
            {
                if (first)
                {
                    first = false;
                }

                else
                {
                    if (String.IsNullOrWhiteSpace(excelReader.GetString(0)))
                        break;

                    else
                    {
                        string numNat = excelReader.GetString(2);
                        DateTime birthDate = excelReader.GetDateTime(9);
                        DateTime startDate = excelReader.GetDateTime(1);

                        if (!String.IsNullOrWhiteSpace(numNat))
                        {
                            if (numNat.Length == 12)
                            {
                                numNat = numNat.Remove(6, 1);
                            }
                        }

                        if (birthDate.Year < 1753)
                            birthDate = DateTime.Now;

                        if (startDate.Year < 1753)
                            startDate = DateTime.Now;

                        dt.Rows.Add(excelReader.GetString(0), excelReader.GetString(0), numNat, startDate, birthDate);

                    }
                }
            }

            bcp.DestinationTableName = "person";
            bcp.BatchSize = 100;

            bcp.ColumnMappings.Add(0, 1);
            bcp.ColumnMappings.Add(1, 2);
            bcp.ColumnMappings.Add(2, 3);
            bcp.ColumnMappings.Add(3, 4);
            bcp.ColumnMappings.Add(4, 5);

            try
            {
                bcp.WriteToServer(dt, DataRowState.Unchanged);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }


            Console.ReadLine();

        }


        public static DataTable CreateDataTable()
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("FirstName", typeof(string));
            dt.Columns.Add("LastName", typeof(string));
            dt.Columns.Add("NumNat", typeof(string));
            dt.Columns.Add("Birthdate", typeof(DateTime));
            dt.Columns.Add("StartDate", typeof(DateTime));

            return dt;
        }
    }
}

I'm filling a datatable with my Excel file and all goes good. The program is running without problem but unfortunatelly there's no insert into my table. Any idea to solve that?

Are you sure that you are looking at the correct database? Sometimes the connection used by Server Explorer is not the same connection used by your program to work on — Steve, Jul 25 '13 at 12:17
If you just pass "bcp.WriteToServer(dt);" something different happens? — Jhonatas Kleinkauff, Jul 25 '13 at 12:20
Be sure to correctly dispose of your objects to avoid any memory leaks. `FileStream`, `IExcelDataReader` and `SqlBulkCopy` implement `IDisposable` and need disposing explicitly, or wrapping in a `using` statement. — Sam Meadley, Jul 25 '13 at 12:21
@Steve I'm sure about this. However, how can I confirm my connection string (or where should I pick this information)? — Traffy, Jul 25 '13 at 12:24
How are you looking at your table after the program run? Are you using Server Explorer? If yes, check the connection used by Server Explorer window to connect to the database. Sometimes you have two copy of the database, one listed in the project files (and linked by Server Explorer connection) and another copied in the working directory (BIN\DEBUG or BIN depending on project type) The program works on the BIN\DEBUG, the Server Explorer connects to the database file in the project folder — Steve, Jul 25 '13 at 12:36
@Traffy sorry. I mean what if you call WriteToServer method without DataRowState.. — Jhonatas Kleinkauff, Jul 25 '13 at 12:39
@Steve Thanks for you help, it was that... Could you post that so I can accept it? — Traffy, Jul 25 '13 at 12:41

score 2 · Accepted Answer · answered Jul 25 '13 at 12:54

If you look at your table using Server Explorer you should check carefully the connection used in this window. It happens very often that you have your database file listed in the project files and the connection used by Server Explorer points to this file in the project folder.
Of course, when your program runs, the connection used is the one specified in your app.config.
In this scenario you have no error (because no error exists) but you look with the Server Explorer to the wrong database where no action occured and you can't see the updates.
The solution is easy, you need to reconfigure the connection used by the Server Explorer to point to the effective value of the |DataDirectory| substitution string (Usually BIN\DEBUG or BIN depending on project type)

SQLBulkCopy - WriteToServer method not working

1 Answers1