Page 1 of 1

Accessing your Database with the Entity Framwork Rate Topic: ***** 1 Votes

#1 Nightfish  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 74
  • View blog
  • Posts: 158
  • Joined: 24-May 11

Posted 15 July 2011 - 04:24 AM

Accessing your Database with the Entity Framwork

For those of you not familiar with the Entity Framework, what it does is basically to provide you with a simple way of communicating with your database without the need to construct command strings and the like. Your queries are right there in the code and you can write them aided by intellisense and you'll be informed of mistakes you make as you make them. Also, it doesn't matter what database you're talking to, you'll always use the same syntax: Linq-to-Entities. I am still far from being an expert but I've done the first steps and I'd like to take this opportunity to recap what I've learned.


Why would we want to do that, you ask? Well, let me give you one simple example of how this looks in comparison to command strings. Using the Northwind database, I'll show you the same query, once in Linq-to-Entities, and once in the form a command string would use. The first one, I typed in less than a minute and got it right on the first try. The second one, I had to debug for quite a while. Made a few typos, forgot one blank at the end of a line so two words got mashed together, didn't know that in SQL CE you need to put column names in brackets and remove underscores in tablenames and replace them with a blank when sending a query.

        'A long and complicated query
        Dim source = From ord In myData.Orders Join cus In myData.Customers On cus.Customer_ID Equals ord.Customer_ID
                     Join emp In myData.Employees On emp.Employee_ID Equals ord.Employee_ID
                     Join orDe In myData.Order_Details On orDe.Order_ID Equals ord.Order_ID
                     Join prod In myData.Products On orDe.Product_ID Equals prod.Product_ID
                     Select cus.Company_Name, ord.Shipped_Date, emp.Last_Name, orDe.Quantity, prod.Product_Name
                     Order By Company_Name, Shipped_Date

        'Same query in SQL
        Dim selStr As String = ""
        selStr &= "SELECT cus.[Company Name], ord.[Shipped Date], Emp.[Last Name], orDe.Quantity, prod.[Product Name] " & vbNewLine & _
                  "FROM Orders AS ord " & vbNewLine & _
                  "INNER JOIN Customers AS cus ON ord.[Customer ID] = cus.[Customer ID] " & vbNewLine & _
                  "INNER JOIN Employees AS Emp ON Emp.[Employee ID] = ord.[Employee ID] " & vbNewLine & _
                  "INNER JOIN [Order Details] AS orDe ON orDe.[Order ID] = ord.[Order ID] " & vbNewLine & _
                  "INNER JOIN Products AS prod ON orDe.[Product ID] = prod.[Product ID] " & vbNewLine & _
                  "ORDER BY cus.[Company Name], ord.[Shipped Date]"



You can't see that here in the forum, but there is syntax highlighting going on in the first query. (http://img837.imageshack.us/img837/862/synhighlight.jpg if you want to see a screeny of it) Makes it easier to read the query. And of course I don't need to fiddle with the new line crap and linking strings together. Also, if I made a mistake somewhere, the compiler would send Mr. Squiggly Line to point this out to me. And finally, I can type this using intellisense so I don't actually have to remember what my tables and columns are called exactly. I can just go with myData. and select what I want after I hit the . (myData being the variable representing my database).


In the following, I'll outline the basic steps in setting things up and I'll show you how to do SELECT, INSERT, UPDATE and DELETE operations.


Goal: We will create an application that let's you view, update, delete and add customers from the well known "Northwind" database.


Step 1: Create a new project

You can do any number of things, for now let's pick a simple "Winforms" application. I'm not going into the details of designing your form, but here's what I did so we're all on the same page. I'll give the names of the controls where they are important.

1) Split Container set for horizontal split and dock it to "fill" the entire Form. In the top panel, we're placing a TableLayoutPanel, in the bottom panel we're placing a DataGridView which I called "grdAllCustomers" because it will display all the customers from the Northwind database. Set both of these to dock to "fill" their respective panel.

