nullable columns

  • Thread starter Thread starter Haz
  • Start date Start date
H

Haz

Hello,
how do you get the table definition of any Relational Database by using the
OLEDB client. (it is either SQL Server or Oracle, but I would expect this to
work with any Relational Database supporting SQL92 standards and using the
OLEDB client.) ?

I need to figure out if a field is a required field (not null) or not.

Thanks a bunch...
Haz
 
¤ Hello,
¤ how do you get the table definition of any Relational Database by using the
¤ OLEDB client. (it is either SQL Server or Oracle, but I would expect this to
¤ work with any Relational Database supporting SQL92 standards and using the
¤ OLEDB client.) ?
¤
¤ I need to figure out if a field is a required field (not null) or not.
¤

You can try the following code, however not all databases implement the same features or implement
them in the same way so there are no guarantees with respect to supportability:

Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection
Dim SchemaTable As DataTable

DatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\My Documents\db1.mdb"

DatabaseConnection.Open()

'Retrieve schema information about Table1 Columns.
SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, "Table1"})

Dim RowCount As Int32
For RowCount = 0 To SchemaTable.Rows.Count - 1
'Console.WriteLine(SchemaTable.Rows(RowCount)!COLUMN_NAME.ToString)
'Console.WriteLine(SchemaTable.Rows(RowCount)!DESCRIPTION.ToString)
'Console.WriteLine(SchemaTable.Rows(RowCount)!IS_NULLABLE.ToString)
'Console.WriteLine(SchemaTable.Rows(RowCount)!TABLE_NAME.ToString)
Next RowCount

DataGrid1.DataSource = SchemaTable

DatabaseConnection.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Haz,
In .Net Framework v1.1, you can use OleDbDataReader.GetSchemaTable() method
to check if a given column is nullable or not. Consider a table test with
following columns.
create table test(col_float_1 float PRIMARY KEY , intcol int )
A code-snippet to check if a column is nullable as follows:
oledbconnection1.Open();
OleDbCommand oledbcommand1 = oledbconnection1.CreateCommand();
oledbcommand1.CommandText = "select * from test";
OleDbDataReader oledbdatareader1 =
oledbcommand1.ExecuteReader(CommandBehavior.SchemaOnly);
Boolean boolean1 = oledbdatareader1.Read(); // false
DataTable datatable1 = oledbdatareader1.GetSchemaTable(); // SchemaTable
DataRowCollection datarowcollection1 = datatable1.Rows; // new
DataRowCollection{DataRow, DataRow}
DataRow datarow1 = datarowcollection1[0];
DataRow datarow2 = datarowcollection1[1];
Boolean boolean2 = (Boolean)datarow1["AllowDBNull"]; //For col_float_1
returns false
Boolean boolean3 = (Boolean)datarow2["AllowDBNull"]; // For intcol returns
true

In .Net Framework v2.0 Beta1, you can use the
OleDbConnection.GetSchema("Columns") method to do the same. More information
on how to use this functionality can be found in Bob'Article on MSDN at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/adonet2schemas.asp
 
Back
Top