SQL Server 一時テーブル


同一名、別接続のテンポラリテーブルの挙動確認

const string CON_STRING = "Data Source=localhost;Initial Catalog=でーたべーす;User ID=ゆーざ;Password=ぱすわーど";
const string CREATE_TMP_QUERY = @"
CREATE TABLE
#tmp_tab
(
id INT,
memo VARCHAR(50)
);
";
const string INSERT_TMP_QUERY = @"
INSERT INTO
#tmp_tab (id,memo)
VALUES
(1,'test');
";
const string SELECT_TMP_QUERY = @"
SELECT
*
FROM
#tmp_tab;";



static void test1()
{
  SqlConnection con1 = new SqlConnection();
  SqlConnection con2 = new SqlConnection();
  con1.ConnectionString = CON_STRING;
  con2.ConnectionString = CON_STRING;

  con1.Open();
  con2.Open();
  using (con1)
  using (con2)
  using (var cmdCreate = con1.CreateCommand())
  using (var cmdIns = con1.CreateCommand())
  using (var cmdSel = con1.CreateCommand())
  using (var cmdSel2 = con2.CreateCommand())
  {
    cmdCreate.CommandText = CREATE_TMP_QUERY;
    cmdCreate.ExecuteNonQuery();
    cmdIns.CommandText = INSERT_TMP_QUERY;
    cmdIns.ExecuteNonQuery();

    cmdSel.CommandText = SELECT_TMP_QUERY;
    Debug.Print("{0}件", cmdSel.ExecuteScalar());

    try
    {
      cmdSel2.CommandText = SELECT_TMP_QUERY;
      Debug.Print("{0}件", cmdSel2.ExecuteScalar());
    }
    catch(Exception ex)
    {
      Debug.Print(ex.Message);
    }
    cmdSel2.CommandText = CREATE_TMP_QUERY;
    cmdSel2.ExecuteNonQuery();
    cmdSel2.CommandText = INSERT_TMP_QUERY;
    cmdSel2.ExecuteNonQuery();

    cmdSel2.CommandText = SELECT_TMP_QUERY;
    Debug.Print("{0}件", cmdSel2.ExecuteScalar());
        
  }//end sql
}//end method
カテゴリー: 開発 タグ: パーマリンク