locked
using output parameter instead of SCOPE_IDENTITY RRS feed

  • Question

  • User355715116 posted

    Hello I want to use OUTPUT parameter instead of SCOPE_IDENTITY 
    followed several resource but couldn't understand How to use it.  Can you please tell me that how can i use this?
    I am adding my existing code. 

    protected string GetInsertQueryWithOutput(DataRow dataRow, out QueryParamList paramList)
            {
                paramList = new QueryParamList();
    
                StringBuilder sqlBuilder1 = new StringBuilder();
                StringBuilder sqlBuilder2 = new StringBuilder();
    
                for (int i = 0; i < dataRow.ItemArray.Length; i++)
                {
                    if (!IsPrimaryKey(dataRow, i))
                    {
                        if (dataRow.ItemArray[i] != DBNull.Value)
                        {
                            sqlBuilder1.Append(dataRow.Table.Columns[i].Caption + ",");
                            sqlBuilder2.Append("@" + dataRow.Table.Columns[i].Caption + ",");
                            DbType dbType = (DbType)Enum.Parse(typeof(DbType), dataRow.Table.Columns[i].DataType.Name);
                            paramList.Add(new QueryParamObj() { ParamName = dataRow.Table.Columns[i].Caption, ParamValue = dataRow.ItemArray[i], DBType = dbType });
                        }
                    }
                }
                if (sqlBuilder1.Length > 0) sqlBuilder1.Remove(sqlBuilder1.Length - 1, 1);
                if (sqlBuilder2.Length > 0) sqlBuilder2.Remove(sqlBuilder2.Length - 1, 1);
    
                string finalQuery = "Insert Into " + dataRow.Table.TableName + "(" + sqlBuilder1.ToString() + ")" + " values(" + sqlBuilder2.ToString() + ");select SCOPE_IDENTITY()";
    
    
                return finalQuery;
            }

    I want to change my query in finalQuery. 
    Regards,

    Monday, August 10, 2020 6:18 AM

Answers

  • User1535942433 posted

    Hi mazharul007,

    I suggest you could check and debug wheater sqlBuilder2 have values.You could breakpoint at your codes and check wheather they have errors.

    You could refer to below codes:

     using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) output INSERTED.ID VALUES(@na,@occ)",con))
        {
            cmd.Parameters.AddWithValue("@na", Mem_NA);
            cmd.Parameters.AddWithValue("@occ", Mem_Occ);
            con.Open();
    
            int modified =(int)cmd.ExecuteScalar();
    
            if (con.State == System.Data.ConnectionState.Open) 
                con.Close();
    
            return modified;
        }

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 14, 2020 9:25 AM

All replies

  • User1535942433 posted

    Hi mazharul007,

    Accroding to your description and codes,you could use output into with a sample insert statement.

    Just like this:

    INSERT INTO  MyTable (col1, col2, col3)
    OUTPUT INSERTED.id, col1, col2, col3
    VALUES (@col1, @col2, @col3);

    In your code,you could use just like this:

    string finalQuery = "Insert Into " + dataRow.Table.TableName + "(" + sqlBuilder1.ToString() + ")" + " OUTPUT INSERTED.ID "+ " values(" + sqlBuilder2.ToString() + ");";

    Best regards,

    Yijing Sun

    Tuesday, August 11, 2020 6:03 AM
  • User355715116 posted

    I tried the way you suggested but after using it it returns null table. What can be the alternative way dear yij sun? 

    Tuesday, August 11, 2020 10:47 AM
  • User1535942433 posted

    Hi mazharul007,

    I suggest you could check and debug wheater sqlBuilder2 have values.You could breakpoint at your codes and check wheather they have errors.

    You could refer to below codes:

     using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) output INSERTED.ID VALUES(@na,@occ)",con))
        {
            cmd.Parameters.AddWithValue("@na", Mem_NA);
            cmd.Parameters.AddWithValue("@occ", Mem_Occ);
            con.Open();
    
            int modified =(int)cmd.ExecuteScalar();
    
            if (con.State == System.Data.ConnectionState.Open) 
                con.Close();
    
            return modified;
        }

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 14, 2020 9:25 AM