sql server - Fetch scope_identity value in C# code from stored procedure in 3 tier architecture

Question

I need to fetch scope_identity() value from a stored procedure after inserting data in C# code.

Here I am using 3 tier architecture. Please anyone help me

protected void Submit_Click(object sender, EventArgs e)
{
    this.CreateUserGroups(strGroupName, strDescription, strGroupType);
}

protected void CreateUserGroups(string strGroupName, string strDescription, string strGroupType)
{
    string strReturn = string.Empty;
    strReturn = objUser.SaveCreateGroups(strGroupName, strDescription, strGroupType);
}

public string SaveCreateGroups(string strGroupName, string strDescription, string strGroupType)
{
    try
    {
        DataManager.ParamBuilder param = new DataManager.ParamBuilder();
        if (strGroupName != string.Empty)
            param.AddParam(System.Data.SqlDbType.VarChar, "@c_groupname", strGroupName);
        else
            param.AddParam(System.Data.SqlDbType.Int, "@c_groupname", DBNull.Value);

        if (strDescription != string.Empty)
            param.AddParam(System.Data.SqlDbType.VarChar, "@c_description", strDescription);
        else
            param.AddParam(System.Data.SqlDbType.Int, "@c_description", DBNull.Value);

        if (strGroupType != string.Empty)
            param.AddParam(System.Data.SqlDbType.Int, "@n_grouptype", strGroupType);
        else
            param.AddParam(System.Data.SqlDbType.VarChar, "@n_grouptype", DBNull.Value);


        String strIsUserExitsInGroup = DataManager.ExecuteScalar("sp_create_user_groups", param.Parameters, true).ToString();

        return strIsUserExitsInGroup;
    }
    catch (Exception e)
    {
        throw new Exception(e.ToString());
    }
}

public static object ExecuteScalar(string procedureNameOrSql, List<SqlParameter> parameters, bool isStoredProcedure)
{
    object scalarValue;
    using (SqlConnection cn = createConnection())
    {
        SqlCommand cmd = new SqlCommand(procedureNameOrSql, cn);
        if (isStoredProcedure)
            cmd.CommandType = CommandType.StoredProcedure;
        if (parameters != null)
            cmd.Parameters.AddRange(parameters.ToArray());
        scalarValue = cmd.ExecuteScalar();
    }
    return scalarValue;
}

My stored procedure:

CREATE Procedure [dbo].[sp_create_user_groups]
  @n_user_group_id int OUTPUT,
  @c_groupname varchar(200),
  @c_description varchar(200),
  @n_grouptype int
As
   Declare @IsGroupNameExists int,        
           @b_active bit

   select @IsGroupNameExists = 0
   set @b_active = 1

BEGIN
IF exists(select top 1 * from gdt_user_groups where c_group_name = @c_groupname)
BEGIN
 select @IsGroupNameExists = 1
END
ELSE
 BEGIN
    SET NOCOUNT ON;
    insert into gdt_user_groups(c_group_name,c_description,n_group_id,b_active,d_modified_dttm,
    d_created_dttm)values(@c_groupname,@c_description,@n_grouptype,@b_active,GETDATE(),GETDATE())
    select @n_user_group_id = SCOPE_IDENTITY();
    select @IsGroupNameExists = 0
 END 

 select @IsGroupNameExists
 END

Here where exactly I can fetch the the scope_identity value in C# code. Anyone help?

Answer 1

You need to get value through output parameter, so you must follow these link:
Get output parameter value in ADO.NET
How to use OUTPUT parameter in Stored Procedure

you just need to create a SqlParameter, set the Direction to Output, and add it to the SqlCommand's Parameters collection. Then execute the stored procedure and get the value of the parameter.

While adding parameter do as below after adding extra parameter in your method

param.AddParam(SqlDbType.Int, "@c_Id", IdValue, ParameterDirection.Output);

then access the value of your output parameter as below:

object value = cmd.Parameters["@c_Id"].Value;

Source: Getting the identity of the most recently added record Code Snippet:

string query = "AddCategory";
int ID;
string connect = @"Server=.SQLExpress;Database=Northwind;Trusted_Connection=Yes;";
using (SqlConnection conn = new SqlConnection(connect))
{
  using (SqlCommand cmd = new SqlCommand(query, conn))
  {
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Category", Category.Text);
    cmd.Parameters.Add("@CategoryID", SqlDbType.Int, 0, "CategoryID");
    cmd.Parameters["@CategoryID"].Direction = ParameterDirection.Output;
    conn.Open();
    cmd.ExecuteNonQuery();
    ID = (int)cmd.Parameters["@CategoryID"].Value;
  }
}

procedure...

CREATE PROCEDURE AddCategory
  -- Add the parameters for the stored procedure here
  @Category NVARCHAR(15),
  @CategoryID INT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  INSERT INTO Categories (CategoryName) VALUES (@Category)
  SET @CategoryID = SCOPE_IDENTITY()
END

Hope the above reference help you more to understand all of this.