MSSQL data tables in WPF MVVM Model

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.
LVL 13
Koen Van WielinkBusiness Intelligence SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
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 SpecialistAuthor 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.
Fernando SotoRetiredCommented:
Hi Koen;

To your question, "I assume that if I want to use the entity framework the table relationships have to be known through foreign keys?", no, this is not correct. All tables that you wish to do CRUD operations on needs to have a Primary Key. Tables that some how relate to each other but do not have a Foreign Key in one or both tables can still build the relationship in the Entity Framework query through a join operation for example.

To your statement, "Having said that all the data is pulled in through stored procedures so in that case it wouldn't matter?", Entity Framework can use stored procedures to retrieve the data from the database.

To your statement, "The existing application uses the sqlconnection object I think so I was planning to stick to that.", that is fine, Entity Framework is most likely an overkill for this project.

To your question, "Can you elaborate on "construct a collection"?", yes, I have done that by example below. I have used the Microsoft Northwind database.
Imports System.Data.SqlClient
Imports System.Collections.ObjectModel

'' Connection string will need to be changed for your site
Dim connStr As String = "Data Source=(local)\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
'' Create a SqlConnection object.
Dim conn As New SqlConnection(connStr)
'' Create an SqlCommand object.
Dim cmd As SqlCommand = conn.CreateCommand()
'' Tell the command object that this command represents an SQL stored procedure call.
cmd.CommandType = CommandType.StoredProcedure
'' The Stored procedure name
cmd.CommandText = "Get All Customers"
'' myCustomerCollection represents the collection that should be bound to the control.
'' Note that I used a ObservableCollection because it implements INotifyCollectionChanged
'' which is needed to detect changes in the collection.
Dim myCustomerCollection As New ObservableCollection(Of MyCustomer)
'' Open the connection to the database
'' Execute a reader to return the results of the stored procedure
Using reader As SqlDataReader = cmd.ExecuteReader()
    '' Read the next record and if reader does not return null/Nothing
    '' then add the values returned to the collection
    While reader.Read
        '' Create a new MyCustomer and fill its properties with
        '' the results of the reader
        Dim customer As New MyCustomer()
        customer.CustomerId = reader("CustomerID").ToString()
        customer.CompanyName = reader("CompanyName").ToString()
        customer.ContactName = reader("ContactName").ToString()
        customer.ContactTitle = reader("ContactTitle").ToString()
        customer.Address = reader("Address").ToString()
        customer.City = reader("City").ToString()
        customer.Region = reader("Region").ToString()
        customer.PostalCode = reader("PostalCode").ToString()
        customer.Country = reader("Country").ToString()
        customer.Phone = reader("Phone").ToString()
        customer.Fax = reader("Fax").ToString()
        '' Add the MyCustomer to the ObservableCollection
    End While
End Using
'' Close the connection to the database

'' This class supports building the collection. It represents the data 
'' being returned from the data reader Also you will need to implement 
'' INotifyPropertyChanged not shown here 
Public Class MyCustomer
    Public Property CustomerId As String
    Public Property CompanyName As String
    Public Property ContactName As String
    Public Property ContactTitle As String
    Public Property Address As String
    Public Property City As String
    Public Property Region As String
    Public Property PostalCode As String
    Public Property Country As String
    Public Property Phone As String
    Public Property Fax As String
End Class

'' myCustomerCollection has been created and now can be bound to the control.

Open in new window

To your question, "would you do this in the model or the viewmodel?", The communications between the database and your application should be done in the Model. The View should display in the needed information to the user and communicate to the ViewModel any changes the View has.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Koen Van WielinkBusiness Intelligence SpecialistAuthor 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 SotoRetiredCommented:
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 SotoRetiredCommented:
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 SpecialistAuthor 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 SotoRetiredCommented:
Koen Van WielinkBusiness Intelligence SpecialistAuthor 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 SpecialistAuthor 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 SotoRetiredCommented:
Not a problem Koen, always glad to help out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.