CSV file synchronized with DataTable changes...

[vasagle-gleblu]

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);

[vasagle-gleblu]

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;
			}
		}
	}
}

[vasagle-gleblu]

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;
                    }
                }
            }
        }