using output parameter instead of SCOPE_IDENTITY

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,

Answer 1

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

Answer 2

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? 

Answer 3

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