Can I make a SQL query and return its result with Azure Functions?

Question

I want to port my C# program to MS Azure Functions. It does a SQL-Query and returns the result (an IEnumerable). I want to use it in PowerApps.

Can I do this and how?

Answer 1

To connect to SQL, you need add a connection string in the app settings. See here for detailed instructions. Here is sample for HTTPTrigger in CSharp that uses Linq to SQL and returns the result from the query

• Got to Function App Settings --> Go to Kudu --> Go to D:\home\site\wwwroot\YourFunction
• Create folder bin
• Upload System.Data.dll, System.Data.Linq.dll

• Upload following TodoItem.csx either from View Files UI on the portal or Kudu

  #r "System.Data.Linq.dll"
  
  using System.Data.Linq.Mapping;
  
  [Table(Name = "TodoItems")]
  public class TodoItem
  {
     [Column]
     public string Id;
     [Column]
     public string Text;
     [Column]
     public bool Complete;
  }
  

  Note: TodoItems is a table your database

• HttpCSharpTrigger function

  #r "System.Data.dll"
  #r "System.Data.Linq.dll"
  
  #load "TodoItem.csx"
  
  using System.Net;
  using System.Data.SqlClient;
  using System.Data.Linq;
  
  public static async Task<HttpResponseMessage> Run(HttpRequestMessage   req, TraceWriter log)
  {
       log.Info("C# HTTP trigger function processed a request.");
  
       var connectionString =   System.Configuration.ConfigurationManager.ConnectionStrings["sqlconn"].ConnectionString;
       SqlConnection conn = new SqlConnection(connectionString);
       DataContext db = new DataContext(conn);
       Table<TodoItem> todoItems = db.GetTable<TodoItem>();
       IEnumerable<TodoItem> items = todoItems.ToList();
  
       return req.CreateResponse(HttpStatusCode.OK, items);
  }
  

Note: sqlconn is the name of the App Setting

You will be able to call into this API from Power Apps

Hope this helps!