none
DataSet Designer & Stored Procedures Filling Tables/Queries

    Question

  • Well,

    First off, these are rather complex multi-database queries, so i've handled them in many different fashions.  I have my own scheme for programmatically loading them, but I wanted to ask a few questions regarding how the DataSet Designer handles DataTable filling from stored procedures. 

    I basically looked over my VB Code and figured that I could probably (and more efficiently) effect my desired loading by incorporating all of the cross database joins (yes I know those aren't real, i've just coded some unique methods of creating the relationships so please ignore that for the purposes of this question). 

    So the application runs and allows the user to affect data as is pertinent to this applications design (as part of a 4 application suite for a client).  At certain points of execution the user will wish to "Export" specific data into an Excel Spread Sheet, which I effect via Interop Automation.  This part is done and works just fine.  At the point of exporting there are two different types of export listings, for an "Auto Booking" process and a "Shipping Manifest".  They have similar data, but the Manifest has fewer columns.  Previous I was conditionally loading rows from the specific tables and gleaning the data I wanted into arrays that could be easily transposed into the Excel Tables. 

    now, I'm looking at instead of handling 4 or 5 different loading processes from the database, I could execute a singular Stored Procedure that would fill a DataTable (actually a view in the Database) with the pertinent data for the condition criteria.  This is the point of contention that I am curious about:

    Stored Procedure:

    Parameters: @IsBooking bit, @StartDate, @EndDate

    The IsBooking flag is the key value for this question.  As 0 the stored procedure performs a SELECT * FROM qry_Items, as 1 the stored procedure performs a SELECT * FROM qry_ItemsAndQuantities.  as you can see the Singular stored procedure returns two different View results.  one has 10 columns the other has and additional 10 columns. 

    The Designer:

    When I drag/Drop the stored procedure onto the designer it creates a "DataTable" with a TableAdapter and by investigating the properties of the TableAdapter I find the StoredProcedure is Defined as the SelectCommand as opposed to an inline SQL statement Select blah, blah, blah from blah. 

    1. I am thus questioning, the nature of the Designer's intelligence?? herewith regards to what happens when I change that @IsBooking flag, it's a different table result.  Since at IsBooking=0 there are only 10 of the 20 columns listed in the DataTable returned by the Stored Procedure, but as IsBooking=1 all 20 are returned, will running the "Fill" method of the adapter simply leave the "extraneous" columns blank (dbnull)?

    2. If the above will cause issues, meaning, that flagging IsBooking=0 will cause a missing column error or some such glitch, how can I tell the designer to "Ignore" the table data returned by the Stored Procedure and simply create the "QueryAdapter" for it so as to manually fill the datatables as I choose.  Currently I drag/drop the stored proc onto the designer and it autocreates the Datatable, I might just want to create a StoredProcedure call with no datatable.  Can I tell the designer to do it that way even though the stored procs return table data?

    3. The Return results of the Stored Procedure fit into the qry_Items and qry_ItemsAndQuantities DataTables as they are defined already in the Dataset.  (Meaning I have those two Views are already defined in the Dataset designer with Select XXX TableAdapter Commands as opposed to the Stored Procedure).  Now I know I can manually execute a StoredProcedure to fill those views regards of the associated adapters, but, can I attach "Multiple" adapters to one DataTable.  Basically, qry_Items would have the qry_ItemsTableAdapter, which has the Fill() method using the SELECT * statement, but also have a ExportByDate() method for the ExportByDate Stored Procedure which would also fill the qry_Items dataTable?

    Thanks

    Jaeden "Sifo Dyas" al'Raec Ruiner

     


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Friday, March 11, 2011 11:06 PM

Answers

  • When I drag/Drop the stored procedure onto the designer it creates a "DataTable" with a TableAdapter and by investigating the properties of the TableAdapter I find the StoredProcedure is Defined as the SelectCommand as opposed to an inline SQL statement Select blah, blah, blah from blah. 


    Then don't drag-and-drop things into the Designer.  Build your own .xsd's and Datasets, then use them as you wish in your apps.  Bonnie on this forum has a great series of posts about this and on her blog:

    http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html

    I know once I implemented this approach, it allowed for more flexibility in app design/architecture IMO.  Without knowing the exact details of your SProc's and specific requirements, you might be better off re-working the architecture and have seperate SProc's do their own things, but control your IsBooking flag on the app side rather than in a single SProc.

     

    james


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    • Marked as answer by JaedenRuiner Friday, March 18, 2011 8:48 PM
    Thursday, March 17, 2011 5:40 PM

