Returning scope_identity

[Mick Walker]

Hi all,

I use the following logic to insert a new record to my database:

SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
conn.ConnectionString = ConnString;
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.BlogPosts_Insert";

cmd.Parameters.AddWithValue("@Title", Title);
cmd.Parameters.AddWithValue("@BlogContent", Content);
cmd.Parameters.AddWithValue("@DatePosted", DatePosted);
cmd.Parameters.AddWithValue("@NoViews", 0);
cmd.Parameters.AddWithValue("@Category", Category);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new InvalidDataException(ex.Message);
}
finally
{
conn.Close();
cmd.Dispose();
}

This works fine. But I want the function to return the scope identity of
the newly inserted record.
My stored proceedure is as follows:
CREATE PROCEDURE [dbo].[BlogPosts_Insert]
-- Add the parameters for the stored procedure here
@Title varchar(200),
@BlogContent text,
@DatePosted datetime,
@NoViews int,
@Category int
AS
BEGIN
Insert into dbo.BlogPosts (
Title,
BlogContent,
DatePosted,
NoViews,
Category
)
Values (
@Title,
@BlogContent,
@DatePosted,
@NoViews,
@Category
)
select scope_identity()
END


Does anyone know how I can return the ID of the newly inserted item.

Kind Regards
Mick

 

[Arne Vajhøj]

I use the following logic to insert a new record to my database:

SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
conn.ConnectionString = ConnString;
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.BlogPosts_Insert";

cmd.Parameters.AddWithValue("@Title", Title);
cmd.Parameters.AddWithValue("@BlogContent", Content);
cmd.Parameters.AddWithValue("@DatePosted", DatePosted);
cmd.Parameters.AddWithValue("@NoViews", 0);
cmd.Parameters.AddWithValue("@Category", Category);

conn.Open();
cmd.ExecuteNonQuery();

This works fine. But I want the function to return the scope identity of
the newly inserted record.
My stored proceedure is as follows:
CREATE PROCEDURE [dbo].[BlogPosts_Insert]
-- Add the parameters for the stored procedure here
@Title varchar(200),
@BlogContent text,
@DatePosted datetime,
@NoViews int,
@Category int
AS
BEGIN ....
select scope_identity()
END

Does anyone know how I can return the ID of the newly inserted item.

You do a SELECT, so you get back a result set.

Use ExecuteReader instead of ExecuteNonQuery and read
the result set.

In this case ExecuteScalar is easier.

Arne

 

[Nicholas Paldino [.NET/C# MVP]]

Either that, or have an OUTPUT variable on the stored procedure which
you set to the id. This will have less overhead, as you don't have to have
a table structure passed over the wire for the return value (and it makes
sense, since you are returning one singular value).

Additionally, you should be using statements as well for the connection
and commands. In doing it the way you are, you still have the chance of not
disposing of the command or connection before you reach the try block.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I use the following logic to insert a new record to my database:

SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
conn.ConnectionString = ConnString;
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.BlogPosts_Insert";

cmd.Parameters.AddWithValue("@Title", Title);
cmd.Parameters.AddWithValue("@BlogContent", Content);
cmd.Parameters.AddWithValue("@DatePosted", DatePosted);
cmd.Parameters.AddWithValue("@NoViews", 0);
cmd.Parameters.AddWithValue("@Category", Category);

conn.Open();
cmd.ExecuteNonQuery();

This works fine. But I want the function to return the scope identity of
the newly inserted record.
My stored proceedure is as follows:
CREATE PROCEDURE [dbo].[BlogPosts_Insert]
-- Add the parameters for the stored procedure here
@Title varchar(200),
@BlogContent text,
@DatePosted datetime,
@NoViews int,
@Category int
AS
BEGIN ...
select scope_identity()
END

Does anyone know how I can return the ID of the newly inserted item.

You do a SELECT, so you get back a result set.

Use ExecuteReader instead of ExecuteNonQuery and read
the result set.

In this case ExecuteScalar is easier.

Arne