Nullability of stored proc result column datatypes-CodePudding

When one executes a stored proc against MS SQL Server, the recordset comes with some type info. Clients are capable of retrieving that type info, e. g. like this (C#/.NET):

SqlCommand cmd = new SqlCommand("dbo.MyProc", conn);
SqlDataAdapter ada = new SqlDataAdapter(cmd);
ada.Fill(ds);
string ColName = ds.Tables[0].Columns[0].ColumnName;
Type ColType = ds.Tables[0].Columns[0].DataType;

What about nullability of those columns? While it's not knowable in the general case, sometimes it is - for example, when a recordset column comes straight from a table field. If SQL Server is smart enough, it can determine column nullability at least for those cases. Does SQL Server report nullability to the clients in whatever metadata it provides along with the recordset?

Specifically in the .NET client, there is AllowDBNull in retrieved column properties, but in the scenario above it's unreliable - it comes across as true both for columns that came from nullable fields, and for columns that came from nonnullable fields. Is this a limitation of the .NET client, or a shortcoming of the underlying protocol?

CodePudding user response：

This is a limitation of SqlDataAdapter.

The TDS protocol does return this information, as you can see in the specification.

In turn, SqlDataReader will return this information via GetSchemaTable. But it does not make its way to a table filled using a DbDataAdapter.

You can see this with the following code

using(var conn = new SqlConnection(YourConnectionString))
{
    conn.Open();
    using(var comm = new SqlCommand("select 1", conn))
    using(var reader=comm.ExecuteReader())
    {
        reader.Read();
        reader.GetSchemaTable().Dump();
    }

    using(var comm = new SqlCommand("select case when getdate() = 1 then 1 end", conn))
    using(var reader = comm.ExecuteReader())
    {
        reader.Read();
        reader.GetSchemaTable().Dump();
    }
}

You will see that the first has AllowDBNull as false and the second as true.

I would advise you in any case to avoid SqlDataAdapter, as it is only actually useful in data-binding UI scenarios. In back-end code, just use a SqlDataReader, or even better: use an ORM such as Dapper or Entity Framework, which will sort out all of this kind of thing for you.