Circular Reference problem in DataColumn.Expression

  • Thread starter Thread starter Rick Bonnett
  • Start date Start date
R

Rick Bonnett

I'm working on a reporting project, one feature of which
is data security at the level of columns. For a partiular
user, a test can be specified that will block the display
of real data values. I'm attempting to implement this
filtering using DataColumn.Expression, and ran into a
problem with circular references. For example, after
selecting some data about employees, I want to test for
the value of field CC_ID. If the value is '9058', then I
will disp;ay the actual value of the ANNUALSALARY field.
If not, I display a null.

This first fragment does not work, giving me a circular
reference exception, presumably because I am referencing
the ANNUALSALARY column in the Expression for the
ANNUALSALARY column.


===================================================
// Create Data Adapter

o_adapter = new OdbcDataAdapter("select
emplid,fullname,cc_id,annualsalary,annualsalary as annsal
from prototype_ee where cc_id = '9058' or emplid
= '7946' order by fullname", o_connection );
// create dataset and fill
m_dataset = new System.Data.DataSet();

o_adapter.Fill(m_dataset, "EEs");
// set a row filter
DataTable m_table = m_dataset.Tables["EEs"];


// output the data
foreach (DataColumn m_col in m_table.Columns)
{
Console.Write(m_col.ColumnName + ",");
}
Console.WriteLine();

DataColumn dcol = m_table.Columns["ANNUALSALARY"];
dcol.Expression = "IIF(trim(CC_ID)
= '9058',ANNUALSALARY,null)";
foreach (DataRow dr in m_table.Rows)
{
foreach (DataColumn m_col in m_table.Columns)
{
Console.Write(dr[m_col] + ",");
}
Console.WriteLine();
}

======================================================

This next fragment does work, by using an additional
column in my SELECT statement to alias ANNUALSALARY as a
different name.

=============================================
// Create Data Adapter

o_adapter = new OdbcDataAdapter("select
emplid,fullname,cc_id,annualsalary from prototype_ee
where cc_id = '9058' or emplid = '7946' order by
fullname", o_connection );
// create dataset and fill
m_dataset = new System.Data.DataSet();

o_adapter.Fill(m_dataset, "EEs");
// set a row filter
DataTable m_table = m_dataset.Tables["EEs"];


// output the data
foreach (DataColumn m_col in m_table.Columns)
{
Console.Write(m_col.ColumnName + ",");
}
Console.WriteLine();

DataColumn dcol = m_table.Columns["ANNUALSALARY"];
dcol.Expression = "IIF(trim(CC_ID) = '9058',ANNSAL,null)";
foreach (DataRow dr in m_table.Rows)
{
foreach (DataColumn m_col in m_table.Columns)
{
Console.Write(dr[m_col] + ",");
}
Console.WriteLine();
}

=================================================

The SQL queries used for these reports are created by
users, and the filtering tests can be defined for any
arbitrary column in the results. Using the alias route, I
would need to:

- force my users to select every column twice, so I had a
convenient alias for each one

or

- loop through the DataTable in my DataSet, adding alias
columns myself prior to setting the Expression on
filtered columns


Is there a better way to accomplish this, or a different
way to avoid the circular reference problem?

As an aside, I don't see why there should be a circular
reference issue here any more than there is for a
statement like 'i = i + 1;'. Seems like an weakness in
DataColumn.Expression.

Help is appreciated as always.

Thanks
Rick
 
Back
Top