Re: Strange Wierdness with SQLServer Bit fields and SQLProvider

I suspect you're not talking to the database you think you are...
perhaps working with a cached connection string to some other server?

But, onto the rest of the post...

> the database uses ref_isvalid as a tristate variable, true, false , dbnull
> (not used)

What is the business meaning of "not used"?  What does it default to?
This is the problem with NULL columns... somewhere, sometime, you have
to codify the meaning of the NULL selection... why not do so in the
database where everyone gets to see and benefit from that?

> For this example ref_isvalid defines if we're in debug mode, ( there are
> 200+ ref tables like this , so changing NOT NULL is not possible.)

Ummm, sure it is... given your schema, this will actually take less
room (since a single bit in a row will be allocated to an INT and
waste the rest of the bits)

create table REF_STATE (
  ref_id int identity(1,1),
  ref_label varchar(25),
  ref_isvalid TINYINT CHECK CONSTRAINT [CK_REF_STATE_ref_isValid]
CHECK  ([ref_isvalid]=0 OR [ref_isvalid]=1 OR [ref_isvalid]=2)
)

Then "null" == 2. You could easily add the non-null column, set the
correct valid and drop the old column.  If you (even better) get rid
of the tristate logic, you can use a column default and non-null it
in-place.

> to get information from this we use a stored procedure
>
> create procedure SP_GetState(@debug bit) as
> begin
>   select * from ref_state where ref_state = 1 or @debug = 1
> end

Eeew, surely you are actually NOT using SELECT * (right?)  Does anyone
actually care about those audit columns you add down the road?  Never
use *.  Never.

> when we loop over the dataset ,  we use this function
>
>  public T DefaultValue<T>(object o , object defaultValue){
>  if(o == null || o == DBNull.Value) return defaultValue;
>  return (T)o;
>  }

Seems like you should really check if "o" is an instance of DBNull
before blindly comparing it's value against DBNull.Value, right?  So

public T DefaultValue<T>(object o , object defaultValue)
{
   if (o == null)
      return defaultValue;

   if (o.GetType == typeof(DBNull) && (DBValue)o == DBNull.Value)
     return defaultValue;

   return (T)o;
}

>  refItem.IsValid = DefaultValue<Boolean>(dr["ref_isValid"],false);

When, exactly, would you pass anything other than false to such a read
(of the REF_* tables).

> Selects direct on the database return the correct data, Executing the
> storedprocedure returns the correct value , Somewhere between the
> DataAdapter.Fill(ds) and the   dr["ref_isvalid"]  it's either loosing the
> value or not setting it.  Any Ideas how to debug this or references to an
> example please?

I will state that you are NOT looking at the same tables, or not using
the same provider, or something like that....  Got an index on the
table that excludes NULL (not that you should EVER have a BIT in an
index (unless it's really late in the column list)).

Marc

p.s. NULL in databases is evil.... it just makes things harder and
forces you to scatter your defaulting logic all over.  Think in terms
of the NullObject pattern... where a specific row/value encodes the
_behavior_ of what you think NULL would mean.  It's more explicit and
easier to follow... For example:

CREATE TABLE [dbo].[Profile](
	[ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT
[DF_Profile_ID]  DEFAULT (newid()),
	[Type] [char](1) NOT NULL CONSTRAINT [DF_Profile_Type]  DEFAULT ('U'),
	[UserName] [nvarchar](150) NOT NULL,
	[Password] [nvarchar](50) NOT NULL,
	[FriendlyName] [nvarchar](50) NOT NULL CONSTRAINT
[DF_Profile_FriendlyName]  DEFAULT (''),
	[OwnerId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Profile_OwnerId]
 DEFAULT ('00000000-0000-0000-0000-000000000000'),
	[ValidFrom] [datetime] NOT NULL CONSTRAINT [DF_Profile_ValidFrom]
DEFAULT (getutcdate()),
	[ValidTo] [datetime] NOT NULL CONSTRAINT [DF_Profile_ValidTo]
DEFAULT ('9999-12-31 23:59:59.997'),
 CONSTRAINT [PK_Profile] PRIMARY KEY CLUSTERED
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Profile]  WITH CHECK ADD  CONSTRAINT
[FK_Profile_Owner] FOREIGN KEY([OwnerId])
REFERENCES [dbo].[Profile] ([ID])
ALTER TABLE [dbo].[Profile] CHECK CONSTRAINT [FK_Profile_Owner]

ALTER TABLE [dbo].[Profile]  WITH CHECK ADD  CONSTRAINT
[CK_Profile_Type] CHECK  (([Type]='R' OR [Type]='U' OR [Type]='P'))
ALTER TABLE [dbo].[Profile] CHECK CONSTRAINT [CK_Profile_Type]

INSERT INTO [dbo].[Profile]
        ([ID], [Type], [UserName], [Password], [FriendlyName],
[ValidFrom], [ValidTo])
VALUES('00000000-0000-0000-0000-000000000000', 'R', '', '', '',
'1753-01-01 00:00:00.000', '1753-01-01 00:00:00.000')

Now I can always code knowing that valid Profile rows are returned
when GetUTCDate()  BETWEEN [ValidFrom] AND [ValidTo].  I can always
code knowing that the [OwnerId] references a Profile row. I can
special case in my code only against the [Type] being 'R' for Root,
etc... thus the query to get all valid profiles with owner names and
type...

SELECT
   p.[Id]
   , p.[FriendlyName]
   , o.[FriendlyName] AS OwnerName
   , o.[Type] AS OwnerType
FROM [dbo].[Profile] AS p
INNER JOIN [dbo.Profile] AS o
   ON p.OwnerId = o.OwnerId
WHERE GetUtcDate() BETWEEN p.[ValidFrom] AND p.[ValidTo]

The alternative where a typically nullable schema looks like this:

SELECT
   p.[Id]
   , p.[FriendlyName]
   , ISNULL(o.[FriendlyName], '') AS OwnerName
   , ISNULL(o.[Type], 'R') AS OwnerType
FROM [dbo].[Profile] AS p
LEFT JOIN [dbo.Profile] AS o
   ON p.OwnerId = o.OwnerId
WHERE
   (p.[ValidFrom] <= GetUtcDate() OR p.[ValidFrom] IS NULL)
   AND (p.[ValidTo] >= GetUtcDate() OR p.[ValidTo] IS NULL)

It's just so much easier in the later schema to "forget" the NULL
issues, plus you end up preventing INDEX use...

-- 
"He uses statistics as a drunken man uses lamp-posts… for support
rather than illumination." Andrew Lang

Marc C. Brooks
http://musingmarc.blogspot.com