Connect time-triggered Azure Function with Database using SQL Client

Question

I am connecting time-triggered Azure Function with SQL Server using SQL client but I am not getting any data.

Here is my code:

local.settings.json:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet",
    "DefaultConnection": "Data Source=; Initial Catalog=;User ID=;Password=;MultipleActiveResultSets=True;Persist Security Info=True;"        
  }
}

Function1.cs:

public class Function1
{
   [FunctionName("Function1")]
   public static async Task Run([TimerTrigger("0 45 14 * * *")]TimerInfo myTimer, ILogger log)
   {
     var sqlConnection = Environment.GetEnvironmentVariable("DefaultConnection");
     using (SqlConnection conn = new SqlConnection(sqlConnection))
     {
      conn.Open();
      var text = "SELECT * from UserMaster where UserId=1234";
      //This query has around 50 data in the database but still getting no data in it.
      
      using (SqlCommand cmd = new SqlCommand(text, conn))
      {
        using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
        {
          while (reader.Read())
          {
             log.LogInformation($"{reader.GetString(0)}{reader.GetString(1)} rows selected");
             Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1));
          }
        }
      }
     conn.Close();
    }
   log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");
  }
}

I am not getting what is wrong with it. Please suggest

Do you get any exception? What happens during debug, especially the SqlConnection and executing the SqlCommand. Are they executed correctly? I assume that you removed the values for "DataSource", "UserID" etc. in your local.settings.json because you do not want to publish them on stackoverflow but normally you have values in there? — rekcul, May 25 '21 at 09:45
Hi @rekcul, I don't get any exception, it only shows that the table has no records. If I use the `ExecuteScalarAsync` instead of `ExecuteReaderAsync` the it works perfectly but the problem is that I want all the records instead of the single column of the 1st row. Also I purposely removed the values for "DataSource", "UserID" etc here in stackoverflow. — Nivi, May 25 '21 at 10:02
How is it going on? Did you got any break? Thanks for your response in advance : ) — Tiny Wang, Jun 03 '21 at 08:28

Tiny Wang · Answer 1 · 2021-06-02T06:09:16.100

I think you can use public override object this[string name] { get; } instead. Here's my code and it worked well, you may compare it with yours.

 using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace Function0602
{
    public static class Function1
    {
        [FunctionName("Function1")]
        public static async Task<List<tinyTest>> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            List<tinyTest> list = new List<tinyTest>();
            var str = "connect_string";
            using (SqlConnection conn = new SqlConnection(str))
            {
                conn.Open();
                var text = "select * from tinyTest;";
                using (SqlCommand cmd = new SqlCommand(text, conn))
                {
                    using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
                    {
                        while (reader.Read())
                        {
                            tinyTest res = new tinyTest();
                            //SqlDataReader skd provide the method of reader["column_name"]
                            res.user_id = (string)reader["user_id"];
                            res.user_name = (string)reader["user_name"];
                            res.age = (int)reader["age"];
                            list.Add(res);
                        }
                    }
                }
                conn.Close();
            }
            log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");
            return list;
        }
    }

    public class tinyTest {
        public string user_id { get; set; }
        public string user_name { get; set; }
        public int age { get; set; }
    }
}

So the steps inside while loop are not executed as far as I understand? — Roman Zeleniy, Jun 02 '21 at 06:00
I think you can check if reader has rows to make sure the query has got correct result. https://i.stack.imgur.com/6hoAe.png If it's false, that means has no rows response. — Tiny Wang, Jun 02 '21 at 06:07

Connect time-triggered Azure Function with Database using SQL Client

1 Answers1