Azure Functions - CRUD to SQL Azure

Question

question

alefred asked · Apr 26, 2020 at 09:57 PM

Azure Functions - CRUD to SQL Azure

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-webapps

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2020-04-27T02:37:27.693Z

xequence answered · Apr 27, 2020 at 02:37 AM

This code came from https://docs.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);
                     }
                 }
             }
         }
     }

·

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question