MSSQL data tables in WPF MVVM Model

Posted on 2014-08-22
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.
Question by:Koen Van Wielink
    LVL 62

    Expert Comment

    by:Fernando Soto
    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.
    LVL 12

    Author Comment

    by:Koen Van Wielink
    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.
    LVL 62

    Accepted Solution

    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.
    LVL 12

    Author Comment

    by:Koen Van Wielink
    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.
    LVL 62

    Expert Comment

    by:Fernando Soto
    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.
    LVL 62

    Expert Comment

    by:Fernando Soto
    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.
    LVL 12

    Author Comment

    by:Koen Van Wielink
    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.
    LVL 62

    Expert Comment

    by:Fernando Soto
    LVL 12

    Author Comment

    by:Koen Van Wielink
    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.
    LVL 12

    Author Closing Comment

    by:Koen Van Wielink
    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.
    LVL 62

    Expert Comment

    by:Fernando Soto
    Not a problem Koen, always glad to help out.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now