CODEWITHSUNDEEP
sql.netvb.netsqldatareader
Simon Dugré
Simon Dugré

Reputation: 18936

SqlDataAdapter and Computed Columns; not maintained?

Let's say I have following SQL schema:

SQL

TABLE Person COLUMNS 
( 
   ID AS INTEGER, 
   Firstname AS varchar(100), 
   Lastname AS varchar(100), 
   Fullname AS (Firstname + ' ' + Lastname)
)
GO
INSERT INTO Person (ID, Firstname, Lastname) VALUES (1, 'Simon', 'Dugré')
GO

VB retreiving class

Dim da as new SqlDataAdapter("SELECT ID, Firstname, Lastname, FullName FROM Person WHERE ID = 1", cn)
Dim ds as new DataSet()
da.Fill(ds)
da.FilleSchema(ds) ' I tried this before or after da.fill, just in case '

Dim dr as DataRow = ds.table(0).row(0)
Assert.IsTrue(dr("Firstname") = "Simon") ' Returns true '
Assert.IsTrue(dr("Lastname") = "Dugré") ' Returns true '
Assert.IsTrue(dr("Fullname") = "Simon Dugré") ' Returns true '
dr("Firstname") = "Nomis"
Assert.IsTrue(dr("Fullname") = "Nomis Dugré") ' Return False, it is still "Simon Dugré" in it. '

While debugging in the immediate window, I see the following: 

?dr.table.Columns("Fullname").Computed ' It says it is set to false... Duhhh... no!'

More informations

I saw at a few places that there is a way to "hardcode" computed column formula directly from code by referring to the property instead of SQL fields... But I can't do that because to keep this question readable, I removed a lot of code but this is in fact part of a whole abstract and generic scenario.

My question is, is there in fact a way to have formula retrieved directly from SQL while calling da.Fill or da.FillSchema or something? Then, when changing Firstname or Lastname, it will automatically have an effect on the Fullname computed column?

Upvotes: 2

Views: 176

Answers (1)

Luaan
Luaan

Reputation: 63772

It's not computed in the DataTable. That has no relation to how the column is defined in the database :)

There's no direct translation between SQL expressions and IL, so you'll have to type that in yourself manually, and maintain it as needed.

Of course, if you're only dealing with simple expressions, it's pretty easy to translate them automatically, you'll just have to write a parser. For stuff like this it's not even all that hard.

Upvotes: 1

Related Questions