1. Add an output parameter to your stored procedure and set it using the SCOPE_IDENTITY() as follows (Assuming that you are using Identity field):
CREATE PROCEDURE [dbo].[SAVE_DATA]
(
@Param1
, @Param2
, @GeneratedID INT OUTPUT
)
AS
BEGIN
INSERT INTO TableName Values(@Param1,@Param2)
SELECT @GeneratedID = SCOPE_IDENTITY();
END
2. Retrieve the newly generated ID in the Save method as follows:
public int Save(int Param1,int Param2)
{
int GeneratedId = 0;
string SP = "SAVE_DATA";
try
{
using (DbCommand dbCommand = Database.GetStoredProcCommand(SP))
{
Database.AddInParameter(dbCommand, "@Param1", DbType.Int32, Param1);
Database.AddInParameter(dbCommand, "@Param1", DbType.Int32, Param2);
Database.AddOutParameter(dbCommand, "@GeneratedId", DbType.Int64, 64);
Database.ExecuteNonQuery(dbCommand);
GeneratedId = Convert.ToInt64(Database.GetParameterValue(dbCommand, "@GeneratedId"));
}
}
catch (Exception ex)
{
}
return GeneratedId;
}
Please note that, I am using the Enterprise Library application block for database access. You can do it in ADO.NET also. See
http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx[
^]