locked
SqlCommand with parameters and SCOPE_IDENTITY RRS feed

  • Question

  • I have following table:

    CREATE TABLE [AUTO TEST] (
      [AUTO ID] [int] IDENTITY (2, 5) NOT NULL ,
      [NAME] [varchar] (30) NOT NULL ,
      CONSTRAINT [PK_AUTO] PRIMARY KEY ([AUTO ID])
    )


    Next code works and returns last generated IDENTITY:

                SqlConnection con = new SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=TEST;Connect Timeout=10");
                con.Open();
    
                SqlCommand cmd1 = con.CreateCommand();
                cmd1.CommandText = "insert into [AUTO TEST](NAME) values('1')";
                int result1 = cmd1.ExecuteNonQuery();
    
                SqlCommand cmd2 = con.CreateCommand();
                cmd2.CommandText = "select SCOPE_IDENTITY()";
                int result2 = Convert.ToInt32(cmd2.ExecuteScalar());
    
                cmd1.Dispose();
                cmd2.Dispose();
                con.Dispose();
    
                MessageBox.Show("result1: " + result1 + Environment.NewLine + "result2: " + result2);
    


    But if I change following line:

                cmd1.CommandText = "insert into [AUTO TEST](NAME) values('1')";
    

    into a command with a parameter:

                cmd1.CommandText = "insert into [AUTO TEST](NAME) values(@p1)";
                cmd1.Parameters.Add("@p1", SqlDbType.VarChar).Value = "1";
    

    insert command is executed successfully but the second command won't return SCOPE_IDENTITY (it returns null).


    I know it could be done this way (put it all in one statement):

                SqlConnection con = new SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=TEST;Connect Timeout=10");
                con.Open();
    
                SqlCommand cmd1 = con.CreateCommand();
                cmd1.CommandText = "insert into [AUTO TEST](NAME) values(@p1); select SCOPE_IDENTITY();";
                cmd1.Parameters.Add("@p1", SqlDbType.VarChar).Value = "1";
                int result1 = Convert.ToInt32(cmd1.ExecuteScalar());
    
                cmd1.Dispose();
                con.Dispose();
                MessageBox.Show("result1: " + result1);
    

    but I'm curious why it won't work with parameters and without them it works. In both cases connection was still open and SCOPE_IDENTITY() should work.

    Saturday, May 2, 2009 12:02 PM

Answers

  • I found my answer. I did some SQL profiling and have found following:

    1. when I use:
    cmd1.CommandText = "insert into [AUTO TEST](NAME) values('1')";
    

    SQL server receives this statement as it is, simple SQL statement.

    2. when I use:

                cmd1.CommandText = "insert into [AUTO TEST](NAME) values(@p1)";
                cmd1.Parameters.Add("@p1", SqlDbType.VarChar).Value = "1";
    SQL server receives following RPC call:

    exec sp_executesql N'insert into [AUTO PROBA](NAME) values(@p1)', N'@p1 varchar(1)', @p1 = '1'
    My guess is that SCOPE_IDENTITY() value is lost because of this procedure call. Insert is performed in this procedure and when procedure finishes we are out of "SCOPE".


    Executing this:
    cmd1.CommandText = "insert into [AUTO TEST](NAME) values(@p1); select SCOPE_IDENTITY();";
    send this to SQL server:

    exec sp_executesql N'insert into [AUTO TEST](NAME) values(@p1); select SCOPE_IDENTITY();', N'@p1 varchar(1)', @p1 = '1'
    that is why this works, SCOPE_IDENTITY command was execute within procedure and it was in the SCOPE of insert command.
    Sunday, May 3, 2009 3:38 PM

All replies

  • try to add this line of code before second command

    cmd1.Parameters.Clear();
    cmd2.Parameters.Clear();
    Sunday, May 3, 2009 8:50 AM
  • try to add this line of code before second command

    cmd1.Parameters.Clear();
    cmd2.Parameters.Clear();

    It would be very strange if it worked but it did not.
    Sunday, May 3, 2009 3:04 PM
  • I found my answer. I did some SQL profiling and have found following:

    1. when I use:
    cmd1.CommandText = "insert into [AUTO TEST](NAME) values('1')";
    

    SQL server receives this statement as it is, simple SQL statement.

    2. when I use:

                cmd1.CommandText = "insert into [AUTO TEST](NAME) values(@p1)";
                cmd1.Parameters.Add("@p1", SqlDbType.VarChar).Value = "1";
    SQL server receives following RPC call:

    exec sp_executesql N'insert into [AUTO PROBA](NAME) values(@p1)', N'@p1 varchar(1)', @p1 = '1'
    My guess is that SCOPE_IDENTITY() value is lost because of this procedure call. Insert is performed in this procedure and when procedure finishes we are out of "SCOPE".


    Executing this:
    cmd1.CommandText = "insert into [AUTO TEST](NAME) values(@p1); select SCOPE_IDENTITY();";
    send this to SQL server:

    exec sp_executesql N'insert into [AUTO TEST](NAME) values(@p1); select SCOPE_IDENTITY();', N'@p1 varchar(1)', @p1 = '1'
    that is why this works, SCOPE_IDENTITY command was execute within procedure and it was in the SCOPE of insert command.
    Sunday, May 3, 2009 3:38 PM