2) Set the aforementioned TableLayoutPanel to have 6 rows and 3 columns. This will serve as our editor to add and edit customers. For the editor fields, I use textboxes and as I need to access those from within my code, they need to get names that I will recognise. I name them like thus:

edtCust_Addr
edtCust_City
edtCust_CompNam
edtCust_ContNam
edtCust_ContTitle
edtCust_Country
edtCust_CustID
edtCust_Fax
edtCust_Phone
edtCust_PostCode
edtCust_Region

"edt" because it's part of an editor, "Cust" because it's editing a customer and the "_blabla" indicates which property of the customer the field relates to. This beats having textbox1 through 10 or whatever. Also, I added 4 buttons for
- adding a customer to the database
- deleting a customer from the database
- updating a customer in the database
- populating the editor fields from the grdAllCustomers

The final result should look something like this:

Posted Image


Step 2: Add the database model


Depending on what database you are working with, you can do this from within visual studio. In your Project Explorer, select the option to add a new item and select "ADO.NET Entity Model" (and enter a name for it). In the wizard, select "generate from database" and pick your database. Select the tables you want to have included and you should see something like the image below once you're done. Don't forget to give a decent name to your entities, that is what you will be refering to whenever you want to talk to the database. For the purpose of this tutorial, I've called them "NorthwindEntities".

Posted Image

For some reason, this is not supported for SQL CE 4.0 at this time but there is a workaround which I will outline in the appendix. One way or another, you've got your entity model and once you're done oogling the awesome graphical representation of your database let's get rolling.


Step 3: Profit! Okay, just kidding. Step 3 is coding

When the form is loaded we need to instantiate our entities or there will be nothing to work with.

Public Class EFDemo
    Dim myData As NorthwindEntities

    Private Sub EFDemo_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'Fetch the entities
        myData = New NorthwindEntities
        fillCustGrid()
    End Sub



The "myData" variable is basically my database. Using myData.TABLENAME I can refer to the individual tables. As you can see, I also called the mysterious "fillCustGrid()" subroutine. As the name suggest, this one fills the grdAllCustomers that we set up earlier:

    Private Sub fillCustGrid()
        Dim allCust = From x In myData.Customers Select x Order By x.Customer_ID Ascending
        grdAllCustomers.DataSource = allCust
    End Sub



The first line is basically your simple "SELECT * FROM CUSTOMERS" query. I'll show something more complicated later, but it's very easy to experiment with this as you have intellisense to help.

At this point, you can go ahead and run the application. The table in the lower half of the form should fill with customers nicely. Now it's time to make the editor work:


Adding a customer is a relatively simple process:
- Create a new Customers Object with the values entered in the editor fields
- Add this object to the context - myData.Customers
- Save the changes (this updates the database)

    Private Sub addCustomer()
        Dim newCustomer As New Customers With {
            .Customer_ID = edtCust_CustID.Text.ToString(),
            .Company_Name = edtCust_CompNam.Text.ToString(),
            .Contact_Name = edtCust_ContNam.Text.ToString(),
            .Contact_Title = edtCust_ContTitle.Text.ToString(),
            .Address = edtCust_Addr.Text.ToString(),
            .City = edtCust_City.Text.ToString(),
            .Region = edtCust_Region.Text.ToString(),
            .Postal_Code = edtCust_PostCode.Text.ToString(),
            .Country = edtCust_Country.Text.ToString(),
            .Phone = edtCust_Phone.Text.ToString(),
            .Fax = edtCust_Fax.Text.ToString()
        }
        myData.Customers.AddObject(newCustomer)
        myData.SaveChanges()
    End Sub



You don't actually have to go and save the changes right away if you don't want to. If you know you're going to perform a lot of actions do the saveChanges last. This will help performance. I'm doing his right here because I want to get my database up to date right away and I am not performing bulk actions now.


