using output parameter instead of SCOPE_IDENTITY[Answered] RSS

• • Reply
  mazharul007

  Member

  12 Points

  42 Posts

  using output parameter instead of SCOPE_IDENTITY

  Aug 10, 2020 06:18 AM|mazharul007|LINK

  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,

• • Reply
  yij sun

  Contributor

  4050 Points

  1570 Posts

  Re: using output parameter instead of SCOPE_IDENTITY

  Aug 11, 2020 06:03 AM|yij sun|LINK

  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

  ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
• • Reply
  mazharul007

  Member

  12 Points

  42 Posts

  Re: using output parameter instead of SCOPE_IDENTITY

  Aug 11, 2020 10:47 AM|mazharul007|LINK

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

• • Reply
  yij sun

  Contributor

  4050 Points

  1570 Posts

  Re: using output parameter instead of SCOPE_IDENTITY

  Aug 14, 2020 09:25 AM|yij sun|LINK

  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

  ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >