Azure Functions - CRUD to SQL Azure

Question

I'm trying to create a simple CRUD to sql azure , but the example in azure site its reference to:
https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/azure-functions/functions-scenario-database-table-cleanup.md

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))
{
    conn.Open();
    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!.
Alfredo

Answer 1

This code came from https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/asynchronous-programming. 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.

[TestClass]  
    public class UnitTest1  
    {  
        [TestMethod]  
        public void TestInsert()  
        {  
            var str = System.Configuration.ConfigurationManager.  
    ConnectionStrings["DefaultConnection"].ConnectionString;  
            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;  
  
                try  
                {  
                    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());  
                    Console.WriteLine("Inserted");  
                }  
                catch (Exception ex)  
                {  
                    Console.WriteLine("Commit Exception Type: {0}", ex.GetType());  
                    Console.WriteLine("  Message: {0}", ex.Message);  
  
                    // Attempt to roll back the transaction.  
                    try  
                    {  
                        transaction.Rollback();  
                    }  
                    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);  
                    }  
                }  
            }  
        }  
    }