How do I return the IDENTITY for an inserted record from a stored Proecedure?
I am adding data to my database, but would like to retrieve the UnitID that is Auto generated.
using (SqlConnection connect = new SqlConnection(connections))
{
SqlCommand command = new SqlCommand("ContactInfo_Add", connect);
command.Parameters.Add(new SqlParameter("name", name));
command.Parameters.Add(new SqlP开发者_JAVA技巧arameter("address", address));
command.Parameters.Add(new SqlParameter("Product", name));
command.Parameters.Add(new SqlParameter("Quantity", address));
command.Parameters.Add(new SqlParameter("DueDate", city));
connect.Open();
command.ExecuteNonQuery();
}
...
ALTER PROCEDURE [dbo].[Contact_Add]
@name varchar(40),
@address varchar(60),
@Product varchar(40),
@Quantity varchar(5),
@DueDate datetime
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO DBO.PERSON
(Name, Address) VALUES (@name, @address)
INSERT INTO DBO.PRODUCT_DATA
(PersonID, Product, Quantity, DueDate) VALUES (@Product, @Quantity, @DueDate)
END
Add an output parameter to your procedure:
@new_id int output
and after the insert statement, assign it value:
set @new_id = scope_identity()
The add it to your calling code:
command.Parameters.Add("@new_id", SqlDbType.Int).Direction = ParameterDirection.Output;
and retrieve the value:
int newId = Convert.ToInt32(command.Parameters["@new_id"].Value);
With MSSQL its safer to use:
SET @AN_INT_OUTPUT_PARAM = SCOPE_IDENTITY()
Or simply
RETURN SCOPE_IDENTITY()
in the Stored procedure Add in the end
SELECT SCOPE_IDENTITY();
in the C# define an integer outside using and assign it inside using
int UnitID;
UnitID = command.ExecuteScalar();
Search Books Online for OUTPUT clause if you have SQL server 2008. This is the best method.
精彩评论