Kevin Bosch on .NET Mumblings on things about .NET and SQL server

I came across a problem the other day which was interesting. Using the SqlDataAdapter it will not allow you to use a field with an expression.; This makes sense as the bulk adapter was built to update a data source which was used to retrieve the data in the first place. As such an expression is simply a calculation of other derived fields and does not need to be saved. In using the SqlDataAdapter for a bulk insert operation one of the fields which was in the source was actually an expression. When trying to run the bulk update from the SqlDataAdapter the code generated the flowing error:

System.InvalidOperationException: The column mapping from SourceColumn 'colname' failed because 
the DataColumn 'colname' is a computed column.

So to get around this problem I decided to write a procedure to cement the expressions as values pre update. This works much like a copy and paste by values in excel would do. You pass in a datatable which had expression in and it. The code would take the expressions copy the values out into a temp column delete the original column then rename the temp column to the original. The net effect of doing this is that the expressions would be converted to values which could be used in the SqlDataAdapter for a bulk update

The code to Cement Expressions is below:

// gets the first col which has an expression on.  
// This will need to be refactored if you have expressions based on expressions as you will 
// need to be aware of dependency order
// if no expressions are found it will return null. 
private DataColumn GetColunmWithExpression(DataTable dt)
{
    DataColumn result = null;
    foreach (DataColumn dcloop in dt.Columns)
    {
        if (!string.IsNullOrEmpty(dcloop.Expression))
        {
            result = dcloop;
            break;
        }
    }
    return result;
}
public void CementExpressionsAsValues(DataTable dt)
{
    DataColumn colWithExpression = GetColunmWithExpression(dt);
    int excapeCounter = 0;
    while (colWithExpression != null && excapeCounter < dt.Columns.Count)
    {           
         string tempColName = colWithExpression.ColumnName + Guid.NewGuid().ToString();
         dt.Columns.Add(tempColName, colWithExpression.DataType);
         foreach(DataRow dr in dt.Rows)
         {
            dr[tempColName] = dr[colWithExpression.ColumnName];
         }
         dt.Columns.Remove(colWithExpression);
         dt.Columns[tempColName].ColumnName = colWithExpression.ColumnName;
         colWithExpression = GetColunmWithExpression(dt);
         excapeCounter++;
    }
}