Need advice with ExecuteScalar()

[akak1997]

Hi all the experts in Code Project, I've a problem with returning the value returned by ExecuteScalar function under OleDbCommand. Here is a code sample. ... cmd.CommandText = searchSqlstr; long result = Convert.ToInt64(cmd.ExecuteScalar()); connection.Close(); return result; Is there a way to rewrite this code so that I DO NOT need the 'result' variable? since we always have to close the connection, if I write: return Convert.ToInt64(cmd.ExecuteScalar()); it would be a bad idea right? So is there a trick to do this? Thanks a Lot

[Grimolfr]

cmd.CommandText = searchSqlstr;

try
{
return Convert.ToInt64(cmd.ExecuteScalar());
}
finally
{
connection.Close();
}

Better yet would be a using(){} statement for the connection object, so that it will automatically be disposed, but since you didn't list all the code from instantiation to disposal, I can't reacreate it accurately.

using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();

try
{
	SqlCommand cmd = connection.CreateCommand();
	cmd.CommandType = CommandType.Text;
	cmd.CommandText = searchSqlstr;
	return Convert.ToInt64(cmd.ExecuteScalar());
}
// Put a catch{} block here, if you need one
finally
{
	connection.Close();
}

}

---

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL GO

(0 row(s) affected)

[akak1997]

Ah! Great tip! totally forgot about the using statements :D Thanks a Lot!

[Steven Campbell]

In the example given, the connection.Close(); is redundant, since the using block will take care of that. Which makes the try-finally is also redundant, (unless you want to implement a catch). So a simple version is:

using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();

SqlCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = searchSqlstr;
return Convert.ToInt64(cmd.ExecuteScalar());

}

---

my blog