none
Why InsertCommand be reseted? RRS feed

  • Question

  • I want insert record into table and return the Indentity of the new record. So I writed a section code like this.

    Firstly,use Commandbuilder create the InsertCommand.

    Then, connect the sql ";Select @ID = @@IDENTITY" to the InsertCommand.CommandText

    So,I want excute the sql like this "INSERT INTO [Employees] ([LastName], [FirstName]) VALUES (@LastName, @FirstName);Select @ID = @@IDENTITY"

    But, unfortunately I found when SqlDataAdapter.Update() excutes, the InserCommand.CommandText be reseted by "INSERT INTO [Employees] ([LastName], [FirstName]) VALUES (@LastName, @FirstName);"

     

    Code Snippet

            private void updateWithIdentityToolStripMenuItem_Click(object sender, EventArgs e)
            {
                con = new SqlConnection(strConString);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = strSelectEmployee;
                SqlDataAdapter dap = new SqlDataAdapter();
                dap.SelectCommand = cmd;

                SqlCommandBuilder builder = new SqlCommandBuilder(dap);
              
                dap.InsertCommand = builder.GetInsertCommand(true);
                dap.UpdateCommand = builder.GetUpdateCommand(true);
                dap.DeleteCommand = builder.GetDeleteCommand(true);

                dap.InsertCommand.CommandText = dap.InsertCommand.CommandText + ";Select @ID = @@IDENTITY";

                dap.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

                SqlParameter param = new SqlParameter("@ID",SqlDbType.Int,0,ParameterDirection.Output, false, 0, 0, "EmployeeID", DataRowVersion.Original, null);
                dap.InsertCommand.Parameters.Add(param);
                try
                {
                    con.Open();
                    dap.Update(dsData, "Employees");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    con.Close();
                }

     

     

    I want to know why my sql has been reset?

     

    Then, I modifyed my source as following codes.

    Call the Clone() method when set the DataAdapter's InsertCommand, then the problem be solved.

    Why?

    I confused!

    Code Snippet

            private void updateWithIdentityToolStripMenuItem_Click(object sender, EventArgs e)
            {
                con = new SqlConnection(strConString);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = strSelectEmployee;
                SqlDataAdapter dap = new SqlDataAdapter();
                dap.SelectCommand = cmd;

                SqlCommandBuilder builder = new SqlCommandBuilder(dap);
              
                dap.InsertCommand = builder.GetInsertCommand(true).Clone();
                dap.UpdateCommand = builder.GetUpdateCommand(true);
                dap.DeleteCommand = builder.GetDeleteCommand(true);

                dap.InsertCommand.CommandText = dap.InsertCommand.CommandText + ";Select @ID = @@IDENTITY";

                dap.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

                SqlParameter param = new SqlParameter("@ID",SqlDbType.Int,0,ParameterDirection.Output, false, 0, 0, "EmployeeID", DataRowVersion.Original, null);
                dap.InsertCommand.Parameters.Add(param);
                try
                {
                    con.Open();
                    dap.Update(dsData, "Employees");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    con.Close();
                }

     

     

    Tuesday, July 15, 2008 2:37 AM

Answers

  •  

    SqlCommandBuilder aims to auto-generate the insert/delete/update commands from SELECT statement. If you register it with data adapter, it will override those commands on the first run of the SELECT statement (which is triggered by Update in your case). Thus, if you want to customize and control the commands by yourself, do not use the SqlCommandBuilder and create commands by yourself.

     

    When you use Clone call on the command, this command is no more under control of the SqlCommandBuilder, thus it looks like the problem is gone.

     

    To solve, you can try one of the below:

    * set the select command and use the builder to control your insert/delete/update commands, or

    * do not use the builder and initialize the commands explicitly.

     

    Refer to MSDN here for more information about SqlCommandBuilder:

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx

     

    Regards,

     Nissim

    Sunday, July 20, 2008 9:55 AM
    Moderator

All replies

  • What is your goal? Do you need to insert row and get identity back only or you also want to update records inside of DataTable with the identity. In first case, you do not need to use SqlDataAdapter at all and you could use SqlCommand with ExecuteNonQuery method to execute INSERT SQL statement and get identity back.

    Ina case if you need to handle identity inside of DataTable here is sample how to do this

     

    http://support.microsoft.com/kb/320897/en-us

     

    Tuesday, July 15, 2008 2:47 AM
    Moderator
  • Thanks for your reply.

     

    As you said, I want update record inside of DataTable, so I used CommandBuilder to create insert, update and delete sql command.

    But my question is why I modified insert sql text has been reseted when SqlDataAdapter.Update() method invoked.

    I want to know the reason of that.

    I guess that the CommandBuilder resets the InsertCommand, but I am not sure.

     

    And I also want to know the reason of why the InsertCommand would not be reseted when SqlDataAdapter.Update() method invoked, if I set the SqlDataAdapter.InsertCommand property as follow codes.

    Code Snippet

    dap.InsertCommand = builder.GetInsertCommand(true).Clone();

     

     

    What's the reason?

     

    Tuesday, July 15, 2008 5:05 AM
  • Is there anybody could help me?

     

    Friday, July 18, 2008 2:09 AM
  •  

    SqlCommandBuilder aims to auto-generate the insert/delete/update commands from SELECT statement. If you register it with data adapter, it will override those commands on the first run of the SELECT statement (which is triggered by Update in your case). Thus, if you want to customize and control the commands by yourself, do not use the SqlCommandBuilder and create commands by yourself.

     

    When you use Clone call on the command, this command is no more under control of the SqlCommandBuilder, thus it looks like the problem is gone.

     

    To solve, you can try one of the below:

    * set the select command and use the builder to control your insert/delete/update commands, or

    * do not use the builder and initialize the commands explicitly.

     

    Refer to MSDN here for more information about SqlCommandBuilder:

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx

     

    Regards,

     Nissim

    Sunday, July 20, 2008 9:55 AM
    Moderator