All replies

  • When I drag/Drop the stored procedure onto the designer it creates a "DataTable" with a TableAdapter and by investigating the properties of the TableAdapter I find the StoredProcedure is Defined as the SelectCommand as opposed to an inline SQL statement Select blah, blah, blah from blah. 


    Then don't drag-and-drop things into the Designer.  Build your own .xsd's and Datasets, then use them as you wish in your apps.  Bonnie on this forum has a great series of posts about this and on her blog:

    http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html

    I know once I implemented this approach, it allowed for more flexibility in app design/architecture IMO.  Without knowing the exact details of your SProc's and specific requirements, you might be better off re-working the architecture and have seperate SProc's do their own things, but control your IsBooking flag on the app side rather than in a single SProc.

     

    james


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    • Marked as answer by JaedenRuiner Friday, March 18, 2011 8:48 PM
    Thursday, March 17, 2011 5:40 PM
  • Ah,

    pretty much what i've been doing, editing the xsd manually, as I know the layout and tags/attributes, but I try not to mess with the format too much as I have my own Generator (Instead of MSDataSetGenerator) as the custom tool for Datasets which creates my own dataset/datatable/tableadpater suite for each dataset.  I've come up with an added plan that basically I have to write some manual code in the partial extended class for the QueriesProcAdapter, which isn't too difficult and so far it works just fine.

    Thanks for the info,

    J"SD"a'RR


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Friday, March 18, 2011 8:48 PM
  • Thanks for the plug for my blog, James. I was just about to suggest the same thing you did (along with a link to that same blog post of mine).
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, March 19, 2011 11:48 PM
  • Hello Jaeden,

    Thanks to all who responded. The designer tools are meant for demo applications, not production code and has its limitations. When writing production code we recommend that you do not use designers but write your own code.

    I do want to attempt to answer your questions more specifically though.

    1. I am thus questioning, the nature of the Designer's intelligence?? herewith regards to what happens when I change that @IsBooking flag, it's a different table result.  Since at IsBooking=0 there are only 10 of the 20 columns listed in the DataTable returned by the Stored Procedure, but as IsBooking=1 all 20 are returned, will running the "Fill" method of the adapter simply leave the "extraneous" columns blank (dbnull)?

    Yes you are correct here. The columns will be returned but will have the value of System.DBNull.

    2. If the above will cause issues, meaning, that flagging IsBooking=0 will cause a missing column error or some such glitch, how can I tell the designer to "Ignore" the table data returned by the Stored Procedure and simply create the "QueryAdapter" for it so as to manually fill the datatables as I choose.  Currently I drag/drop the stored proc onto the designer and it autocreates the Datatable, I might just want to create a StoredProcedure call with no datatable.  Can I tell the designer to do it that way even though the stored procs return table data?

    No you cannot. The designer makes that decision.

    3. The Return results of the Stored Procedure fit into the qry_Items and qry_ItemsAndQuantities DataTables as they are defined already in the Dataset.  (Meaning I have those two Views are already defined in the Dataset designer with Select XXX TableAdapter Commands as opposed to the Stored Procedure).  Now I know I can manually execute a StoredProcedure to fill those views regards of the associated adapters, but, can I attach "Multiple" adapters to one DataTable.  Basically, qry_Items would have the qry_ItemsTableAdapter, which has the Fill() method using the SELECT * statement, but also have a ExportByDate() method for the ExportByDate Stored Procedure which would also fill the qry_Items dataTable?

    The datatable is not bound to the adapter until you call Fill which will not happen until runtime. At this point you can use conditional statements to Fill it any adapter you wish unless you are using Typed datasets.

     

     

    I hope this information is useful.

     


    Terrell An -MSFT
    Monday, March 21, 2011 2:21 PM