Check if Database field is Nullable Using C# Data Reader

Question

Is there a way in C# to determine if a database field is nullable in a relatively database/platform agnostic fashion? In other words, within Oracle I could do something like this:

select column_name, nullable
from all_tab_columns
where table_name = :TABLE

But that approach is tethered to the Oracle, and I'd have to come up with an equivalent for other platforms.

What I was hoping for was a means to do this through the data providers. I was able to extract the datatypes using something similar to this:

AseCommand cmd = new AseCommand(sql, conn);
AseDataReader reader = cmd.ExecuteReader();

for (int i = 0; i < reader.FieldCount; i++)
{
    string name = reader.GetName(i);
    string dbTypeName = reader.GetDataTypeName(i);
    Type dbType = reader.GetFieldType(i);
}

In this example it's Sybase, but the approach has been polymorphic across other platforms, at least for the DBs I've been using.

The only thing I'm missing is whether or not the field is nullable.

Maybe a duplicate of this [link][1] except for the platform agnostic part. [1]: stackoverflow.com/questions/3253943/… — RetroCoder
– RetroCoder, Commented Jun 20, 2014 at 14:27
Thanks for the link. Unfortunately, that helps me deal with the value when it's null but it doesn't tell me if it's possible for the field to be null. It's actually a worthwhile read, though. — Hambone
– Hambone, Commented Jun 20, 2014 at 14:37

Austinh100 · Accepted Answer · 2014-06-24 13:44:15Z

1

OLEDB in C# is a very database agnostic way to interact with your data, it also supports checking for null columns.

    string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\data.accdb;Persist Security Info=False;"; //This will be different for differing connection methods (you can find more on [ConnectionStrings.com][1]
    OleDbConnection conn = new OleDbConnection(connString);
    DataSet mainData = new DataSet();
    OleDbCommand accessCommand = new OleDbCommand("SELECT * FROM YOUR_TABLE_HERE", conn);
    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(accessCommand); //create the SQL command
    conn.Open();
    dataAdapter.Fill(mainData, "YOUR_TABLE_HERE"); //fill the dataset with the data from our SQL
    DataTable dta = mainData.Tables[0];

    DataTable schema = conn.GetSchema();
    schema.Tables["YOUR_TABLE_HERE"].Columns["YOUR_COLUMN_HERE"].AllowDBNull; //gets or sets if the column allows nulls

    conn.close();

More info OLEDB from MS

GetSchema Docs

edited Jun 24, 2014 at 13:44

answered Jun 20, 2014 at 14:29

Austinh100

5983 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Hambone Over a year ago

Thanks for the reply. For whatever reason, I cannot get this to work. I was curious how the database/schema and table name are identified if you only specify a column name. If any variation of this works, it will be the perfect solution.

Austinh100 Over a year ago

@ChrisHamel I've added a more complete example but you should look into the links at the bottom for more information.

Hambone Over a year ago

Thank you -- I'll see if I can get this working. I didn't see GetSchema() as an available method of the DataTable class (at least not via Intellisense). It does show against the connection, but none of the other objects. I'll give feedback if I get it working.

Austinh100 Over a year ago

@ChrisHamel My bad, i don't know what i was doing, this edit should work better.

lcryder Over a year ago

Also, .Net's SQLDataReader has a 'FieldExists' method: if (reader.FieldExists("FieldName"))

|

Collectives™ on Stack Overflow

Check if Database field is Nullable Using C# Data Reader

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related