Azure Functions - CRUD to SQL Azure

Luis Arias 5,981 Reputation points

I'm trying to create a simple CRUD to sql azure , but the example in azure site its reference to:

Anyone can help me with that, my first action it is a simple insert to database, but don't works.

public static async Task<IActionResult> Run(HttpRequest req, ILogger log)
log.LogInformation("C# HTTP trigger function processed a request.");
//log.Info($"ALFLOG======> RequestUri={req.RequestUri}");

string name = req.Query["name"]; 

string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
dynamic data = JsonConvert.DeserializeObject(requestBody);
name = name ?? data?.name;

 var str = Environment.GetEnvironmentVariable("sqlconnec");
 using (SqlConnection conn = new SqlConnection(str))
    var text = "insert into logs values ('demo2')";

    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");

return name != null
    ? (ActionResult)new OkObjectResult($"Hello, {name}")
    : new BadRequestObjectResult("Please pass a name on the query string or in the request body");

//+++We retrieve the userName field, which comes as a parameter to the function, by deserializing req.Content.


PD: my connection strings is "sqlconnec"

Thanks all!.

Azure App Service
Azure App Service
Azure App Service is a service used to create and deploy scalable, mission-critical web apps.
7,341 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. xequence 6 Reputation points

    This code came from I modified it to match my Azure database table for an assertion of success. You will receive a row id = 1 from you're line 17 specification of var rows = await.cmd.ExecuteNonQueryAsync(); You must wrap your command.ExecuteNonQueryAsync in try catch incase you have invalid command. Good luck.

        public class UnitTest1  
            public void TestInsert()  
                var str = System.Configuration.ConfigurationManager.  
                Task task = ExecuteSqlTransaction(str);  
                 System.Threading.Tasks.Task.Run(() => task).GetAwaiter().GetResult();  
            public async Task ExecuteSqlTransaction(string connectionString)  
                using (SqlConnection connection = new SqlConnection(connectionString))  
                    await connection.OpenAsync();  
                    SqlCommand command = connection.CreateCommand();  
                    SqlTransaction transaction = null;  
                    // Start a local transaction.  
                    transaction = await Task.Run<SqlTransaction>(  
                        () => connection.BeginTransaction("SampleTransaction")  
                    // Must assign both transaction object and connection  
                    // to Command object for a pending local transaction  
                    command.Connection = connection;  
                    command.Transaction = transaction;  
                        command.CommandText =  
                            "Insert into Table(A, B, C, D, E, F)" +  
                            " VALUES ('Description', '42', '0', '3.14', '1.59','Title')";  
                        var rows = await command.ExecuteNonQueryAsync();   
                        // Attempt to commit the transaction.  
                        await Task.Run(() => transaction.Commit());  
                    catch (Exception ex)  
                        Console.WriteLine("Commit Exception Type: {0}", ex.GetType());  
                        Console.WriteLine("  Message: {0}", ex.Message);  
                        // Attempt to roll back the transaction.  
                        catch (Exception ex2)  
                            // This catch block will handle any errors that may have occurred  
                            // on the server that would cause the rollback to fail, such as  
                            // a closed connection.  
                            Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());  
                            Console.WriteLine("  Message: {0}", ex2.Message);  
    0 comments No comments