Editing a data table

Question

Situation:

Hello! I am trying to populate a WPF DataGrid toolkit with an MS Access database.

Here is what I have right now (it works):

//Load the datagrid with the database private void LoadDataGrid(string filename, string path) { string databaseConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + "\\" + filename, tableName =""; OleDbConnection conn = null; DataTable schemaTable, table = new DataTable(); try { conn = new OleDbConnection(databaseConn); try { conn.Open(); schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); tableName = "[" + schemaTable.Rows[0].ItemArray[2].ToString() + "];"; string sqlQuery = "SELECT * FROM " + tableName; OleDbCommand command = new OleDbCommand(sqlQuery, conn); OleDbDataReader reader; reader = command.ExecuteReader(); table.Load(reader); DataGrid_.ItemsSource = table.DefaultView; } catch (Exception ex) { System.Windows.MessageBox.Show(ex.Message); } finally { conn.Close(); } } catch (Exception ex) { System.Windows.MessageBox.Show(ex.Message); } } 

In the above code example, the WPF DataGrid toolkit is loaded using the MS Access database.

What I would like to do is the ability to insert a column in the DataGrid at the very beginning. This column will be used to record the line number. I think what might work is to change the table variable (which is a DataTable object ).

---

Question:

So, how can I insert a column into a table variable, add a row number for each row in this new column and have all the data from the database in the DataGrid?

Answer 1

An alternative is to create a column in the DataTable before loading the IDataReader into it.

 // the rest of your code // DataTable table = new DataTable(); DataColumn col = table.Columns.Add("RowNumber", typeof(int)); col.AutoIncrementSeed = 1; col.AutoIncrement = true; // // the rest of your code // table.Load(reader) // // the rest of your code 

Below is a snippet of code that demonstrates the technique from the context of the question.

 //Simulates data coming from a database or another data source DataTable origin = new DataTable(); DataColumnCollection columns = origin.Columns; columns.Add("Id", typeof(int)); columns.Add("Name", typeof(string)); origin.Rows.Add(55, "Foo"); origin.Rows.Add(14, "Bar"); IDataReader reader = origin.CreateDataReader(); DataTable table = new DataTable(); //Sets up your target table to include a new column for displaying row numbers //These are the three lines that make it all happen. DataColumn col = table.Columns.Add("RowNumber", typeof(int)); col.AutoIncrementSeed = 1; col.AutoIncrement = true; //Simulates loading data from the database table.Load(reader); // Examine table through the debugger. Is will have the contents of "origin" with the column "RowNumber" prepended 

Answer 2

The simplest solution is to change the code to include the "virtual" RowNumber field in the original SELECT query:

 SELECT ROWNUM AS ROWNUMBER, * FROM TABLE1 

Unfortunately, Access has nothing like the ROWNUM function, so I think the easiest solution is to add the RowNumber column to the SELECT query, for example:

 SELECT 0 AS ROWNUMBER, * FROM TABLE1 

which will add a column containing all the zeros at the beginning, and then iterate over the resulting DataTable and set the row number, for example:

 int rownumber = 1; foreach (DataRow row in table.Rows) { row["ROWNUMBER"] = rownumber; rownumber++; } 

and then upload the DataTable to the grid.