I would like to pass the azure sql input binding where my sql command is a select statement with a parameter. This does not work. It keeps telling me the 'complete' is not a parameter. What am I doing wrong or is what I'm trying to do impossible with sql input binding? When I have a simple statement like select top(10) id, status from Dispatch - it works. Can I not pass a string to the parameters?

[FunctionName("Function1")]
public async Task Run([TimerTrigger("0 */2 * * * *")]TimerInfo myTimer,
[Sql("select top(10)id, status from Dispatch where status = @critstatus; ",
CommandType = System.Data.CommandType.Text,
Parameters = "@critstatus= {'complete'}",
ConnectionStringSetting = "SqlConnectionString")]
IAsyncEnumerable dispatch, ILogger log)

or

[FunctionName("Function1")]
public async Task Run([TimerTrigger("0 */2 * * * *")]TimerInfo myTimer,
[Sql("select top(10)id, status from Dispatch where status = \''complete\'' ",
CommandType = System.Data.CommandType.Text,
ConnectionStringSetting = "SqlConnectionString")]
IAsyncEnumerable dispatch, ILogger log)

CodePudding user response：

According to this MSFT Documentation, Your SQL Query is not bindable as input to the Timer trigger as we have tested in our local environment.

Supported Input Bindings for the Azure Function Timer Trigger:

To fetch the data by giving input parameters for every 2 min (Timer Trigger) in Azure Function, we can call the SQL Query String inside the Run Method Code.

Function 1.cs:

        public void Run([TimerTrigger("0 */2 * * * *")]TimerInfo myTimer, ILogger log)
        {
            log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");
           
            string connString = "Server=<yourservername>,1433;Initial Catalog=samplesqldb0808;Persist Security Info=False;User ID=<username>;Password=<password>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";


            //Query to fetch data
            string queryString = "SELECT TOP 2 Id, Status FROM [dbo].[Dispatch] WHERE Status = 'Complete';";
            
            //Connection
            using (SqlConnection connection = new SqlConnection(connString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@Status", "Complete");
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        log.LogInformation(String.Format("{0}, {1}", reader["Id"], reader["Status"]));
                        
                        Console.WriteLine(String.Format("{0}, {1}",
                        reader["Id"], reader["Status"]));// etc
                    }
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                }
            }
        }

Created Sample data in the database with below SQL Query:

CREATE  TABLE dbo.Dispatch (
[Id] int  primary  key,
[Status] nvarchar(200) not  null
)

INSERT  INTO Dispatch (Id, Status)
VALUES
(571, 'Pending'), (572, 'Complete'),
(573, 'InProgress'), (598, 'Complete'),
(593, 'Complete'),(581, 'Complete'),
(597, 'InProgress'), (596, 'Pending');

Result:

I could see the results in the console for both of the methods Logging and Console WriteLine:

CodePudding user response：

I was not able to get the parameter to work so for now I was able to get the timer to work with the azure sql input binding.

public async Task  Run([TimerTrigger("0 */2 * * * *")] TimerInfo myTimer,  
            [Sql("select top(10)id, status, arrivaldate from Dispatch where  
                 status = \'in progress\';" ,
                 CommandType = System.Data.CommandType.Text,
                 ConnectionStringSetting = "SqlConnectionString")]
                 IAsyncEnumerable<Dispatch> dispatch, ILogger log)
            {

                IAsyncEnumerator<Dispatch> enumerator = 
                           dispatch.GetAsyncEnumerator();
                   var dispatchList = new List<FindDispatch>();
            while (await enumerator.MoveNextAsync())
            {
                dispatchList.Add(enumerator.Current);
            }
            await enumerator.DisposeAsync();