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

i have function creates table , receives csv file. need id column in auto increments used later use. therefore ran below query id field. before wasn't working because csv file had no id column when time came sent database there error. next idea add blank id column no values csv file , attempt query again. still having issue. error in c# code is: "received invalid column length bcp client colid 1." guessing id column. there way have id column inserted , auto increment @ 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();      } 

i suppose have table in sql server created 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 bulk insert values table can use such code:

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);  (int = 1; < 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);