Using Entity Framework and LINQ to Retrieve Data from SQL Server
The Goals for this Example
The main goal is to learn how to use the Entity Framework and LINQ (language integrated query)
to retrieve the data from an SQL Server database (and, in this example, from our product table in that database).
Somewhat like stored procedures, LINQ should be more secure than plain SQL, as the code is generated before the user submits any data
and the data is typed. This goes a long way toward eliminating hacking attempts that pass malicious data to a web form, which
then uses that data as part of a database query. For a first example, we include no user parameters, however.
Getting Started
Preliminaries
- Much like before, we wish to create a web page that allows the user to click a button on the form
to display data pulled from the product table in our database.
- Open your usual solution in Visual Studio, the solution that contains the web site that you have
been working on in these examples. However, if you wish, you may create a new web site/solution instead.
The directions here assume that you use your existing web site/solution.
- Note: On occasion, Visual Studio has been known to fail to load a previous solution, stopping at a Security Warning window
and not responding to anything. You can right click on the icon tray at the bottom of Windows and start Task Manager.
You are likely to see that devenv.exe (the Visual Studio development environment) is taking up a large percentage of CPU time.
Then, under the Processes tab, click on devenv.exe and then on the
End Process button. That will stop Visual Studio for you.
But how do you get Visual Studio to load your project? One way is to use Computer to find your solution file.
It should be located in a folder named something like "C:\Users\carlsond\Documents\Visual Studio\Projects\WebSite1",
where you replace carlsond with your username and WebSite1 with the name of your project folder. You can browse to the desired
location by drilling down one folder at a time. Inside that last project folder should be the .sln (solution) file for your
web site. Right click this file, perhaps WebSite1.sln, and then select "Open with" and then
"Microsoft Visual Studio".
- If you catch the above problem at a Security Warning window (often saying that running this may harm your computer), you
might try clicking the button for not displaying this message again and then closing the window (assuming that the
window will respond).
- In Server Explorer, open your web.config file and be sure that you have a webapp connection string, as created in
this previous example.
This is the one that allows you to connect to the database using SQL Server authentication. That is,
it passes the username and password to log into SQL Server over the network so as to make the connection.
(If you created a new web site, you can copy your connection from the old web.config file and paste it into the correct
spot in your new web.config file.)
|
Setting up a New Form
- In Solution Explorer, add a new web form called DisplayEF to your web site, adjusting the title (in Source view) as you wish.
- On your DisplayEF form we wish to use the same controls that we have been using in this series of database examples.
Since you already know how to create and adjust these, we will take a shortcut. Look at your form in Source view and
copy in the following code so that it replaces the div section already present.
|
<div style="height: 434px">
<asp:Panel ID="Panel1" runat="server" Height="297px"
style="z-index: 1; left: 13px; top: 125px; position: absolute; height: 297px; width: 915px">
<asp:GridView ID="GridView1" runat="server" Visible="False">
</asp:GridView>
</asp:Panel>
<asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="X-Large"
style="z-index: 1; left: 10px; top: 15px; position: absolute; width: 403px; height: 29px;"
Text="Web App to Display Data from a Table" BackColor="#990000" ForeColor="White"></asp:Label>
<asp:Label ID="Label2" runat="server"
style="z-index: 1; left: 13px; top: 50px; position: absolute"
Text="In this version we use entity framework and LINQ to get the data."></asp:Label>
<asp:Label ID="Label3" runat="server"
style="z-index: 1; left: 14px; top: 92px; position: absolute"
Text="Data from Product table:" Visible="False"></asp:Label>
<asp:Button ID="Button1" runat="server"
style="z-index: 1; left: 174px; top: 92px; position: absolute" Text="Display Data" />
</div>
|
- View your form in Design view to be sure that it looks OK.
- In Solution Explorer check that your web site contains an App_Code folder. If not, right click the web site line
in Solution Explorer and then select Add, Add ASP.NET folder, App_Code.
|
Getting the Data from the Database
Entity Framework
- Save all of your files before going on.
- IMPORTANT: If you have your web.config file open in Visual Studio, close it. In the following steps Visual Studio
will try to store another type of connection string, referred to as "entity connection settings" in
your web.config file. You must not have your web.config file open for this to succeed.
- It is also important to keep track of the names of the various items that you create in this sequence of steps as you
will need to know some of them in order to write your VB code and some of the names cannot be easily found later. If
you use the names given here, these directions will help you, but later you will want to do something like this in
a different setting and will have to keep track of the names yourself.
- Now for the Entity Framework. It provides a way to map database objects like tables to .NET objects that you can
access in your VB code. It is one way to make it easier to work with a database and to do so from within your
normal VB code. We begin by setting up what is called a model.
- Right click the App_Code folder in Solution Explorer. Select Add, Add New Item, ADO.NET Entity Data
Model. The first dialog window will ask you at the bottom for the name of the model and may suggst Model. as the name.
Do NOT use any name with a . in it as that is not allowed. Change that name to ProductModel with no spaces or periods.
The click OK (or Add, depending on how this is labeled).
- At the next screen you are asked what the model should contain. Select
"EF Designer from Database" (or "Generate from database" depending on the labeling) and click Next.
- The next window has you choose your connection to the database. In the pull-down box, choose your webappConnectionString (or
whatever you named the connnection that uses SQL Server authentication in order to connect to your database.
Select the radio button labeled "Yes, include the sensitive data in the connection string". (You are asked about
this because SQL Server authentication sends the password over the network. If this password is only going between 2 servers
on a LAN that only trusted personnel can access, this may be OK. However, in most cases you are going to want to
look up how to store the password in encrypted form and to transmit it using encryption.) Also choose to save the entity
connection settings and name this as webappEntities. Check this picture of what your window should
now look like. Then click Next.
- If asked what version of the Entity Framework to use, the highest numbered one is probably what you want.
- The next window is for choosing your database objects and settings. Keep expanding the tree starting at the Tables node
until you find your Product table (or whatever you named it). Put a check in front of that table. Although multiple tables
can be checked, we are only going to use one in this first example. Leave all 3 check boxes at the bottom checked. In the
box at the bottom, use webappModel for this model namespace. Then click Finish.
- You will now probably be asked whether it is OK to run certain code, maybe with a mention that this could be dangerous.
However, this is exactly what you need to do. Select OK.
- You might also be asked whether to reload certain files that got modified outside of the environment. Select Yes to All.
- After a short pause, you should see the Entity Designer displaying a drawing of the class that was created based on the product
table that you selected in your database. (If you select more than one table, Entity Designer will even draw the lines
showing each relationship between tables, that is, the ones between the primary key in one table and the matching foreign
key in another table.) This picture of Entity Designer shows approximately what
you would see at this point, although some of the names in this picture do not match the names given above.
In the picture, you can see in Solution Explorer the extensions on the filenames used for the model created here. Also,
in the Mapping Details section you can see on the left the column names (field names) and their types for the product table
as found in your database, with the matching names of the fields (and their VB types) in the object on the right.
(If mapping details is not visible, right click the white space around your drawing of the table, and select the item that
displays the mapping details.)
- Be sure to save and close this diagram.
|
Possible Problem
- Important: If you have encountered no problems, do not try the fixes in this section. Just read the
material in case you have any of these problems in the future. Then go on to the next section.
- You may get a warning and/or outright errors, when trying to generate the database Model at the end of the section above,
that a path that starts with W: needs to be in your Trusted Zone. (This may also occur if you use any mapped network
drive or share in place of W:.) For details, refer to this screen shot of the Trusted
Zone warning message.
- If all you have are warnings, not errors, things may work fine. You could try going ahead without using the fix below.
- Other shorter things to try include closing and reopening Visual Studio and/or deleting all of the model files from Solution
Explorer and trying to create the model again.
- Here is an outline of a fix for this:
- There are a couple of ways to start this out:
- First way: In Internet Explorer, use Alt-t (for Tools), Internet Options.
- Second way: In Control Panels, open Internet Options.
- You then either add W: to your local intranet or your trusted sites.
- For the local intranet, do this:
- Click the Security tab, Local Intranet, Sites, Advanced.
- Uncheck the box labeled "Require server verification" if it is checked to start with.
- In the box labeled "Add this website to the zone:", type W: and click the Add button.
- It will add something like file://IIS to the list of websites in this zone.
- For trusted sites, the process is similar:
- Click the Security tab, Trusted Sites, Sites.
- Uncheck the box labeled "Require server verification" if it is checked to start with.
- In the box labeled "Add this website to the zone:", type W: and click the Add button.
- It will add something like file://cis-w2k8server to the list of websites in this zone.
- Next, delete all of the files used by your model in Solution Explorer.
They are all found inside of the App_Code folder. You can delete them in Solution Explorer by selecting them,
right clicking, and selecting Delete.
- Then delete your webappentities connection string from your web.config file for this website.
- Leave your ordinary webapp connection string in place.
- Now create a new model by right clicking on the App_Code folder and following the same directions
that you used previously.
- At some point in this I got an error message that a certain location was already in use. You may or may not get this.
- The problem seemed to be that the new model was trying to overwrite a file and was prevented from doing so.
- I was able to get past this by dismissing the message, I think, and was then given a "Save As"
type of dialog. I made sure that I did not have this location open anywhere (which had been the case
as I had Windows Computer open on that folder), and then saved the file to that same location successfully.
- From there on everything worked, though I still had a warning at the bottom of Visual Studio about the Trusted Zone.
Building the website worked and the resulting web app worked fine.
|
LINQ
- Next, we turn to writing some LINQ (language integrated query) code in our VB code-behind file,
along with some other click handler code.
- Since we have done button click handlers before, we will do that first. Open your DisplayEF.aspx form in Design view if
it is not already opened. Double click the button and you will get an outline of the click handler in the DisplayEF.aspx.vb
code-behind file. Within this function place the following 3 lines which we have used in previous examples.
|
Button1.Visible = False
GridView1.Visible = True
Label3.Visible = True
|
- Next, click on the bottom of your form in Design view until you see a little tag labeled "body" at the top of the
form. (You will probably also see Body highlighted in yellow on a line just below your form.)
Wherever you clicked to get this, now double click this spot. This will give you the outline of a Page_Load event handler.
- Inside this event handler function, place the following code which sets up and executes a LINQ command to get the desired
data into your grid view.
|
Using productEntities As New webappEntities()
Dim products = From ProductTable In productEntities.ProductTables
Select ProductTable
GridView1.DataSource = products.ToList()
GridView1.DataBind()
End Using
|
- Note that the names in the above code may not match yours exactly. ProductTable and the pluralized ProductTables might
be Product and Products (or something else), depending on how you named your table in the database.
- We will go through this line by line.
- The Using statement sets up a variable (an instance) named productEntities that uses webappEntities
(taken from the name given above for the connection settings for the Entity Framework to access the
database via the webappConnectionString). This productEntities instance takes care of the connection to your
database and sends the database the LINQ queries that you use on it. For you, as you write your VB code,
productEntities gives you an object-oriented view of the database table(s).
When "End Using" is reached this productEntities variable is disposed of. This is always a good idea
when you no longer need an item that contains more than a trivial amount of information.
- I have often gotten errors at this point. Specifically, when I type webappEntities in that Using line, Intellisense
often can't find any such thing. Here are some ideas: Perhaps the template that runs at the end of building the Model did
not run correctly. You can try deleting the Model and starting again on that. However, it often has worked if I close Visual
Studio and start it up on the project later. All of a sudden it can find webappEntities. Another idea is to pull up
your model by double clicking the .edmx file and look at its properties in the Properties window and check that you have
the correct Entity Container Name. We have been assuming that this name is webappEntities.
You can also check that there is a reasonable name for the Namespace property. Above we attempted to name it webappModel.
Finally, it may help to save all of your files before creating the Model and immediately after creating the Model.
- The first line inside of the Using construct uses a LINQ command to place into a variable called products,
all the information on all of the products that it can extract from productEntities, our object of product
information (ultimately obtained from the database).
- Note that products is what is termed an implicitly defined variable. Although you did not directly give it a type,
it will be of type IQueryable, if you want to know exactly what it is.
- The LINQ command is similar to SQL and uses some of the same keywords such as Select. This particular command would
read better if our product table had simply been named Product instead of ProductTable. With the shorter name, the
LINQ command would be
From Product In productEntities.Products Select Product. Note the pluralized
form of Product, namely Products, in this command. We want the information on all of the products here.
- Finally, the data in the products object is converted to a list that is used as the data source for your grid view and then
the data is bound to the grid view.
|
Building and Running Your App
- Save all of your files in Visual Studio.
- Then use Build, Build Page.
- If the build succeeded, the Output window will give you a message to that effect.
- If not, fix any problems.
- Use the debugging methods you learned in earlier examples, if need be.
- Then run your app first locally, by clicking the green triangle button labeled as Internet Explorer or by selecting
Debug, Start Without Debugging. Check that your program runs correctly, displaying the desired data in its grid view.
- Then run your app by going to the correct URL with a browser. For example, the URL I am using is:
http://cis2.stvincent.edu/carlsond/Wrox6/DisplayEF.aspx.
- Do not be surprised if this does not work. First, this depends on having the project folder (Wrox6 here) somewhere under
the webroot on your webserver. (In my URL, the carlsond folder is the mapped network drive on the web server cis2.stvincent.edu
where I created the project in Visual Studio in the first place. If you have no network drive for this, you can use Website,
Copy Web Site to copy your website files. You then click Connect, select an appropriate folder for the copied files, highlight
in the left panel the files to be copied, and click the right arrow to copy those files to the folder shown in the right panel.
You can later transfer these files to your web server, perhaps by ftp.
- Even now, your web form probably does not work correctly on the live web server. This is because the IIS web server
needs to be told to use a certain application pool, one probably named ASP.NET v4.0, for your app.
Have your instructor or system administrator do the following,
unless you are the system administrator, in which case you should do these steps yourself.
- Log into the web server and start IIS Manager.
- In IIS Manager, under the overall website on this web server, find the Application Pools folder. Either find an existing
application pool that meets the following coditions, or right click in the empty space and add an application pool that
fulfills these conditions. The conditions are: Under Basic settings, it should use the .NET Framework v4.0 or later,
Managed Pipeline Mode needs to be set to Integrated, and check the box for "Start application pool" immediately.
Under Advanced Settings, Identity should be set either to NetworkService or ApplicationPoolIdentity. You may have to
figure out which one to use by trial and error. Also, Load User Profile should be set to true.
- In IIS Manager, find the person's website folder for this particular web application. For me, this folder was Wrox6.
- Right click this folder and select Convert to Application.
- Click the Select button next to the "Application pool" text box. Choose the application pool noted above.
This is the one I named ASP.NET 4.0. Click OK and OK.
- If you get the wrong application pool, you can right click the application and select Manage Application, Advanced Settings.
Click at the end of the application pool line and use the drop down to select the correct application pool.
- For further help on geting web apps to run under IIS on a live web server, see chapter 19, Deploying Your Web Site, in
Imar Spaanjaars's book Beginning ASP.NET 4 in C# and VB.
- Now check to see if the web form, accessed on the live web server, works correctly. Hopefully the answer is yes.
|
|
Search CIS Site
Tutorials
