Retrieve custom table data with Azure SQL

Question

I have an Azure Web Site, using a free Azure SQL database, and I have installed Umbraco CMS 7.1.1 to develop the site with. I have also created a custom table using Azure's SQL Management feature and I have created a couple of test rows with dummy text. How can I connect to my custom table and display the data on a page?

Usually I work with MySQL and fetching data is relatively easy to do but I'm having trouble converting my code to work with Azure SQL. The following is my code which is almost identical to when I use MySQL, but with this snippet I get the error "Keyword not supported: 'flush interval'". Has anyone been able to fetch custom table data with Azure SQL?

ConnectionStringSettings cs = ConfigurationManager.ConnectionStrings["umbracoDbDSN"];

using(SqlConnection con = new SqlConnection(cs.ToString()))
{
    string sql = "SELECT * FROM [dbo].[MyTable]";
    con.Open();
    using(SqlCommand cmd = new SqlCommand(sql,con))
    {
        SqlDataReader reader = cmd.ExecuteReader(); 
    }
    con.Close();
}

Change cs.ToString() to cs.ConnectionString. The returned ConnectionStrings is of type ConnectionStringSettings object, that has a property ConnectionString. By calling the ToString method on the object you don't fetch the connection string but the serialization of the object, which is not a valid connection string. — keenthinker

Robert Foster Robert Foster · Accepted Answer · 2014-05-02T11:26:08

If you are accessing this in an Umbraco-based website and have the tables within the same database that Umbraco is using, you can get the connection string by accessing the ConnectionString property on the DatabaseContext:

using (var con = new SqlConnection(Umbraco.Core.ApplicationContext.Current.DatabaseContext.ConnectionString)) {

  // Your code here
}

However, you may find it advantageous to use the built-in PetaPoco support that Umbraco offers. There's a good example of using PetaPoco here: http://creativewebspecialist.co.uk/2013/07/16/umbraco-petapoco-to-store-blog-comments/

Retrieve custom table data with Azure SQL

1 Answers