Tuesday, September 29, 2009

Connecting to SQL Azure from ASP.NET

Let’s talk a little bit about SQL Azure.
Generally when you subscribe for SQL Azure at https://sql.azure.com/ you will receive your activation token (although is bit too late already to go for it, but you may have a chance). Once activated the service, it will automatically create a new project for you named “SDS-only CTP Project” and you will have options to manage it.
Then you go and create your first SQL Azure data base. From now on you will be fighting to use the toolset you used to, when developing against SQL Server. However there are some niceties you need to learn in order to get working with it.
First of all is how to connect to SQL Azure using either sqlcmd or SSMS (SQL Server Management Studio). I wrote detailed post about it here: http://blogs.staykov.net/2009/08/how-to-use-sql-azure.html
Next you will want to simply run a GridView bound to the SQL Azure database. Well … do not expect that either Server Explorer, or SQL DataSource creation wizard will work with it. Just forget about these tools for now. You are on your own. Or not exactly.
Here: SqlAzureSample.zip I have uploaded two sample projects and simple SQL script. So after you have read how-to-connect-to-sql-azure, and after you have set up your first database and assigned a login to it, you can start with the projects:
  • create_insert.sql simply creates a table and inserts some records into it.
  • WebSiteToSqlAzure.zip contains simple ASP.NET web-site which is bound to SQL Azure
  • WebRoleToSqlAzure.zip contains a CloudService project with single web role, which is bound to SQL Azure
Both web sites that are attached are using one and the same ConnectionString:
"Server=tcp:[YOUR_SERVER].ctp.database.windows.net;Database=[YOUR_DB];User ID=[LOGIN_WITHOUT_@SERVER];Password=[PASSWORD];Trusted_Connection=False;"
There are some placeholders for you to replace:
[YOUR_SERVER] – the server your CTP of SQL Azure resides. Something like "ccmlt4qacx
[YOUR_DB] – the database name you have created
[LOGIN_WITHOUT_@SERVER] – the login you have associated with database
[PASSWORD] – the password you have created for the login
Hope that this will be a good starting point for you!
And hope to continue Azure series :)