ExecuteScalar - nemiro-net/nemiro.data.dll GitHub Wiki

The ExecuteScalar method of the SqlClient class allows a specified query and get only one field.

Often this method is used to add data to a table to get identifier of the newly added record.

C#

using (SqlClient client = new SqlClient())
{
  // sql query or stored procedure name
  client.CommandText = "INSERT INTO table (field1, fieald2)" +
                       "VALUES (@field1, @fieald2);" +
                       "SELECT SCOPE_IDENTITY();"; 
  // parameters
  client.Parameters.Add("@field1", SqlDbType.DateTime).Value = DateTime.Now;
  client.Parameters.Add("@fieald2", SqlDbType.NVarChar, 50).Value = "hello, world!";
  // query execution
  var result = client.ExecuteScalar();
  Console.WriteLine("ID: {0}", result);  
}

Visual Basic .NET

Using client As New SqlClient()
  ' sql query or stored procedure name
  client.CommandText = "INSERT INTO table (field1, fieald2)" & _
                       "VALUES (@field1, @fieald2);" & _
                       "SELECT SCOPE_IDENTITY();"
  ' parameters
  client.Parameters.Add("@field1", SqlDbType.DateTime).Value = Now
  client.Parameters.Add("@fieald2", SqlDbType.NVarChar, 50).Value = "hello, world!"
  ' query execution
  Dim result As Object = client.ExecuteScalar()
  Console.WriteLine("ID: {0}", result)
End Using