C# – SQL Server on Ubuntu 20.04 トランザクションでクエリを制御する方法 No.106

dotnet new console -o My2ndApp
cd My2ndApp
code .

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="System.Data.SqlClient" Version="4.8.5" />
  </ItemGroup>

</Project>

dotnet restore

using System;
using System.Data.SqlClient;
using System.Threading;

namespace ConsoleApp2
{
    class Program
    {
        static void Main(string[] args)
        {
            // Build connection string
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = "localhost";
            builder.UserID = "sa";
            builder.Password = "password";
            builder.InitialCatalog = "sampledb1";
            Boolean success = false;
            int retryCount = 0;
            int maxRetryCount = 4;
            while (!success && retryCount < maxRetryCount)
            {
                try
                {
                    // Connect to SQL
                    Console.Write("Connecting to SQL Server ... \n");
                    Console.WriteLine("Current Timestamps:" + DateTime.UtcNow.ToLocalTime());
                    Console.WriteLine("-----------------------------");
                    using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
                    {
                        connection.Open();
                        Console.WriteLine("Done.");

                        SqlCommand command = connection.CreateCommand();
                        SqlTransaction transaction;

                        // Start a local transaction.
                        transaction = connection.BeginTransaction();

                        // Must assign both transaction object and connection
                        // to Command object for a pending local transaction
                        command.Connection = connection;
                        command.Transaction = transaction;

                        try
                        {
                            Random rnd = new Random();
                            int rnum = rnd.Next(1,1000);
                            command.CommandText =
                                "Insert into sampletb (id, cdatetime, note) VALUES ('"+ rnum + "','" + DateTime.Now + "','Description');";
                            command.ExecuteNonQuery();

                            // Attempt to commit the transaction.
                            transaction.Commit();
                            Console.WriteLine("one record is written to database.");
                        }
                        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);
                            }
                        }

                        // execute select query
                        try
                        {
                            String sql = "SELECT id, cdatetime, note FROM sampletb";
                            using (SqlCommand cmd = new SqlCommand(sql, connection))
                            {
                                using (SqlDataReader reader = cmd.ExecuteReader())
                                {
                                    while (reader.Read())
                                    {
                                        Console.WriteLine("{0} {1} {2}", reader.GetInt32(0), reader.GetDateTime(1), reader.GetString(2));
                                    }
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine("Error Message is " + ex.ToString());
                        }
                    }
                    success = true;
                }
                catch (SqlException e)
                {

                    Console.WriteLine("Current Timestamps:" + DateTime.UtcNow.ToFileTime() + " :Error MEssage is " + e.ToString());
                    retryCount++;

                    if (retryCount != maxRetryCount)
                    {
                        Console.WriteLine("=============================");
                        Console.WriteLine("Retry count: " + retryCount + "\nCurrent Timestamps:" + DateTime.UtcNow.ToLocalTime());
                        Console.WriteLine("Waiting for 15 seconds");
                        Console.WriteLine("=============================");
                        Thread.Sleep(15000);
                    }
                    else
                    {
                        Console.WriteLine("=============================");
                        Console.WriteLine("Max retry count " + (retryCount - 1) + " reached. Exiting");
                        Console.WriteLine("=============================");
                    }
                }
            }
        }
    }
}

$ dotnet run
Connecting to SQL Server ... 
Current Timestamps:1/7/2023 4:07:53 PM
-----------------------------
Done.
one record is written to database.
3366109 12/9/2022 1:59:12 PM int
3366109 12/9/2022 1:59:12 PM mcranentiv
6732218 12/9/2022 1:59:12 PM orlsojlvuu
10098327 12/9/2022 1:59:12 PM uaclnlkehv
13464436 12/9/2022 1:59:12 PM xngcehsmtc
16830545 12/9/2022 1:59:12 PM llvzgooqaa
20196654 12/9/2022 1:59:12 PM atmordnpfz
23562763 12/9/2022 1:59:12 PM qpzlferdra
26928872 12/9/2022 1:59:12 PM lnbjlmclxx
30294981 12/9/2022 1:59:12 PM xfspqvetbf
0 12/9/2022 1:59:12 PM Description

// Attempt to commit the transaction.
//transaction.Commit();
//Console.WriteLine("one record is written to database.");

Connecting to SQL Server ... 
Current Timestamps:1/7/2023 4:06:42 PM
-----------------------------
Done.
Error Message is System.InvalidOperationException: ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.
   at System.Data.SqlClient.SqlCommand.ValidateCommand(Boolean async, String method)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at ConsoleApp2.Program.Main(String[] args) in /home/xxx/My2ndApp/Program.cs:line 83