.NET Framework
ADO.NET

前書き

備考

SQL文をコマンドとして実行する

// Uses Windows authentication. Replace the Trusted_Connection parameter with
// User Id=...;Password=...; to use SQL Server authentication instead. You may
// want to find the appropriate connection string for your server.
string connectionString = @"Server=myServer\myInstance;Database=myDataBase;Trusted_Connection=True;"

string sql = "INSERT INTO myTable (myDateTimeField, myIntField) " +
    "VALUES (@someDateTime, @someInt);";

// Most ADO.NET objects are disposable and, thus, require the using keyword.
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand(sql, connection))
{
    // Use parameters instead of string concatenation to add user-supplied
    // values to avoid SQL injection and formatting issues. Explicitly supply datatype.

    // System.Data.SqlDbType is an enumeration. See Note1
    command.Parameters.Add("@someDateTime", SqlDbType.DateTime).Value = myDateTimeVariable;
    command.Parameters.Add("@someInt", SqlDbType.Int).Value = myInt32Variable;

    // Execute the SQL statement. Use ExecuteScalar and ExecuteReader instead
    // for query that return results (or see the more specific examples, once
    // those have been added).

    connection.Open();
    command.ExecuteNonQuery();
}

注1： MSFT SQL Server固有のバリエーションのSqlDbType列挙を参照してください。

注2： MySQL固有のバリエーションについては、 MySqlDbType列挙を参照してください。

ベストプラクティス - SQL文の実行

public void SaveNewEmployee(Employee newEmployee)
{
    // best practice - wrap all database connections in a using block so they are always closed & disposed even in the event of an Exception
    // best practice - retrieve the connection string by name from the app.config or web.config (depending on the application type) (note, this requires an assembly reference to System.configuration)
    using(SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionName"].ConnectionString))
    {
        // best practice - use column names in your INSERT statement so you are not dependent on the sql schema column order
        // best practice - always use parameters to avoid sql injection attacks and errors if malformed text is used like including a single quote which is the sql equivalent of escaping or starting a string (varchar/nvarchar)
        // best practice - give your parameters meaningful names just like you do variables in your code
        using(SqlCommand sc = new SqlCommand("INSERT INTO employee (FirstName, LastName, DateOfBirth /*etc*/) VALUES (@firstName, @lastName, @dateOfBirth /*etc*/)", con))
        {
            // best practice - always specify the database data type of the column you are using
            // best practice - check for valid values in your code and/or use a database constraint, if inserting NULL then use System.DbNull.Value
            sc.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar, 200){Value = newEmployee.FirstName ?? (object) System.DBNull.Value});
            sc.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar, 200){Value = newEmployee.LastName ?? (object) System.DBNull.Value});

            // best practice - always use the correct types when specifying your parameters, Value is assigned to a DateTime instance and not a string representation of a Date
            sc.Parameters.Add(new SqlParameter("@dateOfBirth", SqlDbType.Date){ Value = newEmployee.DateOfBirth });

            // best practice - open your connection as late as possible unless you need to verify that the database connection is valid and wont fail and the proceeding code execution takes a long time (not the case here)
            con.Open();
            sc.ExecuteNonQuery();
        }

        // the end of the using block will close and dispose the SqlConnection
        // best practice - end the using block as soon as possible to release the database connection
    }
}

// supporting class used as parameter for example
public class Employee
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
}

ADO.NETでの作業のベストプラクティス

• 経験則は最小限の時間で接続を開くことです。プロシージャの実行がこれを超えると、接続オブジェクトを接続プールに戻すよう明示的に接続を閉じます。既定の接続プールの最大サイズ= 100.接続プールにより、SQL Serverへの物理接続のパフォーマンスが向上します。 SQL Serverでの接続プーリング
• 使用中のブロック内のすべてのデータベース接続をラップして、例外が発生しても常に閉じられて処理されるようにします。 ステートメントの使用の詳細については、 usingステートメント（C＃リファレンス）を参照してください。
• app.configまたはweb.configから接続文字列を名前で取得します（アプリケーションの種類によって異なります）
  • これには、 System.configurationへのアセンブリ参照が必要です
  • 構成ファイルの構成方法の詳細は、「 接続文字列と構成ファイル」を参照してください。
• 入力値のパラメータは常に使用する
  • SQLインジェクション攻撃を避ける
  • 文字列のエスケープまたは開始（varchar / nvarchar）のsqlに相当する一重引用符を含むような形式のテキストを使用すると、エラーを回避できます。
  • データベース・プロバイダに、すべてのデータベース・プロバイダでサポートされていない問合せ計画を再利用させることで、効率が向上します。
• パラメータを操作する場合
  • SQLパラメータの型とサイズの不一致は、挿入/更新/選択の失敗の一般的な原因です
  • コード内の変数と同じように、Sqlパラメータの意味のある名前を付けてください
  • 使用している列のデータベースのデータ型を指定します。これにより間違ったパラメータ型が使用されないため、予期しない結果が発生する可能性があります。
  • コマンドに渡す前に、受信したパラメータを検証します（「 ガーベッジ・イン、ガーベッジ・アウト 」というように）。できるだけ早くスタックに入ってくる値を検証する
  • たとえば、DateTimeの文字列値を割り当てず、実際のDateTimeインスタンスをパラメータの値に代入するなど、パラメータ値を割り当てるときに正しい型を使用します
  • 文字列型パラメータのサイズを指定します。これは、パラメータの型とサイズが一致する場合、SQL Serverは実行計画を再利用できるためです。 MAXに-1を使用する
  • AddWithValueメソッドを使用しないでください。主な理由は、必要に応じてパラメータタイプや精度/スケールを指定するのを忘れてしまうことです。追加情報については、既にAddWithValueの使用を止めることができますか？を参照してください。
• データベース接続を使用する場合
  • できるだけ早く接続を開いて、できるだけ早く閉じてください。これは、外部リソースを使用する場合の一般的なガイドラインです
  • データベース接続インスタンスを共有しないでください（例：シングルトンホストにSqlConnection型の共有インスタンスがある場合）。必要に応じてコードで常に新しいデータベース接続インスタンスを作成してから、呼び出しコードを破棄し、完了したら「破棄」します。その理由は次のとおりです。
    • ほとんどのデータベースプロバイダには何らかの接続プーリングがあり、新しい管理接続を作成するのは安価です
    • コードが複数のスレッドで作業を開始すると、将来のエラーもなくなります。

ベンダー固有のクラスを抽象化するための共通インターフェースの使用

var providerName = "System.Data.SqlClient";    //Oracle.ManagedDataAccess.Client, IBM.Data.DB2
var connectionString = "{your-connection-string}";
//you will probably get the above two values in the ConnectionStringSettings object from .config file

var factory = DbProviderFactories.GetFactory(providerName);
using(var connection = factory.CreateConnection()) {    //IDbConnection
    connection.ConnectionString = connectionString;
    connection.Open();
    
    using(var command = connection.CreateCommand()) {    //IDbCommand
        command.CommandText = "{query}";
        
        using(var reader = command.ExecuteReader()) {    //IDataReader
            while(reader.Read()) {
                ...
            }
        }
    }
}