从三层体系结构的存储过程中获取C#代码中的scope_identity值 [英] Fetch scope_identity value in C# code from stored procedure in 3 tier architecture

问题描述

在C#代码中插入数据后，我需要从存储过程中获取 scope_identity()值.

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

我在这里使用3层体系结构.请任何人帮助我

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;
}

我的存储过程:

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

这里正是我可以在C#代码中获取scope_identity值的地方.有人帮忙吗?

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

推荐答案

您需要通过输出参数获取值，因此必须遵循以下链接:
在ADO.NET中获取输出参数值
如何在存储过程中使用OUTPUT参数

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

您只需要创建一个SqlParameter，将Direction设置为Output，并将其添加到SqlCommand的Parameters集合中.然后执行存储过程并获取参数的值.

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;

来源: 获取最近添加的记录的身份代码段:

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;
  }
}

程序...

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.

这篇关于从三层体系结构的存储过程中获取C#代码中的scope_identity值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！