Nowadays different databases are used in the majority of applications. Concerning .NET programs the most popular way to access databases is using ADO.NET components. It's not a secret that ADO.NET uses disconnected model and component named DataTable doesn't have such thing like current data row. But very often developers have to refresh current data row that can be changed by another application or server side logic. Below I consider solution of this problem for Oracle database, but the same way is suitable for other RDBMS.
All code samples are intended for Microsoft Visual Studio 2005; as database I have used Oracle 10i and Oracle Data Provider (ODP.NET) version 10.2.0.2.20 that could be downloaded from Oracle site: http://www.oracle.com/technology/software/tech/windows/odpnet/index.html.
ADO.NET developers gave for us very "useful" event named RowUpdated. So if you have updated row using DataAdapter component you will know that you did it. That's good but if this row has been updated by another user or server-side logic you will know nothing. In my previous article I considered some aspects concerning ADO.NET concurrent model. In principle current article is logical continuation of previous one because they have the common goal: negotiate ADO.NET model restrictions.
We should solve following questions:
As we know ADO.NET dataset doesn't have such thing as cursor so it also doesn't have such concept as "current row". But if we use application that has DataGridView on form this question is easy to solve. We simply can use the property of the one named "BindingContext" and get 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 application code we can take current row:
DataRow cur_row = dataSet1.Tables["Colors"].DefaultView[bindingManagerBase.Position].Row;So the point "a" is easy.
To solve points "b" and "c" first we should decide how we can identify our current row to get it from database. Obviously we should use some key. The majority of tables in 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 current row in another RDBMS you should use something else. Ok, so we decided to use ROWID as key to identify a row so don't forget to include ROWID in your "SELECT" expression.
Then we can write a class named "RefreshRow" that has 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"
Refresh method works very easy: it extract row from database and put 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 row according 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 the line "switch (Type.GetTypeCode(/*col.DataType*/odr.GetFieldType(index)))" this method get type from oracle DataReader. If you set column's type manually you can uncomment "col.DataType" line instead.
For our experiments we will use easy .NET application that contains DataGridView component on Form, DataSet and Button named "RefreshCurrentRow". Also 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;
The example how to use RefreshRow class you can see below:
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, SQL expression that has construction like "t.rowid=:snotra__rowid". That's all. We can test it now.
First run test application. Then start sqlplus and execute command:
update colors set red=1, green=1, blue=1 where color_id=1; commit;
Then select 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.