ReadOnlyException DataTable DataRow "Column X is read only."

Question

I've got a short piece of code that originally created an SqlDataAdapter object over and over.

Trying to streamline my calls a little bit, I replaced the SqlDataAdapter with an SqlCommand and moved the SqlConnection outside of the loop.

Now, whenever I try to edit rows of data returned to my DataTable, I get a ReadOnlyException thrown that was not thrown before.

NOTE: I have a custom function that retrieves the employee's full name based on their ID. For simplicity here, I used "John Doe" in my example code below to demonstrate my point.

ExampleQueryOld works with the SqlDataAdapter; ExampleQueryNew fails with the ReadOnlyException whenever I try to write to an element of the DataRow:

• ExampleQueryOld

This works and has no issues:

public static DataTable ExampleQueryOld(string targetItem, string[] sqlQueryStrings) {
  DataTable bigTable = new DataTable();
  for (int i = 0; i < sqlQueryStrings.Length; i++) {
    string sqlText = sqlQueryStrings[i];
    DataTable data = new DataTable(targetItem);
    using (SqlDataAdapter da = new SqlDataAdapter(sqlText, Global.Data.Connection)) {
      try {
        da.Fill(data);
      } catch (Exception err) {
        Global.LogError(_CODEFILE, err);
      }
    }
    int rowCount = data.Rows.Count;
    if (0 < rowCount) {
      int index = data.Columns.IndexOf(GSTR.Employee);
      for (int j = 0; j < rowCount; j++) {
        DataRow row = data.Rows[j];
        row[index] = "John Doe"; // This Version Works
      }
      bigTable.Merge(data);
    }
  }
  return bigTable;
}

• ExampleQueryNew

This example throws the ReadOnlyException:

public static DataTable ExampleQueryNew(string targetItem, string[] sqlQueryStrings) {
  DataTable bigTable = new DataTable();
  using (SqlConnection conn = Global.Data.Connection) {
    for (int i = 0; i < sqlQueryStrings.Length; i++) {
      string sqlText = sqlQueryStrings[i];
      using (SqlCommand cmd = new SqlCommand(sqlText, conn)) {
        DataTable data = new DataTable(targetItem);
        try {
          if (cmd.Connection.State == ConnectionState.Closed) {
            cmd.Connection.Open();
          }
          using (SqlDataReader reader = cmd.ExecuteReader()) {
            data.Load(reader);
          }
        } catch (Exception err) {
          Global.LogError(_CODEFILE, err);
        } finally {
          if ((cmd.Connection.State & ConnectionState.Open) != 0) {
            cmd.Connection.Close();
          }
        }
        int rowCount = data.Rows.Count;
        if (0 < rowCount) {
          int index = data.Columns.IndexOf(GSTR.Employee);
          for (int j = 0; j < rowCount; j++) {
            DataRow row = data.Rows[j];
            try {
              // ReadOnlyException thrown below: "Column 'index'  is read only."
              row[index] = "John Doe";
            } catch (ReadOnlyException roErr) {
              Console.WriteLine(roErr.Message);
            }
          }
          bigTable.Merge(data);
        }
      }
    }
  }
  return bigTable;
}

Why can I write to the DataRow element in one case, but not in the other?

Is it because the SqlConnection is still open or is the SqlDataAdapter doing something behind the scene?

Solution 1

using DataAdapter.Fill does not load the database schema, which includes whether a column is a primary key or not, and whether a column is read-only or not. To load the database schema, use DataAdapter.FillSchema, but then that's not your questions.

using DataReader to fill a table loads the schema. So, the index column is read-only (probably because it's the primary key) and that information is loaded into the DataTable. Thereby preventing you from modifying the data in the table.

I think @k3b got it right; by setting ReadOnly = false, you should be able to write to the data table.

foreach (System.Data.DataColumn col in tab.Columns) col.ReadOnly = false; 

Solution 2

I kept getting the same exception while trying different approaches. What finally worked for me was to set the column's ReadOnly property to false and change the value of the Expression column instead of row[index] = "new value";

Comments

[jp2code]

Mr. Pieng: A follow up, after a year. Is there a way to unload or discard the schema after the DataReader retrieves the data?

[Fun Mun Pieng]

@jp2code You can probably create a new Table and copy just the data without the schema info. If it's only a specific property that you want to change such as the read-only flag, you might be better off just setting the value of that property.

[Misiu]

I already used my up votes for today, but virtual +1 for this answer. I was fighting with this for couple of hours before I found out that one of columns was read only. Now my little DataGridView wok like a charm! :) Thanks!