CSV file synchronized with DataTable changes... #2124
-
Hello, I am trying to get a CSV file synchronized with a DataTable in a small C# project. I am using CSVHelper to manipulate the CSV file. The project has event handlers on the DataTable to initiate any updates to the CSV file. However, after about 20 - 30 iterations of updating the file becomes corrupted and the function coughs-up a Null Reference exception. I am not sure how to improve upon my implementation. Your feedback will be most appreciated. static readonly CultureInfo culture = CultureInfo.CurrentCulture;
public static DataTable DataTableFromCSV(FileInfo oFile, bool blnTableReadOnly = false)
{
// Create backup
FileInfo destFile = new FileInfo(Path.ChangeExtension(oFile.FullName, ".bak"));
oFile.CopyTo(destFile.FullName, true);
// Load data from file
DataTable dt = new DataTable();
using (StreamReader reader = new StreamReader(oFile.FullName))
{
using (CsvReader csv = new CsvReader(reader, culture))
{
// Do any configuration to 'CsvReader' before creating CsvDataReader.
using (CsvDataReader dr = new CsvDataReader(csv))
{
dt.Load(dr);
dt.TableName = Path.GetFileNameWithoutExtension(oFile.Name);
}
}
}
// DataTable readonly or not.
if (!blnTableReadOnly)
{
foreach (DataColumn col in dt.Columns)
{
col.ReadOnly = blnTableReadOnly;
}
}
return dt;
}
public static void UpdateCSV_MapEvents(DataTable dt, FileInfo oFile)
{
dt.RowChanged += (sender, e) => UpdateCSV_RowChanged(sender, e, oFile);
dt.RowDeleted += (sender, e) => UpdateCSV_RowDeleted(sender, e, oFile);
}
public static void UpdateCSV_RowChanged(object sender, DataRowChangeEventArgs e, FileInfo oFile)
{
if (e.Action == DataRowAction.Change)
{
DataTable dt = (DataTable)sender;
long iSeekAhead = 0;
int iRowIndex = dt.Rows.IndexOf(e.Row);
// Open file
using (FileStream fsFile = new FileStream(oFile.FullName, FileMode.Open, FileAccess.ReadWrite, FileShare.None))
using (StreamReader sr = new StreamReader(fsFile))
using (StreamWriter sw = new StreamWriter(fsFile))
using (CsvWriter csvWriter = new CsvWriter(sw, culture))
{
// Calculate how far to skip ahead to modified row
int lineNumber = (csvWriter.Configuration.HasHeaderRecord) ? (iRowIndex + 1) : iRowIndex;
for (int k = 0; k < lineNumber; k++)
{
iSeekAhead += sr.ReadLine().Length + NewLine.Length;
}
fsFile.Seek(iSeekAhead, SeekOrigin.Begin);
// Build updated row
for (int i = 0; i < dt.Columns.Count; i++)
{
// Write row values
csvWriter.WriteField(e.Row[i]);
}
}
}
else if (e.Row.RowState == DataRowState.Added)
{
DataTable dt = (DataTable)sender;
long iSeekAhead = 0;
int iRowIndex = dt.Rows.IndexOf(e.Row);
// Open file
using (FileStream fsFile = new FileStream(oFile.FullName, FileMode.Open, FileAccess.ReadWrite, FileShare.None))
using (StreamReader sr = new StreamReader(fsFile))
using (StreamWriter sw = new StreamWriter(fsFile))
using (CsvWriter csvWriter = new CsvWriter(sw, culture))
{
// Calculate how far to skip ahead to add new row
int lineNumber = (csvWriter.Configuration.HasHeaderRecord) ? (iRowIndex + 1) : iRowIndex;
for (int k = 0; k < lineNumber; k++)
{
iSeekAhead += sr.ReadLine().Length + NewLine.Length;
}
fsFile.Seek(iSeekAhead, SeekOrigin.Begin);
// Build up new row
for (int i = 0; i < dt.Columns.Count; i++)
{
// Write row values
csvWriter.WriteField(e.Row[i]);
}
}
}
}
public static void UpdateCSV_RowDeleted(object sender, DataRowChangeEventArgs e, FileInfo oFile)
{
if (e.Action == DataRowAction.Delete)
{
DataTable dt = (DataTable)sender;
DataRow row;
long iSeekAhead = 0;
int iRowIndex = dt.Rows.IndexOf(e.Row);
// Open file
using (FileStream fsFile = new FileStream(oFile.FullName, FileMode.Open, FileAccess.ReadWrite, FileShare.None))
using (StreamReader sr = new StreamReader(fsFile))
using (StreamWriter sw = new StreamWriter(fsFile))
using (CsvWriter csvWriter = new CsvWriter(sw, culture))
{
// Calculate how far to skip ahead to deleted row
int lineNumber = (csvWriter.Configuration.HasHeaderRecord) ? (iRowIndex + 1) : iRowIndex;
for (int k = 0; k < lineNumber; k++)
{
iSeekAhead += sr.ReadLine().Length + NewLine.Length;
}
fsFile.Seek(iSeekAhead, SeekOrigin.Begin);
// Overwrite deleted row and write out remaining rows
for (int j = lineNumber; j < dt.Rows.Count; j++)
{
row = dt.Rows[j];
for (int i = 0; i < dt.Columns.Count; i++)
{
csvWriter.WriteField(row[i]);
}
csvWriter.NextRecord();
}
}
}
}
// Implementation:
var file = new FileInfo(...);
var dt = DataTableFromCSV(file);
UpdateCSV_MapEvents(dt, file); |
Beta Was this translation helpful? Give feedback.
Replies: 2 comments
-
Well, to answer my own question I worked out what UpdateCSV_RowChanged() needs to be. This new version seems to work better. If there is a better way to do this please feel free to share... public static void UpdateCSV_RowChanged(object sender, DataRowChangeEventArgs e, FileInfo oFile)
{
if ((e.Action == DataRowAction.Change) || (e.Row.RowState == DataRowState.Added))
{
DataTable dt = (DataTable)sender;
int iRowIndex = dt.Rows.IndexOf(e.Row);
StringBuilder sb = new StringBuilder();
// Open file
using (FileStream fsFile = new FileStream(oFile.FullName, FileMode.Open, FileAccess.ReadWrite, FileShare.None))
using (StreamReader sr = new StreamReader(fsFile))
using (CsvDataReader csvDataReader = new CsvDataReader(new CsvReader(sr, culture)))
using (StreamWriter sw = new StreamWriter(fsFile))
using (CsvWriter csvWriter = new CsvWriter(sw, culture))
{
long iSeekAhead = 0;
int lineNumber = (csvWriter.Configuration.HasHeaderRecord) ? (iRowIndex + 1) : iRowIndex;
try
{
// Set current position to the beginning
fsFile.Position = 0;
// Calculate how far to skip ahead to modified row
for (int k = 0; k < lineNumber; k++)
{
iSeekAhead += sr.ReadLine().Length + NewLine.Length;
}
// Build updated row
for (int i = 0; i < dt.Columns.Count; i++)
{
// Row values
if (i < (dt.Columns.Count - 1))
sb.Append(e.Row[i].ToString() + csvWriter.Configuration.Delimiter);
else
sb.Append(e.Row[i].ToString());
}
fsFile.Seek(iSeekAhead, SeekOrigin.Begin);
sw.WriteLine(sb.ToString());
}
catch
{
return;
}
}
}
} |
Beta Was this translation helpful? Give feedback.
-
There were a few cases where some files were still being corrupted. I have updated the StreamReader with an explicit encoding declaration. static readonly Encoding enc = Encoding.Default;
static readonly CultureInfo culture = CultureInfo.CurrentCulture;
public static void UpdateCSV_RowChanged(object sender, DataRowChangeEventArgs e, FileInfo oFile)
{
if ((e.Action == DataRowAction.Change) || (e.Row.RowState == DataRowState.Added))
{
DataTable dt = (DataTable)sender;
int iRowIndex = dt.Rows.IndexOf(e.Row);
StringBuilder sb = new StringBuilder();
CsvConfiguration config = new CsvConfiguration(culture)
{
Delimiter = ",",
Encoding = enc,
Mode = CsvMode.RFC4180
};
// Open file
using (FileStream fsFile = new FileStream(oFile.FullName, FileMode.Open, FileAccess.ReadWrite, FileShare.None))
using (StreamReader sr = new StreamReader(fsFile, enc))
using (CsvDataReader csvDataReader = new CsvDataReader(new CsvReader(sr, config)))
using (StreamWriter sw = new StreamWriter(fsFile))
using (CsvWriter csvWriter = new CsvWriter(sw, config))
{
long iSeekAhead = 0;
int lineNumber = (csvWriter.Configuration.HasHeaderRecord) ? (iRowIndex + 1) : iRowIndex;
try
{
// Set current position to the beginning
fsFile.Position = 0;
// Calculate how far to skip ahead to modified row
for (int k = 0; k < lineNumber; k++)
{
iSeekAhead += sr.ReadLine().Length + NewLine.Length;
}
// Build updated row
for (int i = 0; i < dt.Columns.Count; i++)
{
// Row values
if (i < (dt.Columns.Count - 1))
sb.Append(e.Row[i].ToString() + csvWriter.Configuration.Delimiter);
else
sb.Append(e.Row[i].ToString());
}
fsFile.Seek(iSeekAhead, SeekOrigin.Begin);
sw.WriteLine(sb.ToString());
}
catch
{
// Abort the update.
return;
}
}
}
} |
Beta Was this translation helpful? Give feedback.
There were a few cases where some files were still being corrupted. I have updated the StreamReader with an explicit encoding declaration.