jump to navigation

Building an ASP.Net application with C# and Entity Framework June 27, 2009

Posted by fofo in asp.net, c# 3.0, Sql Server, Visual Studio 2008.
Tags:
trackback

In this post I will show you a step by step example on how to build an ASP.NET application with C# and Entity Framework. First let’s try to define what EF is and why it is going to help us to create easily data-centric applications.Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet. Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time.

ADO.NET Entity Framework (EF) is included with .NET Framework 3.5 Service Pack 1 and Visual Studio 2008 Service Pack 1

So you must donwload and have this software installed if you want to follow along.

It is not going to be a very difficult example. I will just bring data from a entity model to a gridview control and then give the user a way to filter that data.

There are many new enhancements regarding EF in VS 2010 and .Net .4.0 framework. I will not be talking about that since these enhancements are brand new and I am currently looking into them. I will just mention model-first development. This feature allows you to create your entity data model from scratch and then generate a database from it!!! We will do the opposite in this example.

Obviously for this example we will need a database. I will use the Pubs database. This is a well known database and many people are familiar with its schema.You can download the Pubs database from this link. If you need some help on how to install the database have a look here .

I have installed SQL Server 2008 Standard edition in my PC. SQL Server 2008/2005 Express editions will work just fine.You can download SQL Server 2008 Express edition from this link. I have attached the Pubs database in my local instance of the SQL Server.

Let’s start out project.

1) Launch Visual studio 2008

2) Create a new Project and from the available templates choose “ASP.Net web application”

3) Choose C# as your language of development and save your project in your hard drive with a name e.g “EFWebApplication” and click “OK”.

4) Click on the Solutions Explorer and open the Default.aspx page.

5) From the Toolbox drag and drop on the page , a Gridview control, a Textbox control and Button control. Leave the default names.

6) From the Toolbox drag and drop a EntityDatasource control on the page.

7) Choose the data source of the gridview control to be the EntityDatasource1 object.

8) Now we are ready to create our entity model. Right – click on your project from the Solutions Explorer window and Add a new Item. From the availble templates choose ADO.NET Entity data model. Give it the name Pubs.edmx and click the Add button.

9) In the Entity data model wizard window choose Generate from database and click Next .

10) Click New Connection, choose the Server name and from the databases your Pubs database is attached and then connect to the Pubs database and test your connection and click OK

11) If you notice you will see that there is something called Entity connection string and looks like this

metadata=res://*/Pubs.csdl|res://*/Pubs.ssdl|res://*/Pubs.msl;provider=System.Data.SqlClient;provider connection string=”Data Source=FOFO-PC;Initial Catalog=pubs;Integrated Security=True”

The connection string used by the Entity Framework contains not only the database connection string, but a metadata parameter with pointers to three mapping files separated by a pipe character.These mapping files will be generated when we finish this wizard.

12) Click Next on your wizard window and from the databases objects available choose Tables and more specifically the Titles, Authors and TitleAuthor tables. Leave the Model namespace as pubsModel and click Finish.

13) Our new Pubs.edmx file called is created and all the entities are generated which are basically classes derived from the data model . Have a look at the picture below to see the Entity model in the Designer window. You can zoom in and zoom out using the appropriate buttons.

pubsedmx

14) Have a look at the Mapping details of each entity type (Authors, Titles,  TitleAuthor). You can change if you want the property names for a particular entity type. For example you can change au_lname to lastname of the Authors entity type. You can also change the names of the entity types and singularise them in a way so they resemble more like a class name. Change Authors to Author and Titles to Title from the Designer.

15) Go to your default.aspx page and click on the EntityDatasource object and hit the option Configure Data Source. In the window that appears choose Named Connection and select the PubsEntities that will apear in the drop-down and hit the Next button.

pubsef-1

16) In the next step from the EntitySetName select authors. Select all fields and and enable automatic inserts,updates,deletes and hit the Finish button.

pubsef-2

17) In your gridview control enable paging,sorting,editing,deleting and selection.

pubsef-3

18) Build and run your application and see the records in your web page. Try to sort,edit, delete records. Well done!!!!

19) Let’s give the ability to the user to apply a filter to the data by typing something to the textbox and return only the relevant data.Choose your EntityDatasource object and from the Properties window select  Where. Click on the “…” to launch the Expression editor window.

20) We will create an expression to use it as a filter(e.g filter the records by city name). The expression could be something like this

it.city=@city OR @city IS NULL

This expression above is written in Entity SQL which is T-SQL like syntax.

Click the Add Parameter button and under name write “city” and the value will be a control, so from the Parameter Source select Control and from the ControlID your textbox control (e.g TextBox1)

Go to show advanced properties and in the “Type” field choose String.  Your settings should be like this:

pubsef-4jpg

21) Hit the OK button to close the Expression Editor window. Hit F5 to run your application. In the textbox type

“Oakland” and hit the button control. See the filtered results. That is all!!!! We did that without writing a single line of code.

If you need the source code for this example, just leave a comment and I will email it to you as soon as possible.

Hope it helps!!!

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to Furl

Comments»

1. RRaveen - July 2, 2009

Dear Friends,

I hope you are doing well. I have launched a web site http://www.codegain.com and it is basically aimed C#,JAVA,VB.NET,ASP.NET,AJAX,Sql Server,Oracle,WPF,WCF and etc resources, programming help, articles, code snippet, video demonstrations and problems solving support. I would like to invite you as an author and a supporter. Looking forward to hearing from you and hope you will join with us soon.

Please forward this email to all of your friends who are related IT. Send to us your feed about site also.

Thank you
RRaveen
Founder CodeGain.com

2. Steve - July 27, 2009

That’s fine if you want to bind your gridview to a single table. But I often find myself wanting to bind gridviews to search results that carry across multiple tables.

To use your example, say you have a search box searching for the title of the book. In your results, it’s perfectly reasonable to want to include the author name, but I can’t seem to find an easy way to do this. Can you shed any light?

3. VN - August 26, 2009

Gr8 begining :)

4. ASP.NET 4.0 Entity DataSource and GridView « DOT NET RULES - September 27, 2009

[…] I have talked about EF and how to build an application with EF and C# in one of my previous posts. In this post I will talk more about the Entity Datasource object and enhancements made in the […]

5. raj - April 28, 2010

That’s fine if you want to bind your gridview to a single table. But I often find myself wanting to bind gridviews to search results that carry across multiple tables.

To use your example, say you have a search box searching for the title of the book. In your results, it’s perfectly reasonable to want to include the author name, but I can’t seem to find an easy way to do this. Can you shed any light?

6. Cleo Arrendell - December 31, 2010

Hello. I hope you dont mind me commenting here. I wanted to contact you but I cannot find your contact info. I want to subscribe to your blog for new posts but I am unable find your RSS subscription link. Please help. Thank you.

7. fofo - December 31, 2010

you have to scroll down in the left column and you will find the RSS subscription link.

8. Riz - April 14, 2011

Hello
Thanks for explaining asp.net i a simple way , i need the source code.

9. Riz - April 14, 2011

hello
thnks for explaining all that i a simple way, kindly provide me the source code.
Tkank u

10. mj - November 9, 2011

I am new to entity frame work .I wanted to get max of a column value in select query .How to acheive this .Help will be really apreciated .

this is the existing query here it.[PaymentYear] should be max of payment year and similerly in where condition it.[PaymentYear]=max[Paymentyear]

11. Lalith - March 30, 2012

As a beginner helpful code for me
thanks


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: