Storing Null values

I am creating a UDT which is not a Native but UserDefined Format. I use the IBinarySerialize and should implement Read en Write methods.

When i have a null value should i always write some null flag to the database storage Or is there another way to deal with null values like writing nothing Hope somebody can give me some more details about this.

I don't really understand how reading/writing is working. Because when i store a UDT with a null value in the database i cannot do a normal select on it. I get an error like: Error message is: Data is Null. This method or property cannot be called on Null values. When i use the .ToString() after the columnname, i get the NULL as result. So there is something wrong with null values

Answer this question

Storing Null values

Raupes

I get an error each time i store a null value and running a select SQL statement. The error is: An error occurred while executing batch. Error message is: Data is Null. This method or property cannot be called on Null values.

I was using the following code as example. Maybe i do something wrong

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize=8000,IsFixedLength=false)]
public struct TestType : INullable, IBinarySerialize
{
    private string strtest;

    public override string ToString()
    {
        return "";
    }

    public bool IsNull
    {
        get
        {
            return (strtest == null) || (strtest == String.Empty);
        }
    }

    public static TestType Null
    {
        get
        {
            TestType h = new TestType();
            return h;
        }
    }

    public static TestType Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        TestType u = new TestType();
        u.strtest = s.Value;
        return u;
    }

    public void Write(System.IO.BinaryWriter w)
    {
        if (this.IsNull)
        {
            w.Write((int)0);
            return;
        }
        else
        {
            w.Write((int)1);
            w.Write(strtest);
        }
    }

    public void Read(System.IO.BinaryReader r)
    {
        int type = r.ReadInt32();
        if (type == 0)
        {
            strtest = null;
            return;
        }

        strtest = r.ReadString();
    }
}

Carlo_DiGiacomo
So there is nothing wrong in my code. It looks a little strange because when a varchar (normal SQL datatype) is null inside the database you don't get this error. So I was expecting to get a NULL value in return. Or am I wrong about this
Eagle 1
Your code is correct as long as program that receives resultset knows how to handle it. In this case Managent Studio doesn't know how to represent NULL value of unknown datatype and raises an error (so it is rather Management Studio's than SQL Server's error).

For me it is a bit strange too because Management Studio shows non-null UDTs as binary data, so it can show null values as "NULL" as well.

eBaccus

You should select UDT values using .ToString() method or Convert T-SQL function:
 
create table dbo.a (MyColumn dbo.TestType NULL) 
insert dbo.a values ('qwerty')
insert dbo.a values (NULL)
 
select MyColumn.ToString() from dbo.a
select convert(varchar(100), MyColumn) from dbo.a 
 
 
P.S. .NET 2.0 introduces String.IsNullOrEmpty() static method so your IsNull method 
can look as simple as:
 
public bool IsNull
{
    get
    {
        return String.IsNullOrEmpty(strtest);
   }
}

BrianNoyes

The general concept of reading/writing is that you need to store (write) enough information to be able to restore UDT from it. If your UDT is NULL, then you have to store the information that it is actually NULL. It can be just a "isNull" flag that you read/write first. If it is true then you initialize your UDT to NULL state, if false - read additional information describing the state of UDT.

Here is a simple example for array UDT.

private int[] _arr;

public bool IsNull()
{
return (_arr == null) || (_arr.Length == 0);
}

public void Write(System.IO.BinaryWriter w)
{
if (this.IsNull)
{
w.Write((int)0);
return;
}

foreach (int value in _arr)
{
w.Write(value);
}
}

public void Read(System.IO.BinaryReader r)
{
int length = r.ReadInt32();

if (length==0)
{
_arr = null;
return;
}

_arr = new int[length];
for (int index = 0; index < length; i++)
{
_arr[index] = r.ReadInt32();
}
}

Storing Null values

Answer this question