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);
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();
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";
SqlParameter paramOutId = new SqlParameter("@OP_ID", SqlDbType.Int);
paramOutId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramOutId);
SqlParameter paramOutName = new SqlParameter("@OP_NAME", SqlDbType.Char, 30);
paramOutName.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramOutName);
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());
}
}