DataReader obtains wrong information about PrimaryKeys

Topics: Data Access Application Block
Feb 23, 2011 at 11:29 AM

Hi,

as my topic says I use a DataReader to get data from database.

I use the ExecuteReader-Methode to fill the reader. If I run the GetSchemaTable-Methode of the reader afterwards the correct data is shown

expexct one. The IsKey-Value is always false. It dosen't matter which database table I use.

Here a short script for a table I get no PK:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test](
	[Id] [int] NOT NULL,
	[Value] [int] NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Here the code I use:

var db = DatabaseFactory.CreateDatabase();
var reader = db.ExecuteReader(CommandType.Text, "SELECT * FROM Test");
var table = reader.GetSchemaTable();
var isKey = table.Rows[0]["IsKey"]; // <-- isKey == DBNull

Thanks for your help,

regards,

Jan

Feb 23, 2011 at 12:51 PM

In raw ADO.NET, you would need to call the ExecuteReader method passing CommandBehavior.KeyInfo

SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.KeyInfo);

But since this is specific to a SqlCommand, DAAB doesn't provide you to pass this parameter in ExecuteReader.  Thus, you would need to make a SqlCommand and use it call the ExecuteReader method.

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Feb 23, 2011 at 1:02 PM

Thanks for the answer.

But I want to use the same code for several different database types, so this is not an option.

It seems that I have to fill in the PKs by hand.

Feb 24, 2011 at 7:14 AM

I'm thinking you can create an extension method which accepts the CommandBehavior parameter.  Inside the extension method, you can perform the actual casting of the DbCommand to the specific commands which supports this.  This is not a 100% solution but it still allows you to separate the logic for specific database providers and calling it an agnostic manner.

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com