Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

If you have used the Entity Framework that shipped with Visual Studio 2008 SP1, you would really start appreciating the flexibility it offers for building schema driven data access layer and get it to the UI Layer either directly or using a middle tier such as WCF RIA Service.   Check my earlier post on this, if you are interested further :)

Meanwhile, the other exciting stuff that has been around is the SQL Azure which is part of the Windows Azure platform.  SQL Azure provides relational data over the web which means, the Database is hosted, maintained and all is done by us and you get to store your database and query the same as if you were running it in your local Data Center or server.  Of course, SQL Azure is currently CTP and you can get free access to it if you have the Azure Tokens.

While I had earlier written about Migrating your database to SQL Azure that example used an ASP.NET front end which had a GridView doing direct data binding with SQL DataSource.   Obviously, one would want to use some of the more abstract controls such as LINQ DataSource / Entity DataSource.

The trick however, here is that, when you create an Entity DataSource from your local database, the Entity Modelling Designer gets access to pull all the required information and build a nice schema with meta data.  However, if you try to bind your Entity Model to a SQL Azure tool, then you wouldn’t  get this flexibility since SQL Azure doesn’t provide support for the same currently. 

Kevin Hoffman provides a nice work around in his post which I want to repeat here for the sake of continuity in the post.  If you would like to generate an Entity Model out of your SQL Azure Database, have a local copy of the database running with the same schema.  The trick is to first point your Entity Designer tool to your local database and allow it to pull all the required information from here and build the entity model.  Thereafter, you can just visit the SQL Azure Portal at https://sql.azure.com and pick up the identical database’ connection string to be copied to your web.config file to replace the local connection string (Read my earlier post for a steps).

Now, the Entity Framework connection string is a little complicated with a lot of settings.  The connection string path is provided as a property within the main connection string. For example a typical Entity Framework connection string in the web.config file looks as below:-

connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;CONNECTION STRING;Encrypt=True;;MultipleActiveResultSets=False&quot;" providerName="System.Data.EntityClient" /></connectionStrings>

In the above, as you can see, the actual connection string starts from provider connection string setting. 

You need to replace that portion with the Connection string copied from the SQL Azure Portal for your Database.  Make sure you don’t mess up with the $quot and other settings by mistake.

Secondly, if you are running Visual Studio 2010 Beta 2 and trying to accomplish this, in the copied connection string, you need to change the User Id part to USERNAME@SERVERNAME (read my previous post on this for more information) .  Also, the default password that is copied from the SQL Azure portal is “mypassword” which you have to change to your actual password.

Finally you also need to set the MultipleActiveResultSets to False since SQL Azure doesn’t support the same currently.  Otherwise, you will get an error.

Once you have taken care of all the steps above, your page works seamlessly as it did while binding the entity framework to your local database (provided you have an identical database to one that is running on SQL Azure)

I have highlighted the important portions in the connection string so that you can take care of those when binding.

Cheers !!!

Print | posted on Thursday, December 17, 2009 7:23 PM

Comments on this post

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
I can't seem to get this to work with VS '10. I changed the user id to USERNAME@SERVERNAME like you suggested but the page still isn't working.
Left by Sell Gold on Jan 15, 2010 5:35 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
This is indeed a great opportunity!
Left by Freelance Writing Jobs on Feb 17, 2010 12:35 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
I'm having the same problem as "Sell Gold" dont know what happens, need to check everything,
Left by Ganar dinero on Feb 22, 2010 9:26 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
I had the same problem also, and couldn't quite figure it out. its funny how things can change between different versions, you would think if the change was made it would be made clear! Cheers
Left by saddles for sale on Feb 27, 2010 2:52 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
thanks I was looking for this.

Julie
how can i get pregnant fastget pregnant tips
Left by Julie on Mar 10, 2010 9:41 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
Your weblog happens to be not just informative but also very stimulating too. There are a limited number of people who are capable of write technical articles that creatively. we are on the lookout for information regarding this topic. We ourselves went through several websites to find knowledge with regard to this.I will keep coming back !!
Left by Insurance Quotes on Mar 14, 2010 1:22 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
Visual Studio 2010 in awesome
Left by Healthy Living Tips on Mar 14, 2010 2:47 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
The beta shows lots of promise
Left by sarasota real estate appraiser on Mar 14, 2010 2:50 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
Entity Framework abstracts the relational (logical) schema of the data that is stored in a database and presents its conceptual schema to the application.
The disadvantage of this approach is that if this schema is changed the application is not shielded from the change. Your suggestion about binding entity framework with SQL Azure Database is very helpful.
Thanks
Left by tall ship on Mar 25, 2010 10:42 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
Thinking good when everyone seems to have a good post with this one. Glad that I hear it from you dude.
Left by Cebu SEO Specialists on Mar 27, 2010 8:14 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
Entity-relationship modeling, introduced in the 1970s by Peter Chen. Entity Framework has made entity-relationship modeling executable. They achieved this using a combination of XML schema files, behind the scenes code generation (creating .NET objects), and the ADO.NET Entity Framework APIs.
This coding very helpful for me. Cheers
Left by Diet on Mar 28, 2010 1:36 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
I'm having the same problem as "Sell Gold" dont know what happens, need to check everything,
Left by CNA Training on Mar 29, 2010 6:09 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
Thanks for sharing this post.

