In Ado.Net, can I determine if columns in result sets are nullable?

Question

I need to determine the structure of a result set returned by ExecuteReader. I am using the following approach:

    public List<NameAndType> ResultSetStructure(DataTable columns)
    {
        var ret = new List<NameAndType>();
        foreach (DataRow column in columns.Rows)
        {
            ret.Add(new NameAndType { Name = column[NameIndex].ToString(),
                                          Type = column[TypeIndex].ToString()
            });
        }
        return ret;
    }

(snip)

 using (SqlDataReader dr = command.ExecuteReader())
        {

            var rawColumns = dr.GetSchemaTable();
            var columns = ResultSetStructure(rawColumns);

This gives me column names and types, but I would also like to know if the column is nullable, so that I know which of the following options to choose:

decimal density = dr.GetDecimal(0); 
decimal? density = dr.IsDBNull(0) ? (decimal?)null : dr.GetDecimal(0);

Can I accomplish that? TIA.

Edit: I just found what I need:

column[13].ToString()

In Denali there are much easier ways to do this (new metadata discovery DMVs). But why not just always assume NULLable? If a column is not NULLable, only one side of your conditional will ever be run. — Aaron Bertrand, Sep 08 '11 at 19:06
@Aaron Bertrand It's a good strategy, but the problem is that you kind of want the client side to match the constraint so that consumers know if they can validly set a property to NULL. You're right that it's not the kind of thing you want to decide at runtime anyway, you really want it to be decided at compile-time, so inspecting at runtime doesn't help you there. For a fully dynamic thing - you really have to assume nullable. — Cade Roux, Sep 08 '11 at 19:15
@Cade I would suggest that if you want the client side to match, you design the client side in tandem with the database constraints. Checking this at runtime (and building validation on the client side in response) is going to get pretty hairy, pretty quick. :-) — Aaron Bertrand, Sep 08 '11 at 19:20
@Aaron Bertrand That's what I do. But like you said if you are doing something very dynamic (I'm not sure what the OP's overall system is), you pretty much just have to assume nullability - it's a minefield - in .NET string is always nullable because it's a reference type. — Cade Roux, Sep 08 '11 at 19:24
@Aaron: Denali is not an option yet. AFAIK their last release still intermittently fails to enforce FKs. — Arne Lund, Sep 08 '11 at 19:25
@Cade: I am generating C# wrappers off of a few dozen stored procedures. I would rather drag them over onto my dbml file, but Linq-2-sql has a bug. It fails to determine result set structure correctly for several stored procedures. I am almost done with my generator. — Arne Lund, Sep 08 '11 at 19:29
@Arne Lund If you insert into a table temporarily (use the OPENROWSET trick http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure), you can then inspect the table - nullability should be inferred . SET FMTONLY is also a way to inspect the output schema without actually running the proc, but this won't help nullability problem, I don't think. — Cade Roux, Sep 08 '11 at 19:41
@Cade Roux: When I ran my procedures against an empty database, I got correct result set structure, including (I think) nullability. When I set SET FMTONLY ON and ran one of them, it blew up: Msg 208, Level 16, State 0, Procedure MyProc, Line 27 Invalid object name '#t'. All these procs use temporary tables, and Linq-2-Sql chokes on them as well. The simpler procedures are already exposed with Linq-2-Sql, but it fails on more complex ones. — Arne Lund, Sep 08 '11 at 20:04
@Arne Lund SET FMTONLY has some weaknesses - that's exactly what affects LINQ. The OPENROWSET trick should work, though. Messy to be making permanent tables temporarily, but you can do it in a sandbox database or schema. — Cade Roux, Sep 08 '11 at 20:37

score 0 · Answer 1 · answered Sep 08 '11 at 19:17

I guess there is no such way to know whether a column is nullable or not. You can try writing an extension method something like below:

     public static decimal GetDecimal(this SqlDataReader reader, int columnIndex) 
     {    
        if(!reader.IsDBNull(columnIndex))  
             {      
             return reader.GetDecimal(colIndex);    
          }
         else
           {
                   return 0; 
         }
       }

Hope this would be some help!!

score 0 · Accepted Answer · answered Sep 08 '11 at 20:23

The following code gets the job done:

            ret.Add(new NameAndType { Name = column[NameIndex].ToString(),
                                          Type = column[TypeIndex].ToString(),
                                          IsNullable = column[13].ToString().Equals("True")

In Ado.Net, can I determine if columns in result sets are nullable?

2 Answers2