Why do you see grayed out GenerateDBDirectMethods checkbox in “TableAdapter Configuration Wizard”

In Visual Studio 2008 or Visual Studio 2010, you’ve started developing your Data project and let’s say you’re designing your DataSets through Visual Studio’s nice DataSet Designer just by drag/dropping your entities (tables, views) into the designer , from “Server Explorer” that you connected to your SQL Server .

If your entity is a “Table” you will see  your DataTable in the designer body with its TableAdapter at the bottom of the box as follows :

If you click on this DataTable’s “t1TableAdapter” part you will its properties as follows :

Here in the “GenerateDBDirecetMethods” is automatically set to True for Tables. As a consequence, Visual Studio can genereate the SelectCommand, InsertCommand, DeletCommand, UpdateCommand s for us automatically. Let me expand the properties with a more detailes screenshot :

But if you drag/drop a “View ” to do body as follows :

and check its “TableAdapter” properties you will see as follows :

“GenerateDBDirectMethods” is false and you only have “SelectCommand” filled, but the rest of the commands (InsertCommand, DeleteCommand, UpdateCommand) are “empty”.

Question : Why is that ?

Simply this question is answered in here . I used a “View” as an example to illustrate why the command (InsertCommand, DeleteCommand, UpdateCommand) cannot be generated automatically. It is also possible for you to see “GenerateDBDirectMethods” to fail for just a SELECT command you defined but with some JOINs etc.

Long story short, this (InsertCommand, DeleteCommand, UpdateCommand) commands cannot be generated for you automatically for lots of situations. Most of the time, you will see those are generated for your “Table” drag/drops to the designer or for a DataSet that you defined with a simple SQL query like “SELECT col1, col2 FROM table1”. Reason is very simple…  Other than those DataTable’s connected to a table or through simple “SELECT” command scenarios, we can’t be able to reliably figure out which field in which table to INSERT/UPDATE/DELETE (all the “writing” intended operations).

Solution 1:

You can write the missing (InsertCommand, DeleteCommand, UpdateCommand) commands manually. For my example above it’s very easy.

Solution 2:

Right mouse click on the TableAdapter of your “GenerateDBDirectMethods” with False for your view and click “Configure“. You should “Table Adapter Configuration Wizard”s “Enter a SQL Statement” part. Click ok “Previous” to go “Choose a Command Type” page in the wizard and select “Create new stored procedures” radio box and click Next. You will see “Enter a SQL Statement for the SELECT Stored Procedure” and type your “SELECT” query from the underlying table for a “start” instead of SELECT from your view and click Next. In “Create the Stored Procedures” page type meaningful names for your stored procedures and click “Next”. You will be in “Choose Methods to Generate” page and this time you will be able check “GenerateDBDirectMethods” . Clicking “Next” will result wizard to create the stored procedures for you. Click on “Finish” to close the Wizard.

Now it’s time to go to “Server Explorer” and expand “Stored Procedures” to see your newly created stored procedures. Double click on them and modify as you need for each of those command. Because “you” know what and where really needs to be done when an Insert, Update, Delete command called for this “DataTable”.