c# - Import a CSV file to SQL Server using SqlBulkCopy

Question

I have this function that creates a table and then receives a CSV File. I need an ID column in it that auto increments which would be used for later use. Therefore I ran the below query with the ID field. Before it wasn't working because initially the CSV File had no ID column so when time came for it to be sent to the database there would be an error. So my next idea was to add a blank ID column with no values to the CSV file and then attempt the query again. Still having an issue. The error in my c# code is: "Received an invalid column length from the bcp client for colid 1." Which am guessing is the ID column. Is there a way to have this ID column inserted and auto increment at the same time?

private void button2_Click(object sender, EventArgs e)
    {
        string connectionString = "Data Source=LPMSW09000012JD\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
        string query = "CREATE TABLE [dbo].[" + textBox1.Text + "](" +"ID int IDENTITY (1,1) PRIMARY KEY," + "[Code] [varchar] (13) NOT NULL," +
       "[Description] [varchar] (50) NOT NULL," + "[NDC] [varchar] (50) NULL," +
        "[Supplier Code] [varchar] (38) NULL," + "[UOM] [varchar] (8) NULL," + "[Size] [varchar] (8) NULL,)";


        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            command.Connection.Open();
            command.ExecuteNonQuery();
        }

        SqlConnection con = new SqlConnection("Data Source=LPMSW09000012JD\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True");
        string filepath = textBox2.Text; //"C:\Users\jdavis\Desktop\CRF_105402_New Port Maria Rx.csv";
        StreamReader sr = new StreamReader(filepath);
        string line = sr.ReadLine();
        string[] value = line.Split(',');
        DataTable dt = new DataTable();
        DataRow row;
        foreach (string dc in value)
        {
            dt.Columns.Add(new DataColumn(dc));
        }

        while (!sr.EndOfStream)
        {
            value = sr.ReadLine().Split(',');
            if (value.Length == dt.Columns.Count)
            {
                row = dt.NewRow();
                row.ItemArray = value;
                dt.Rows.Add(row);
            }
        }
        SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
        bc.DestinationTableName = textBox1.Text;
        bc.BatchSize = dt.Rows.Count;
        con.Open();
        bc.WriteToServer(dt);
        bc.Close();
        con.Close();

    }

Answer 1

I suppose you have a table in SQL Server which you created this way:

CREATE TABLE [dbo].[Table1] (
    [Column1]   INT           IDENTITY (1, 1) NOT NULL,
    [Column2]   NVARCHAR (50) NOT NULL
);

file containing such values:

Column1,Column2
1,N1
2,N2
3,N3

So to bulk insert values to the table you can use SqlBulkCopy this way:

var lines = System.IO.File.ReadAllLines(@"d:data.txt");
if (lines.Count() == 0) return;
var columns = lines[0].Split(',');
var table = new DataTable();
foreach (var c in columns)
    table.Columns.Add(c);

for (int i = 1; i < lines.Count() - 1; i++)
    table.Rows.Add(lines[i].Split(','));

var connection = @"your connection string";
var sqlBulk = new SqlBulkCopy(connection);
sqlBulk.DestinationTableName = "Table1";
sqlBulk.WriteToServer(table);