SQL call for a scalar

Question

When I work with SqlDataAdapter, I usually follow this pattern. Especially note the using/fill combination, which is basically a one-liner. The other stuff is just the facility around it.

private void PopulateData(DataTable table, long index)
{
  SqlCommand command = new SqlCommand(
    commandText.Replace("{ID}", "" + index), Connection);

  using (SqlDataAdapter adapter = new SqlDataAdapter(command))
    adapter.Fill(table);
}

Now, I'll be getting a number of scalars, so naturally I'm prone to use the appropriate method for that. However, I'm not certain how to make the code shorter without affecting readability.

private String GetString(long index)
{
  String output;
  using (SqlCommand command = new SqlCommand(CommandText, Connection))
  {
    command.Connection.Open();
    output = command.ExecuteScalar() as String;
    command.Connection.Close();
  }
  return output;
}

Preferably I'd like to get using/returning of the scalar value to be done as a one-liner too. Is it at all possible?

Or is the pattern of open-get-close inside the using-scope the best way to go? Perhaps it depends on the different circumstances? In such case - which?

Answer 1

I am not sure about the Private/Public scope of this method, someone else will have to talk about that. but you can return inside a using statement, so instead of this:

private String GetString(long index)
{
  String output;
  using (SqlCommand command = new SqlCommand(CommandText, Connection))
  {
    command.Connection.Open();
    output = command.ExecuteScalar() as String;
    command.Connection.Close();
  }
  return output;
}

you would just return like this

private String GetString(long index)
{
  using (SqlCommand command = new SqlCommand(CommandText, Connection))
  {
    return (command.ExecuteScalar()).ToString();
  }
}

The Command is automatically taken care of so you don't have to worry about explicitly disposing of it.

I assume that the connection is opened and closed by the piece of code that calls this private method, so I left out the connection open and close.

I am kind of on edge about doing it that way, but it seems like you would only want to open one connection for all the SqlCommands that you need to run during the course of the application run.

Answer 2

Just continuation to @Malachi an here is a generic version of ExecuteScalar,

so it is good so you dont have to cast it again in code.

private T ExecuteScalar<T>(long index)
{
    using (var command = new SqlCommand(commandText, connection))
    {
        var output = command.ExecuteScalar();
        if (output != null)
        {
            return (T)Convert.ChangeType(output, typeof(T), CultureInfo.InvariantCulture);
        }
        return default(T);
    } 
}

Answer 3

Since Close() is the same as Dispose(), you can use using on Connection too. This allows you to get rid of the output variable:

private string GetString()
{
    using (Connection)
    using (var command = new SqlCommand(CommandText, Connection))
    {
        command.Connection.Open();
        return (string)command.ExecuteScalar();
    }
}

I also changed as into a cast: if there is a mistake and ExecuteScalar() actually returns something that isn't a string, you want to know it right away clearly, not learn it later when you get a confusing NullReferenceException.

One more thing I changed is to use var: you can clearly see what the type is, you don't need to repeat it.