Get the ID of a Newly Inserted Row

Question

Hi everyone, I need to get the ID of a newly inserted row. And I was able to create an SP from the SQL Server. Please see below:

ALTER PROCEDURE [dbo].[spInser_CategoryHeader]
    -- Add the parameters for the stored procedure here
    (
        @SchoolYear nvarchar(50),
           @Term nvarchar(50),
           @GradeLvl nvarchar(50),
           @SubjectCode nvarchar(50),
           @TotalCategoryPercentage decimal(5,2),
           @SchoolCode nvarchar(50),
           @getUser nvarchar(50),
           @CategoryHeaderID int OUTPUT
           )
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    BEGIN TRANSACTION
    IF NOT EXISTS (SELECT CategoryHeaderID FROM tblCategoryHeader WHERE CategoryHeaderID=@CategoryHeaderID)
        BEGIN
            INSERT INTO [dbo].[tblCategoryHeader]
               ([SchoolYear]
               ,[Term]
               ,[GradeLvl]
               ,[SubjectCode]
               ,[TotalCategoryPercentage]
               ,[SchoolCode]
               ,[getUser])

                VALUES
                (@SchoolYear,
                @Term,
                @GradeLvl,
                @SubjectCode,
                @TotalCategoryPercentage,
                @SchoolCode,
                @getUser)

                SELECT @CategoryHeaderID = SCOPE_IDENTITY()

        COMMIT TRANSACTION
        END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION
        RAISERROR('Duplicate record found. All entries are reverted to it original state.',16,1)
    END

END

In my c# application, I am using a 3 tier architecture. I was able to get my objective if I will write my ADO code in my .cs file. Please see below:

 

private void InsertGradeCategory()
        {
            using (SqlConnection con = DBCS.GetConString())
            {
                con.Open();
                SqlTransaction sqlTran = con.BeginTransaction();
                SqlCommand cmd = con.CreateCommand();
                cmd.Transaction = sqlTran;
                try
                {
                    using (SqlConnection cons = DBCS.GetConString())
                    {
                        SqlCommand cmds = new SqlCommand("spInser_CategoryHeader", cons);
                        cmds.CommandType = CommandType.StoredProcedure;

                        cmds.Parameters.AddWithValue("@SchoolYear", ddlSchoolYear.SelectedValue);
                        cmds.Parameters.AddWithValue("@Term", ddlTerm.SelectedValue);
                        cmds.Parameters.AddWithValue("@GradeLvl", ddlGradeLvl.SelectedValue);
                        cmds.Parameters.AddWithValue("@SubjectCode", ddlSubjects.SelectedValue);
                        cmds.Parameters.AddWithValue("@TotalCategoryPercentage", lblTotalPercentage.Text);
                        cmds.Parameters.AddWithValue("@SchoolCode", lblSchoolCode.Text);
                        cmds.Parameters.AddWithValue("@getUser", lblUsername.Text);

                        SqlParameter outputParameter = new SqlParameter();
                        outputParameter.ParameterName = "@CategoryHeaderID";
                        outputParameter.SqlDbType = SqlDbType.Int;
                        outputParameter.Direction = ParameterDirection.Output;
                        cmds.Parameters.Add(outputParameter);

                        cons.Open();
                        cmds.ExecuteNonQuery();

                        string empId = outputParameter.Value.ToString();
                        hfCategoryHeaderId.Value = empId;
                    }

                }
            }
        }

 But I prefer writing my ADO from my DataAccess code. Here's what I started:

 

public int InsertCategoryHeader(CategoryHeaderTable head)
        {
            using (SqlConnection con = DBCS.GetConString())
            {
                SqlCommand cmd = new SqlCommand("spInser_CategoryHeader", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                try
                {

                    cmd.Parameters.AddWithValue("@SchoolYear", head.SchoolYear);
                    cmd.Parameters.AddWithValue("@Term", head.Term);
                    cmd.Parameters.AddWithValue("@GradeLvl", head.GradeLvl);
                    cmd.Parameters.AddWithValue("@SubjectCode", head.SubjectCode);
                    cmd.Parameters.AddWithValue("@TotalCategoryPercentage", head.CategoryPercentage);
                    cmd.Parameters.AddWithValue("@SchoolCode", head.SchoolCode);
                    cmd.Parameters.AddWithValue("@getUser", head.getUser);

                    SqlParameter outputParameter = new SqlParameter();
                    outputParameter.ParameterName = "@CategoryHeaderID";
                    outputParameter.SqlDbType = SqlDbType.Int;
                    outputParameter.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(outputParameter);

                    return cmd.ExecuteNonQuery();

                    //string studId = outputParameter.Value.ToString(); //problem here

                }
                catch (Exception)
                {

                    throw;
                }
            }
        }

Thanks in advance for any help.