ExecuteScalar vs ExecuteNonQuery when returning an identity value

by

As suggested by Aaron, a stored procedure would make it faster because it saves Sql Server the work of compiling your SQL batch. However, you could still go with either approach: ExecuteScalar or ExecuteNonQuery. IMHO, the performance difference between them is so small, that either method is just as “proper”.

Having said that, I don’t see the point of using ExecuteScalar if you are grabbing the identity value from an output parameter. In that case, the value returned by ExecuteScalar becomes useless.

An approach that I like because it requires less code, uses ExecuteScalar without output parameters:

public static int SaveTest(Test newTest)
{
    var conn = DbConnect.Connection();
    const string sqlString = "INSERT INTO dbo.Tests ( Tester , Premise ) " +
                             "               VALUES ( @tester , @premise ) " +
                             "SELECT SCOPE_IDENTITY()";
    using (conn)
    {
        using (var cmd = new SqlCommand(sqlString, conn))
        {
            cmd.Parameters.AddWithValue("@tester", newTest.tester);
            cmd.Parameters.AddWithValue("@premise", newTest.premise);

            cmd.CommandType = CommandType.Text;
            conn.Open();
            return (int) (decimal) cmd.ExecuteScalar();

        }
    }
}

Happy programming!

EDIT: Note that we need to cast twice: from object to decimal, and then to int (thanks to techturtle for noting this).

More Related Contents:

  1. ExecuteNonQuery returning -1 when using sql COUNT despite the query string
  2. C#: SQL Server Connection String
  3. Delete from two tables? [closed]
  4. An unhandled exception of type ‘System.InvalidOperationException’ occurred in System.Data.dll?
  5. Using stored procedure output parameters in C#
  6. Insert entire DataTable into database at once instead of row by row?
  7. SQL Query slow in .NET application but instantaneous in SQL Server Management Studio
  8. Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax
  9. How to execute an SSIS package from .NET?
  10. How to retrieve data from a SQL Server database in C#?
  11. Bulk Update in C#
  12. SqlParameter does not allows Table name – other options without sql injection attack?
  13. Any way to SQLBulkCopy “insert or update if exists”?
  14. EF 6 – How to correctly perform parallel queries
  15. What’s the fastest way to bulk insert a lot of data in SQL Server (C# client)
  16. Delete a single record from Entity Framework?
  17. How to pass XML from C# to a stored procedure in SQL Server 2008?
  18. How to use SqlCommand to CREATE DATABASE with parameterized db name?
  19. Failed to update .mdf database because the database is read-only (Windows application)
  20. What size do you use for varchar(MAX) in your parameter declaration?
  21. GO statements blowing up sql execution in .NET
  22. How to Identify the primary key duplication from a SQL Server 2008 error code?
  23. Provider named pipes provider error 40 could not open a connection to SQL Server error 2 [duplicate]
  24. Is it possible to use SqlGeography with Linq to Sql?
  25. How to get efficient Sql Server deadlock handling in C# with ADO?
  26. Cyrillic encoding in C#
  27. Generating sql code programmatically
  28. Accessing System Databases/Tables using LINQ to SQL?
  29. ‘Must Declare the Scalar Variable’ Error When Passing a Table-Valued Parameter to a Parameterized SQL Statement
  30. What does a timestamp in T-Sql mean in C#?

Leave a Comment