Data Access from an Azure Function (.net core 3.1) to Azure SQL DB

Question

What would be a more appropriate way to do data access from an Azure function to Azure SQL DB. In my case, it will an HTTP triggered function. I need to parse the message body and insert the data in a DB table. I have already created the table in the database.

I extracted the required properties from the JSON body and formed a hard-coded sql statement like so,

var text = "INSERT INTO dbo.customer VALUES ("
                + customerData.id
              + ", '" + customerData.prefix + "'"
              + ", '" + customerData.firstname + "'"
              + ", '" + customerData.lastname + "'"
    
                + ")";
    //I have tried using this method
            using (SqlConnection conn = new SqlConnection(str))
            {
                conn.Open();

                
                using (SqlCommand cmd = new SqlCommand(text, conn))
                {
                    // Execute the command and log the # rows affected.
                    var rows = await cmd.ExecuteNonQueryAsync();
                    log.LogInformation($"{rows} rows were updated");
                }
          }

This works for this simple insert but, I feel like this is not ideal for a solution. I am not able to find many examples where Azure Functions are used to access Azure SQL DB. Should I use EF Core or the current method needs to be modified? Please suggest Thanks

This is opinioned based and isn't allowed on Stackoverflow. That being said, I would suggest [Dapper](https://www.nuget.org/packages/Dapper/), since your query is now suffering from [SQL Injection](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) and Dapper allows for easy parameterization. — Preben Huybrechts, Aug 06 '20 at 12:50

score 0 · Answer 1 · edited Aug 06 '20 at 18:25

0

You can use EF Core or enhance your code to another level of interaction with SQL: Using SqlCommand with PARAMETERS for values. => No code injection, all values of all data types it will works. With your actual code, probably you will have problems with some data ... like a ' inside a string ;)

see MS Docs: SqlCommand.Parameters

Dapper, suggested by Teja is another good and easy way to interact with DBs, IMHO a middle way from EF and pure SQL commands (with our without params) but I'm not sur that works in .Net Core (2.x to 3.1)

Note: I use a my class to interact with the SQL , Azure or not, and at the end of the tube, everyone use an SqlCommand with parameters ... also EF and, I suppose, Dapper also ... is the only way, without writing a brand new SqlClient ;)

edited Aug 06 '20 at 18:25

Dharman

30,962
25
85
135

answered Aug 06 '20 at 18:19

Davide Dolla

340
4
9

Thank you. Does the latest version of EF core allow DB first approach? It was hard to find any examples with EF core + DB first approach – Teja Rebb Aug 06 '20 at 18:45
Sorry, I don't use EF ... for me is too heavy and not in line with my philosophy and I prefer to have the code under my control. But I saw something on EF from colelagues and I dont think that you can use pure SQL commands and EF. From years (from .Net Framework 1) I use my personal classes to interact with the DB using DataAdapter and SqlCommand + SqlConnection. – Davide Dolla Aug 06 '20 at 20:38
Which type of examples do you need to understand how implement your solution? If it's only for an example to how insert in SQL with properties, I can write for you. Let me known ;) – Davide Dolla Aug 06 '20 at 20:42
Hi thanks. By DB first I meant, I don't want EF core to manage the tables/DB structures ( Primary keys/Indexes) (DDL). I want to get the table reference and do inserts / updates using LINQ (DML only). I will be using the same DB in different functions. – Teja Rebb Aug 07 '20 at 07:50
Hi, why you need LINQ? If I right understand you need only to insert/update data on SQL from Functions. IMHO if you want a fast Function you have to 2 way: 1 - use SqlCommand with Parameters and handle the filling the params from props of item with your code 2 - use Dapper that is more lightweight than EF and handle the filling the params it depend by the effort to write the code to map data coming from HTTP and SqlParamteres ... but without Dapper and some Reflection you can write a code that fill the param from props of data class. – Davide Dolla Aug 07 '20 at 08:42

Data Access from an Azure Function (.net core 3.1) to Azure SQL DB

1 Answers1