Create a DataTable and DataView - High-Flying C# Coding Tips


Sponsored Links

73058_New Scooba® 230 Floor Washing Robot + Free Shipping!

 

Create a DataTable / DataView

This page takes a look at the C# DataTable and the DataView.

DataTable

A C# DataTable allows us to store data in a structure similar to a table from a database.  Similarities exist such as that the DataTable is made up from DataColumns and DataRows, just like a database table is made up from columns and rows.  A DataView is a subset of the data inside a DataTable, that is a DataView returns data from the DataTable but refinded by criteria you select, similar to a SQL query, such as similar to applying the SQL Where clause.

The System.Data namespace includes the DataTable and DataView.  In order to be able to use them, you'll need to call in the System.Data namespace first:


using System.Data;


Once we have referenced the System.Data namespace, we can now use the DataTable and DataView.  The most probable reason for using a DataTable is that you've called a database stored procedure and used a SqlDataAdapter or similar and the returned dataset results from the SQL database have been filled into a DataSet object, which in turn contains DataTables for each table of results that have been returned from the SQL stored procedure.  In this case, the DataColumns and DataRows have already been created for you based on the data returned from the database.  However, there maybe a time when you want to have a DataTable to hold data in C#, regardless of whether you're using a database.  There are many ways to hold and organise data in C#, such as creating objects and storing them in a List or similar, but we won't enter that discussion here, we'll just look at how you can use the DataTable.

Ok, so firstly to create a DataTable from scratch, we create a DataTable, and then create some DataColumns which we then assign (add) to the DataTable.  In this example, we're also going to specifically state the SQL type of one of our DataColumns, as we're going to use this DataColumn as our primary key later on, and so making it an integer instead of just a string is important when it comes to comparing numeric values, as if you leave the type as a string but use it to compare numbers, you'll get incorrect ordering of results, so you should be using integers to get correct ordering of whole numbers.  To be able to specify SQL types you need to call in the System.Data.SqlTypes namespace:


using System.Data.SqlTypes;


Great, now we're ready to create the DataTable:


// Create a new DataTable
DataTable newDataTable = new DataTable();

// Create some DataColumns
DataColumn newColumn1 = new DataColumn("ID", typeof(SqlInt32)); // we've made this DataColumn of type SQL int
DataColumn newColumn2 = new DataColumn("Name");
DataColumn newColumn3 = new DataColumn("Age");

// Add the DataColumns to the DataTable
newDataTable.Columns.Add(newColumn1);
newDataTable.Columns.Add(newColumn2);
newDataTable.Columns.Add(newColumn3);


Now we've created a DataTable and created it's structure by saying the DataTable is made up of 3 DataColumns.  The DataTable allows more SQL table like similarities to be assigned, such as constraints, default values and specifying a primary key.

DataTable Primary Key

We're going to create a primary key for our DataTable now, we'll need this later when we use the DataView.  To create a primary key we need to create an array of DataColumns, the array holding the keys, so we can have one or more keys.  Here, we'll just make the ID DataColumn the primary key:


// set primary key to the ID column
DataColumn[] primaryKeyColumns = new DataColumn[] { newDataTable.Columns["ID"] };
newDataTable.PrimaryKey = primaryKeyColumns;


DataRow

Next, to give our DataTable some data, we need to add new DataRows containing the data to the DataTable.  If we use the DataTable's NewRow method, the newly created DataRow will contain the DataColumns from the DataTable.  The DataRows then need to be added to the DataTable:


// add some rows of data
for (int i = 1; i <= 5; i++)
{
    // create a new DataRow
    DataRow newDataRow = newDataTable.NewRow();

    // Give the columns some values
    newDataRow["ID"] = i;
    newDataRow["Name"] = "Name " + i.ToString();
    newDataRow["Age"] = (i * 10) + 10;

    // Add the DataRow to the DataTable
    newDataTable.Rows.Add(newDataRow);
}


So now we've created a DataTable, DataColumns to specify the columns, DataRows to add new rows and specified the primary key, we want to find out what's in the DataTable.

Get values from DataRow

We can examine the DataTable and retrieve values from the DataRows, such as with here:


// get a value from a row in the datatable
string firstRowName = newDataTable.Rows[0].ToString();


Find out Column names

If we have data returned from the database we may not know the names of the columns, we can find this out by using:


// get a column name
string columnName = newDataTable.Columns[1].ColumnName;


Loop through the DataRows in a DataTable

We can use a For loop and iterate through the DataRows in a DataTable, as shown here:


string idsString = "";

// find out what's in the DataTable
foreach (DataRow userDataRow in newDataTable.Rows)
{
    idsString += userDataRow["ID"].ToString() + " ";
}
// idsString = "1 2 3 4 5 "


We can loop through each DataColumm in each DataRow if we need to using the following example:


string tableContents = "";

foreach (DataRow dataRow in newDataTable.Rows)
{
    foreach (DataColumn dataColumn in newDataTable.Columns)
    {
        tableContents += dataColumn.ColumnName + ": ";
        tableContents += dataRow[dataColumn].ToString() + " ";
    }
}
// idsString = "ID: 1 Name: Name 1 Age: 20 ID: 2 Name: Name 2 Age: 30 ID: 3 Name: Name 3 Age: 40 ID: 4 Name: Name 4 Age: 50 ID: 5 Name: Name 5 Age: 60"


DataView

The DataView allows us to get a subset of data from a DataTable and we can also order the data.  This makes the DataView useful if you have already got a returned dataset but only want to show some of the results, say in a repeater.

In the following example, we create a new DataView, specifying the DataTable from which it should be created from.  We then supply the filter expression so that we only get rows with an ID of greater than or equal to 2.  We set the ordering of the data to that of ID DESC (descending) as like in SQL.  DataViewRowState.CurrentRows gets the rows in their current state (should they have been edited or still the original).


// Create a DataView
DataView newDataView = new DataView(newDataTable, "ID >= 2", "ID DESC", DataViewRowState.CurrentRows);


Loop through DataViewRows in a DataView

So in the above example we have specified the DataView to get the rows from newDataTable where the ID >= 2 and order the results by ID DESC.  Just like above, we can loop through the DataViewRows which belong to the DataView.Below demonstrates this:


string filteredResults = "";
foreach (DataRowView drv in newDataView)
{
    filteredResults += drv["ID"].ToString() + " ";
}

// 5 4 3 2


Sorting a DataView

We can specify further to change the ordering of DataView, so if you are displaying the data results for instance, you might have a button for the user to reverse the ordering of the results, and then back again.  Using the DataView.Sort property, we can change the ordering of the data:


// DataView sorting of results
newDataView.Sort = "ID ASC";

filteredResults = "";
foreach (DataRowView drv in newDataView)
{
    filteredResults += drv["ID"].ToString() + " ";
}
// 2 3 4 5