View with nullable columns

Posts   
 
    
Posts: 4
Joined: 19-Dec-2012
# Posted on: 19-Dec-2012 15:22:25   

hello,

i´ve created a stored procedure:

CREATE PROCEDURE dbo.sproc_cu_CustomerOrders @Country AS VARCHAR(50) AS BEGIN SELECT c.CustomerID, c.CustomerName, c.ChangedAt, o.ArticleID, o.Artikelname FROM customers c INNER JOIN orders o ON o.CustomerID = c.CustomerID WHERE c.Country = @Country END GO

The column ChangedAt is a DateTime-column and DBNull is allowed.

In llblGen the column in the view marked as 'Is nullable'.

Why is the column-property in the view not nullable?

Thats make no sense.

can you tell me a useable workaround to use views and stored procedures?

best regards

Micha

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 19-Dec-2012 21:31:58   

Please specify the Designer version and (release date).

Posts: 4
Joined: 19-Dec-2012
# Posted on: 20-Dec-2012 11:42:18   

Walaa wrote:

Please specify the Designer version and (release date).

llblGen Pro v3.5 Final November 6th, 2012

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 20-Dec-2012 14:57:11   

What do you mean with 'Why is the column-property in the view not nullable?' ? Do you refer to a typed view you mapped onto the stored procedure resultset? Did you receive an error when you fetched the typed view?

Frans Bouma | Lead developer LLBLGen Pro
Posts: 4
Joined: 19-Dec-2012
# Posted on: 20-Dec-2012 16:03:28   

Otis wrote:

What do you mean with 'Why is the column-property in the view not nullable?' ? Do you refer to a typed view you mapped onto the stored procedure resultset? Did you receive an error when you fetched the typed view?

Thats right, i generate a TypedView as a resultset from a stored procedure.

the property ChangedAt in the view isn´t nullable, but in the generator and in the sql-server it was flagged as nullable.

thats really strange, because in case of datetime DBNull-value went in vb.net to 1/1/0001 00:00:00, in case of boolean a DBNull-value became false, etc.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 20-Dec-2012 18:49:43   

If I'm not mistaken, I think the following thread is discussing a similar issue: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=21055

Bing Ping
User
Posts: 15
Joined: 21-Dec-2012
# Posted on: 21-Dec-2012 17:49:54   

I installed latest 2.6 and used it to open my 2.0 project for conversion (recommended by LLBLGen). Whenever I selected llblGen Pro v3 in the template group, the button ‘Start Generator’ got disabled.

What is the problem? Is it the license issue because I use v2.0 license.

How do I get the license of v2.6?

Thanks

(please next time, start a new thread instead of posting in a random thread irrelated to your problem. Thanks. -- Otis)

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 22-Dec-2012 04:55:02   

Hi BingPing!

Please select .net2 or .net35 for the target platform, apparently the template binding is not enable for net4. Anyway this is not important for the conversion templates. It has nothing to do with the licences.

BTW, if you purchase v3 and want a v2.x licence, open a HelpDesk thread to ask for it.

Note: I deleted your attachment as we don't like files that might damage people's machines (i.e. viruses, macros). Next time please just upload the image. Anyway I understand what is your issue.

David Elizondo | LLBLGen Support Team
Posts: 4
Joined: 19-Dec-2012
# Posted on: 03-Jan-2013 12:24:29   

happy new year.

so i'll explain the steps i've done.

i´ve created the following tables in my SQL-database:

table customers

column datatyp null values allowed

CustomerID int Unchecked CustomerName varchar(100) Unchecked ChangedAt datetime Checked Country varchar(50) Unchecked

table orders

column datatyp null values allowed

OrderID int Unchecked CustomerID int Unchecked ArticleID int Unchecked Artikelname varchar(100) Unchecked

After that, i´ve create a stored procedure:


CREATE PROCEDURE dbo.sproc_cu_CustomerOrders
    @Country AS VARCHAR(50)
AS
BEGIN
SELECT c.CustomerID, c.CustomerName, c.ChangedAt, o.ArticleID, o.Artikelname FROM customers c 
INNER JOIN orders o ON o.CustomerID = c.CustomerID
WHERE c.Country = @Country
END
GO

In the next step, i`ve generated in llblgen the stored procedure with a typedview-class as resultset.

In llblgen the column ChangedAt is marked as 'Is nullable'. But the column in the typedview-class is not nullable. It is datetime instead of datetime? .

So why does typedview-class not has nullable fields?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 04-Jan-2013 12:13:40   

TypedView classes follow the same pattern as typed datatables. They have done so since v1 which was released back in 2003 in a time when there weren't nullable types. We do this to make it easy for typedviews to bind to controls, as most controls work with datatables.

So instead there are properties for nullability testing: Is<fieldname>Null.

We didn't change this as it would break a lot of code, and in general one wouldn't need it: datatables themselves don't work with 'null' but with DBNull.Value, and controls bound to datatables expect DBNull.Value, not null.

It's in today's light of .NET 4.5 a little old-fashioned, as nullable types are common and we use them elsewhere so why not in typedview / list classes? Well, see above simple_smile

Frans Bouma | Lead developer LLBLGen Pro
JSobell
User
Posts: 145
Joined: 07-Jan-2006
# Posted on: 30-Oct-2015 04:16:22   

Old message, but this caught us out too. We're using the TypedView DataRow object, but we can't copy NULL items from an associated Entity object into it. We have to null check each entity field and only assign the value if HasValue is true, depending on the default value in the DataRow remaining null. Do we still need the non-nullability of the values? Is it worth making this a definable option against the TypedView and/or project? Would we have to have a ternary condition in the property setter to convert Null to DBNull.Value?

e.g.


public System.DateTime? DateOfBirth 
 {
  get { return IsDateOfBirthNull() ? (System.DateTime)TypeDefaultValue.GetDefaultValue(typeof(System.DateTime)) : (System.DateTime)this[_parent.DateOfBirthColumn]; }
  set { this[_parent.DateOfBirthColumn] = value.HasValue ? (object)value.Value : DBNull.Value; }
 }

Cheers, Jason

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 30-Oct-2015 10:23:36   

Please create new thread for your question and refer to the Guidelines here for all details needed. https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7717