c# - Importing excel data to SQL -

i using asp.net make application, have holdup, problem user should import excel file (pattern of file same), in excel file there cell bunch of names, looks this

 id         names   1       mick jagger           elton john   2       james hetfield           axl rose 

and on, have 2 or more names per cell ... far, imported excel file dataset

sqlcmd.connection.open();             using (oledbconnection excelconnection = new oledbconnection(excelconnstring))             {                 using (oledbcommand cmd = new oledbcommand("select " + companyid + ", *                                                              [sheet1$]"                  , excelconnection))                 {                     oledbdataadapter dasheet =                      new oledbdataadapter(cmd.commandtext, excelconnection);                      datatable table;                     table = new datatable();                     dasheet.fill(table);                     prepareddata.tables.add(table);                 }             } 

so after successful import, have list of objects imported data set, awesome, 2 or more names per cell giving me trouble, in object value it's written "mick jagger \n elton john", trying replace , add data set (i tried making new data set didn't work out good)

        int rows = prepareddata.tables[0].rows.count;         int columns = prepareddata.tables[0].columns.count;          (int = 0; < rows; i++)         {             (int s = 0; s < columns; s++)             {                 if (s == 4 || s == 5 || s == 6 || s == 7)                 {                     object valuetoreplace;                     object replaced;                      valuetoreplace = prepareddata.tables[0].rows[i].itemarray[s];                     replaced = valuetoreplace.tostring().replace("\n", ";");                      prepareddata.tables[0].rows[i].itemarray[s] = replaced;                 }             }         } 

point of @ end want import modified data sql table using (which working)

     sqlbulkcopy bulkcopy = new sqlbulkcopy(sqlcmd.connection);      bulkcopy.destinationtablename = "excel_import";         try         {             bulkcopy.writetoserver(fixeddata);         }         catch (exception e)         {          } 

so can me , advice me how modify

prepareddata.tables[0].rows[i].itemarray[s] = replaced; 

when reference itemarray property net framework builds copy of data in current row. if try use set new values set values on copy not on original row data.

this source code of itemarray property reference souce

    public object[] itemarray {         {             int record = getdefaultrecord();             _table.recordmanager.verifyrecord(record, this);             object[] values = new object[_columns.count];             (int = 0; < values.length; i++) {                 datacolumn column = _columns[i];                 verifyvaluefromstorage(column, datarowversion.default, column[record]);                 values[i] = column[record];             }             return values;         } 

as can see, every time reference itemarray property new copy of object array created , filled data. now, if change entry in array not changing internal data maintained datarow class. changes unknown datarow.

however solution simple, use

 valuetoreplace = prepareddata.tables[0].rows[i][s].tostring();  prepareddata.tables[0].rows[i][s] = valuetoreplace.replace("\n", ";");