As a bit of background, I use dotConnect for Oracle, MySQL and SQL Server together in a query tool. I retrieve metadata for tables including column name, data type and key column usage using abstract code that uses DbCommandBase and DbDataReader. The code works as expected across all databases except for when querying information_schema views in SQL Server.
I call ExecuteReader on a DbDataReader with options (CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo). If I include these options I get additional schema table result rows when querying information_schema views in SQL Server. I get the expected number of schema rows if I call ExecuteReader with CommandBehavior.SchemaOnly only, but then I don't get the key information.
I have included a unit test that illustrates the issue. I'm running dotConnect for SQL Server version 2.5.42, Visual Studio 2008 SP1, Windows 7 RC.
My question is, is this expected behavior when applying CommandBehavior.KeyInfo to information_schema views?
Thanks,
Code: Select all
[TestMethod]
public void KeyColumnIssue() {
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.IntegratedSecurity = true;
builder.DataSource = "localhost";
builder.InitialCatalog = "master";
SqlConnection connection = new SqlConnection(builder.ToString());
connection.Open();
// works for all tables/views outside of information schema
String sql = "select * from information_schema.table_constraints";
// CommandBehavior.SchemaOnly
// works as expected
Console.WriteLine("CommandBehavior.SchemaOnly");
SqlCommand command1 = new SqlCommand(sql, connection);
SqlDataReader reader1 = command1.ExecuteReader(CommandBehavior.SchemaOnly);
foreach (DataRow row in reader1.GetSchemaTable().Rows) {
Console.WriteLine("Ordinal: {0} Name: {1} IsKey: {2}", row["ColumnOrdinal"], row["ColumnName"], row["IsKey"]);
}
reader1.Close();
// CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo
// returns extra key column rows, with duplicates, inconsistent extra rows for each view in information_schema
Console.WriteLine();
Console.WriteLine("CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo");
SqlCommand command2 = new SqlCommand(sql, connection);
SqlDataReader reader2 = command2.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
foreach (DataRow row in reader2.GetSchemaTable().Rows) {
Console.WriteLine("Ordinal: {0} Name: {1} IsKey: {2}", row["ColumnOrdinal"], row["ColumnName"], row["IsKey"]);
}
reader2.Close();
connection.Close();
}
Code: Select all
CommandBehavior.SchemaOnly
Ordinal: 0 Name: CONSTRAINT_CATALOG IsKey:
Ordinal: 1 Name: CONSTRAINT_SCHEMA IsKey:
Ordinal: 2 Name: CONSTRAINT_NAME IsKey:
Ordinal: 3 Name: TABLE_CATALOG IsKey:
Ordinal: 4 Name: TABLE_SCHEMA IsKey:
Ordinal: 5 Name: TABLE_NAME IsKey:
Ordinal: 6 Name: CONSTRAINT_TYPE IsKey:
Ordinal: 7 Name: IS_DEFERRABLE IsKey:
Ordinal: 8 Name: INITIALLY_DEFERRED IsKey:
CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo
Ordinal: 0 Name: CONSTRAINT_CATALOG IsKey: False
Ordinal: 1 Name: CONSTRAINT_SCHEMA IsKey: False
Ordinal: 2 Name: CONSTRAINT_NAME IsKey: False
Ordinal: 3 Name: TABLE_CATALOG IsKey: False
Ordinal: 4 Name: TABLE_SCHEMA IsKey: False
Ordinal: 5 Name: TABLE_NAME IsKey: False
Ordinal: 6 Name: CONSTRAINT_TYPE IsKey: False
Ordinal: 7 Name: IS_DEFERRABLE IsKey: False
Ordinal: 8 Name: INITIALLY_DEFERRED IsKey: False
Ordinal: 9 Name: id IsKey: True
Ordinal: 10 Name: depid IsKey: True
Ordinal: 11 Name: class IsKey: True
Ordinal: 12 Name: depsubid IsKey: True
Ordinal: 13 Name: class IsKey: True
Ordinal: 14 Name: value IsKey: True
Ordinal: 15 Name: id IsKey: True
Ordinal: 16 Name: depid IsKey: True
Ordinal: 17 Name: class IsKey: True
Ordinal: 18 Name: depsubid IsKey: True
Ordinal: 19 Name: class IsKey: True
Ordinal: 20 Name: value IsKey: True
Ordinal: 21 Name: id IsKey: True
Ordinal: 22 Name: indid IsKey: True
Ordinal: 23 Name: depid IsKey: True
Ordinal: 24 Name: class IsKey: True
Ordinal: 25 Name: depsubid IsKey: True
Ordinal: 26 Name: class IsKey: True
Ordinal: 27 Name: value IsKey: True