SqlDataAdapter Get Identity after insert

Question

I create a SqlDataAdapter after a fill it to Dataset. My question is that after insert I want to get the IDENTITY value for primary column. For example a give buttonedit1 editvalue is Id (this is my primary column) after insert I want to get Identity value in ButtonEdit1 text.

Can I make this unless use SQL command like Select @IDentity

thanks.

public void Form1_Load(object sender,EventArgs e)
{
   try
   {
      SqlConnection con = new SqlConnection(@"ConString");

      con.Open();

      adap = new SqlDataAdapter("select Id,Caption from SKMenu where ID=-1",con);
      adap.Fill(ds, "Table");

      bs.DataSource = ds.Tables["Table"];

      buttonEdit1.DataBindings.Add("Text", bs, "Id");
      buttonEdit2.DataBindings.Add("Text", bs, "Caption");

      bs.AddNew();
}

private void button1_Click(object sender, EventArgs e)
{
   SqlCommandBuilder cv = new SqlCommandBuilder(adap);            

   bs.EndEdit();

   adap.Update(ds.Tables["Table"]);
}

One way or another, you **must** call something like `SELECT SCOPE_IDENTITY()` or use an `OUTPUT` statement on your `INSERT` call to get the newly inserted identity value - there's no other way, really. — marc_s, Jun 28 '11 at 21:03
Your code is also very incomplete - there's a starting `try { ....` but no proper end to this (no end brace, and no catch or finally statement blocks).... — marc_s, Jun 28 '11 at 21:04
Thanks for answer but if I update a row twice time or it is not insert it is update.all time it will get to my new ID — IsimYok, Jun 28 '11 at 21:07

score 3 · Answer 1 · answered Mar 07 '14 at 14:33

    public static bool CreateEntity(object entity, out long id)
    {
        bool created = false;
        long newid = -1;

        DataTable table = new DataTable();

        using (SqlConnection conn = new SqlConnection(Provider.Connection()))
        {
            string sqlcreate = "select * from {0} where id = -1;";
            conn.Open();
            using (SqlDataAdapter da = new SqlDataAdapter(String.Format(sqlcreate, entity.GetType().UnderlyingSystemType.Name), conn))
            {

                using (SqlCommandBuilder build = new SqlCommandBuilder(da))
                {
                    using (DataSet ds = new DataSet())
                    {
                        da.Fill(ds);

                        DataRow dr = ds.Tables[0].NewRow();

                        ClassProperties.Update(entity, dr);
                        da.InsertCommand = build.GetInsertCommand();
                        da.InsertCommand.CommandText += ";SELECT SCOPE_IDENTITY()";
                        da.InsertCommand.Parameters.Clear();
                        for (int i = 1; i < dr.ItemArray.Length; i++)
                        {
                            da.InsertCommand.Parameters.AddWithValue("@p" + i, dr.ItemArray[i]);
                        }

                        var result = da.InsertCommand.ExecuteScalar();
                        if (result != null)
                        {
                            created = true;
                            newid = Convert.ToInt64(result);
                        }
                    }
                }
            }
        }

        id = newid;
        return created;
    }

This worked for me. Since SCOPE_IDENTITY() returns object (var) I use this since the table I use has int identity field: sqlDataAdapter.InsertCommand.CommandText += ";SELECT Convert(int, SCOPE_IDENTITY())"; Then later after adding parameters: identity = (int)sqlDataAdapter.InsertCommand.ExecuteScalar(); (Working on getting this formatted correctly; four spaces & Ctrl-K do not work.) — pwrgreg007, May 04 '17 at 17:39

score 1 · Answer 2 · answered Jun 28 '11 at 21:11

If you change the command on the InsertCommand on the Adpater to this you should be fine

INSERT INTO  SKMenu  ( Caption ) VALUES ( @Caption);
SELECT Id, Caption FROM SKMenu WHERE id = SCOPE_IDENITY();

You could also use the OUTPUT Clause

The update command should be something like

UPDATE  SKMenu  SET Caption = @Caption WHERE ID = @id;

IIRC the dataset is smart enough to know when to use Update Or Insert, as long as you call ds.AcceptChanges() after the adap.Update().

SqlDataAdapter Get Identity after insert

2 Answers2

Linked