John.
Left by ranเถื่อน on Apr 01, 2010 4:57 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
Congratulations for that winning shot!
Left by Care Nurse Resume on Apr 07, 2010 9:18 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
The disadvantage of this approach is that if this schema is changed the application is not shielded from the change. Your suggestion about binding entity framework with SQL Azure Database is very helpful.
Thanks
Left by legitimate home based job on Apr 13, 2010 2:53 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
I am so impressed w/evernote so far. really pro stuff. thx folks
Left by CNA Practice Test on Apr 16, 2010 2:23 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
SQL framework eases dynamic SQL generation by providing a convenient Object Model for constructing database independent SQL queries. Current version supports, complex queries, Paging and Cross Tabs
Left by juvenile boot camps on Apr 29, 2010 2:44 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
With SQL Azure, developers building Web 2.0, ASP.NET and PHP applications can use familiar tools and data models to develop on a pay-as-you-grow, secure, scalable and highly available database service at minimal infrastructure cost.
Left by oracle data guard training on Apr 29, 2010 6:21 PM

# # re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
Your suggestion about binding entity framework with SQL Azure Database is very helpful.
Left by Lasik leikkaus on May 02, 2010 4:02 AM

# www

Requesting Gravatar...
Your suggestion about binding entity framework with SQL Azure Database is very helpful.
Left by linkslondon on May 05, 2010 2:55 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
this is information is so important for a newbie like me.thanks for this

cebu seo specialist
Left by Arries Jay Germo on May 07, 2010 4:52 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
Thanks for such a great post and the review, I am totally impressed! Keep stuff like this coming.
Left by First Aid Kits on May 07, 2010 8:49 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
Congratulations for that winning shot!.
Left by Streamlight Fire Vulcan on May 10, 2010 6:54 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
Looks like a great blog. I can see that everyone has participated. I currently work for both
San Diego Auto Insurance and also as a
San Diego Electricians which helps me to be able to help people everyday.
Left by san diego electrician on May 21, 2010 12:18 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
Thanks for sharing !So nice to see you !I will buy a wallet for my best friend! Shopping Cheap Ed Hardy
Left by cute belly rings on Jun 03, 2010 6:15 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...


If anyone wants a more in-depth look at how to write Rack Middleware (and Rack apps), you might like my Rack Middleware screencast:

http://remi.org/2009/02/28/rack-part-3-middleware.html

Thanks for showing off all of the sweet things that we get with Rails 2.3, now that Rails has such awesome Rack support :)
Left by belly button rings on Jun 03, 2010 6:17 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...

Post this change, the application was able to bind data from the SQL Azure Database without any issues.
Left by Gucci bags on Jun 04, 2010 2:53 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
how to post this change? i cannot get it work. I am doing something wrong somewhere
Left by CNA Training on Jun 04, 2010 10:43 PM

# CNA

Requesting Gravatar...
Thanks for the useful information
Left by Nick on Jul 26, 2010 6:51 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
This is great. Thanks so much.
Left by Baby Modeling on Aug 19, 2010 2:26 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
Thanks a lot man, this is exactly the kind of specifics I was looking for!

Cheers
Left by Phlebotomy Training on Aug 24, 2010 9:01 AM

# aasdf

Requesting Gravatar...
konstruktiv eigene Ideen und Anregungen einzubringen. Wir sind der Meinung, dass eine command nationale Herangehensweise dem internationalen Charakter des Internets und habitation vielfältigen thomas sabo
Herausforderungen und Entwicklungen
Left by asdfsaf on Sep 07, 2010 11:42 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
I know you have seen these folks who seem to be able to get levels fast in World Of Warcraft and race to level 70 with lightning speed....
Left by tail lights on Sep 23, 2010 6:59 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
Really you have shared some useful information over the post. It was really the most important day for you. I like the post very much. Thanks a lot for sharing.
Left by investment on Oct 03, 2010 4:10 AM

# Frankenfish Likes it

Requesting Gravatar...
I am impressed with the post. I am sure it will be able to grab the attention of visitors like me too. Lastly, I would like to thank you for sharing.
Left by frankenfish on Dec 05, 2010 7:38 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
I do realise this is a very basic example, but it does introduce you to a number of fundamental ways to interact with your SQL Azure database. You have no successfully used SQL Management Studio 2008 R2 and Visual Studio 2010 (Beta 2) to contact SQL Azure. To top it all off, we have (hopefully) managed to use the Entity Framework to query real dat
a in the database.
Left by judaic on Jan 04, 2011 1:17 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
The website is too busy to show the webpage
HTTP 408/HTTP 409
What you can try:
Refresh the page.

What is this and how can I read rest of the pages of your blog?
Left by Jogos Gratis on Jan 12, 2011 5:28 AM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
I will instantly grab your rss feed to stay privy of any updates. Good work and much success in your business efforts
Left by free scholarship on Jun 04, 2011 6:59 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
I just want to comment on the design. Its too loud. Its doing way too much and it takes away from what youve got to say --which I think is really important.
Left by car accessories on Jun 04, 2011 7:02 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
I enjoy your blog! I’ve been reading for the previous two months, and I feel your posts are brilliant!
Left by car under 500 dollar on Jun 04, 2011 7:03 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
the post is really the freshest on this deserving topic. I harmonise with your conclusions and will eagerly look forward to your forthcoming updates. Saying thanks will not just be enough, for the tremendous lucidity in your writing
Left by car review and price on Jun 04, 2011 7:05 PM

# Free Article Submission Directory

Requesting Gravatar...
It’s always refreshing reading good blog, Well with your permission allow me to grab your rss feed to keep up to date with any new incoming post. Thanks a million and please keep up the good work.
Left by Free Article Submission Director on Aug 24, 2011 2:02 PM

# re: Binding Entity Framework to your SQL Azure Database – Visual Studio 2010 Beta 2

Requesting Gravatar...
awesome, thanks for the info on visual studios. I still use free stuff. It's more convenient. thanks.
Left by electrician san diego on Nov 16, 2011 4:09 AM

Your comment:

 (will show your gravatar)