none
Hierarchical Table Example for ADO.NET - anybody have one I can inspect? - in C# RRS feed

  • Question

  • Hi,

    Does anybody have a complete simple parent - child hierarchical table using either untyped or typed datasets and involving the DataGridView control for outputting/inputting data?  Preferably using stored procedures, where the child has a relation that is a field that is the parent's primary key?

    I would like to study it, since I'm having huge problems trying to get mine to work, even though I understand the theory.

    Preferably in C# but I'll take VB.  I'm using Visual Studio 2008 if that matters.

    RC
    Wednesday, April 29, 2009 11:42 AM

Answers

  • To anybody reading this thread in the future: beware of theory.  Turns out the big problem in doing this sort of thing is 'binding' the dataset to a form.  You have to really know binding inside and out, even after you master datasets and update/delete/insert using stored procedures.

    A much better route, unless you plan to become a professional ADO programmer and/or unless you want to depend on the unreliable help of the kindness of strangers (I'm not complaining, since beggars can't be choosers), is to rely on drag-and-drop wizards,  which handle much of this behind the scenes when used with the DataGridView and the BindingNavigator toolbar. Karli Watson's book is required reading here.

    Turns out Wizards may actually be the solution to this problem.  I found a book by Karli Watson "Beginning C#2005 Databases" which walks you through how to use parent - child tables in DataGridView controls, and how you can update parent and child, and in what order to do so, using drag-and-drop functionality.  At least his examples work.  The only drawback, says the author, is that for "true" "hierarchical" relationships, where a key refers to the same table it resides in, you cannot use this drag-and-drop technique easily, though I rarely encounter such tables.  Most of the time it's a simple parent/child/grandchild table schema which the drag-and-drop Wizard handles with no problem.


    RC
    • Marked as answer by RonConger09 Friday, May 1, 2009 3:09 PM
    Friday, May 1, 2009 3:09 PM

All replies

  • //Example
    Here are the example tables {Files, Components}

    //Files table
    Columns {FileRec, FileName, Title}

    //Components Table
    Columns {CompRec, FileRec, RefDes, CompTitle}

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;

    public class DataLayer
    {
        private SqlConnectionStringBuilder sb;
        private SqlConnection conn;
        private DataSet ds;

        public DataLayer()
        {           
            sb = new SqlConnectionStringBuilder(); 
            sb.DataSource = @"JOHN\SQLEXPRESS";
            sb.InitialCatalog = "TestingDatabase";
            sb.IntegratedSecurity = true;
            conn = new SqlConnection(sb.ConnectionString);
            PopulateDataSet();
        }

        private DataTable FillTable(String tableName)
        {
             DataTable table = new DataTable(tableName);
             String sql = String.Format("SELECT * FROM {0}", tableName);
             using (SqlDatAdapter da = new SqlDataAdapter(sql, conn)
             {
                  conn.Open();
                  da.Fill(table);
                  da.Close();
             }
             return table;
        }

        private void PopulateDataSet()
        {
            DataTable files = FillTable("Files");
            files.PrimaryKey = new DataColumn[] { files.Columns[0] };
            DataTable comp = FillTable("Components");
            comp.PrimaryKey = new DataColumn[] { comp.Columns[0] };
            ds.Tables.Add(files);
            ds.Tables.Add(comp);  

             //Add relation  
                ds.Relations.Add("Files_Comp",
                    ds.Tables["Files"].Columns["FileRec"],
                    ds.Tables["Components"].Columns["FileRec"],
                    true);
        }
    }

    If this example makes sense to you, we can continue with it, if it doesn't make sense thus far perhaps you can explain a little more what you are after.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Wednesday, April 29, 2009 9:20 PM
    Wednesday, April 29, 2009 6:05 PM
  • I did this by hand so i noticed I made a mistake in forgetting the namespace.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Wednesday, April 29, 2009 6:06 PM
  • John Grove--you're very helpful.  I was actually trying to contact you but outside your work I did not have an email and I did not want to bug you for something non-work related.  I appreciate this.  Can you post more?  I just eyeballed it and it looks fine so far--I will reconstruct this inside Visual Studio, linking to AdventureWorks (I have the "Lite" version but it's about the same as the full version I think).

    So far the code looks fine, in that it will do a generic SELECT depending on the tableName input into FillTable (which you have not specified yet).

    I'll input into a Forms new project and if I have problems I'll post again...in the meantime I look forward to talking to you again soon.  Sorry I did not expect a reply (been losing hope...not too many people do ADO.NET programming compared to other things)...so I would have replied sooner.

    I appreciate it very much...btw I am not a professional, and actually could use a professional for some client projects involving this area (have bookmarked your site). (I see from another thread you never, ever use the Wizards--very wise.  I've spent the last three days battling the Wizard generated strongly typed DataSets, and for hierarchical datasets (as opposed to anything drag-and-drop read only/ single table, which you can also use the DataAdapter for) the Wizard generated code gives a false sense of mastery and is a trap...)

    RC
    ronconger@hotmail.com
    Wednesday, April 29, 2009 9:01 PM
  • /*

    Hi, JohnGrove... I got it to work, modifying it somewhat, since I had a problem with "integrated security" (You have to log on, and I only use Windows Authentication since it's a local db so no worries about security)...anyway, I set up the DataLayer class as a nested class inside of a blank form, Frm 2, is this oK?  I tested it after setting up a connection to AdventureWorks using a SqlConnection object I named "sqlConnection1AdventureWorks" (drag and drop, but it's very handy).  As you can see it works fine...

    Catch you later (thanks again)...RC

    */

     

    using System;

    using System.Collections.Generic;

    using System.ComponentModel;

    using System.Data;

    using System.Drawing;

    using System.Linq;

    using System.Text;

    using System.Windows.Forms;

    using System.Data.SqlClient;

     

    namespace AnimalPetWinApp1

    {

        public partial class Form2 : Form

        {

     

            DataLayer myDataLayer;

            public Form2()

            {

                InitializeComponent();

                

                string myConnectString = sqlConnection1AdventureWorks.ConnectionString;

                myDataLayer = new DataLayer(myConnectString);

               

            }

     

    //start of nested class to the form, “DataLayer”

          class DataLayer

        {

            private SqlConnectionStringBuilder sb;

            private SqlConnection conn;

            private DataSet ds;

     

            public DataLayer (string myConnectString)

            {

     

                sb = new SqlConnectionStringBuilder();

     

                conn = new SqlConnection(myConnectString);

                conn.Open(); 

               

                Console.WriteLine("open? {0}", conn.State); //yes, open

     

                conn.Close();

                Console.WriteLine("close? {0}", conn.State); //yes, close

        

            }

     

     

        }

            }

     

        }

     

     

    /* 
    
    Hi, JohnGrove... I got it to work, modifying it somewhat, since I had a problem with "integrated security" (You have to log on, and I only use Windows Authentication since it's a local db so no worries about security)...anyway, I set up the DataLayer class as a nested class inside of a blank form, Frm 2, is this oK?  I tested it after setting up a connection to AdventureWorks using a SqlConnection object I named "sqlConnection1AdventureWorks" (drag and drop, but it's very handy).  As you can see it works fine...
    
    Catch you later (thanks again)...RC
        }
    
    
    

     

    • Edited by RonConger09 Wednesday, April 29, 2009 9:58 PM
    Wednesday, April 29, 2009 9:03 PM
  • I forgot the connection was set to AdventureWorks, I was trying to show you a generic example from a hypothetical database.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Wednesday, April 29, 2009 9:16 PM
  • Understood about AdventureWorks.  Thanks John Grove.  If you have any objects to using a nested class, as I did above in this thread, let me know.  I forget the rules for nested classes but I think they can access the enveloping class...so it should not be a problem.  I'm signing off now (6PM EST) but I look forward to additional instruction tommorrow...at your convenience...

    Wednesday, April 29, 2009 10:04 PM
  • Looks like you are off to a good start
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Wednesday, April 29, 2009 10:12 PM
  • Thanks JohnGrove!  I would like to talk to you offline about a commercial database project with a Windows or Java front end GUI ... at some point please email me if you're interested...I'm just gathering information at this point, nothing more (I work as a technical consultant but I don't actually code for a living...just a hobby...like golf, it's great when you hit the ball just right but normally quite frustrating!)

    Goin' offline now...catch you later.

    RC
    Wednesday, April 29, 2009 10:27 PM
  • You instantiated a SqlConnectionStringBuilder yet never used it because you passed in a string.

    You should change that.

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Thursday, April 30, 2009 12:30 AM
  • "You instantiated a SqlConnectionStringBuilder yet never used it because you passed in a string." - ok, roger.  Eagerly awaiting more code today...this is great.  Last night I did study how to add stuff programmically, including stuff I already knew, like relations, datasets, but even stuff like getting schema from another table and stored parameter information at runtime (useful for debugging).
    RC
    Thursday, April 30, 2009 2:07 PM
  • I like your enthusiasm and eagerness to learn, but let me kind of clarify something with regards to forums.

    Usually in MSDN forums, you post a succinct question or a specific issue you may be having. You also provide code on something you might be stumped on so others can provide insight. They do have a forum here on asking architecture questions, which I suspect may be an option for you.

    But what we try to shun is making a forum out of a thread. I don't want to discourage you because you seem to be on fire with learning all this new stuff and it is very encouraging to me. I do want to make a recommendation to you and this is my opinion.

    Best ADO.NET books
    Programming Microsoft® ADO.NET 2.0 Core Reference
    Pro ADO.NET 2.0

    Not published yet, but I eagerly await David Sceppa's new book
    Programming the Microsoft ADO.NET Entity Framework
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Thursday, April 30, 2009 4:32 PM
  • Sounds good JohnGrove.  I look forward to any other code you may upload on the goal of constructing a hierarchical parent-child table, with or without strongly typed dataset, that binds to a dataGridView. 

    BTW, debugging my old "Schema" drag-and-drop example, that compiled but did not work, I noticed that ADO.NET is so primitive that a Stored Procedure referenced that does not exist will still compile and not even give a runtime error (it just won't work), hence:

     SqlCommand insertAnimalCommand = new SqlCommand();
     insertAnimalCommand.Connection = testConnection;
     SqlCommand insertAnimalCommand = new SqlCommand();
     insertAnimalCommand = new SqlCommand("BOGUS_NAME_HERE_WILL_COMPILE");
     insertAnimalCommand.CommandType = CommandType.StoredProcedure;

    Perhaps this made my previous code not work (I am looking into it)...but I await anything you can send my way.

    I agree with the below, though Sceppa's book Prog. MS ADO.NET 2.0 Core Ref. on Chap. 11, Advanced Updating Scenarios (2005 edition) is incomplete IMO... off to lunch now.

    RC

    Not published yet, but I eagerly await David Sceppa's new book
    Programming the Microsoft ADO.NET Entity Framework
    Thursday, April 30, 2009 4:52 PM
  • Also, see if what Rong suggest is of any help to you

    http://social.msdn.microsoft.com/forums/en-US/winformsdatacontrols/thread/8d23be94-aa2e-4e2a-99bd-eb35e3e81324/
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Thursday, April 30, 2009 5:04 PM
  • JohnGrove--Thanks for the links but they don't do for me (here's why):  the link http://msdn.microsoft.com/en-us/library/c12c1kx4.aspx deals with Northwind tables Orders and Customers, which are *not* [ARE--sorry--my mistake] a parent/child the way I see define it...but wait***.  If no parent/child w/ foreign key constraints, it makes insert/updates easy I suspect, since it's a more lax "data relation" (but I'll bookmark it to do it later, as an exercise).

    Why do I need complete code?  Because of things like this:  do I need to create a ForeignKey constraint manually (programically, using ForeignKeyConstraint), even if I create a drag-and-drop .xsd schematic?  There are some indications this is true, and, if so, maybe that's why my program is throwing an exception when I try and save--but I doubt it.  Does one thing override the other (drag and drop overrides manual insertion of ForeignKeyConstraint--I suspect the opposite is true, since the manual code gets compiled later than the drag-and-drop code?)  Also little 'gotchas' like the problem I mentioned before (stored procedure that does not exist).  Also the code for the stored procedure itself...I have an example, but want to double check it (involving the OUTPUT parameters for all keys that are to be cascaded, including, counterintutitively to me, the last key (child or grandchild) to the 'end' table, which logically does not need to be OUTPUT but in all the examples I've seen is, perhaps because it's fed back 'upstream' to its parents).

    I would be disappointed if you cannot help me...I can even pay you--what is your rate? :-)  ...as you know somebody has to be taught once in order to fish...right now it's really tough... but if it's too much of a hassle that's OK (I don't care about time...even if it takes a week of you suggesting steps to do, that I can do and post here, that would be fine...)

    Any help appreciated and thanks for far...

    RC

    *** before hitting the Send key, I notice that in fact FK does exist, like in my program, here's a screen shot: http://ronconger.angelfire.com/sqlserver/index.album/screenshot-of-parent-child-xsd-relationship?i=2&s=1  my child is "P2SelectCommand" table while the parent is "A2SelectCommand" (sorry for the ackward titles..they sound like commands)...OK, I see now.  I'm OK, they're OK.  In fact we're both fine.  Indeed the example at the link above, which is a walkthrough of this: http://msdn.microsoft.com/en-us/library/y8c0cxey.aspx , might do the trick.

    Tell you what:  a proposal--do you want me to try the walkthrough at the link: http://msdn.microsoft.com/en-us/library/y8c0cxey.aspx ?  But will you help me if I have questions?  I can post to this thread.  I really don't want to labouriously spend time and find this example is only a "read only" (which I can do already) and not an Insert/Update (write/read) example.  The language here scares me it might be 'read only':

    "To test the form  Compile and run the application.  You will see two DataGridView controls, one above the other. On top are the customers from the Northwind Customers table, and at the bottom are the Orders corresponding to the selected customer. As you select different rows in the upper DataGridView, the contents of the lower DataGridView change accordingly." [WHO CARES! I CAN DO THIS ALREADY...IT'S QUITE TRIVIAL--BUT DOES IT ALSO PERSISTENTLY SAVE CHANGES MADE BY THE USER VIA PESSIMISTIC CONCURRENCE?--RC]

    But, this link also linked to the above: http://msdn.microsoft.com/en-us/library/ykdxa0bc.aspx  (Walkthrough: Validating Data in the Windows Forms DataGridView Control) implies maybe this previous walkthrough is 'read/write'?  Is it?

    I don't want to start another example unless I'm confident it will achieve the objectives I have in mind, namely, a dataset involving parent/child...any advice appreciated!

    RC

    Thursday, April 30, 2009 6:24 PM
  • Ron,
    I am not paid from Microsoft nor am I a consultant, I do this for free when I can because I like to help out and it is a place I also learn from others. The benefit of posting your questions is that many other experienced developers get to offer share there input.

    I will offer my help if I can, but I will not monopolize the chance for others to offer their help. Trust me, many others are very experienced and helpful. That is what makes these forums so great.

    A DataSet with parent/child is rather elementary. Since a DataSet is disconnected from the data source after it has been appropriately filled, the question naturally arises, what do I want to do now? David Sceppa, since you seem to have his book discusses the DataRelation object pretty well.

    I do not know your domain and what it involves so it is difficult to help you architect an appropriate DataLayer class when I do not know what your application even does.

    If you have questions that can be addressed specifically, you should post them in short intervals, but I do not think anyone will completely write your application for them. You will have to do deliberating yourself, I cannot tutor you as the forums were not designed for tutoring, but designed for questions and specific issues as such. I hope in that regard I can help as well as others.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Thursday, April 30, 2009 6:48 PM
  • John,

    I appreciate your help.  For the last two hours I went through the example at : http://msdn.microsoft.com/en-us/library/c12c1kx4.aspx  and the "walkthrough" that links to it.  Much to my chagrin, what I discovered was this (as I suspected):

    Just as I thought, the walk through is simply exactly what it says:  it allows you to read (not write) data from a parent and child table, so that selecting a row on the split pane gridDataView of the parent will give the corresponding row to the Child. Nothing more.  I include my code below.

    But this does nothing for me--remember, the whole point of this thread is to find how you can construct a hierarchical table where saving something in the parent table row will automatically update to the corresponding child table row--that is, the PK of the parent, if it changes, will also update the child field that references this PK (for an Update), or, automatically insert the new PK of the new parent row (for an Insert).

    See here for a screenshot of the problem, note the last line:  http://ronconger.angelfire.com/sqlserver/index.album/how-to-undate-child-from-parent-id?i=3   and   http://ronconger.angelfire.com/sqlserver/index.album/how-to-undate-child-from-parent-id?i=3&s=1  (see last line "JohnHELPME").

    Keep in mind, based on my other posts, I know a lot of how to go about doing this "in theory".  The problem is, I cannot make it work in practice, hence I thought yesterday you were going to upload some code (I was pumped!).

    My tables, as can be seen from the screenshots of the previous sentence, comprise:  the parent, Animal, has two fields: "AnimalID", an int that's the PK, and "AnimalType" (VarChar(50)).  The parent used to have a 'timestamp' but I got rid of it to make the MSDN example work properly (long story).  The child, Pets, has three fields:  AnimalID (that either is a relation or a Foreign Key constraint with the parent table), PetID (int, Primary Key), and "FirstName", a varchar(50) that may be null.

    Can you help me out?  Even in words, not code. 

    Below is what I did, based on the link you provided.  What do I now have to do to 'save' this?

    As an alternative, if you decide this is too much for you to answer now, can you kindly tell me how I can get the primary key of the parent, programically?  Here is my idea:  instead of using dataGridView(s) for both parent and child updates/inserts/deletes, which is very nice to be sure, I can also construct a simple series of textboxes that accept user input, then, I can programically insert this user input information into my tables serially using "SqlCommand.Parameters.Add"...then .ExecuteNonQuery();, as you no doubt know.  Of course this is not as "elegant" as using a dataGridView, "all at once", but no big deal.  But to do this, how do I get the last new PK created of the parent table, in an efficient fashion?  That is, I can use DataReader to read the highest (i.e., last) Primary Key of the parent table (here, Animals), and then use this to input into the new FK of the child table (here, Pets), but that is not "efficient"?  Do you have something quicker, like @@INDENTITY or SCOPE_IDENTITY() as a stored procedure?  If so, please upload or suggest in words.  Again, this is not what I intended to do when I started this project 3 days ago, but at this point I'm willing to settle for this "inelegant" way of doing things, just so I can move on from this topic (I'm not going to make a living out of ADO.NET, as you might imagine).

    Thanks for reading this far.

    RC

    Code below:

    using System;

    using System.Collections.Generic;

    using System.ComponentModel;

    using System.Data;

    using System.Drawing;

    using System.Linq;

    using System.Text;

    using System.Windows.Forms;

    using System.Data.SqlClient;

    using System.Data.Sql;

     

    namespace AnimalPetWinApp1

    {

       

        public partial class Form2 : Form

        {

            private DataGridView masterDataGridView = new DataGridView();

            private BindingSource masterBindingSource = new BindingSource();

            private DataGridView detailsDataGridView = new DataGridView();

            private BindingSource detailsBindingSource = new BindingSource();

     

            public Form2()

            {

                InitializeComponent();

                masterDataGridView.Dock = DockStyle.Fill;

                detailsDataGridView.Dock = DockStyle.Fill;

                SplitContainer splitContainer1 = new SplitContainer();

                splitContainer1.Dock = DockStyle.Fill;

                splitContainer1.Orientation = Orientation.Horizontal;

                splitContainer1.Panel1.Controls.Add(masterDataGridView);

                splitContainer1.Panel2.Controls.Add(detailsDataGridView);

     

                this.Controls.Add(splitContainer1);

                this.Load += new System.EventHandler(Form2_Load);

                this.Text = "DataGridView master/detail demo";

     

            }

     

            private void button1_Click(object sender, EventArgs e)

            {

             

            }

     

            private void GetData()

            {

                try

                {

                    DataSet data = new DataSet();

                    data.Locale = System.Globalization.CultureInfo.InvariantCulture;

                    ///////

                    ///////

                    //ANIMALS --> CUSTOMERS;

                    SqlDataAdapter masterDataAdapter = new SqlDataAdapter("SELECT * FROM Animals", sqlConnection1Animal1);

                    masterDataAdapter.Fill(data, "Animals");

     

                    // PETS --> ORDERS

                    SqlDataAdapter detailsDataAdapter = new SqlDataAdapter("SELECT * FROM Pets", sqlConnection1Animal1);

                    detailsDataAdapter.Fill(data, "Pets");

     

                    data.Tables["Animals"].Columns["AnimalID"].AutoIncrement = true;

                    data.Tables["Animals"].Columns["AnimalID"].AutoIncrementSeed = -1;

                    data.Tables["Animals"].Columns["AnimalID"].AutoIncrementStep = -1;

     

                    data.Tables["Pets"].Columns["PetID"].AutoIncrement = true;

                    data.Tables["Pets"].Columns["PetID"].AutoIncrementSeed = -1;

                    data.Tables["Pets"].Columns["PetID"].AutoIncrementStep = -1;

     

                    //establish relationship

                    DataRelation relation = new DataRelation("AnimalsPets", data.Tables["Animals"].Columns["AnimalID"], data.Tables["Pets"].Columns["AnimalID"]);

                    data.Relations.Add(relation);

                    masterBindingSource.DataSource = data;

                    masterBindingSource.DataMember = "Animals";

                    // bind the details (Pets) data

                    detailsBindingSource.DataSource = masterBindingSource;

                    detailsBindingSource.DataMember = "AnimalsPets";

                   // masterDataGridView.Columns["TimeStamp"].Visible = false; //this.dataGridView2.Columns[3].Visible = false;

                  //  detailsDataGridView.Columns["TimeStamp"].Visible = false; //hide to prevent runtime exception// UPDATE: won’t work quite right—just delete timestamp fields in all tables and program now works

                }

     

                catch (SqlException ex)

                {

                    Console.WriteLine(ex.Message);

     

                }

            }

     

            private void Form2_Load(object sender, EventArgs e)

            {

                masterDataGridView.DataSource = masterBindingSource;

                detailsDataGridView.DataSource = detailsBindingSource;

                this.GetData();

                masterDataGridView.AutoResizeColumns();

                detailsDataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

            }

        }

    }

     



    Thursday, April 30, 2009 9:04 PM
  • To anybody reading this thread in the future: beware of theory.  Turns out the big problem in doing this sort of thing is 'binding' the dataset to a form.  You have to really know binding inside and out, even after you master datasets and update/delete/insert using stored procedures.

    A much better route, unless you plan to become a professional ADO programmer and/or unless you want to depend on the unreliable help of the kindness of strangers (I'm not complaining, since beggars can't be choosers), is to rely on drag-and-drop wizards,  which handle much of this behind the scenes when used with the DataGridView and the BindingNavigator toolbar. Karli Watson's book is required reading here.

    Turns out Wizards may actually be the solution to this problem.  I found a book by Karli Watson "Beginning C#2005 Databases" which walks you through how to use parent - child tables in DataGridView controls, and how you can update parent and child, and in what order to do so, using drag-and-drop functionality.  At least his examples work.  The only drawback, says the author, is that for "true" "hierarchical" relationships, where a key refers to the same table it resides in, you cannot use this drag-and-drop technique easily, though I rarely encounter such tables.  Most of the time it's a simple parent/child/grandchild table schema which the drag-and-drop Wizard handles with no problem.


    RC
    • Marked as answer by RonConger09 Friday, May 1, 2009 3:09 PM
    Friday, May 1, 2009 3:09 PM
  • Offering help and then not getting acknowledged when you do is not very encouraging to me or anyone. Marking your own thread as an answer when others have gone out of there way to explain things to you is also not helpful. I have tried to explain to you best as I could without making this thread into its own forum that these forums were not designed to give you the type of help you are after.

    Unreliable help from strangers is also a disparaging remark.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Friday, May 1, 2009 3:17 PM
  • Hey John Grove--no offense meant--don't take it so hard.  I'm not knocking you.  Honest.  But to be fair, the day before yesterday you led me to believe you were going to walk me through an example 'manually' involving SqlDataAdapters--and you backed out.  I thought perhaps your employer was discouraging you--or maybe you're lazy.  Who cares.  I'm not complaining--like I said, beggars can't be choosers.  I appreciate your help but note you didn't even answer my previous question about whether you can use SqlCommand to insert data 'serially' and then return the parent PK manually.  That would have been helpful.  I don't want to make this a back and forth drama, so let's just let this thread die unless you have something constructive to add. 

    Like I said, Karli Watson's book examples are working.  I don't care they are drag-and-drop.  They work, and he has complex "hierarchical tables".  He specifically says Drag and Drop does not work for 'self referencial' tables where the PK references the same table the PK resides in, but, like I said before, this kind of schema is rare (to me). 

    Finally, data binding is crucial as well.  I have a "manual" sqlDataAdapter example from the ADO.NET cookbook by O'Reilly, but it's "Console Mode".  Won't do for Forms, because you have to also bind the control to the adapter, and that involves some work.  Better to rely on drag-and-drop (unless you are lucky enough to have somebody walk you through an example--maybe your cubicle next door neighbor at work--but that's not you, apparently).

    BTW, if you send me a complete simple sample that works in Forms, and if I understand it, I would have (and still am willing to) send to a C-note for your effort, by money order.  I don't care about drama--I just wanted a little more control over 'drag-and-drop' functionality, but I guess I won't get it.  Too bad, but I'm not learning this for anything except ASP.NET (which BTW Watson has a chapter on).

    Thanks for your effort, and I hope you won't stop replying to any future questions I may have in this forum, but that's up to you.

    RC
    Friday, May 1, 2009 4:03 PM
  • "Maybe I am lazy"?

    You said you have David Sceppa's book. Leverage that, don't expect others to write your code for you, as I have said now three times, these forums are not about that. Helping others does not necessarily mean giving what they want but helping them get it themselves.

    I have presented you with a core class you could start with which establishes the connection and a way to fill your tables and make joins. If you have a specific question ask it, otherwise don't ask me or others to architect your DataLayer class. Don't be helpless.

    It seems you are the one who doesn't want to take initiative, that is the reason and only reason I have "backed off". If someone expects others to fo their work for them or offer "incentives" like a cash reward, then that is not help, that is "doing their work".

    Maybe others are interested in that, but I am not. Particularly so because you are resorting to remarks that are less then edifying to say the least because you are not getting your way.

    Note the title of your thread is "Hierarchical Table Example for ADO.NET - anybody have one I can inspect?" I did provide you an example and offered you also a link. End of story. The issue is that you have multiple questions you expect answered in a single thread. 

    "I appreciate your help but note you didn't even answer my previous question about whether you can use SqlCommand to insert data 'serially' and then return the parent PK manually. 

    That has nothing at all to do what this thread started out with. It has evolved to this.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Friday, May 1, 2009 4:25 PM
    Friday, May 1, 2009 4:12 PM
  • Your links John Grove were not relevant for the reasons I stated.  What is relevant, for purposes of anybody else struggling with this issue, is that as outlined in Karli Watson's book in this thread, drag-and-drop functionality is working.  Some drawbacks I noted earlier already about self-referential tables, and now that I'm going through some of his examples, they also include you have to cast rows rather dramatically (see example below), and you have to be able to figure out how to 'bind' a certain control (combo box, list box, griddataview, but not listbox view)--certain controls can bind, others cannot (i.e. ListBox), but most of the "good" or useful controls can bind to your datasource by clicking on a drop-down box, as explained by Watson.  Also Watson explains you have to lay out your tables properly (i.e., avoid 'self-referential' tables, which don't work with drag-and-drop) and further Watson seems to use GUIDs as PK's a lot, I suspect for good reason, though I don't see why INT will not also work, though I'm sticking with GUIDs since they don't run into the AutoIncrement resulting in the same number problem as easily as INTs, hence I think the reason GUIDs are used in drag-and-drop.  There's a small performance penalty from using GUIDs I've read, but the simplicity they introduce is probably well worth it.

    Drag-and-drop is very easy, very intuitive, and it just works, unlike the manual method.

    In a later chapter, which I have not gotten to yet, Watson explains Update, Delete, Insert, Add.  Just like in the manual mode, you have to make sure your tables schema is respected (i.e., generally good to delete first, in 'down-top' order, then, in 'top-down' order,  insert, then add, then Update.  But this is the same as in the 'manual' mode with the difference that in the drag-and-drop the function .GetChanges() method is used, rather than the more complicated .Select method. 

    In short, I got what I wanted from Watson.  Thanks for any responses, lazy or not.   I consider this thread closed.

    If anybody has a homegrown 'manual' way of doing these kind of parent-child updates, I'm still interested, please email me, though I won't pay money now that I can get what I want with drag-and-drop.

    RC

    //example of the heavy casting involved when getting row information for a _CellClick handler, but it's worth it.

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)

    {

               

                DataRow myDataRow;

                DataRowView myDataRowView;

     

                myDataRowView = ((DataRowView)(dataGridView1.CurrentRow.DataBoundItem));

     

                myDataRow = myDataRowView.Row;

     

                string s = ((FolktaleDBDataSet6Tables.StoryRow)myDataRow).Summary; //  messagebox “summary” column displayed

            MessageBox.Show(s, "CaptionHere", MessageBoxButtons.OK);

     

            Console.WriteLine("Columns of row selected");

            Console.WriteLine("{0},{1},{2},{3},{4}", ((FolktaleDBDataSet6Tables.StoryRow)myDataRow).StoryId, ((FolktaleDBDataSet6Tables.StoryRow)myDataRow).EndingId, ((FolktaleDBDataSet6Tables.StoryRow)myDataRow).ClassificationId, ((FolktaleDBDataSet6Tables.StoryRow)myDataRow).SourceId, ((FolktaleDBDataSet6Tables.StoryRow)myDataRow).Name);

     

    Console.WriteLine(“This is the row number…”);

    Console.WriteLine("{0}", e.RowIndex.ToString());

     

    }

    Friday, May 1, 2009 9:58 PM
  • A forum is not the place for theoretical detailed explanations that require 30 separate answers to end the thread. We already went 16 and you still wanted more. [And this is after calling me helpful in multiple places and still not getting recognized point wise].

    Since you are newer to the forums then myself, you should at least consider what I say, rather then to make it a point to be disappointed because someone didn't take the time to give you step by step instructions and provide on site support in a single thread.

    That is not me being lazy, it is you not respecting the nature of the forums and how they work. It is also your project so it is your responsibility. It is clear who the lazy one is here.


    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Saturday, May 2, 2009 7:58 PM
  • OK I gave you a "helpful", if that was your point (I wasn't clear what exactly was your point...I'm getting lazy again?).

    Now please consider this:

    what is the future of Entity Framework?  And what does that mean to the traditional "Cobbs" way of using relational databases, and using DataSets?  I say this after skimming through "Programming Entity Framework" by Julia Lerman.

    Seems like the "drag and drop" approach of Karli Watson is the future (because EF sure looks very OO and drag/drop to me).

    I'll see if I can post this in a separate thread (since you seem to not like long threads) if there's a place for it ...

    RC
    Sunday, May 3, 2009 10:03 PM
  • Drag and drop is cool, but for enterprise applications, few professional developers rely upon them with ADO.NET. It is true that they are coming a long way with the drag and drop features that is commendable. But all my applications that I have developed, I have never used them. I do use them to build quick and dirty examples as such, but for the flexibly I usually need, I like to do everything in code. That is my preference because I like to know exactly every piece of what my application is doing and how it is architected.

    Some new developers rely upon the drag and drop so much that we see they really do not know what is happening under the hood and we see questions on an almost daily basis as a result because they don't know exactly how to fine tune a TableAdapter. Some do.

    Some prefer DataAdapter's like me, some don't.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Sunday, May 3, 2009 10:31 PM
    Sunday, May 3, 2009 10:27 PM
  • By the way it is Codd, not Cobb

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Monday, May 4, 2009 1:48 PM
  • After reading this thread John I think you have the patience of a saint.

    • Proposed as answer by JohnGrove Wednesday, June 3, 2009 1:54 AM
    Tuesday, June 2, 2009 12:50 PM
  • Perhaps, but John's way and I bet yours, is doomed to the dustbin of history.  Like I say, the Entity Framework, which is derived from the drag-and-drop technique as found in Karli Watson's book, is the future.

    Good luck with your traditional programming, which like Cobol might make sense for legacy projects.

    MS
    Monday, August 24, 2009 7:16 PM
  • Two abusive posts in one thread. Are you trying to break a record in insults? Does it make you feel better by doing that?

    I have been playing with the Entity Framework since its beta stage. ADO.NET is hardly obsolete yet. If it was, what are you doing in its forums?



    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Monday, August 24, 2009 7:24 PM
  • Us "dustbin" coders will be writing code long after the "drag and drop" folks get bored or flustered. I often like to remind the younger developers that are enamored with all the new toys, most of those toys are written by older coders that don't use drag and drop.

    John you must have been a teacher or pastor in a previous life. :)
    Monday, August 24, 2009 7:47 PM
  • One of the reasons I suggested writing code was because I wanted Ron to understand what he was doing. And in the process learn a thing or two about ADO.NET and DataBinding. A lesson not only did he not learn but I get insulted for suggesting it.

    But it turned out, he wanted me to do his entire project for him or at least walk him step by step every step of the way. I tried to explain the forums are not for that.

    Ron, though you insulted me without any warrant, I hold no grudges against you. I only can hope that you develop the ambition to  learn any subject you endeavor from top to bottom.

    By the way thanks Michael, I always enjoy your posts as you are quite an advanced "database guy".
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Monday, August 24, 2009 7:54 PM
  • Thank you sir.
    Monday, August 24, 2009 7:57 PM
  • You are very righteous JohnGrove, but that only gets helpful post points in your mind, not in actuality.  Actually what happened in the thread you mention was that you implied you would walk me through step by step for ADO.NET (console mode), apparently had second thoughts the next day, declined, then I did it myself using Karli Watson's book using drag-and-drop.  Oh and I did also do it using parameterized queries.  Thanks for nothing, but like I say beggars cannot be choosers.

    Yes, forums are not for learning, books are.  But once in a while you do get helpful advice (maybe even from you?) on these boards.  I have found that typically you'll get a reply to relatively simple questions, and ADO.NET without Entity Framework is not simple, user friendly or robust IMO, hence the dearth of helpful replies here.  Compare with the other Microsoft boards and the volume of replies.

    Glad to hear you have been playing around with EF.  Think of it as your meal ticket going away, as thousands of teen programmers offshore learn to do things the EF way and put you and your traditional pals out of business.  But that will be 5 - 10 years down the road, so not to worry.

    Sunday, August 30, 2009 9:35 PM
  • Chris,
    Can you lock this post since it has been so abusive?
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Sunday, August 30, 2009 9:43 PM
  • In the future, here is a link that will help you understand forum etitquette

    http://www.catb.org/~esr/faqs/smart-questions.html#disclaimer
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Thursday, September 3, 2009 6:36 PM
  • Gotta have the last word, eh JohnGrove?

    LOL. 

    Sunday, September 6, 2009 7:24 PM
  • I originally subscribed to this thread out of interest in the issue. It is a shame that it has thrown off more heat than light. I intend to add one last comment (after which I will unsubscribe from this thread) in the hope that something helpful may be learnt.

    Although I hang out in these forums I run a reasonable sized technology company. One of the things my managers do before hiring anybody is to study what they get up to in forums like these, I am sure that most other quality Tech businesses do likewise.
    Frankly Ron, John Grove has behaved impeccably, you on the other hand may want to consider keeping quiet about your online pseudonym before you next look around.

    Monday, September 7, 2009 9:39 AM
  • I wanted to agree with 0748Paul and Johngrove. I too am a professional software developer and engineer. Whereas there are always fads of the lifesaving kind, all of the fads fairly rapidly die out. I have never walked away shaking my head after having a conversation with John. I approve of his methodology invariably. What they will find after a while is that it's not the fancyness of todays trick that gets you through. It's the wise application of tried and true technology which John has mastered.
    Renee
    Wednesday, September 9, 2009 4:00 AM
  • Thank you for those kind words.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Wednesday, September 9, 2009 4:24 AM
  • I wanted to agree with 0748Paul and Johngrove. I too am a professional software developer and engineer. Whereas there are always fads of the lifesaving kind, all of the fads fairly rapidly die out. I have never walked away shaking my head after having a conversation with John. I approve of his methodology invariably. What they will find after a while is that it's not the fancyness of todays trick that gets you through. It's the wise application of tried and true technology which John has mastered.
    Renee

    Well Renee, that's very well and good, but COBOL is also fiercely defended, as is structured programming.  Truth is, languages evolve, and the way that John and you traditionalists code in ADO is (or appears to me to be) reminescent of structured programming vs the more modern OOP.

    As for 0748Paul (born in July 1948 I take it, meaning he's close to retirement), I am more likely to be a boss than a worker, and frankly I would hire (and in my consulting capacity have hired) a 17 year old coder out of Bangladore rather than a tried and true but set in their ways older US programmer, at a fraction of the cost and nearly the same quality, but that's just me.

    Good luck with your career.  I code for fun, not money, as I've found the real money is elsewhere (away from foreign competition I might add!)

    RC
    Sunday, October 18, 2009 2:48 PM
  • I wanted to agree with 0748Paul and Johngrove. I too am a professional software developer and engineer. Whereas there are always fads of the lifesaving kind, all of the fads fairly rapidly die out. I have never walked away shaking my head after having a conversation with John. I approve of his methodology invariably. What they will find after a while is that it's not the fancyness of todays trick that gets you through. It's the wise application of tried and true technology which John has mastered.
    Renee

    Well Renee, that's very well and good, but COBOL is also fiercely defended, as is structured programming.  Truth is, languages evolve, and the way that John and you traditionalists code in ADO is (or appears to me to be) reminescent of structured programming vs the more modern OOP.

    As for 0748Paul (born in July 1948 I take it, meaning he's close to retirement), I am more likely to be a boss than a worker, and frankly I would hire (and in my consulting capacity have hired) a 17 year old coder out of Bangladore rather than a tried and true but set in their ways older US programmer, at a fraction of the cost and nearly the same quality, but that's just me.

    Good luck with your career.  I code for fun, not money, as I've found the real money is elsewhere (away from foreign competition I might add!)

    RC

    Funniest thing I have read in a while. Your quality statement is quite frankly a blatant error.

    Formerly MichaelTx
    Monday, October 19, 2009 2:11 PM
  • Dear Ron,

    I am an OS engineer and knew Grace Hopper who invented cobol. I NEVER thought much of the lsnduage, not because Grace invented it but because it was a business language and I don't think much of business people because they want a simple world. The world isn't simple and there no reason why computers should be.

    Renee
    Monday, October 19, 2009 9:01 PM
  • And Ron. I don't value people who are driven by money as I see them as part of the problem rather than then solution.

    Renee
    Monday, October 19, 2009 9:06 PM
  • I wanted to agree with 0748Paul and Johngrove. I too am a professional software developer and engineer. Whereas there are always fads of the lifesaving kind, all of the fads fairly rapidly die out. I have never walked away shaking my head after having a conversation with John. I approve of his methodology invariably. What they will find after a while is that it's not the fancyness of todays trick that gets you through. It's the wise application of tried and true technology which John has mastered.
    Renee

    Well Renee, that's very well and good, but COBOL is also fiercely defended, as is structured programming.  Truth is, languages evolve, and the way that John and you traditionalists code in ADO is (or appears to me to be) reminescent of structured programming vs the more modern OOP.

    As for 0748Paul (born in July 1948 I take it, meaning he's close to retirement), I am more likely to be a boss than a worker, and frankly I would hire (and in my consulting capacity have hired) a 17 year old coder out of Bangladore rather than a tried and true but set in their ways older US programmer, at a fraction of the cost and nearly the same quality, but that's just me.

    Good luck with your career.  I code for fun, not money, as I've found the real money is elsewhere (away from foreign competition I might add!)

    RC

    Funniest thing I have read in a while. Your quality statement is quite frankly a blatant error.

    Formerly MichaelTx

    Well I don't have your experience MichaelTx--I've only subcontracted programming a handful of times.  So are you are saying the quality of software engineers overseas is nearly the same or better than in the USA? 

    Tuesday, October 20, 2009 6:12 PM
  • And Ron. I don't value people who are driven by money as I see them as part of the problem rather than then solution.

    Renee

    Well you are a wealth of experience Renee if you knew Grace Hopper.  And in this field of programming, with foreign experience coming online, you won't soon have a problem with people driven by money.  In my field, well, let's just say it's a bit more money driven.  But here I code for fun so I guess I'm part of the solution according to you.

    Happy coding!
    Tuesday, October 20, 2009 6:15 PM
  • Lesser quality in general. There are exceptions. The disingenuous idea that hiring a 17 year old developer ( I'll venture further to say 17-23ish ) from any country who can understand complex algorithm and design issues pertaining to enterprise systems is laughable. I am not talking about some 3 data table, 5,000 lines of code room reservation system, but 100,000+ lines of code systems.

    Most of the coders I have worked with that are either non-educated or self-educated offshore tend to code in a hit and miss style. They end up having to work twice as many hours in a day just to make things work and to hit a deadline and project owners mistakenly see that as hard work. They code by failure and retry. I've seen them be handed a project and spend 5 minutes thinking and start writing code.  I'll take someone with a classic math based education and some years of real-world experience who can think out their projects ahead of time. They may cost more salary wise but you save in the back-end of avoiding to spend several cycles fixing bugs or re-desiging just to get it to work.

    We had several of our clients who thought they could reproduce our product by contracting in the subcontinent, needless to say they are back after 2-3 years and several millions wasted and have nothing to show for it but white paper. We have also gambled and hired a couple developers, who were self-trained ( we call them garage coders ) who new the buzzwords and they failed to produce because they did not have the educational background or experience on a real team to grasp more complex design issues, not to mention being able to comunicate both verbally and in writing. They can design the heck out of a game UI, iPhone app, or they have grand ideas that they cannot implement, but in the end they need to be carried or fail.

    Wednesday, October 21, 2009 2:03 PM