从Azure中的WebJob访问SQL数据库 [英] SQL Database access from a WebJob in Azure

问题描述

我计划将WebJobs用作NServiceBus的轻量级替代品，但想首先验证是否可以从触发的WebJob处理程序进行常规SQL Azure数据库查询?我的数据库访问将通过EntityFrameworks.

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.

此SO线程表明WebJobs不支持SQL数据库，但我希望这仅意味着SQL数据库不能用作WebJob处理程序的触发机制?

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作业-如何连接到Azure MS SQL数据库?

我还没有找到发出SQL数据库查询的WebJob示例，但是由于WebJob可以访问与主WebSite相同的应用程序配置，因此我认为可以提供数据库连接详细信息吗?

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?

推荐答案

Webjobs是可以在Azure上运行的任何可执行文件(因此.NET程序可以正常运行). 触发机制是特定的，不能使用SQL Azure，但是您可以在可执行代码与Webjob本身一样中运行SQL Azure .

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.

例如，此webjob在对SQL Azure数据库执行查询并将结果写入到配置的存储容器中的文本文件之前，先等待"testwebjobsqueue"上的消息"web-jobs-testing-sql":

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();
            }
        }
    }
}   

当然，最好将连接字符串，存储容器名称等存储在托管该Webjob的网站的配置设置中(您可以在天蓝色门户中的配置选项卡"，因此您在网站上可访问的文件中没有任何设置.)

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).

这篇关于从Azure中的WebJob访问SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！