SQL call for a scalar

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?

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.

StackExchange Code Review Q#60028, answer score: 5