Azure Functions - CRUD to SQL Azure

Luis Arias 5,981 Reputation points
2020-04-26T21:57:44.977+00:00

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

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
    2020-04-27T02:37:27.693+00:00

    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);  
                        }  
                    }  
                }  
            }  
        }  
    
    0 comments No comments