We help IT Professionals succeed at work.

MSSQL data tables in WPF MVVM Model

1,092 Views
Last Modified: 2014-10-14
Let me start out by stating I'm an absolute novice when it comes to programming. I've done some online courses but never programmed an application myself. I've now been tasked by my company to redevelop our existing R&D application which is writted in VB.Net. The current application is based around Windows Forms. The idea is to start from scratch, and redesign it using Windows Presentation Foundation and the Model-View-View Model framework. I've done a lot of research so far, and the concepts are clear to me, but I'm now stuck on what I assume should be fairly straightforward.
All the examples I've found so far implement some model examples usually as what I'd describe as a single record: a customer, a product, etc, But I can't seem to find any examples that handle how to pull a large number of records from a database at once, and display it inside a grid. In our existing application we have 1 form for the list of items, and another for the details of a single item. From what I understand, I need to translate the data aspects of these forms to models. I can see how to handle a class for a single item, but I don't know where to start for the list of, say 200 records with 30 or 40 different columns. Should this be a model as well? If so, what should it look like, and how does it bind to the grid?
Sorry if this is a bit vague, but like I said I'm new to all of this and been tossed into the deep end. I'm sure I can do it, but I need a bit of help to get me going.
Thanks in advance.
Comment
Watch Question

Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi Koen;

I am learning WPF myself and I am hoping that I can help you with this question. So the database is MS SQL database. The next thing that you need to determine is how you will pull the data down to the Model. One option is to connect to the database using Entity Framework and another would be to use ADO .Net. Although Entity Framework is build upon the ADO.Net framework, Entity Framework builds the plumbing and mapping of classes to tables in the database.

Depending on which technology you decide on will determine how to create the model / classes. If you use Entity Framework then the model classes will be created for you by the designer and If you use ADO .Net you will need to build the classes that will hold the results of the collection yourself.

When you build a query to get the data from the database it will return the number of rows that the query satisfies. This may be one or more rows. You will need to construct a collection to hold all the rows that were returned. Then that collection can be bound to a control.
Koen Van WielinkBusiness Intelligence Specialist

Author

Commented:
Hi Fernando,

Thanks for your reply. I assume that if I want to use the entity framework the table relationships have to be known through foreign keys? We don't have this in the database and I don't think we want to add this. Having said that all the data is pulled in through stored procedures so in that case it wouldn't matter? Sorry, very new to this. The existing application uses the sqlconnection object I think so I was planning to stick to that.
Can you elaborate on "construct a collection"? And would you do this in the model or the viewmodel?
Thanks again, really appreciate the help.
Retired
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Koen Van WielinkBusiness Intelligence Specialist

Author

Commented:
Hi Fernando,

Thanks again, things are starting to make sense now, but I'm also seeing a new issue. First a few more questions for clarity:

1. MyCustomer is a class that creates a new customer object I assume, which was not included in the example?
2. Should this code be put inside a function which returns the observable collection?

Now for the issue. Your example lists all the properties of MyCustomer and populates them 1 by 1. Our dataset can have quite a lot of columns, say 30 or 40. Would all of those have to be declared as properties in the item class? The really tricky part here is that the properties are not fixed. The columns returned depend on the user's selection and the available data. The final dataset returned by the procedure is created dynamically and we don't know upfront how many or exactly which columns will be returned. This worked ok in Windows Forms applications which use a datatable object, but I don't know how we could declare these items as properties upfront. Any thoughts on this?
Thanks again.
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi Koen;

To your question, "MyCustomer is a class that creates a new customer object I assume, which was not included in the example?", I had realize that and I had updated the post but probably after you had originally read it. Please see the original post for the class.

To your question, "Should this code be put inside a function which returns the observable collection?", well that depends. You stated you were going to use MVVM. If that is the case then the INotifyPropertyChanged and INotifyCollectionChanged should handle that.

To the last part of your question I will need to do some research to see what type of solution may be possible.
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi Koen;

Please have a look at this article, Problems and Solutions with Model-View-ViewModel, they have a section on how to handle Dynamic Properties which can help in your design.
Koen Van WielinkBusiness Intelligence Specialist

Author

Commented:
Thanks for that Fernando. I'll have a look at that either tomorrow or on Monday. It's getting late here and my brain isn't really absorbing much anymore.
Will keep you posted.
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
OK
Koen Van WielinkBusiness Intelligence Specialist

Author

Commented:
Hi Fernando,

Sorry for the delay. It's been a crazy busy week and unfortunately I haven't had the time to look at your solution yet.
Will do so ASAP.
Koen Van WielinkBusiness Intelligence Specialist

Author

Commented:
Hi Fernando,

Please accept my sincere apologies for neglecting this question. I know from experience how annoying it is when you take the time to help a person with his or her problem and you don' t get any feedback. Unfortunately I've been insanely busy with other stuff the last month and haven't been able to look into the final details.
I'm accepting your answer anyway as it has helped me to get started, and I'm at least able to query the database and, I think, load an observable collection. I haven't been able to link it to a gridview yet so don't know if the results are as expected, but at least I'm not getting any errors when running the code. It wouldn't be fair to keep this question open any longer, as it will be some time still before I can work through this.
So again my apologies for not replying sooner, and thanks for the answers. It really has helped to get me started.
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Not a problem Koen, always glad to help out.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.