SQL Database access from a WebJob in Azure

Question

I plan to use WebJobs as a lightweight substitute for NServiceBus but wanted to first verify that routine SQL Azure Database queries can be made from a triggered WebJob handler? My database access will be through EntityFrameworks.

This SO thread indicates that WebJobs does not support SQL Database but I hope this just means that SQL Database cannot be used as a triggering mechanism for a WebJob handler?

Azure Web Job - How to connect to an Azure MS SQL Database?

I have not found a WebJob sample that issues SQL Database queries but since a WebJob has access to the same app config as the main WebSite I assume database connection details can be made available?

Answer 1

Webjobs are any executable that can run on Azure (so .NET programs will run fine). The triggering mechanism is specific and CANNOT utilize SQL Azure but you can run SQL Azure in your executable code WITHIN the webjob itself.

For Example, this webjob waits for the message 'web-jobs-testing-sql' on 'testwebjobsqueue' before executing the query on the SQL Azure database and writing the results to the text file in the configured storage container:

namespace AzureWebJobs
{
    class AzureSqlTest
    {
        static void Main()
        {
            JobHost host = new JobHost();
            host.RunAndBlock(); 
        }

        public static void SyndicateFiles([QueueInput("testwebjobsqueue")] string inputText, 
                            [BlobOutput("temp/WebJobs-log.txt")]TextWriter writer)
        {
            if (!inputText.StartsWith("web-jobs-testing-"))  
                return;

            writer.WriteLine(String.Format("Starting to do processing for " + inputText + " at {0}", DateTime.Now.ToShortTimeString()));
            string storageContainerName = ConfigurationManager.AppSettings["StorageContainerNameTemp"].ToLower();

            AzureStorageUtils.ConfigureStorage(storageContainerName);

            SQLTest sqlTest = new SQLTest();
            sqlTest.RunSqlQuery(inputText, writer);
            writer.WriteLine(String.Format("Syndication Finished at {0}", DateTime.Now.ToShortTimeString()));
        }
    }


    class SQLTest
    {
        public SQLTest()
        {

        }

        public void RunSqlQuery(string queueMessage, TextWriter writer)
        {
            if (queueMessage == "web-jobs-testing-sql")
            {
                string connectionString = "Server=tcp:YourDatabaseServerName.database.windows.net,1433;Database=YourDatabaseName;User ID=YourSQLAzureUserID;Password=YourStrongPassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;";
                SqlConnection sqlConnection1 = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand();


                cmd.CommandText = "SELECT * FROM Users";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = sqlConnection1;

                sqlConnection1.Open();

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    // Data is accessible through the DataReader object here.
                    while (reader.Read())
                    {
                        writer.WriteLine(reader.GetValue(1).ToString());
                    }
                    reader.Close();
                }
                sqlConnection1.Close();
            }
        }
    }
}   

Of course it would be best to store your connection string, storage container names, etc. in the configuration settings of your website hosting the webjob (you can do this in the 'app settings' and 'connection strings' sections of the 'configure tab' in the azure portal so you don't have any settings in files accessible on the website).

Answer 2

We do not have a triggers for SqlAzure yet. It is something we would consider opening up by way of opening up extensibility for allowing you to Trigger functions based on different events such as SQL Azure, File System watchers etc. You can share the ConnectionString that you can share between your site and webjob. The following post captures this Use connectionstring in WebJob on Azure