c# - import text file(500 MB) data into SQL Server -

here code using importing text files larger 500 mb database.

i want in batches if format related error occur in text file during execution @ least half of contents uploaded.

if other suggestion uploading such large text file please specify.

private datatable createdatatablefromfileloop() {     string filename = "";      if (fileuploadexcel.hasfile)     {        try        {            filename = path.getfilename(futextloop.filename);            futextloop.saveas(server.mappath("~/calltext") + filename);            //statuslabel.text = "upload status: file uploaded!";        }        catch (exception ex)        {            statuslabel.text = "upload status: file not uploaded. following error occured: " + ex.message;        }     }      datatable dt = new datatable();     datacolumn dc;     datarow dr;      dc = new datacolumn();     dc.datatype = system.type.gettype("system.string");     dc.columnname = "sr no";     dc.unique = false;     dt.columns.add(dc);      dc = new datacolumn();     dc.datatype = system.type.gettype("system.string");     dc.columnname = "mobile";     dc.unique = false;     dt.columns.add(dc);      dc = new datacolumn();     dc.datatype = system.type.gettype("system.string");     dc.columnname = "name";     dc.unique = false;     dt.columns.add(dc);      dc = new datacolumn();     dc.datatype = system.type.gettype("system.string");     dc.columnname = "fath_hus_name";     dc.unique = false;     dt.columns.add(dc);      dc = new datacolumn();     dc.datatype = system.type.gettype("system.string");     dc.columnname = "address";     dc.unique = false;     dt.columns.add(dc);      dc = new datacolumn();     dc.datatype = system.type.gettype("system.string");     dc.columnname = "city";     dc.unique = false;     dt.columns.add(dc);      dc = new datacolumn();     dc.datatype = system.type.gettype("system.string");     dc.columnname = "pin code";     dc.unique = false;     dt.columns.add(dc);      dc = new datacolumn();     dc.datatype = system.type.gettype("system.string");     dc.columnname = "contact number";     dc.unique = false;     dt.columns.add(dc);      dc = new datacolumn();     dc.datatype = system.type.gettype("system.string");     dc.columnname = "activation_date";     dc.unique = false;     dt.columns.add(dc);      dc = new datacolumn();     dc.datatype = system.type.gettype("system.string");     dc.columnname = "subs_type";     dc.unique = false;     dt.columns.add(dc);      dc = new datacolumn();     dc.datatype = system.type.gettype("system.string");     dc.columnname = "status";     dc.unique = false;     dt.columns.add(dc);      dc = new datacolumn();     dc.datatype = system.type.gettype("system.string");     dc.columnname = "subs_name";     dc.unique = false;     dt.columns.add(dc);      streamreader sr = new streamreader(server.mappath("~/calltext") + filename);      sqlconnection conn = new  sqlconnection("server=.;database=temp;trusted_connection=true;");  if (ddlsub.selectedvalue.tostring() == "reliance") {    try    {         string input;         string mob_chk;          while ((input = sr.readline()) != null)         {             string[] s = input.split(new char[] { ',' });              dr = dt.newrow();             sqlcommand comm = new sqlcommand("select mobile call mobile='" + s[1] + "'", conn);             conn.open();             sqldatareader sdr = comm.executereader();              if (sdr.hasrows)             {                 goto end_of_loop;             }             if (!sdr.hasrows)             {                 dr["sr no"] = s[0];                 dr["mobile"] = s[1];                 dr["name"] = s[3];                 dr["fath_hus_name"] = s[4]+s[5]+s[6] + s[7];                 dr["address"] = s[8]+s[9];                 dr["city"] = s[10];                 dr["pin code"] = s[11];                 dr["contact number"] = s[16];                 dr["activation_date"] = s[18];                 dr["subs_type"] = s[15];                 //dr["status"] = s[10];                 dr["subs_name"] = ddlsub.selectedvalue.tostring();             }              dt.rows.add(dr);         end_of_loop:             conn.close();         }          sr.close();          dt.rows[0].delete();    }    catch (exception ex)    {            statuslabel.text = "upload status: file not uploaded. following error occured: " + ex.message;    } }  if (dt.rows.count > 0) {    return dt; } else {    return null; } 

if file processed entirely can process backwards , truncate after each record has been processed, way can process entire file , free disk space of rows process them. , can "safely" read entire file when resuming error.

first you'd have declare filestream keep track of file , pass streamreader

filestram fs = new filestream(server.mappath("~/calltext") + filename, filemode.open); streamreader sr = new streamreader(fs); 

now can read file backwards until \n or \r found , "sr.readline", first need size of file, know size should have after done row.

long oldlen = fs.length; fs.seek(-2,seekorigin.end);//dont begin end, because line-end right there //here goes simple while read file backwards until find '\n' or '\r'.  ... //you should aso check bof fs.seek(1,seekorigin.current); //this case sr.readline() gets confused line-end found //here can input=sr.readline() , have ... ... 

now can truncate old size minus size of row read.

fs.setlength(oldlen - encoding.unicode.getbytecount(input));//replace unicode whatever encoding file has. 

you accumulate sizes of rows read , truncate file once theres error or operation finished