C# – SQL Server on Ubuntu 20.04 接続リトライ設定 VS Code No.105

投稿日 投稿者 kumoman

自己投資としてチャレンジしている内容を Blog で公開しています。今回は C＃ を使った SQL Server 接続時の「エラーハンドリング」と「リトライ」方法について紹介します。

Summary

▼1. 接続時のエラーハンドリング & リトライ

SQL Server への接続時のエラーハンドリングおよび、リトライについて、以下の本 blog で Python や Java を使った実現方法を紹介しました。

(2022/12 時点)

外部にある SQL Server やクラウド上の SQL Database に接続する際、ネットワークの瞬断の影響を受けることはよくあるため、接続時にエラーハンドリングをし、インターバルの間隔を入れてリトライすることは必須です。今回は C# で試してみます。

▼2. 事前準備

Ubuntu 20.4 で Visual Studio Code 開発環境にて C# を使い SQL Server へ接続する環境の構築方法はこちらを参考にしてください。C# SQL Server on Ubuntu20.04 に接続する方法 VS Code No.104

▼3. C# で SQL Server 接続失敗時の 「エラーハンドリング」 および 「リトライ」を実装

3-1. アプリケーションの作成し、作成したディレクトリで VSCode を起動

dotnet new console -o MytestApp
cd MytestApp
code .

3-2. MytestApp.csproj に System.Data.SqlClient を追加し保存 (Ctrl+ s)

SQL Server への接続に必要な .NET Data Provider for SQL Server の名前空間 System.Data.SqlClient を登録します。

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

3-3. 追加した System.Data.SqlClient を反映

dotnet restore

3-4. SQL Server の停止

SQL Server への接続を失敗させるため、SQL Server を停止しておきます。

sudo systemctl stop mssql-server

3-5. C# のコード Program.cs を作成

SQL Server には sa のユーザーで接続しています。sa のパスワードは Password で指定します。Database 名は “sampledb1” を指定しています。

こちらの blog で作成したサンプルデータを参照し出力します。Python – SQL Server on Ubuntu サンプルデータの作成 v2 No.97

While を使いエラーになった場合 15 秒のインターバルで 3 回のリトライを実施しています。SqlClient の接続タイムアウトは既定で 15 秒です。

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

namespace ConsoleApp1
{
    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.");
                        // execute select query
                        String sql = "SELECT id, cdatetime, note FROM sampletb";
                        using (SqlCommand command = new SqlCommand(sql, connection))
                        {
                            using (SqlDataReader reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    Console.WriteLine("{0} {1} {2}", reader.GetInt32(0), reader.GetDateTime(1), reader.GetString(2));
                                }
                            }
                        }
                    }
                    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("=============================");
                    }
                }
            }
        }
    }
}

3-6. 実行結果

$dotnet run
Connecting to SQL Server ... 
Current Timestamps:12/30/2022 10:55:25 PM
-----------------------------
Current Timestamps:133168821405413795 :Error MEssage is System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at ConsoleApp1.Program.Main(String[] args) in /home/xxxxx/mssqlcode/sqlsrvd/MytestApp/Program.cs:line 30
ClientConnectionId:00000000-0000-0000-0000-000000000000
=============================
Retry count: 1
Current Timestamps:12/30/2022 10:55:40 PM
Waiting for 15 seconds
=============================
Connecting to SQL Server ... 
Current Timestamps:12/30/2022 10:55:55 PM
-----------------------------
Current Timestamps:133168821700745634 :Error MEssage is System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)

(省略)

ClientConnectionId:00000000-0000-0000-0000-000000000000
=============================
Retry count: 2
Current Timestamps:12/30/2022 10:56:10 PM
Waiting for 15 seconds
=============================
Connecting to SQL Server ... 
Current Timestamps:12/30/2022 10:56:25 PM
-----------------------------
Current Timestamps:133168821995888490 :Error MEssage is System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.

(省略)

ClientConnectionId:00000000-0000-0000-0000-000000000000
=============================
Retry count: 3
Current Timestamps:12/30/2022 10:56:39 PM
Waiting for 15 seconds
=============================
Connecting to SQL Server ... 
Current Timestamps:12/30/2022 10:56:54 PM
-----------------------------
Current Timestamps:133168822145931870 :Error MEssage is System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)

(省略)

/home/xxxxx/mssqlcode/sqlsrvd/MytestApp/Program.cs:line 30
ClientConnectionId:00000000-0000-0000-0000-000000000000
=============================
Max retry count 3 reached. Exiting
=============================

▼4. 参考情報

  1. C# – Visual Studio Marketplace
  2. Python – SQL Server on Ubuntu のエラーハンドリング & リトライ方法  No.93
  3. Java – SQL Server on Linux に接続時の「エラーハンドリング」 &「リトライ」方法 No.6

以上です。参考になりましたら幸いです。



コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です