Today I was asked to look at a puzzling problem with TableAdapters at another project at my current customer. The problem they had was that they could not make the TableAdapter read back the generated SQL Server identity column values when saving a dataset (ta.Update) to insert new rows.
The first thing I checked was the 'Advanced options' of the TableAdapter to see if the 'Refresh the data table' setting was on. It wasn't. "But, we checked that option when we added the adapter". I checked the option and finished the wizard, then checked the setting again. It wasn't set...
I had noticed that details in the wizard summary page was rather short, so I did the wizard steps and inspected the summary again: "Generated SELECT statement" and "Generated INSERT statement", but no "Generated UPDATE statement" and no "Generated DELETE statement". I suspected that this was the cause of the problem, but what could cause the missing SQL commands ? The UpdateCommand and DeleteCommand of the TableAdapter's property window was empty. According to the online help, these statements "will be generated if there is enough information", but no hints to which information is needed. Really helpful.
Looking at the table definition in SQL Server, I then noticed that the table had no defined primary key. I selected the identity column and applied the "Set Primary Key" action and saved the table definition in SQL Server. After reconfiguring the TableAdapter in Visual Studio, the data table now has a defined primary key and - lo and behold - update/delete commands and refresh data on save.
Problem solved.
Showing posts with label TableAdapter. Show all posts
Showing posts with label TableAdapter. Show all posts
Wednesday, May 02, 2007
Wednesday, February 21, 2007
Effective use of TableAdapters and DataSets
Some of our customers are not ready for using an O/R Mapper as the data access layer, so I have to do the boring job of implementing it manually the old-fashioned way (sigh...). I then use VS2005 TableAdapters for implementing the domain object persistence using only the wizards and thus mostly generated code for the DAL.
TableAdapters are code-generated extension to typed datasets; and even if I am no fan of datasets, as long as the stay in the black-box, I can live with them. In addition to the auto-generated TableAdapters, I must manually create adapters that map my domain objects to/from the typed datasets to complete the repository component. This is where the effort goes, I have never written less SQL statements since using TableAdapters - and as a DBA, I actually like writing and tuning SQL statements.
I have been using TableAdapters since fall 2005, and this post is about effective use of TableAdapters and their DataSets. To illustrate my recommendations, I will use the DataSet shown in this figure (click to enlarge):
The central entity table in the dataset is 'DocumentCard', which is an aggregate root that contains a file-collection table (identifying relationship) and some extra information tables that are needed in the 'DocumentCard' domain object. The document cards are structured into a category treeview, using the 'DocumentCategory' entity. The TableAdapters options are configured to make the DataSets updatable as applicable for each table:
The Dataset objects are connected to each other using Relation objects, and together with the generated CRUD SQL statements, this makes for reading and easily modifying data the typed dataset way. Applying a filter when reading data using a combination of a single query and the specification pattern is not as easy, but I shown how this can be done towards the end.
I recommend that the default query of the table adapter is used only as a 'schema definition' query for the dataset, i.e. the query used for defining the table and for generating the CRUD statements that make the typed dataset updatable so that TableAdapter.Update( DataSet) automagically modifies the database to reflect the changes to the dataset. I give the default query a name like 'FillByNoFilter' (perhaps 'DefineSchemaQuery' would be better), and then add a separate 'Fill' query for use by the code. This way the table schema and the C_UD statements stay constant when the inevitable changes affect the logic of the data access layer.
Note that changes to the default query are immediately propagated by the table adapter mechanism to all other queries in the TableAdapter to ensure that they all adhere to the same schema. E.g. if you remove a field from the schema, all queries are automatically updated to have the same SELECT as the default query. This has a rather nasty side-effect on a specific type of scalar type queries, e.g. the 'LookupCardIdFromKey' method from the above example; it will just replace the select statement returning the scalar value with the default select statement returning a record set. This only happens to scalar queries that do not use a SQL function, and I recommend applying the MAX() function to any such query the return just a field instead of a computed value.
I recommend using "info" as the suffix for read-only datasets that are aggregated into a domain object. In the above example, status info about the physical files is aggregated from the FileHandler file management system; e.g. file upload progress. The info-tables have no generated C_UD SQL statements as they are read-only. I do not recommend aggregating this kind of info-table into the root-table of the entity as "assimilated" fields, as this quickly leads to the TableAdapter not being able to generate the C_UD statements for the dataset. You could of course add the info fields manually to the dataset and set their read-only property to true, but as you add more queries to the table adapter, you will have a hard time making the returned data fit the dataset schema - and not the least, ensuring that the fields are filled with data.
The relations of the data set are useful for more than just defining the structure of the tables making up the data set. The way that a typed dataset automatically populates the relations when filling the related tables and then exposes a relation as a collection, makes it possible to keep the number of database queries to a minimum when filling a complex object graph such as the three level deep annotated structure shown in the above example.
A typical implementation of filling a treeview with structured master-details data for a specific customer, would be fetching the nodes and then for each node fetch the related data. I call this read approach recursive-chunked. If there are N nodes, there would be N+1 database reads; one for the node table, and recursively one read for each node to read the related table in chunks. In my example, there are five tables, and with a large N, the number of database reads would explode using such a fill implementation.
I recommend that each table in a relation structure has a query that takes the aggregate root 'read specification' as parameters and returns non-chunked datasets, leaving it to the typed dataset to fill the relations and automatically provide the structure. This way, if there are M tables, there will be only M database reads to fill the complete structure. If there is e.g. two tables the recursive-chunked will use N+1 reads, while the non-chunked will always use 1+1 reads. Using the relation mechanism of typed datasets of course incurs some overhead, but as the use of TableAdapters implies use of typed datasets, why not utilize them to the maximum?
In my example the non-chunked implementation would always require 5 database reads. The recursive-chunked approach would hardly ever use fewer reads, e.g. with just a single category with two related document cards would result in 1+1*(1+2*(1+1+1)) = 8 reads. If there was four categories each with six cards each, there would be 1+4*(1+6*(1+1+1)) = 77 reads. So even if the chunked reads each contain less data, the overhead more of database roundtrips will soon dwarf the five non-chunked roundtrips.
The last tip about effective use of table adapters is about how to support the specification pattern while keeping the number of TableAdapter queries to a minimum. Even if the queries are created using wizards and generated code, each extra item is still another item to maintain. My way of doing this is based on my previous recommendation to use the default query as a schema definition and SQL statement provider only, making it the stable information source for the table adapter.
I recommend using the default query as the basis for building a dynamic parameterized SQL statement that filters the record set according to the specification, and dynamically injecting it into the TableAdapter to apply the selection. The custom ORM adapter is the specification interpreter and needs to build a DbCommand object containing a parameterized SELECT statement adhering to the schema of the TableAdapter. Thus, the interpreter needs access to the TableAdapter's private command collection to get the schema definition query. In addition, the adapter needs access to the private DataAdapter to be able to inject and execute the dynamic query to fill the DataSet.
As the needed TableAdapter members are private, you need to utilize the partial class mechanism to create extra internal properties that provides access to the private members. Double-click the design canvas of the dataset class to generate a partial class extending the dataset, then add the properties to the table adapter:
using System.Data.SqlClient;
namespace KjellSJ.DataAccess.DocumentCardDataSetTableAdapters
{
public partial class DocumentCardTableAdapter
{
internal SqlDataAdapter DataAdapter
{
get { return this._adapter; }
}
internal string SelectFromStatement()
{
return this.CommandCollection[0].CommandText;
}
}
}
namespace KjellSJ.DataAccess
{
partial class DocumentCardDataSet
{
//empty class generated by double-clicking the dataset design canvas
}
}
Adding specification filters that requires the use of related database tables in the query complicates the logic of the selection interpreter, especially when the filter is Nullable<T>. A nullable filter should only be applied if it is not null, i.e. the .HasValue property is true. Basically you have two options, joining the related table to the table of the defining query, or using sub-selects. As the interpreter should only add the related table to the query when at least one refering filter is defined in the specification, using a sub-select is usually the simplest thing to do.
This is how you can implement a hard-coded interpreter and apply the parameterized selection to the TableAdapter:
namespace KjellSJ.DataAccess
{
public class DocumentCardAdapter
{
public ProjectDocuments ListProjectDocuments( EApprovalSession session)
{
return DoListProjectDocuments(session, null, null);
}
public ProjectDocuments ListProjectDocuments( EApprovalSession session, DocumentCardFilter cardFilter)
{
DocumentCardTableAdapter ta = new DocumentCardTableAdapter();
SqlCommand filterCommand = new SqlCommand();
filterCommand.CommandType = CommandType.Text;
string sql = ta.SelectFromStatement();
sql += " WHERE 1=1";
filterCommand.Parameters.AddWithValue( "@ClientKey", customerKey);
filterCommand.Parameters.AddWithValue( "@ProjectKey", projectKey);
if (cardFilter.IsMapped.HasValue)
{
sql += (cardFilter.IsMapped.Value) ? " AND EXISTS " : " AND NOT EXISTS";
sql +=
" (SELECT * FROM DocumentRequirementMapping . . .)";
}
if(String.IsNullOrEmpty( cardFilter.FreeText)==false)
{
sql += " AND (Code like @FreeText";
sql += " OR Title like @FreeText";
sql += " OR Description like @FreeText";
sql += ")";
filterCommand.Parameters.AddWithValue( "@FreeText", wildcard);
}
. . .
filterCommand.CommandText = sql;
return DoListProjectDocuments(session, filterCommand, cardFilter.ExcludeEmptyCategoryNodes);
}
private ProjectDocuments DoListProjectDocuments( EApprovalSession session, SqlCommand filterCommand, bool? excludeEmptyNodes)
{
DocumentCardTableAdapter taData = new DocumentCardTableAdapter();
. . .
if(filterCommand==null)
{
taData.Fill(ds.DocumentCard, customerKey, projectKey);
}
else
{
filterCommand.Connection = taData.Connection;
taData.DataAdapter.SelectCommand = filterCommand;
taData.DataAdapter.Fill(ds.DocumentCard);
}
. . .
}
Having SQL fragments as text in the code should be avoided, but this is the simplest way to make a table adapter support dynamic criteria. Alternatively, I could have used a stored procedure taking the specifications as parameters and implementing all this logic in the database itself; having a 'FillBySpecification' query using the sproc. Alas, I don't recommend using sprocs for queries, and I hardly ever use sprocs for CRUD operations either. Why ? Well, read the discussions summarized in this post by Frans Bouma and decide for your self.
Following these guidelines should help you get the most out of table adapters while writing almost no SQL statements, and still have a maintainable data access layer. My strongest advice is to follow the 'schema definition' query recommendation, as having a stable foundation for the TableAdapter is a must for ensuring that the dataset CRUD operations stays correct as the adapters evolve.
TableAdapters are code-generated extension to typed datasets; and even if I am no fan of datasets, as long as the stay in the black-box, I can live with them. In addition to the auto-generated TableAdapters, I must manually create adapters that map my domain objects to/from the typed datasets to complete the repository component. This is where the effort goes, I have never written less SQL statements since using TableAdapters - and as a DBA, I actually like writing and tuning SQL statements.
I have been using TableAdapters since fall 2005, and this post is about effective use of TableAdapters and their DataSets. To illustrate my recommendations, I will use the DataSet shown in this figure (click to enlarge):
The central entity table in the dataset is 'DocumentCard', which is an aggregate root that contains a file-collection table (identifying relationship) and some extra information tables that are needed in the 'DocumentCard' domain object. The document cards are structured into a category treeview, using the 'DocumentCategory' entity. The TableAdapters options are configured to make the DataSets updatable as applicable for each table:
The Dataset objects are connected to each other using Relation objects, and together with the generated CRUD SQL statements, this makes for reading and easily modifying data the typed dataset way. Applying a filter when reading data using a combination of a single query and the specification pattern is not as easy, but I shown how this can be done towards the end.
I recommend that the default query of the table adapter is used only as a 'schema definition' query for the dataset, i.e. the query used for defining the table and for generating the CRUD statements that make the typed dataset updatable so that TableAdapter.Update( DataSet) automagically modifies the database to reflect the changes to the dataset. I give the default query a name like 'FillByNoFilter' (perhaps 'DefineSchemaQuery' would be better), and then add a separate 'Fill' query for use by the code. This way the table schema and the C_UD statements stay constant when the inevitable changes affect the logic of the data access layer.
Note that changes to the default query are immediately propagated by the table adapter mechanism to all other queries in the TableAdapter to ensure that they all adhere to the same schema. E.g. if you remove a field from the schema, all queries are automatically updated to have the same SELECT as the default query. This has a rather nasty side-effect on a specific type of scalar type queries, e.g. the 'LookupCardIdFromKey' method from the above example; it will just replace the select statement returning the scalar value with the default select statement returning a record set. This only happens to scalar queries that do not use a SQL function, and I recommend applying the MAX() function to any such query the return just a field instead of a computed value.
I recommend using "info" as the suffix for read-only datasets that are aggregated into a domain object. In the above example, status info about the physical files is aggregated from the FileHandler file management system; e.g. file upload progress. The info-tables have no generated C_UD SQL statements as they are read-only. I do not recommend aggregating this kind of info-table into the root-table of the entity as "assimilated" fields, as this quickly leads to the TableAdapter not being able to generate the C_UD statements for the dataset. You could of course add the info fields manually to the dataset and set their read-only property to true, but as you add more queries to the table adapter, you will have a hard time making the returned data fit the dataset schema - and not the least, ensuring that the fields are filled with data.
The relations of the data set are useful for more than just defining the structure of the tables making up the data set. The way that a typed dataset automatically populates the relations when filling the related tables and then exposes a relation as a collection, makes it possible to keep the number of database queries to a minimum when filling a complex object graph such as the three level deep annotated structure shown in the above example.
A typical implementation of filling a treeview with structured master-details data for a specific customer, would be fetching the nodes and then for each node fetch the related data. I call this read approach recursive-chunked. If there are N nodes, there would be N+1 database reads; one for the node table, and recursively one read for each node to read the related table in chunks. In my example, there are five tables, and with a large N, the number of database reads would explode using such a fill implementation.
I recommend that each table in a relation structure has a query that takes the aggregate root 'read specification' as parameters and returns non-chunked datasets, leaving it to the typed dataset to fill the relations and automatically provide the structure. This way, if there are M tables, there will be only M database reads to fill the complete structure. If there is e.g. two tables the recursive-chunked will use N+1 reads, while the non-chunked will always use 1+1 reads. Using the relation mechanism of typed datasets of course incurs some overhead, but as the use of TableAdapters implies use of typed datasets, why not utilize them to the maximum?
In my example the non-chunked implementation would always require 5 database reads. The recursive-chunked approach would hardly ever use fewer reads, e.g. with just a single category with two related document cards would result in 1+1*(1+2*(1+1+1)) = 8 reads. If there was four categories each with six cards each, there would be 1+4*(1+6*(1+1+1)) = 77 reads. So even if the chunked reads each contain less data, the overhead more of database roundtrips will soon dwarf the five non-chunked roundtrips.
The last tip about effective use of table adapters is about how to support the specification pattern while keeping the number of TableAdapter queries to a minimum. Even if the queries are created using wizards and generated code, each extra item is still another item to maintain. My way of doing this is based on my previous recommendation to use the default query as a schema definition and SQL statement provider only, making it the stable information source for the table adapter.
I recommend using the default query as the basis for building a dynamic parameterized SQL statement that filters the record set according to the specification, and dynamically injecting it into the TableAdapter to apply the selection. The custom ORM adapter is the specification interpreter and needs to build a DbCommand object containing a parameterized SELECT statement adhering to the schema of the TableAdapter. Thus, the interpreter needs access to the TableAdapter's private command collection to get the schema definition query. In addition, the adapter needs access to the private DataAdapter to be able to inject and execute the dynamic query to fill the DataSet.
As the needed TableAdapter members are private, you need to utilize the partial class mechanism to create extra internal properties that provides access to the private members. Double-click the design canvas of the dataset class to generate a partial class extending the dataset, then add the properties to the table adapter:
using System.Data.SqlClient;
namespace KjellSJ.DataAccess.DocumentCardDataSetTableAdapters
{
public partial class DocumentCardTableAdapter
{
internal SqlDataAdapter DataAdapter
{
get { return this._adapter; }
}
internal string SelectFromStatement()
{
return this.CommandCollection[0].CommandText;
}
}
}
namespace KjellSJ.DataAccess
{
partial class DocumentCardDataSet
{
//empty class generated by double-clicking the dataset design canvas
}
}
Adding specification filters that requires the use of related database tables in the query complicates the logic of the selection interpreter, especially when the filter is Nullable<T>. A nullable filter should only be applied if it is not null, i.e. the .HasValue property is true. Basically you have two options, joining the related table to the table of the defining query, or using sub-selects. As the interpreter should only add the related table to the query when at least one refering filter is defined in the specification, using a sub-select is usually the simplest thing to do.
This is how you can implement a hard-coded interpreter and apply the parameterized selection to the TableAdapter:
namespace KjellSJ.DataAccess
{
public class DocumentCardAdapter
{
public ProjectDocuments ListProjectDocuments( EApprovalSession session)
{
return DoListProjectDocuments(session, null, null);
}
public ProjectDocuments ListProjectDocuments( EApprovalSession session, DocumentCardFilter cardFilter)
{
DocumentCardTableAdapter ta = new DocumentCardTableAdapter();
SqlCommand filterCommand = new SqlCommand();
filterCommand.CommandType = CommandType.Text;
string sql = ta.SelectFromStatement();
sql += " WHERE 1=1";
filterCommand.Parameters.AddWithValue( "@ClientKey", customerKey);
filterCommand.Parameters.AddWithValue( "@ProjectKey", projectKey);
if (cardFilter.IsMapped.HasValue)
{
sql += (cardFilter.IsMapped.Value) ? " AND EXISTS " : " AND NOT EXISTS";
sql +=
" (SELECT * FROM DocumentRequirementMapping . . .)";
}
if(String.IsNullOrEmpty( cardFilter.FreeText)==false)
{
sql += " AND (Code like @FreeText";
sql += " OR Title like @FreeText";
sql += " OR Description like @FreeText";
sql += ")";
filterCommand.Parameters.AddWithValue( "@FreeText", wildcard);
}
. . .
filterCommand.CommandText = sql;
return DoListProjectDocuments(session, filterCommand, cardFilter.ExcludeEmptyCategoryNodes);
}
private ProjectDocuments DoListProjectDocuments( EApprovalSession session, SqlCommand filterCommand, bool? excludeEmptyNodes)
{
DocumentCardTableAdapter taData = new DocumentCardTableAdapter();
. . .
if(filterCommand==null)
{
taData.Fill(ds.DocumentCard, customerKey, projectKey);
}
else
{
filterCommand.Connection = taData.Connection;
taData.DataAdapter.SelectCommand = filterCommand;
taData.DataAdapter.Fill(ds.DocumentCard);
}
. . .
}
Having SQL fragments as text in the code should be avoided, but this is the simplest way to make a table adapter support dynamic criteria. Alternatively, I could have used a stored procedure taking the specifications as parameters and implementing all this logic in the database itself; having a 'FillBySpecification' query using the sproc. Alas, I don't recommend using sprocs for queries, and I hardly ever use sprocs for CRUD operations either. Why ? Well, read the discussions summarized in this post by Frans Bouma and decide for your self.
Following these guidelines should help you get the most out of table adapters while writing almost no SQL statements, and still have a maintainable data access layer. My strongest advice is to follow the 'schema definition' query recommendation, as having a stable foundation for the TableAdapter is a must for ensuring that the dataset CRUD operations stays correct as the adapters evolve.
Tuesday, October 25, 2005
TableAdapter with multiple related DataTables
In my ongoing VB6-to-VS2005 project, we have started using .NET 2.0 TableAdapters in the data access layer instead of SqlDataReaders to get strongly typed parameters and data when using our stored procedures. The main reason for this is that the customer in the existing solution has change management problems due to the untyped nature of DataReaders and the "late bound" style of accessing column values using .GetString[columnIdx].
When the customer has to change the result set of a stored procedure, this type of data access provides no compile time verification when getting a column value, and introduced bugs will not show up until run-time. Using TableAdapters "can dramatically increase your development and stabilization phase productivity by leveraging compile time verification for column changes or additions" [VB.NET team]. Compile time catching of bugs is way better than run-time exceptions, logging and debugging efforts.
The downside is of course lesser performance as a TableAdapter uses the DataSet/DataTable classes, but the size of our result sets are generally small and should thus not incure too much overhead. In addition, the number of required stored procedure calls might go down when retrieving 1:M related data from several related tables (e.g. master-details, multi-select picklists, etc). More on this below.
Read more about using TableAdapters in the VB.NET team blog and at MSDN. What is not covered in details in these articles, however, is how to work with hierarchical data; i.e. how to use TableAdapters to work with a DataSet that contains several related DataTables (e.g. order+order lines, person+favorite colors+favorite cars, etc).
Before you start to working with TableAdapters, you should recognize that a TableAdapter is not a new class in the .NET 2.0 framework. A TableAdapter is just a 'code generated' class that internally uses the classic DataAdapter, DataSet and DataTable classes. The generated code provides you with a typed dataset based on the SQL query / stored procedure you specify in the TableAdapter wizard. The generated class may also provide you with insert, update, and delete methods (see details at MSDN2). Note that datatable must have a defined key column before the insert, update, and delete SQL statements can be generated.
There are two main ways to create a set of TableAdapters. Start by selecting 'Data-Data Sources' from the VS2005 main menu, this opens the 'Data Sources' explorer. Then either create a new data source and select multiple stored procedures in the checkbox list, this will generate one TableAdapter for each selected "sproc"; or open a dataset in designer mode, right click and select 'Add-TableAdapter', this will add another TableAdapter to your dataset. Both ways will create multiple DataTables within a DataSet, however without any DataRelations.
When the customer has to change the result set of a stored procedure, this type of data access provides no compile time verification when getting a column value, and introduced bugs will not show up until run-time. Using TableAdapters "can dramatically increase your development and stabilization phase productivity by leveraging compile time verification for column changes or additions" [VB.NET team]. Compile time catching of bugs is way better than run-time exceptions, logging and debugging efforts.
The downside is of course lesser performance as a TableAdapter uses the DataSet/DataTable classes, but the size of our result sets are generally small and should thus not incure too much overhead. In addition, the number of required stored procedure calls might go down when retrieving 1:M related data from several related tables (e.g. master-details, multi-select picklists, etc). More on this below.
Read more about using TableAdapters in the VB.NET team blog and at MSDN. What is not covered in details in these articles, however, is how to work with hierarchical data; i.e. how to use TableAdapters to work with a DataSet that contains several related DataTables (e.g. order+order lines, person+favorite colors+favorite cars, etc).
Before you start to working with TableAdapters, you should recognize that a TableAdapter is not a new class in the .NET 2.0 framework. A TableAdapter is just a 'code generated' class that internally uses the classic DataAdapter, DataSet and DataTable classes. The generated code provides you with a typed dataset based on the SQL query / stored procedure you specify in the TableAdapter wizard. The generated class may also provide you with insert, update, and delete methods (see details at MSDN2). Note that datatable must have a defined key column before the insert, update, and delete SQL statements can be generated.
There are two main ways to create a set of TableAdapters. Start by selecting 'Data-Data Sources' from the VS2005 main menu, this opens the 'Data Sources' explorer. Then either create a new data source and select multiple stored procedures in the checkbox list, this will generate one TableAdapter for each selected "sproc"; or open a dataset in designer mode, right click and select 'Add-TableAdapter', this will add another TableAdapter to your dataset. Both ways will create multiple DataTables within a DataSet, however without any DataRelations.
Use the dataset designer to add the relations you need to be able to navigate between the DataTables at run-time. Adding relations allows you e.g. to use .GetChildRows(relationName)to read data related to the current master record.
Note that the relations are added to the .Relations collection of the DataSet, not as .ChildRelations of the "parent" DataTable. You must use the .Fill() method of each TableAdapter to populate all the DataTables and their DataRelations at run-time. Note that the .GetData() method returns a single DataTable without populating any relations.
Using TableAdapters have allowed us to reduce the number of stored procedure calls (round-trips to SQL Server) dramatically. The SqlDataReader code first read all the master records, then did three extra stored procedure calls for each of the master records to get the 1:M related data. E.g. with 20 master records, 61 round-trips to the SQL Server was needed. With TableAdapters, only 4 stored procedure calls are needed to fill the four related DataTables. Reading data for all master records is now as simple as doing a foreach() on the root DataTable and using .GetChildRows(relationName)to get the related data. This reduction should gain most of the raw performance difference between DataReaders and TableAdapters, or even perform better.
How to update related DataTables using TableAdapters is documented here at MSDN2.
Subscribe to:
Posts (Atom)