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?
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.