GetSchemaTable and Primary Keys

  • Thread starter Thread starter jlapenta
  • Start date Start date
J

jlapenta

I am trying to get the Primary Keys for a table using GetSchemaTable as
follows:


string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =
d:\\access\\northwind.mdb";
string strSQL = "select * from PRODUCTS";

string strMessage;

OleDbConnection cn = new OleDbConnection(strConnection);
cn.Open();
OleDbCommand cmd = new OleDbCommand(strSQL , cn);


OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly |
CommandBehavior.KeyInfo);
DataTable tbl = rdr.GetSchemaTable();

if (tbl.PrimaryKey.Length > 0)
{
strMessage = "";
foreach (DataColumn column in tbl.PrimaryKey)
{
strMessage += "Column :" + column.ColumnName + " ";
}

MessageBox.Show("Primary KEY fields: " + strMessage);
}
else
{
MessageBox.Show("NO PRIMARY KEY");
}

I have tried using CommandBehavior.KeyInfo and
CommandBehvavior.SchemaOnly as the arguments as well, with the same
results.

Anyone know what i am doing wrong?
 
On Sat, 17 Apr 2004 18:29:08 GMT, (e-mail address removed) wrote:

¤ I am trying to get the Primary Keys for a table using GetSchemaTable as
¤ follows:
¤
¤
¤ string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =
¤ d:\\access\\northwind.mdb";
¤ string strSQL = "select * from PRODUCTS";
¤
¤ string strMessage;
¤
¤ OleDbConnection cn = new OleDbConnection(strConnection);
¤ cn.Open();
¤ OleDbCommand cmd = new OleDbCommand(strSQL , cn);
¤
¤
¤ OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly |
¤ CommandBehavior.KeyInfo);
¤ DataTable tbl = rdr.GetSchemaTable();
¤
¤ if (tbl.PrimaryKey.Length > 0)
¤ {
¤ strMessage = "";
¤ foreach (DataColumn column in tbl.PrimaryKey)
¤ {
¤ strMessage += "Column :" + column.ColumnName + " ";
¤ }
¤
¤ MessageBox.Show("Primary KEY fields: " + strMessage);
¤ }
¤ else
¤ {
¤ MessageBox.Show("NO PRIMARY KEY");
¤ }
¤
¤ I have tried using CommandBehavior.KeyInfo and
¤ CommandBehvavior.SchemaOnly as the arguments as well, with the same
¤ results.
¤
¤ Anyone know what i am doing wrong?

I don't believe that you can use an OleDbDataReader for this. I don't have a C# example handy but
the following should be easy to convert:

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 Primary Keys.
SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Primary_Keys, _
New Object() {Nothing, Nothing, "Table1"})

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

'Displays all of the fields and related properties that comprise the primary key in a
DataGrid
DataGrid1.DataSource = SchemaTable

DatabaseConnection.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
The table of schema information returned by DataReader.GetSchemaTable
can take a little getting used to. The schema table tells you information
about the columns in the resultset - column names, data types, key
information, etc. Each column in your DataReader's resultset (SELECT *
FROM Products) corresponds to a row in the DataTable of schema information.
The easiest way to view this schema information is to display the contents
of the DataTable in a DataGrid on a form.


Here's some code you can use to look at the available columns in the
schema table:

OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo |
CommandBehavior.SchemaOnly);
DataTable tbl = rdr.GetSchemaTable();

Console.WriteLine("Available columns in the schema table:");
foreach (DataColumn col in tbl.Columns)
Console.WriteLine("\t" + col.ColumnName);
Console.WriteLine();


After looking at this information, you'll find ColumnName and IsKey as
available columns in the schema table. So, you could use the following
code to list the column names and whether or not they're part of the key:

Console.WriteLine("Rows in the schema table:");
foreach (DataRow row in tbl.Rows)
Console.WriteLine("\t{0}\t{1}", row["ColumnName"], row["IsKey"]);



I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.
 
Back
Top