Dealing with DataTables

Prerequisites

You must have a simple knowledge about tables in databases, like the meaning of unique properties, primary key, foreign key etc.

Introduction

In this article, I will show how to create simple data tables, how to put data or store it in them, and how to deal with this data row by row or column by column as per your needs...

Tables, in their simple form, have columns and rows of data like in the figure here. This table has 4 columns and 3 rows. You should notice that they are indexed beginning with 0, and not 1 as in the development environment.

Creating a new project

Open Visual Studio .NET and create a new project by choosing File | New | Project, and specify the project type as Visual C#, and the template as Windows Application.

Drag and drop a "DataGrid" from the Toolbar and resize it to the size you want it to be. In the form load event handler, we will write code to create a data table and specify its columns and their types, and specify the primary key of the table and fill the table with data. After that, we will retrieve all of the data rows of that table to the datagrid to display it in its cells.

Let's do it in code...

private void Form1_Load(object sender, System.EventArgs e)
{
    DataTable test=new DataTable("test1");
    
    test.Columns.Add("id",typeof(int));
    test.Columns.Add("Name",typeof(string));
    test.Columns.Add("tel",typeof(string)); 

    test.Columns["id"].Unique=true;
    test.PrimaryKey=new DataColumn[] {test.Columns["id"]}; 

    for(int j=0;j<=10;j++)
    {
        test.Rows.Add(new object[]{j,string.Format("Name{0}",
            j),string.Format("0{0}",405860313+j)});
    }
    test.AcceptChanges();

    //retrieving the data from table
    DataRow[] all=test.Select();

    DataRow[] rows=test.Select("id<5","Name DESC",
        DataViewRowState.CurrentRows);
    DataTable result=new DataTable();

    result.Columns.Add("id",typeof(int));
    result.Columns.Add("Name",typeof(string));
    result.Columns.Add("tel",typeof(string));

    foreach(DataRow r in rows)
        result.ImportRow(r);
        this.dataGrid1.DataSource=result;
}

Analyzing the source code

Let's now try to go through the code step by step and analyze it; in the first line, I create a new instance of the DataTable class and pass the table name to the constructor. After that, we should build the schema of that table, so we add 3 columns: "id" of type "int", "Name" of type "string", and "tel" of type "string".

We want "id" to be "unique", that is all data in that field should be unique and there should be no redundancy. We then make the same column a primary key in the next line of code.

After that, it is up to you to fill the table with data you want. I filled the table by using a simple loop, and after filling the data, you should commit or accept the changes you made to the table (here it is filling the table with data).

Retrieving the data from the table is simple. To retrieve all data rows from the table, just call or invoke the method "Select()" and don't pass any parameter to it, but if you want to retrieve data from the table with a specific criteria, you should pass these criteria to the Select method.

In either case, the results from the "Select" method are returned as a collection of data rows (one or more rows), and you retrieve the results to the DataRow[] array.

To retrieve all data rows from table "test", invoke the following:

DataRow[] all=test.Select();

To retrieve all data rows that have an ID less than 5 and also sorted descending, you pass these criteria to the Select method like:

DataRow[] rows=test.Select("id<5","id DESC",DataViewRowState.CurrentRows);

After returning the results into a DataRow[] array, you want to display it into the data grid, so you copy the data rows in the DataRow collection array; in this case, you can copy each row from the rows collection and import it into the table by using a foreach loop:

foreach(DataRow r in rows)
    result.ImportRow(r);

After that, specify the data source of the data grid to be the "result" table.

To get a specific row and specific field, you can write code like this:

Data_table_name[row_index].field_name

Example

string ss="First Student's Name: ";
ss+= this.dataSet11.Students[0].Name;
MessageBox.Show(ss);

The above listing of code returns the field "name" (i.e., the name of the student) into row number zero (i.e., the first row)...the first row in the "name" column.

To search for a specific data in primary key rows, you can invoke the Find() method. I tried the following code to search for id=212 in the primary key "id" in the Students table...

Notice: I tried to make the "Name" column a primary key in the Students table, and tries to search for a specific name, but there was an exception that I couldn't solve. If any one has a solution for this, post a message below this article....

int d="212";
if(dataSet11.Tables["Students"].Rows.Find(d)!= null)
    MessageBox.Show("Yes, Found 212 in id primary key field");
else
   MessageBox.Show("No, Not Found the value 212 in the id primary key field");

If you make ("id" + "Name") as the primary key in the database and try to search for the data row that has id=22 and Name="Mohammad", you don't get the correct result even if the record is there with the specific criteria exists.

Try the following and post a solution if you can find any.

DataRow foundRow;
object[]Values = new object[2];
// Set the values of the keys to find.
Values[0] = "22";
Values[1] = "Mohammad";
foundRow=this.dataSet11.Students.Rows.Find(Values);
if(foundRow != null)
    MessageBox.Show("Yes");
else
    MessageBox.Show("No");

(Copying data from a table stored into dataset to a data table and displaying it.)

DataTable tt=new DataTable();

for(int t=0;t<dataSet11.Students.Columns.Count;t++)
tt.Columns.Add(dataSet11.Students.Columns[t].ColumnName);

DataRow[] rr=dataSet11.Students.Select();

foreach(DataRow r in rr)
{
    tt.ImportRow(r);
}
dataGrid1.DataSource=tt;

Thanks a lot for reading and hope you enjoy this article...

But after reading, please don't forget to evaluate my article; it is important... Thanks again...