Click here to Skip to main content
Click here to Skip to main content

How to refresh current row in ADO.NET application

, 27 Feb 2007
Rate this:
Please Sign up or sign in to vote.
Refreshing current row in ADO.NET application using ODP.NET data provider
Screenshot - currowrefresh.gif

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)))" 
this method gets the type from oracle DataReader. If you set the column's type manually you can uncomment "col.DataType" line instead.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Michael Milonov
Web Developer
Russian Federation Russian Federation
I started to write programs 10 years ago and used very different languages. Professionally, I developed systems that work with very large databases, mainly on Oracle. To date, I learned about 15 computer languages. As the moment, I'm in love with C# and the .NET framework, although I widely use Java, C++, Perl, PL/SQL. I prefer don't do user interfaces but like to develop reusable components.
PhD in Computer Science,
CEO of Snotra Tech

Comments and Discussions

 
QuestionRowId to identify row? Pinmemberpurplepangolin6-Mar-07 6:23 
AnswerRe: RowId to identify row? PinmemberMichael Milonov6-Mar-07 7:28 
GeneralRe: RowId to identify row? Pinmemberpurplepangolin7-Mar-07 3:16 
Newshttp://quadriderm.tangoing.info/ Pinsusshttp://quadriderm.tangoing.info/5-Dec-07 4:36 
Newshttp://quadriderm.tangoing.info/ Pinsusshttp://quadriderm.tangoing.info/5-Dec-07 4:36 
GeneralThe second part of this article (continuation) PinmemberMichael Milonov28-Feb-07 3:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141220.1 | Last Updated 27 Feb 2007
Article Copyright 2007 by Michael Milonov
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid