8 Replies - 5331 Views - Last Post: 09 May 2013 - 11:10 AM

#1 AndrewMack  Icon User is offline

  • New D.I.C Head

Reputation: 7
  • Posts: 42
  • Joined: 12-April 13

[ADO.NET] - Diconnected Model, Unique IDs, and Parent/Child relations

Posted 08 May 2013 - 10:55 AM

Hi there!

** DISCLAIMER (sort of) : long(er) post > main question/s found in and following the list of events/scenario marked [THE SCENARIO]



I'm in the process of teaching myself C# all over again (I develop using WinDev at work :wacko:) and I have a question concerning the disconnected model of ADO.NET.

I have a small pet project that I'm working on as I move through my book (Pro C# 5.0 and the .Net 4.5 Framework by Andrew Troelsen) which is essentially a small project tracker which allows me to create a new project and assign tasks to the project and child tasks to those tasks.

Here's my question: When I create a new Project I need its unique ID to assign child Tasks to that Project; however, I don't want to have to connect to the database to retrieve that unique ID. Does ADO.NET have anything to help me with this? - I'm guessing I'm asking for too much but could it somehow know that the records are NEW records and know that when it inserts the new Project record into the Projects table (and gets a new unique ID assuming someone else connected to the database and inserted a new Project record using the ID assigned to the Project I created) that it will then change the Project ID value of the Tasks I created to the new ID?


[THE SCENARIO]

- Jerry gets onto the application and gets the data set. Next Project ID in the sequence is 100.

- Tom gets onto the application and gets the data set. Next Project ID in the sequence is 100 (still).

- Jerry creates a new Project (ID 100) and creates new tasks assigned to that Project (ID 100)

- Tom also creates a new Project (ID 100) and creates some new tasks assigned to that Project (ID 100)

- Tom saves his work to the database. All is well with the world - everything goes okay.

- Jerry saves his work to the database; however, his Project is given the ID 101 instead of 100 as the ID 100 was used!

- *** NOW *** ??? Will ADO.NET know to also assign the new Tasks Jerry made to Jerry's Project (ID 101) or will his Tasks remain assigned to Project ID 100 (the Project that Tom made and saved)?


I probably shouldn't even be thinking about this, eh? - why not just keep it all in memory (a Project class has an array/list/collection of Tasks. If the Project's ID property is NULL then it's new and needs to be inserted (same with its Tasks) and just create a method to do the logic of inserting to the database and handling assigning IDs myself)? Maybe save an XML file if they close without saving to the database so they can pick up from where they left off with no work/data lost?


Too long? - sorry :( I've been working using the connected model for a couple years now @ work (that's how they want it) I'm finding it a little odd making the transition to disconnected processes.

If you actually read all that - THANK YOU! ANY help (even "you're an idiot why are you doing it like that!?" is helpful, really, if it's true of course haha)

Is This A Good Question/Topic? 0
  • +

Replies To: [ADO.NET] - Diconnected Model, Unique IDs, and Parent/Child relations

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9210
  • View blog
  • Posts: 34,592
  • Joined: 12-June 08

Re: [ADO.NET] - Diconnected Model, Unique IDs, and Parent/Child relations

Posted 08 May 2013 - 12:49 PM

Quote

Here's my question: When I create a new Project I need its unique ID to assign child Tasks to that Project; however, I don't want to have to connect to the database to retrieve that unique ID. Does ADO.NET have anything to help me with this?



No.. you need to rearrange your thinking. If your database holds your unique information and Ids then you *need* to insert a record into the 'project' table to get said ID. What's the aversion for making this quick insert/return call?


Quote

I'm guessing I'm asking for too much but could it somehow know that the records are NEW records and know that when it inserts the new Project record into the Projects table (and gets a new unique ID assuming someone else connected to the database and inserted a new Project record using the ID assigned to the Project I created) that it will then change the Project ID value of the Tasks I created to the new ID?


Sure.. but these are discrete tasks, right? That is unless you are going to do some sort of bulk send to a stored procedure.. but eventually your app will need to marry the new project id to the task ids, right?



Quote

[THE SCENARIO]

- Jerry gets onto the application and gets the data set. Next Project ID in the sequence is 100.

- Tom gets onto the application and gets the data set. Next Project ID in the sequence is 100 (still).

- Jerry creates a new Project (ID 100) and creates new tasks assigned to that Project (ID 100)

- Tom also creates a new Project (ID 100) and creates some new tasks assigned to that Project (ID 100)

- Tom saves his work to the database. All is well with the world - everything goes okay.

- Jerry saves his work to the database; however, his Project is given the ID 101 instead of 100 as the ID 100 was used!

- *** NOW *** ??? Will ADO.NET know to also assign the new Tasks Jerry made to Jerry's Project (ID 101) or will his Tasks remain assigned to Project ID 100 (the Project that Tom made and saved)?


No.. this isn't some sort of T-1000 AI unit here.. you are the developer and you need to work these kinks out. How does the app know what project number is available when it loads each time? A call to the DB, right?

What should occur is both open up the app, the app makes a hit to the DB to find the next available project number, and tells them both it's available. Both go about their business entering data. When Jerry saves - the save method would call a stored procedure with what ever project id value, check to see if that is available, and insert returning the project id value. The app catches this returned value and then uses it as a parameter value for the subsequent tasks..

Now Tom has that same value and goes to save.. the save method calls the 'insert project' stored procedure (just like jerry), but when the procedure checks to make sure the project number is still available it finds it used.. so it must find the next available value (much like when the app starts it calls the db and gets the next available value).. inserts the project id, and returns the new project id. Since this id is different the project id (in the GUI) updates to reflect it, and (as with jerry) the id is used to insert tasks.

See - collision avoided!

Quote

Maybe save an XML file if they close without saving to the database so they can pick up from where they left off with no work/data lost?


Why not prompt to save?
Was This Post Helpful? 1
  • +
  • -

#3 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5529
  • View blog
  • Posts: 11,839
  • Joined: 02-June 10

Re: [ADO.NET] - Diconnected Model, Unique IDs, and Parent/Child relations

Posted 08 May 2013 - 12:55 PM

Question 1:

Quote

Here's my question: When I create a new Project I need its unique ID to assign child Tasks to that Project; however, I don't want to have to connect to the database to retrieve that unique ID.

Then where do you think it is going to come from?
If you kept a number someplace else, like the registry then you have to keep them synchronized, which is never guaranteed. All it takes is one crash between writing to the database and writing to the secondary location and they get out of synch. What is the problem with doing a simply query to the database for the next project ID?

Question 2:

Quote

  • Jerry gets onto the application and gets the data set. Next Project ID in the sequence is 100.
  • Tom gets onto the application and gets the data set. Next Project ID in the sequence is 100 (still).

That sounds like the first frak-up in the logic to fix. As soon as the new ID is issued it should not be re-issued.

Question 3:

Quote

Maybe save an XML file if they close without saving to the database so they can pick up from where they left off with no work/data lost?

Sounds reasonable. Just serialize your 'project' class.

This post has been edited by tlhIn`toq: 08 May 2013 - 12:56 PM

Was This Post Helpful? 1
  • +
  • -

#4 AndrewMack  Icon User is offline

  • New D.I.C Head

Reputation: 7
  • Posts: 42
  • Joined: 12-April 13

Re: [ADO.NET] - Diconnected Model, Unique IDs, and Parent/Child relations

Posted 08 May 2013 - 01:57 PM

Oh wow! - thanks, guys! I wasn't sure if anyone would really read all that.

I thought I was hoping for too much from ADO.NET haha thanks for the confirmation!

I was hoping to be able to allow "Jerry" to work on creating his stuff or have it sit for a day or 2 before applying/saving it to the database. While that doesn't make sense at all for such a simple task it would help me get the logic right and find all the hiccups in the process before I tackle any sort of "real" project that made use of the Disconnected Model.

I certainly understand what you guys are saying as that's pretty much how a typical "Add Record" process is performed @ work(in its most basic sense). I'm looking for a way to work with near-full functionality without an internet connection. What sucks about having it all in memory is that if his computer crashes (or my application crashes! *oops!*) then he'd be SOL unless I'm maintaining an XML document or something similar... right?

Again - thank you guys VERY much for your help! Really appreciate your time, effort, and knowledge!
Was This Post Helpful? 0
  • +
  • -

#5 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5529
  • View blog
  • Posts: 11,839
  • Joined: 02-June 10

Re: [ADO.NET] - Diconnected Model, Unique IDs, and Parent/Child relations

Posted 08 May 2013 - 02:08 PM

Personally I think you're making too much work for yourself based on the idea of not submitting incomplete items to the database. I don't know why that is so previlant in your design.

Just add a bool column for "released" or "completed". Then incomplete items are marked as such. Its a lot easier to filter by that then to write two completly different schemes for storing the same information.

Besides, you always have to think about the future. What if Jerry is 90% done with 3 projects then has a heart attack? His work has to be available for someone else to pick up and complete. So having it on the database instead of a local XML document makes that possible.

Of course you could just keep things on a local database when off-line, then synchronize when an internet connection becomes available. Then you still don't have two different schemes: Just two different connected addresses.
Was This Post Helpful? 1
  • +
  • -

#6 AndrewMack  Icon User is offline

  • New D.I.C Head

Reputation: 7
  • Posts: 42
  • Joined: 12-April 13

Re: [ADO.NET] - Diconnected Model, Unique IDs, and Parent/Child relations

Posted 09 May 2013 - 05:07 AM

Yea, I see what you're saying. It's not that I don't want an incomplete Task or Project in the database - it's just that I was hoping to allow the User to have control over when that (updating the database with the User's new/modified data) would actually happen. In which case syncing a local database and the server database would potentially work well. I'll have to talk to the DB Admin here at work to get a few tips!

Thanks a lot! Have a good one, eh!
Was This Post Helpful? 0
  • +
  • -

#7 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5529
  • View blog
  • Posts: 11,839
  • Joined: 02-June 10

Re: [ADO.NET] - Diconnected Model, Unique IDs, and Parent/Child relations

Posted 09 May 2013 - 08:54 AM

Ran across this. maybe it will help.

http://csharp-statio...DotNet/Lesson05
Was This Post Helpful? 1
  • +
  • -

#8 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5832
  • View blog
  • Posts: 12,686
  • Joined: 16-October 07

Re: [ADO.NET] - Diconnected Model, Unique IDs, and Parent/Child relations

Posted 09 May 2013 - 10:09 AM

For such a scenario, you'd essentially have two local databases. One is the snapshot of the last time the user synced. One is the working copy. The disconnection users cannot make any changes to the snapshot, only their working copy. When then go to sync again, their pending changes from the working copy are applied and all is well.

You, the developer, has to make this happen.

One thing you might be getting confused by: primary keys should never, ever, be public or assigned by the user. Users see them, expect things to be sequential, get all confused if there's a break in the sequence, etc.

For project ID 100, if that's a field, the disconnected user won't get to fill that in. Or it will be temporary. Or it's a non a unique key in a table.

When you sync, if you have potential issues with your data structures, you might need to allow for some user interface to resolve conflicts.
Was This Post Helpful? 1
  • +
  • -

#9 AndrewMack  Icon User is offline

  • New D.I.C Head

Reputation: 7
  • Posts: 42
  • Joined: 12-April 13

Re: [ADO.NET] - Diconnected Model, Unique IDs, and Parent/Child relations

Posted 09 May 2013 - 11:10 AM

Thanks, tlhIn`toq - that's a great quick example and tutorial!

I'm finding I'm mixing up my terms when I begin coding in C# - WinDev uses the same/similar key words yet have different meaning/purposes than C#/.NET. It's been a small hurdle (more like an annoyance). I hope the more I practice with C# (I only have one or two evenings available for this in the week) that I'll get used to/comfortable with the terms in C#.

Baavgai, I agree with you 100% - I'll never show a User an ID/Primary Key value except in very few exceptions. Never is it directly accessible/editable by a User.

I certainly understand (or at least am aware of) ADO.NET's abilities a lot more now. Thank you guys very much for the clarifications! I really appreciate the time you've given to answer my questions and point out where & what was wrong in my thinking!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1