Getting if a Column is nullable (declared as not null or implicit declared nullable) using SqlClient on C#

Question

I have this code which shows the database I created using SqlClient, but I don't know how I could check if a column was added as nullable or not nullable in the table.

string[] defaultTables = { "AspNetUsers", "AspNetRoleClaims", "AspNetUserClaims", "AspNetUserLogins", "AspNetUserRoles", "AspNetUserTokens", "AspNetRoles", "__EFMigrationsHistory" };
dblist.ForEach((string DbName) => {
    strConnection = $@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog={DbName};Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

    using (SqlConnection conn = new SqlConnection(strConnection)) {
        conn.Open();
        using (cmdSql = new SqlCommand(strConnection, conn)) {
            DataTable dt = conn.GetSchema("Tables");

            foreach (DataRow row in dt.Rows) {
                string tablename = row[2].ToString();
                if (!defaultTables.Any(x => x == tablename)) {
                    tables.Add(tablename);

                    using (cmdSql = new SqlCommand($"Select * FROM {tablename}", conn)) {
                        SqlDataReader reader = cmdSql.ExecuteReader();
                        if (reader.Read()) {
                            for (int i = 0; i < reader.FieldCount; i++)
                                Console.Write(reader.GetName(i) + " " + reader.GetDataTypeName(i) + $"" + ',');
                        }
                    }
                }
            }
        }
    }
});

Use the INFORMATION_SCHEMA.Columns system view and your info is in the IS_NULLABLE column — Steve, Feb 14 '20 at 14:05
Well, `SqlDataReader` won't tell you - it just reads the results, it doesn't care whether the column is `NULL` or `NOT NULL` — stuartd, Feb 14 '20 at 14:05
Maybe this SQL statement helps you: https://stackoverflow.com/questions/5204684/query-to-check-whether-a-column-is-nullable — mu88, Feb 14 '20 at 14:06
@stuartd I didnt know, Thanks I thought since it returned the value type it could also return if it was nullable. — , Feb 14 '20 at 14:06
Check if the type returned here is nullable: `reader.GetDataTypeName(i)` using g [check for nullable](https://stackoverflow.com/questions/8939939/correct-way-to-check-if-a-type-is-nullable#8939958). I am not sure if sqlite has metadata but if it does, I would use that instead because it will answer even more questions. — CodingYoshi, Feb 14 '20 at 14:09
@stuartd not actually true; it *sometimes* knows - see my answer — Marc Gravell, Feb 14 '20 at 14:11

score 1 · Accepted Answer · answered Feb 14 '20 at 14:27

Your approach seems a bit complicated to extract that kind of information. We have the INFORMATION_SCHEMA views in the database that contains exactly the information required.

So for example:

using(SqlConnection con = new SqlConnection(.....)) 
{
    con.Open();
    DataTable dt = new DataTable();

    // Get info on all tables...
    SqlCommand cmd = new SqlCommand(@"SELECT * FROM INFORMATION_SCHEMA.TABLES 
                                      WHERE TABLE_TYPE = 'BASE TABLE'", con);
    dt.Load(cmd.ExecuteReader());
    foreach (DataRow r in dt.Rows)
    {
        // Get info on the current table's columns
        string sqltext = $"SELECT * FROM INFORMATION_SCHEMA.Columns 
                           WHERE TABLE_NAME = '{r.Field<string>("TABLE_NAME")}'";
        SqlCommand cmd1 = new SqlCommand(sqltext, con);
        DataTable dt1 = new DataTable();
        dt1.Load(cmd1.ExecuteReader());
        foreach (DataRow x in dt1.Rows)
        {
            Console.WriteLine($"Table:{x.Field<string>("TABLE_NAME")}");
            Console.WriteLine($"Column:{x.Field<string>("COLUMN_NAME")}");
            Console.WriteLine($"Data type:{x.Field<string>("DATA_TYPE")}");
            Console.WriteLine($"Nullable:{x.Field<string>("IS_NULLABLE")}");
        }
    }

what about using it like this inside the for loop in my post? ```((DataRow)schemaTable.Rows[i]).Field("AllowDBNull")``` — , Feb 14 '20 at 15:32

score 0 · Answer 2 · answered Feb 14 '20 at 14:11

0

It isn't 100% reliable, but GetSchemaTable() on the data-reader can show this, if it knows (which isn't always); specifically, you're looking for the AllowDBNull property. It isn't a great API to look at, though - it returns a DataTable that you need to investigate, so it might need a bit of playing to find what you are after.

citation

answered Feb 14 '20 at 14:11

Marc Gravell

1,026,079
266
2,566
2,900

yes I found this way it worked (wrote it inside the for loop) ```((DataRow)schemaTable.Rows[i]).Field("AllowDBNull")``` – Feb 14 '20 at 15:31
@PontiacGTX If you think this answers your question, then please do your part and mark it as the answer. And understand the risk you accept with this approach - perhaps add a comment in the code just in case someone has to come along later. – SMor Feb 14 '20 at 16:40
@Smor good point - as the docs say _"Set if the consumer can set the column to a null value **or if the provider cannot determine whether the consumer can set the column to a null value**. Otherwise, not set. A column may contain null values, even if it cannot be set to a null value."_ – stuartd Feb 15 '20 at 23:06
@stuartdall the answers seems to be valid.I tried using AllowDBNull with a different code before and gave the table metadata itself probably and differs from the actual nullability of the column which belong to the table I queried. – Feb 20 '20 at 12:28

Getting if a Column is nullable (declared as not null or implicit declared nullable) using SqlClient on C#

2 Answers2