How to refresh current row in ADO.NET application

• Download demo project - 18.7 KB
• Download source files - 1.2 KB

Introduction

Nowadays different databases are used in a majority of applications. Concerning .NET programs the most popular way to access databases is using ADO.NET components. It is no secret that ADO.NET uses a disconnected model and component named DataTable that does not have such a thing as a current data row. But very often developers have to refresh current data row that can be changed by another application or by server side logic. I consider the code below to be a solution to this problem for Oracle database, but the same is suitable for any other RDBMS.

Prerequisites

All code samples are intended for Microsoft Visual Studio 2005. I have used Oracle 10i and Oracle Data Provider (ODP.NET) version 10.2.0.2.20 as the database. It can be downloaded from the Oracle website at http://www.oracle.com/technology/software/tech/windows/odpnet/index.html.

Reality

ADO.NET developers gave us a very "useful" event named RowUpdated. If you have updated a row using the DataAdapter component, you will know that you did it. That is good but if this row has been updated by another user or server-side logic you will not know anything. In my previous article I considered some aspects concerning ADO.NET concurrent model. In principle, the current article is a logical continuation of the previous one because both articles have a common goal: to negotiate ADO.NET model restrictions.

Ways to solve a problem

We should solve the following questions:

1. Get current row from DataTable
2. Get record that corresponds to the current row from Oracle database
3. Put data from record to DataTable (refresh current row)

As we know, ADO.NET dataset doesn't have such a thing as a cursor, so it also does not have a concept as a "current row". But if we use an application that has DataGridView on a form this question is easy to solve. We can simply use the property of the one named "BindingContext" and get the current row position from DataGridView. It takes three lines of code:

public partial class Form1 : Form
{
        ..........
        private BindingManagerBase bindingManagerBase;
        public Form1()
        {
              InitializeComponent();
              ..........              
  bindingManagerBase = 
       dataGridView1.BindingContext[dataSet1.Tables["Colors"]];
  ..........

Somewhere in the application code we can take the current row:

DataRow cur_row = dataSet1.Tables["Colors"].DefaultView
            [bindingManagerBase.Position].Row;

So point "1" is easy.

To solve points "2" and "3", first we should decide how we can identify our current row to get it from the database. Obviously we should use some key. The majority of tables in the database have primary keys, so we can use them to identify our row. But to make our solution more universal we can use field name "ROWID". Each table and view of Oracle database has this field but if you have to refresh the current row in another RDBMS you should use something else. Now that we have decided to use ROWID as key to identify a row, don't forget to include ROWID in your "SELECT" expression.

Then we can write a class named "RefreshRow" that has the following public methods and property:

public  RefreshRow(OracleConnection conn) //constructor
public void Dispose() //destructor
public void Refresh(DataRow row) //refreshing row

public String SQL 
// sql expression to extract row from database, 
//it should has rowid parameter obligatory, 
//for example "select t.*, 
//t.rowid from colors t where t.rowid=:snotra__rowid"

The way the Refresh method works is very easy: it extracts a row from the database and puts data into DataRow:

public void Refresh(DataRow row)
{
    OpenDMLQuery();
    oracleDMLCommand.CommandText = sql;
    oracleDMLCommand.Parameters.Add(":snotra__rowid", OracleDbType.Varchar2,   
                                  RowId(row), ParameterDirection.Input);
    OracleDataReader odr = null;
    try
    {
          odr = oracleDMLCommand.ExecuteReader();
          if (odr.Read())
          {
               for (int i = 0; i < odr.FieldCount; i++)
               {
                   DataColumn col = row.Table.Columns[odr.GetName(i)];
                   SetColumnValue(col, i, odr, row);
               }
          }
    }
    catch (OracleException oe)
    {
       System.Windows.Forms.MessageBox.Show(oe.Message, "Error", 
            System.Windows.Forms.MessageBoxButtons.OK, 
        System.Windows.Forms.MessageBoxIcon.Error);
    }
    catch (Exception e)
    {
       System.Windows.Forms.MessageBox.Show(e.Message, "Error", 
                System.Windows.Forms.MessageBoxButtons.OK, 
                System.Windows.Forms.MessageBoxIcon.Error);
    }
    finally
    {
       if (odr != null)
       {
          odr.Close();
          odr.Dispose();
       }
    }
}

Private method SetColumnValue puts data into a row according to the column's data type:

private void SetColumnValue(DataColumn col, int index, OracleDataReader odr, 
    DataRow row)
{
     if (odr.GetValue(index) == DBNull.Value)
     {
                row[col] = DBNull.Value;
                return;
     }
     try
     {
          row.Table.BeginLoadData();
          switch (Type.GetTypeCode(/*col.DataType*/odr.GetFieldType(index)))
          {
                    case TypeCode.Int16:
                        row[col] = odr.GetInt16(index);
                        break;
                    case TypeCode.Int32:
                        row[col] = odr.GetInt32(index);
                        break;
                    case TypeCode.Int64:
                        row[col] = odr.GetInt64(index);
                        break;
                    case TypeCode.Decimal:
                        row[col] = odr.GetDecimal(index);
                        break;
                    case TypeCode.Single:
                        row[col] = odr.GetFloat(index);
                        break;
                    case TypeCode.Double:
                        row[col] = odr.GetDouble(index);
                        break;
                    case TypeCode.Byte:
                        row[col] = odr.GetByte(index);
                        break;
                    case TypeCode.Boolean:
                        row[col] = odr.GetBoolean(index);
                        break;
                    case TypeCode.String:
                        row[col] = odr.GetString(index);
                        break;
                    case TypeCode.DateTime:
                        row[col] = odr.GetDateTime(index);
                        break;
                    //add other types if you like
                    default:
                        row[col] = odr.GetValue(index);
                        break;
          }
     }
     catch (Exception)
     {
        throw new Exception("column: " + col.ColumnName + " cast is not 
                             valid");
     }
     finally
     {
         row.Table.EndLoadData();
     }
}

According to the line

"switch (Type.GetTypeCode(/*col.DataType*/odr.GetFieldType(index)))" 

Building the application

For our experiments we will use an easy .NET application that contains DataGridView component on Form, DataSet and Button named "RefreshCurrentRow". Our program should access Oracle database and ODP.NET is very suitable for it.

In our samples we will use a simple table named "COLORS".

create table COLORS
(
  COLOR_ID   NUMBER not null,
  COLOR_NAME varchar2(100) not null,
  RED number not null,
  GREEN number not null,
  BLUE number not null,
  CONSTRAINT PK_COLORS PRIMARY KEY (COLOR_ID)
)
--fill table-------------
insert into colors(color_id, color_name, red, green, blue) 
        values(1, 'black', 0, 0, 0);
insert into colors(color_id, color_name, red, green, blue) 
        values(2, 'white', 254, 254, 254);
insert into colors(color_id, color_name, red, green, blue) 
        values(3, 'red', 254, 0, 0);
insert into colors(color_id, color_name, red, green, blue) 
        values(4, 'green', 0, 254, 0);
insert into colors(color_id, color_name, red, green, blue) 
        values(5, 'blue', 0, 0, 254);
insert into colors(color_id, color_name, red, green, blue) 
        values(6, 'yellow', 0, 254, 254);
commit;

Given below is an example on how to use RefreshRow class:

private void button1_Click(object sender, EventArgs e)
{
            if (bindingManagerBase != null)
            {
                DataRow cur_row = 
                dataSet1.Tables["Colors"].DefaultView
            [bindingManagerBase.Position].Row;
                if (cur_row != null)
                {
                    RefreshRow refreshRow = new RefreshRow(connection);
                    refreshRow.SQL = "select t.*, 
                t.rowid from colors t where 
                     t.rowid=:snotra__rowid";
                    refreshRow.Refresh(cur_row);
                    refreshRow.Dispose();
                }
            }
}

It takes OracleConnection object, DataRow object that contains ROWID column obligatory and an SQL expression that has the following construction:

"t.rowid=:snotra__rowid"

That's all. We can test it now.

Test

First run the test application. Then start sqlplus and execute command:

update colors set red=1, green=1, blue=1 where color_id=1;
commit;

Then select the first row in DataGridView (color name is black) and push button "RefreshCurrentRow". RED, GREEN and BLUE columns should change values. So… great, it's working!

If you prefer other RDBMS or other data provider you should rewrite RefreshRow class using appropriate data provider and data types.

Points of interests

I came to .NET from Delphi and was a bit frustrated with the possibilities that disconnected ADO.NET components offer. As I result, I decided to write my own solution which allows me to use concurrent models that I need and some other extra features like single record refreshing, partial data selection among others.