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="CONNECTION STRING;Encrypt=True;;MultipleActiveResultSets=False"" 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 !!!
While working on SQL Azure connectivity from Visual Studio 2010, I faced the above error. The full error text is as below:-
“Server name cannot be determined. It must appear as the first segment of the server's dns name (servername.database.windows.net). Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match. “
As the message suggested, I tried putting the username@servername but it didn’t help. The strange this was, the same connection string worked from Visual Studio 2008. Let me add more clarity to this.
With SQL Azure, you can migrate your on-premise database to SQL Azure (read my previous post on this) and thereafter, just change the connection string in your Web / Desktop Application Configuration file to point to the SQL Azure Database. The SQL Azure portal provides the connection string in the following format
Server=tcp:SERVERNAME.database.windows.net;Database=DATABASENAME;User ID=USERNAME;Password=myPassword;Trusted_Connection=False;Encrypt=True;
So, in normal cases, you can create an application and do a data binding to get the connection string inserted into the configuration file. Later, once you migrate the data into the SQL Azure service, you can just change the connection string to the above (after adding the correct Servername, UserId, Password, Database name etc.,)
I did the above steps in Visual Studio 2008 and the application showed zero difference between binding from the local database instance and from the SQL Azure instance.
However, when I was trying this in Visual Studio 2010 Beta 2, the local instance bound properly (as expected) and when I replaced the connection string with the SQL Azure Database connection string, it started throwing the error explained in the beginning of the post.
The change I had to make was, in the connection string, for the User ID I had to specify USERNAME@SERVERNAME example rajiv@sdkldfldfdfd (both username and servername are imaginary. note the servername is just the name of the server and not the fully qualified path with .database.windows.net etc., which is not required)
Post this change, the application was able to bind data from the SQL Azure Database without any issues.
So, for Visual Studio 2008, it was simply username and for Visual Studio 2010, it required username@servername
Cheers !!!