How to extract and update data from Azure SQL DB from Azure functions

Question

So I have this Azure function that works, and an Azure SQL DB with some data. But I cannot find a decent example to get data from the DB into the function. Surely, crafting a query string and SQLCommand.BeginExecuteReader/EndExecuteReader is not the preferred way, right? LINQtoSQL perhaps?

Thanks, Bezz

There are a lot of different way to get data from sql using C#/.Net, what are you asking for ? What have you tried so far ? — Thomas
– Thomas, Commented Jun 13, 2018 at 1:49
Thx for your response. I'm sure there are different ways to do this. At the moment, I'm doing just a proof of concept, so I guess I'm looking for a "Hi, Scott Hanselman here and in this five minute video, I'm going to show you how to do basic CRUD work from your Azure function to Azure SQL DB for our record store test app."-kinda answer :-) — DevBezz
– DevBezz, Commented Jun 13, 2018 at 7:52
Hmmmm, maybe it's just as simple as this: SqlConnection sqlConnection1 = new SqlConnection("Your Connection String"); SqlCommand cmd = new SqlCommand(); SqlDataReader reader; cmd.CommandText = "SELECT * FROM Customers"; cmd.CommandType = CommandType.Text; cmd.Connection = sqlConnection1; sqlConnection1.Open(); reader = cmd.ExecuteReader(); // Data is accessible through the DataReader object here. sqlConnection1.Close(); — DevBezz
– DevBezz, Commented Jun 13, 2018 at 8:51

Mikhail Shilkov · Accepted Answer · 2018-06-13 07:56:09Z

1

You are free to use any .NET Data Access library that's available for other types of applications: ADO.NET, Entity Framework, Dapper etc.

A simple example:

Use Azure Functions to connect to an Azure SQL Database.

edited Jun 13, 2018 at 7:56

answered Jun 13, 2018 at 4:19

Mikhail Shilkov

35.3k4 gold badges78 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DevBezz Over a year ago

Thx for your response. I'm sure there are different ways to do this. At the moment, I'm doing just a proof of concept, so I guess I'm looking for a "Hi, Scott Hanselman here and in this five minute video, I'm going to show you how to do basic CRUD work from your Azure function to Azure SQL DB for our record store test app."-kinda answer :-)

DevBezz Over a year ago

Sorry Mikhail, overlooked your link. Thanks for that, but that's just what I meant by crafting a query string. I followed SQLCommand and ended up with BeginExecuteReader. Maybe my knowledge of SQLCommand is too limited and there might be a simpler way to do a simple select query.

DevBezz · Accepted Answer · 2018-06-13 22:45:57Z

Apparently, it was quite simple. This code did the trick. Although I'm not completely happy with the fact that I'm crafting a query string. For now, it will do.

#r "System.Configuration"
#r "System.Data"

using System.Configuration;
using System.Data.SqlClient;
using System.Threading.Tasks;
using System.Net;

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
    var str = ConfigurationManager.ConnectionStrings["sqldb_connection"].ConnectionString;
    var caterers = new List<string>();

    using (SqlConnection conn = new SqlConnection(str))
    {
         conn.Open();

         SqlCommand cmd = new SqlCommand();
         SqlDataReader reader;

         cmd.CommandText = "SELECT * FROM Caterers";
         cmd.Connection = conn;

         reader = cmd.ExecuteReader();

         if (reader.HasRows)
         {
             while (reader.Read())
             {
                 caterers.Add(reader.GetString(1));
             }
         }

         conn.Close();
    }
    return req.CreateResponse(HttpStatusCode.OK, caterers);
}

Collectives™ on Stack Overflow

How to extract and update data from Azure SQL DB from Azure functions

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related