c# - How to prepare data for a BULK INSERT command -

i trying dynamically write fields of given sql database table file later used source file bulk insert command.

i'm using sqldatareader read each field stores value object. depending on type of object, write value file. code i'm using:

streamwriter writer = new streamwriter(filepath, false, encoding.getencoding("iso-8859-1");  foreach (var fields in rows) {     foreach (object val in fields)     {         if (val != dbnull.value)         {             if (val bool)                 writer.write((bool)val ? 1 : 0);             else if (val byte[])                 writer.write(bitconverter.tostring((byte[])val).replace("-", ""));             else if (val datetime)                 writer.write(string.format("{0} {1}", ((datetime)val).tostring("yyyy-mm-dd"), ((datetime)val).tostring("hh:mm:ss:fff")));             else if (val string)                 writer.write(((string)val).replace("\0", string.empty));             else                 writer.write(val);         }          // field terminator         writer.write('\0');     } } 

later, run bulk insert command follows:

bulk insert tablename 'c:\filename' (    fieldterminator = '\0'    rowterminator = '\0'    keepnulls ); 

the issue have error during bulk insert: "unexpected end of file". doesn't happen every row or every table.

i'd able handle type of column kind of data without errors.

i solved issue. nothing wrong code above. keep question here others use since couldn't find equivalent solution in of google searching.

my particular issue after writing files didn't close filestreams gracefully filestream.close or dispose method. caused corruption ending bytes of file data had not yet been flushed disk.

using lastrow parameter in sql bulk insert command allowed me import in each file row corrupt. , closing filestreams ensure doesn't happen again.