C# で SQL Server に 接続する 方法

0 件のコメント

C# の SqlClient を利用して Microsoft SQL Server に接続する方法をまとめます。 どちらかと言うと基本的な実装例となるようにサンプルコードを作成しました。

目次

概要

大まかには以下のような流れで処理を行います。

  1. 接続文字列 の 準備
  2. データベース接続 準備
  3. SQLの実行

以下ではそれぞれについて具体的な方法をいくつか記載していますが、一般的に利用する方法とすれば以下のような実装が普通かと思います。 「とりあえず急ぐので結論を!」と言う方は以下の2か所だけ確認いただければ良いかな、と思います。

  1. 接続文字列 の 準備
  2. データベース接続 準備
  3. SQLの実行

接続文字列 の 準備

接続文字列の作り方は大きく3パターンあるかと思います。

ソースコード上にべた書き

一番簡単な方法かと思います。 ソース上に埋め込まれているので外から見えないのが良い点でしょうか。 半面、ソースコードの修正をしないと接続先を変更できないのが悪い点でしょうか。 この実装を行うならせめて次に記載する ConnectionStringBuilder を利用した方法がおススメです。

public string GetConnectionString1()
{
    return @"Data Source=(サーバー名/IPアドレス);"
            + @"Integrated Security=False;"
            + @"User ID=(ユーザー名);"
            + @"Password=(パスワード)";
}

ConnectionStringBuilderを利用して生成

前述のべた書きとほとんど変わりませんが、プロパティへ値を設定していくものとなっているので、誤字脱字はないかと思います。

using System.Data.SqlClient;

public string GetConnectionString2()
{
    var builder = new SqlConnectionStringBuilder()
    {
        DataSource = "(サーバー名/IPアドレス)",
        IntegratedSecurity = false,
        UserID = "(ユーザー名)",
        Password = "(パスワード)"
    };

    return builder.ToString();
}

app.config または web.config から取得

通常はこの方法で実装されるかと思います。 テスト環境、本番環境でソースを変更せずに接続先を変更できる点が良い点と思います。 ユーザー名とパスワードがクリアテキストで記載されている点は悪い点かと思います。 運用等でパスワード管理を考える必要があるかもしれません…。。

app.config または web.config の add[@name] で指定された名前は 接続文字列 を取得する際の キー になります(L3:"sqlsvr")。 ソースコードにおいてこの キー を指定することで 接続文字列を取得します。 ここでは "sqlsvr" を利用していますが、特定できれば任意の文字列で問題ありません。

app.config または web.config

<configuration>
  <connectionStrings>
    <add name="sqlsvr"
         connectionString="Data Source=(サーバー名/IPアドレス);Persist Security Info=True;User ID=(ユーザー名);Password=(パスワード)"
         providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

ソースコード

using System.Configuration;

public string GetConnectionString3()
{
    return ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
}

データベース接続

接続を実装する方法もいくつか書き方がありますが、ここでは以下の3パターンを例として載せます。

基本の実装例

基本的な実装を見るために try-catch や using 、トランザクションといった余計なものを排除したシンプルな実装例です。 もちろんこのままではいろいろ問題があるので利用できませんが…基本は理解しやすいと思います。

using System.Configuration;
using System.Data.SqlClient;

public void Connect1()
{
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;

    // データベース接続の準備
    var connection = new SqlConnection(connectionString);

    // データベースの接続開始
    connection.Open();

    // 実行するSQLの準備
    var command = new SqlCommand();
    command.Connection = connection;
    command.CommandText = @"SELECT count(*) FROM T_USER";

    // SQLの実行
    command.ExecuteNonQuery();

    // データベースの接続終了
    connection.Close();
}

try-catchを用いた実装例

データベース接続を行うとき、どこでエラーが起こるかわからないので try-catch を行います。 この実装例は try-catch を行う

using System;
using System.Configuration;
using System.Data.SqlClient;

public void Connect2()
{
    SqlConnection connection = null;
    SqlCommand command = null;

    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;

    try
    {
        // データベース接続の準備
        connection = new SqlConnection(connectionString);

        // データベースの接続開始
        connection.Open();

        // SQLの実行
        command = new SqlCommand()
        {
            Connection = connection,
            CommandText = @"SELECT count(*) FROM T_USER"
        };
        command.ExecuteNonQuery();
    }
    catch (Exception exception)
    {
        Console.WriteLine(exception.Message);
        throw;
    }
    finally
    {
        // データベースの接続終了
        connection.Close();
    }

}

using と try-catch を用いた実装例

一部のオブジェクトは破棄を保証する必要があるので、コードで記載するのではなく using で担保するような実装例です。 スコープ外れた時点で破棄が必要なオブジェクトは自動的に破棄されますが、 using を用いて記載しておくと明示的になるかと思います。

using System;
using System.Configuration;
using System.Data.SqlClient;

public void Connect3()
{
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;

    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
        try
        {
            // データベースの接続開始
            connection.Open();

            // SQLの実行
            command.CommandText = @"SELECT count(*) FROM T_USER";
            command.ExecuteNonQuery();
        }
        catch (Exception exception)
        {
            Console.WriteLine(exception.Message);
            throw;
        }
        finally
        {
            // データベースの接続終了
            connection.Close();
        }
    }
}

トランザクション を用いた実装例

前述の using & try-catch に加えてトランザクション処理を行う場合の実装例です。

using System;
using System.Configuration;
using System.Data.SqlClient;

public void Create(string id, string password)
{
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;

    using (var connection = new SqlConnection(connectionString))
    {
        try
        {
            // データベースの接続開始
            connection.Open();

            using (var transaction = connection.BeginTransaction())
            using (var command = new SqlCommand() { Connection = connection, Transaction = transaction })
            {
                try
                {
                    // 実行するSQLの準備
                    command.CommandText = @"INSERT INTO T_USER (ID, PASSWORD) VALUES (@ID, @PASSWORD)";
                    command.Parameters.Add(new SqlParameter("@ID", id));
                    command.Parameters.Add(new SqlParameter("@PASSWORD", password));

                    // SQLの実行
                    command.ExecuteNonQuery();
                }
                catch
                {
                    // ロールバック
                    transaction.Rollback();
                    throw;
                }
                finally
                {
                    // コミット
                    transaction.Commit();
                }
            }
        }
        catch (Exception exception)
        {
            Console.WriteLine(exception.Message);
            throw;
        }
        finally
        {
            // データベースの接続終了
            connection.Close();
        }
    }
}

SQL の 実行

SQLの実行とひとくくりにしても SELECT を実行するのと、 INSERT, UPDATE を実行するのとでは意味が違います。 ここではまとめ切れないので、また次回にします…。。