none
DataColumn.Expression, Computed Table Columns & Scalar Functions RRS feed

  • Question

  • Well,

    I am working with some "Computed" columns in my table, as in, the Table that is created in SQL Server.  The Computation is performed utilizing Stored Procedures (more accurately Scalar Functions). 

    Create Table whatever (
     CalcColumn As (dbo.CalcValue(ID))
    )
    So here I am, working with the Dataset generated from this Database in SQL server, and though the computed columns are visible, I am thinking that It might be best for me not to depend on the back end computation.  The Back end computation (performed by the SQL Server Scalar functions) is used by other scalar functions for other computations and by the multiple views (queries) I've designed to operate with this database.  Some of this was designed with the initial focus of not having a Front End Application, so i was doing everything via SQL and SSMS.

    I am now writing that front end application, and am realizing that though the computation works just fine, if I want the user interface to Update the computed values based upon the current table values I have to do one of two things:

    1. Upon Changing a value, update the DataTable to the Database and then Refill that row, so the computed values are re-gleaned from the Database.
    2. Programmatic computation of the computed fields. (basically, using the Stored Proc (scalar functions) to recompute the values on the fly.

    I am thinking that #2 is the most appropriate method to use, since when change some of the column values that are utilized in the computed columns, i do not necessarily want to "save" this data in the database, just see the new values.  #1 would effectively destroy the old data in deference to retrieving simple computed calculations. 

    However, using #2, I am aware of the "Expression" property of the DataColumn and have used it for more simple equations:  (IIF([Value] <> 0, [Value1] + [Value2] + [Value3] / [Value], 0))

    But such "Expressions" are computed directly from the columns in the current DataTable, and my calculations for this database are bit more complex, hence the usage of Scalar Functions.  So, Is there a way that I can link a "DataColumn" to be computed, such as the "Expression" property allows, but instead of having operate on a simple equation, to have it call out to a Scalar Function of the database?

    Thanks

    Jaeden "Sifo Dyas" al'Raec Ruiner

     

     


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Saturday, May 29, 2010 8:00 PM

Answers

  • > have it call out to a Scalar Function of the database?

    Not really.  Keep in mind that one of the central ideas behind DataSet is "disconnected data".  It is supposed to work without needing a connection to the database other than for the Fill or Update.

    You'll have to weight the pros and cons of calling the database UDF from your program vs. building the logic into your program.  But if the Expression property is inadequate, you are not going to get any additional help from the DataSet class.

     

    • Marked as answer by JaedenRuiner Sunday, May 30, 2010 3:16 PM
    Saturday, May 29, 2010 9:28 PM

All replies

  • > have it call out to a Scalar Function of the database?

    Not really.  Keep in mind that one of the central ideas behind DataSet is "disconnected data".  It is supposed to work without needing a connection to the database other than for the Fill or Update.

    You'll have to weight the pros and cons of calling the database UDF from your program vs. building the logic into your program.  But if the Expression property is inadequate, you are not going to get any additional help from the DataSet class.

     

    • Marked as answer by JaedenRuiner Sunday, May 30, 2010 3:16 PM
    Saturday, May 29, 2010 9:28 PM
  • That's what I thought.  Thanks.

    J"SD"a'RR


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Sunday, May 30, 2010 3:16 PM