How do I execute a SqlDataReader after sqlInsertCmd.ExecuteNonQuery

Question

I am inserting a data row into my SQL Server database and then I want to query the data to get the unique identifier from the inserted row but my SqlDataReader is returning an empty dataset. I am thinking it maybe that the transaction hasn't been committed or something like that but I am not sure. I do not get an error.

Here is my code:

try
{
    strQuery = "INSERT INTO clientnames VALUES(NEWID(),'" + txtACLastName.Text + "','" + txtACFirstName.Text + "'," + 1 + ")";

    using (SqlCommand sqlInsertCmd = new SqlCommand(strQuery, sqlConn))
    {
        intQueryResult = sqlInsertCmd.ExecuteNonQuery();

        if (intQueryResult == 0)
        {
            blnSuccess = false;
            goto InsertClientNamesError;
        }
        else
        {
            blnSuccess = true;
        }

        sqlInsertCmd.Dispose();
    }

    if (blnSuccess)
    {
        strQuery = "select clientID from clientnames where firstname = '" + txtACFirstName.Text + "' and lastname = '" + txtACLastName.Text + "'";

        using (SqlCommand sqlSelectCmd = new SqlCommand(strQuery, sqlConn))
        {
            SqlDataReader sqlDataRead = sqlSelectCmd.ExecuteReader();

            while (sqlDataRead.Read())
            {
                strClientID = sqlDataRead.ToString();
            }

            sqlDataRead.Close();
            sqlSelectCmd.Dispose();
        }
    }
}
catch (Exception exQuery)
{
    System.Windows.MessageBox.Show("InsertClientNames: Error, " + exQuery.Message + ", has occurred.");
}

Answer 1

Add the following line to your stored procedure that inserts the record

SELECT SCOPE_IDENTITY()

This will return the last identity value inserted in that table.

And use cmd.ExecuteScalar() instead of ExecuteNonQuery()

ExecuteScalar() executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored. [More info][1]

Answer 2

You are not getting the desired result because perhaps the SqlConnection is not opened explicitly (just a guess hard to tell without having full code). But this link shows you how to read from reader --> https://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.110).aspx

But I suggest that you Please do not do it this way. Reason is you are making Two round trips to the DB Server when only one would have done the job for you IF you were using stored procedures. Also you are exposing yourselves to SQL Injection attacks as you are not parameterizing your queries.

Stored procedure:

CREATE PROCEDURE dbo.INS_clientnames 
(
   @FirstName varchar(100),
   @LastName  varchar(100),
   @NewID     int out
)
AS
BEGIN
  Declare @Err int
  set  @NewID = NewID() -- Get the New ID and store it in the variable ( @NewID ) that the SP will return back to the caller 

  INSERT INTO clientnames values (@NewID , @FirstName ,  @LastName) 
  SET @Err = @@ERROR

  IF @Error <> 0 -- Check If there was an error
  Begin 
       SET  @NewID  = -1 -- Indicates that there was an error. You could log this into a Log Table with further details like error id and name.
  END 

RETURN
END

C# code to execute the above stored procedure and get the NewID:

using(SqlConnection conn = new SqlConnection(connectionString ))
{
    using(SqlCommand cmd = new SqlCommand("dbo.INS_clientnames", conn))
    {
         cmd.CommandType = CommandType.StoredProcedure;

         // set up the parameters that the Stored Procedure expects
         cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 100);
         cmd.Parameters.Add("@LastName" , SqlDbType.VarChar, 100);
         cmd.Parameters.Add("@NewId"    , SqlDbType.Int).Direction = ParameterDirection.Output;

         // set parameter values that your code will send to the SP as parameter values
         cmd.Parameters["@FirstName"].Value = txtACFirstName.Text ;
         cmd.Parameters["@LastName"].Value  = txtACLastName.Text  ;

         // open connection and execute stored procedure
         conn.Open();
         cmd.ExecuteNonQuery();

         // read output value from @NewId
         int NewID = Convert.ToInt32(cmd.Parameters["@NewId"].Value);
    }
}

Answer 3

I see two approaches to do this:

1. either you generate the new GUID on the client side in your C# code and pass it into the query - then you already know what the new id is going to be, so you don't need to do a second query to get it:
2. you create your GUID on the server side and return it to the caller using the OUTPUT clause in your query

Approach #1:

// define connection string and query
string connStr = "--your connection string here--";
string query = "INSERT INTO dbo.Clients(ClientID, FirstName, LastName) VALUES(@ID, @First, @Last);";

using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand(query, conn))
{
    // create the GUID in C# - this is the ID - no need to go get it again - this *IS* the id
    Guid id = Guid.NewGuid();

    // set the parameters
    cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = id;
    cmd.Parameters.Add("@First", SqlDbType.VarChar, 50).Value = "Peter";
    cmd.Parameters.Add("@Last", SqlDbType.VarChar, 50).Value = "Miller";

    // open connection, execute query, close connection
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
}

Approach #2:

// define connection string and query
string connStr = "--your connection string here--";

// query has an "OUTPUT" clause to return a newly inserted piece of data
// back to the caller, just as if a SELECT had been issued
string query = "INSERT INTO dbo.Clients(ClientID, FirstName, LastName) OUTPUT Inserted.ClientID VALUES(NEWID(), @First, @Last);";

using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand(query, conn))
{
    // set the parameters - note: you do *NOT* send in a GUID value - the NEWID() will create one automatically, on the server
    cmd.Parameters.Add("@First", SqlDbType.VarChar, 50).Value = "Frank";
    cmd.Parameters.Add("@Last", SqlDbType.VarChar, 50).Value = "Brown";

    // open connection
    conn.Open();

    // execute query and get back one row, one column - the value in the "OUTPUT" clause
    object output = cmd.ExecuteScalar();

    Guid newId;

    if (Guid.TryParse(output.ToString(), out newId))
    {
        //
    }

    conn.Close();
}