SqlCommand

  • SqlCommandの作成
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT COUNT(ID) FROM T_USER";
MessageBox.Show(((int)(cmd.ExecuteScalar())).ToString());
SqlCommand cmd = new SqlCommand("SELECT COUNT(ID) FROM T_USER", con);
MessageBox.Show(((int)(cmd.ExecuteScalar())).ToString());
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT COUNT(ID) FROM T_USER";
MessageBox.Show(((int)(cmd.ExecuteScalar())).ToString());
  • 行を返すクエリー
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT ID,NAME FROM T_USER";

using(SqlDataReader rdr = cmd.ExecuteReader())
{
    string oData = string.Empty;
    while (rdr.Read())
    {
        oData += rdr["ID"].ToString() + rdr["NAME"].ToString() + Environment.NewLine;
    }
}

MessageBox.Show(oData);
  • 1つの値を返す
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT COUNT(ID) FROM T_USER";
MessageBox.Show(((int)(cmd.ExecuteScalar())).ToString());
  • アクションクエリ。SqlCommandでは、クエリにより処理された行数がExecuteNonQueryメソッドの戻り値として返されます。またDMLクエリ以外を実行すると、ExecuteNonQueryから-1が返されます。
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "INSERT INTO T_USER (ID,NAME,BIRTHDAY,COMMENT,DATA) VALUES (200,'kondo','1988/2/3',NULL,14)";
int insertCount = cmd.ExecuteNonQuery();

cmd.CommandText = "UPDATE T_USER SET COMMENT='あああ' WHERE ID=200";
int uypdateCount = cmd.ExecuteNonQuery();
  • StatementCompletedイベント
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "UPDATE T_USER SET COMMENT='あああ' WHERE ID=1"
                     + "UPDATE T_USER SET COMMENT='あああ' WHERE ID=2 OR ID=3 OR ID=4"
                     + "UPDATE T_USER SET COMMENT='あああ' WHERE ID=101 OR ID=102";

cmd.StatementCompleted += new StatementCompletedEventHandler(cmd_StatementCompleted);
int insertCount = cmd.ExecuteNonQuery();


void cmd_StatementCompleted(object sender, StatementCompletedEventArgs e)
{
    MessageBox.Show(string.Format("ステートメントは:{0}を変更",e.RecordCount.ToString()));
}
  • トランザクションでのクエリ実行。SqlConnectionではSqlTransactionを開始する場合、全てのクエリをそのトランザクションに関連付けする必要があります。そうしないとInvalidOperationExceptionが発生します。
using (SqlTransaction trn = con.BeginTransaction())
{
    try
    {
        SqlCommand cmd = con.CreateCommand();
        cmd.Transaction = trn;
        cmd.CommandText = "UPDATE T_USER SET COMMENT='あああ' WHERE ID=200";
        int uppdateCount = cmd.ExecuteNonQuery();
        trn.Commit();
    }
    catch (Exception ex)
    {
        trn.Rollback();
    }
}
  • パラメータクエリ
using (SqlCommand cmd = con.CreateCommand())
{
    cmd.CommandText = "SELECT ID, NAME FROM T_USER WHERE ID=@P_ID";
    
    SqlParameter paramId = new SqlParameter("@P_ID", SqlDbType.Int);
    paramId.Value = 200;
    cmd.Parameters.Add(paramId);
    
    SqlDataReader rdr = cmd.ExecuteReader();

    string oData = string.Empty;
    while (rdr.Read())
    {
        oData += rdr["ID"].ToString() + "/"+ rdr["NAME"].ToString() + Environment.NewLine;
    }

    MessageBox.Show(oData);
}
  • パラメータの方向
using (SqlCommand cmd = con.CreateCommand())
{
    cmd.CommandText = "SELECT @OP_ID=ID, @OP_NAME=NAME FROM T_USER WHERE ID=@P_ID";

    //@OP_ID(出力パラメータ)
    SqlParameter paramOutId = new SqlParameter("@OP_ID", SqlDbType.Int);
    paramOutId.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(paramOutId);

    //@OP_NAME(出力パラメータ)
    SqlParameter paramOutName = new SqlParameter("@OP_NAME", SqlDbType.Char, 30);
    paramOutName.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(paramOutName);

    //@P_ID(入力パラメータ)
    SqlParameter paramInId = new SqlParameter("@P_ID", SqlDbType.Int);
    paramInId.Direction = ParameterDirection.Input;
    paramInId.Value = 200;
    cmd.Parameters.Add(paramInId);

    cmd.ExecuteNonQuery();

    if (paramOutName.Value == DBNull.Value)
    {
        MessageBox.Show(paramOutId.Value.ToString() + "/" + "名前無し");
    }
    else
    {
        MessageBox.Show(paramOutId.Value.ToString() + "/" + paramOutName.Value.ToString());
    }
}