The ExecuteDataSet method question

Topics: Data Access Application Block
Feb 7, 2007 at 10:43 AM
Edited Feb 7, 2007 at 10:45 AM
hi I'm trying to using the ExecuteDataSet method to get a new dataSet, but now I meet a trouble, when I get a new dataSet form ExecuteDataSet,I find dataSet.tables0.PrimaryKey is null! like this:

DataSet dataset = new DataSet();
dataset = db.ExecuteDataSet(CommandType.Text, "select * from Customers");
int keyNum = dataset.Tables0.PrimaryKey.Length;

keyNum return 0, this make me crazy,I really need to know the PrimaryKey.How can I do when use EntLib? thanks


Feb 8, 2007 at 5:25 PM
Under the covers, the DAAB is doing nothing more than filling a DataSet. If you were to convert your code above to ADO.NET and not use the DAAB, you wouldn't get the primary key information. The following is similar to your code above and it does not return a schema:

string connectionString = "Database=Test;Server=(local);Integrated Security=SSPI;";
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connectionString);
DataSet ds = new DataSet();
adapter.Fill(ds);
 
// Returns Zero...
int keyNum = ds.Tables[0].PrimaryKey.Length;

So, Enterprise Library isn't doing anything to cause the schema information to be missing.

In ADO.NET, you would need to add a setting to tell the DataAdapter to grab schema information. This has some gotachas and may not always work with all database providers, but it will work with Sql Server. The setting is:

adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

And the code would now be:

string connectionString = "Database=Test;Server=(local);Integrated Security=SSPI;";
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connectionString);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataSet ds = new DataSet();
adapter.Fill(ds);
 
// Works! Now equals 1 in my case...
int keyNum = ds.Tables[0].PrimaryKey.Length;

Unfortunately, I don't see a way to specify the MissingSchemaAction with Database.ExecuteDataSet in the DAAB :( You may want to request this feature via the IssueTracker.

Regards,

Dave

_________________________

David Hayden
Microsoft MVP C#