when using ExecuteScalar it writes 2 Records instead of one

Question

I need to know what order number has been written to the database. To achieve this I use the SQL expression EXECUTESCALAR.

I debugged the program and monitored the database. The record is written once into the Database when I execute the 2nd Command it writes the same record a 2nd time. Can you help me out what I need to change so it does not write 2 records and I get my Order number back?

This is my code:

public static int CreateDocumentNumber(string userId, string todaysDate, decimal docprice, decimal docpaid, int packageId, int orderstatus)
{
    int orderId = 0;   //return value order Id

    //create order 
    string connectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    string insertSql = "INSERT INTO [dbo].[LD_Orders](TD_OrdUserID, TD_OrdDate, TD_OrdCost, TD_OrdPaid, TD_OrdPackage, TD_OrdStatus)" +
        " VALUES (@UserId, @Date, @Cost, @Paid, @Package, @Status);SELECT SCOPE_IDENTITY()";

    using (SqlConnection myConnection = new SqlConnection(connectionString))
    {
        myConnection.Open();
        SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
        myCommand.Parameters.Add("@UserId", SqlDbType.VarChar).Value = userId;
        myCommand.Parameters.Add("@Date", SqlDbType.Date).Value = todaysDate;
        myCommand.Parameters.Add("@Cost", SqlDbType.Decimal).Value = docprice;
        myCommand.Parameters.Add("@Paid", SqlDbType.Decimal).Value = docpaid;
        myCommand.Parameters.Add("@Package", SqlDbType.Int).Value = packageId;
        myCommand.Parameters.Add("@Status", SqlDbType.Int).Value = orderstatus;
        myCommand.ExecuteNonQuery();  <---- FIRST RECORD WRITTEN

        // time to collect the last order id
        orderId = Convert.ToInt32(myCommand.ExecuteScalar());   <---- SECOND RECORD WRITTEN

        myConnection.Close();
    }
    return orderId;
}

Every `Execute*` call triggers an insert statement, so you don't need two in this function — Zruty, Jul 11 '14 at 04:39
THank you Zruty, so I just delete my first excecuteNonQuery and all would be fine then — Rene, Jul 11 '14 at 04:48
This is possible duplicate as I feel that what Rene is asking is present here. http://stackoverflow.com/questions/14246744/executescalar-vs-executenonquery-when-returning-an-identity-value — dotnetstep, Jul 11 '14 at 04:58
You're executing the `INSERT` twice - so why does it surprise you that two rows are inserted?!?!?!?! — marc_s, Jul 11 '14 at 05:32

Zruty · Accepted Answer · 2014-07-11T04:52:32.230

Your code should work if you remove the line myCommand.ExecuteNonQuery();

Both ExecuteNonQuery() and ExecuteScalar() send the command to SQL server, the only difference is how they treat the result. Basically, you are executing two INSERT statements, which you don't need.

On a side note, it is very seldom justified to do hardcore ADO.NET coding nowadays. You typically save a lot of time if you use some ORM, like Entity Framework or my personal favorite, Dapper.NET.

Here's how you could rewrite your entire function using Dapper:

public static int CreateDocumentNumber(string userId, string todaysDate, decimal docprice, decimal docpaid, int packageId, int orderstatus)
{
    //create order 
    string connectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    string insertSql = "INSERT INTO [dbo].[LD_Orders](TD_OrdUserID, TD_OrdDate, TD_OrdCost, TD_OrdPaid, TD_OrdPackage, TD_OrdStatus)" +
        " VALUES (@UserId, @Date, @Cost, @Paid, @Package, @Status);SELECT SCOPE_IDENTITY()";

    using (SqlConnection myConnection = new SqlConnection(connectionString))
    {
        myConnection.Open();
        int orderId = myConnection.Query<int>(
            insertSql,
            new {
                   UserId = userId,
                   Date = todaysDate,
                   Cost = docprice,
                   Paid = docpaid,
                   Package = packageId,
                   Status = orderstatus
                }).Single();
    }
    return orderId;
}

Appreciate the feedback and the dapper.net suggestion. Thank you. I tested it with removing my line and it worked perfect. — Rene, Jul 13 '14 at 23:23

when using ExecuteScalar it writes 2 Records instead of one

1 Answers1