To work with existing customers, we need to get information from the grid to the editor fields. The idea is that you have clicked on a row in the grid and want to transfer this customer to the editor. First I retrieve the customer's ID, then I select the corresponding object containing my customer's data from myData and load those values into the editor fields. As the database allows for null values you need to do a bit of verification to make sure you're not trying to put a null reference into the fields. If I find a null value, I just put an empty string in it's place.

    Private Sub fillEditor()
        Dim currentCustID = grdAllCustomers.CurrentRow.Cells(0).Value.ToString()
        Dim currentCustomer = From x In myData.Customers Where x.Customer_ID = currentCustID
                              Select x

        'If a customer has been found, fill the editor with the customer information
        If currentCustomer.Count > 0 Then
            'The database allows null values. This results in a null reference exception if you try to
            'access the corresponding field of the currentCustomer object
            If currentCustomer.ToList.Item(0).Address = Nothing Then
                edtCust_Addr.Text = ""
            Else
                edtCust_Addr.Text = currentCustomer.ToList.Item(0).Address.ToString()
            End If

            If currentCustomer.ToList.Item(0).City = Nothing Then
                edtCust_City.Text = ""
            Else
                edtCust_City.Text = currentCustomer.ToList.Item(0).City.ToString()
            End If

            If currentCustomer.ToList.Item(0).Company_Name = Nothing Then
                edtCust_CompNam.Text = ""
            Else
                edtCust_CompNam.Text = currentCustomer.ToList.Item(0).Company_Name.ToString()
            End If

            If currentCustomer.ToList.Item(0).Contact_Name = Nothing Then
                edtCust_ContNam.Text = ""
            Else
                edtCust_ContNam.Text = currentCustomer.ToList.Item(0).Contact_Name.ToString()
            End If

            If currentCustomer.ToList.Item(0).Contact_Title = Nothing Then
                edtCust_ContTitle.Text = ""
            Else
                edtCust_ContTitle.Text = currentCustomer.ToList.Item(0).Contact_Title.ToString()
            End If

            If currentCustomer.ToList.Item(0).Country = Nothing Then
                edtCust_Country.Text = ""
            Else
                edtCust_Country.Text = currentCustomer.ToList.Item(0).Country.ToString()
            End If

            If currentCustomer.ToList.Item(0).Customer_ID = Nothing Then
                edtCust_CustID.Text = ""
            Else
                edtCust_CustID.Text = currentCustomer.ToList.Item(0).Customer_ID.ToString()
            End If

            If currentCustomer.ToList.Item(0).Fax = Nothing Then
                edtCust_Fax.Text = ""
            Else
                edtCust_Fax.Text = currentCustomer.ToList.Item(0).Fax.ToString()
            End If

            If currentCustomer.ToList.Item(0).Phone = Nothing Then
                edtCust_Phone.Text = ""
            Else
                edtCust_Phone.Text = currentCustomer.ToList.Item(0).Phone.ToString()
            End If

            If currentCustomer.ToList.Item(0).Postal_Code = Nothing Then
                edtCust_PostCode.Text = ""
            Else
                edtCust_PostCode.Text = currentCustomer.ToList.Item(0).Postal_Code.ToString()
            End If

            If currentCustomer.ToList.Item(0).Region = Nothing Then
                edtCust_Region.Text = ""
            Else
                edtCust_Region.Text = currentCustomer.ToList.Item(0).Region.ToString()
            End If

        End If
    End Sub



Then I link this subroutine to the approriate button:

    Private Sub btnFillEdit_Click(sender As System.Object, e As System.EventArgs) Handles btnFillEdit.Click
        fillEditor()
    End Sub



Of course you could just as well do this with an event that's raised whenever you click a cell in the grid.


Now that we have values in our editor, we can do stuff with them:

If we want to update our customer, step 1 is again to grab the correct customer from myData. I called this guy the "updatedCustomer". Then we change all his information to the values now present in the editor. You could (and probably should) go ahead and make sure anything has been changed before you set the values here but for the sake of brevity, I will not do this here. Once you're done setting the values, you gotta let your object context know that changes have been made. This is what the ObjectStateManager does. Then, as before, we save the changes.

    Private Sub updateCustomer()
        Dim updatedCustomer As Customers = (From x In myData.Customers Where x.Customer_ID = edtCust_CustID.Text).ToList()(0)

        With updatedCustomer
            .Company_Name = edtCust_CompNam.Text.ToString()
            .Contact_Name = edtCust_ContNam.Text.ToString()
            .Contact_Title = edtCust_ContTitle.Text.ToString()
            .Address = edtCust_Addr.Text.ToString()
            .City = edtCust_City.Text.ToString()
            .Region = edtCust_Region.Text.ToString()
            .Postal_Code = edtCust_PostCode.Text.ToString()
            .Country = edtCust_Country.Text.ToString()
            .Phone = edtCust_Phone.Text.ToString()
            .Fax = edtCust_Fax.Text.ToString()
        End With

        myData.ObjectStateManager.ChangeObjectState(updatedCustomer, System.Data.EntityState.Modified)
        myData.SaveChanges()
    End Sub




Finally, let's go and get rid of a customer. As always when deleting stuff from a database, you have to keep constraints in mind. I suggest deleting a customer you added with our self-made method because those guys have no orders and we haven't set up deleting orders yet. Deleting customers is very similar to updating them, again the object state manager is called to change the status of the customer we pried from the object context.

    Private Sub deleteCustomer()
        Dim deletedCustomer As Customers = (From x In myData.Customers Where x.Customer_ID = edtCust_CustID.Text).ToList()(0)      
        Try
            myData.ObjectStateManager.ChangeObjectState(deletedCustomer, System.Data.EntityState.Deleted)
            myData.SaveChanges()
        Catch ex As Exception
            MsgBox("Cannot delete customers that still have orders associated with them")
        End Try
    End Sub



Now just link these functions to the appropriate buttons and you should be good to go. I also made a function to clear the editor and called that when I felt the editor should be cleared.


Appendix A: Getting SQL Server CE 4.0 to work with the Entity Framework

For some reason, generating a model from a .sdf file was supported for sql ce 3.5 but it no longer is for sql ce 4.0. But as always, the interwebs has a workaround:

1) Download EdmGen2:

http://archive.msdn....?ReleaseId=4216

Unzip and build the project with visual studio.


2) cmd -> go to the bin/debug folder of edmgen2

3) run the following 3 commands from the console:

EdmGen2 /ModelGen " Data Source= C:\database\Northwind.sdf" System.Data.SqlServerCe.4.0 Northwind

EdmGen2 /FromEdmx Northwind.edmx

EdmGen2 /Validate Northwind.edmx


Replace "C:\database\Northwind.sdf" with where you put your database and if your database is not called northwind, obviously replace those names as well.

Now you'll find the 4 new files in the debug folder of edmgen2, the .edmx (the model) and 3 files of metadata. Copy those to your project and add the .edmx as an existing file via the project explorer. Continue from where the image of the model is up there in the tutorial.

That's all folks!

Let me know if this works to get you started on the Entiy Framework. I'll probably add more of these in the future if there's interest and I'll update this as needed.

Is This A Good Question/Topic? 2
  • +

Replies To: Accessing your Database with the Entity Framwork

#2 TemiU  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 09-October 13

Posted 09 October 2013 - 12:14 PM

 Dim allCust = From x In myData.Customers Select x Order By x.Customer_ID Ascending


I tried to load the form (step 3) but keep receiving the following error: Customer_ID is not a member of MyNamespace.Customer.
(Nor do I see any datamembers when I press the "." operator following the x).

When I comment out the last section ([code] 'Order By . . . [\code])and try to run the program, I receive the following error:

An unhandled exception of type 'System.NotSupportedException' occurred in EntityFramework.dll

Additional information: Data binding directly to a store query (DbSet, DbQuery, DbSqlQuery) is not supported. Instead populate a DbSet with data, for example by calling Load on the DbSet, and then bind to local data. For WPF bind to DbSet.Local. For WinForms bind to DbSet.Local.ToBindingList().
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1