Inserting data from CSV in mutiple tables via ASP.NET (C#), SQL Server -

i have csv file 7 columns, user has upload can added in database. found in reading csv , putting info in single table, however, data has spread on 3 tables.

my code inserting data 1 table:

        protected void upload(object sender, eventargs e)     {         //upload , save file         string csvpath = server.mappath("~/temp/") + path.getfilename(fileupload1.postedfile.filename);         fileupload1.saveas(csvpath);          datatable dt = new datatable();         dt.columns.addrange(new datacolumn[7] {          new datacolumn("title", typeof(string)),         new datacolumn("artist", typeof(string)),         new datacolumn("years", typeof(string)),         new datacolumn("position", typeof(string)),         new datacolumn("senddate", typeof(string)),         new datacolumn("sendfrom", typeof(string)),         new datacolumn("sendtill", typeof(string))});           string csvdata = file.readalltext(csvpath);         foreach (string row in csvdata.split('\n'))         {             if (!string.isnullorempty(row))             {                 dt.rows.add();                 int = 0;                 foreach (string cell in row.split(';'))                 {                     dt.rows[dt.rows.count - 1][i] = cell;                     i++;                 }             }         }          string consstring = configurationmanager.connectionstrings["connection"].connectionstring;         using (sqlconnection con = new sqlconnection(consstring))         {             using (sqlbulkcopy sqlbulkcopy = new sqlbulkcopy(con))             {                 //set database table name                 sqlbulkcopy.destinationtablename = "dbo.ingevoerd";                 con.open();                 sqlbulkcopy.writetoserver(dt);                 con.close();             }         }     } 

as can see, takes 7 columns, , puts them in table [dbo].[ingevoerd]

how can split data put column 'title' , 'years' in table called song, 'artist' in table called artiest, , 'position', 'senddate', 'sendfrom' 'sendtill' in table called lijst?

for more information, put down comment.

imho not best way handle upload because content not flat data can bulk upload in breeze; there many entitiest (at least 3) should linked.

i go 'old style' approach of calling insert each row proper parameters.

you looping through whole recordset when reading csv make like:

    protected void upload(object sender, eventargs e)     {         //upload , save file         string csvpath = server.mappath("~/temp/") + path.getfilename(fileupload1.postedfile.filename);         fileupload1.saveas(csvpath);          string consstring = configurationmanager.connectionstrings["connection"].connectionstring;         using (sqlconnection con = new sqlconnection(consstring))         {             con.open();             using (sqltransaction tran = con.begintransaction())             using (sqlcommand cmd = new sqlcommand())             {                 cmd.connection = con;                 cmd.transaction = tran;                 cmd.commandtype = system.data.commandtype.storedprocedure;                 cmd.commandtext = "your_sp_name_here";                 cmd.parameters.add(new sqlparameter("@title",system.data.sqldbtype.nvarchar));                 cmd.parameters.add(new sqlparameter("@artist", system.data.sqldbtype.nvarchar));                 // other parameters follow                 // ...                  string csvdata = file.readalltext(csvpath);                 foreach (string row in csvdata.split('\n'))                 {                     if (!string.isnullorempty(row))                     {                         // every row call command , fill in parameters proper values                         cmd.parameters["@title"].value = row[0];                         cmd.parameters["@artist"].value = row[1];                         // ...                         cmd.executenonquery();                     }                 }                  // when done commit transaction                 tran.commit();             }         }     } 

inside stored procedure handle 'split' of data in relevant tables taking steps required avoid duplicates , maybe linking data among tables:

create procedure your_sp_name_here(@title nvarchar(50), @artist nvarchar(50), @year int) begin  -- add logic & checks here if needed  -- ...  -- ...   -- if ok insert rows  insert songs (title, year) values (@title, @year)  insert artiest (artist) values (@artist) end