How to shorten this code for SQL-query

[K Viltersten]

I'm using the code below. I know for sure
that the data obtained will be a single
value. Hence, it feels somewhat like nuking
a fly to use the adapter etc. Is it
possible to shorten the code for this
specific, special case? How?

string commandString
= "select sth from smwhr where id = 5";
SqlConnection connection
= new SqlConnection(conStr);
SqlCommand command
= new SqlCommand(commandString, connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds);
connection.Close();
return ds.Tables[0].Rows[0][0].ToString();
--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.

[Jeroen Mostert]

K Viltersten wrote:

I'm using the code below. I know for sure
that the data obtained will be a single
value. Hence, it feels somewhat like nuking
a fly to use the adapter etc. Is it
possible to shorten the code for this
specific, special case? How?

SqlCommand.ExecuteScalar().

--
J.

[Alberto Poblacion]

"K Viltersten" <tm**@viltersten.comwrote in message
news:6h************@mid.individual.net...

I'm using the code below. I know for sure
that the data obtained will be a single
value. Hence, it feels somewhat like nuking
a fly to use the adapter etc. Is it
possible to shorten the code for this
specific, special case? How?

string commandString
= "select sth from smwhr where id = 5";
SqlConnection connection
= new SqlConnection(conStr);
SqlCommand command
= new SqlCommand(commandString, connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds);
connection.Close();
return ds.Tables[0].Rows[0][0].ToString();

If you are only getting a single value, use ExecuteScalar:

string commandString
= "select sth from smwhr where id = 5";
SqlConnection connection
= new SqlConnection(conStr);
SqlCommand command
= new SqlCommand(commandString, connection);
connection.Open();
object result = command.ExecuteScalar();
connection.Close();
return result.ToString();

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

K Viltersten wrote:

I'm using the code below. I know for sure
that the data obtained will be a single
value. Hence, it feels somewhat like nuking
a fly to use the adapter etc. Is it
possible to shorten the code for this
specific, special case? How?

Actually, you are nuking the fly twice...

string commandString
= "select sth from smwhr where id = 5";
SqlConnection connection
= new SqlConnection(conStr);
SqlCommand command
= new SqlCommand(commandString, connection);
connection.Open();

Here you are executing the query and throwing away the result:

command.ExecuteNonQuery();

SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();

Here you are executing the query again:

da.Fill(ds);

connection.Close();
return ds.Tables[0].Rows[0][0].ToString();

All you need is:

string commandString = "select sth from smwhr where id = 5";
string value;
using (SqlConnection connection = new SqlConnection(conStr)) {
using (SqlCommand command = new SqlCommand(commandString, connection)) {
value = (string)command.ExecuteScalar();
}
}
return value;

--
Göran Andersson
_____
http://www.guffa.com

[K Viltersten]

>I'm using the code below. I know for sure

>that the data obtained will be a single
value. Hence, it feels somewhat like nuking
a fly to use the adapter etc. Is it
possible to shorten the code for this
specific, special case? How?

Actually, you are nuking the fly twice...

>string commandString
= "select sth from smwhr where id = 5";
SqlConnection connection
= new SqlConnection(conStr);
SqlCommand command
= new SqlCommand(commandString, connection);
connection.Open();

Here you are executing the query and throwing away the result:

>command.ExecuteNonQuery();

>SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();

Here you are executing the query again:

>da.Fill(ds);

>connection.Close();
return ds.Tables[0].Rows[0][0].ToString();

All you need is:

string commandString = "select sth from smwhr where id = 5";
string value;
using (SqlConnection connection = new SqlConnection(conStr)) {
using (SqlCommand command = new SqlCommand(commandString, connection))
{
value = (string)command.ExecuteScalar();
}
}
return value;

Where do you open/close the connection? Is it
performed automagically, perhaps? I'm affraid
that it might not work on my computer as i'm
still on v2 of C#. Does it matter in this case?

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.

[Alberto Poblacion]

"K Viltersten" <tm**@viltersten.comwrote in message
news:6h************@mid.individual.net...

[...]
Where do you open/close the connection? Is it
performed automagically, perhaps?

No, it's not automatic. He forgot to add connection.Open() inside the
"using" block.

>I'm affraid
that it might not work on my computer as i'm
still on v2 of C#. Does it matter in this case?

No problem. There's nothing in that code that requires a newer version.
In fact, if I recall correctly, it should even work in C# 1.0.

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

K Viltersten wrote:

>>I'm using the code below. I know for sure
that the data obtained will be a single
value. Hence, it feels somewhat like nuking
a fly to use the adapter etc. Is it
possible to shorten the code for this
specific, special case? How?

Actually, you are nuking the fly twice...

>>string commandString
= "select sth from smwhr where id = 5";
SqlConnection connection
= new SqlConnection(conStr);
SqlCommand command
= new SqlCommand(commandString, connection);
connection.Open();

Here you are executing the query and throwing away the result:

>>command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();

Here you are executing the query again:

>>da.Fill(ds);
connection.Close();
return ds.Tables[0].Rows[0][0].ToString();

All you need is:

string commandString = "select sth from smwhr where id = 5";
string value;
using (SqlConnection connection = new SqlConnection(conStr)) {
using (SqlCommand command = new SqlCommand(commandString, connection))
{
value = (string)command.ExecuteScalar();
}
}
return value;

Where do you open/close the connection? Is it
performed automagically, perhaps? I'm affraid
that it might not work on my computer as i'm
still on v2 of C#. Does it matter in this case?

As Alberto pointed out, I forgot to open the connection.

Closing the connection, however, is done automatically. When you exit
the using block, the Dispose method of the object is called, which
closes the database connection.

The using block also uses a try...finally, so the connection is always
closed, even if the code inside the block would cause an exception.

--
Göran Andersson
_____
http://www.guffa.com

[K Viltersten]

<snip>

>Where do you open/close the connection? Is it
performed automagically, perhaps? I'm affraid
that it might not work on my computer as i'm
still on v2 of C#. Does it matter in this case?

As Alberto pointed out, I forgot to open the connection. Closing the
connection, however, is done automatically. When you exit the using block,
the Dispose method of the object is called, which closes the database
connection.

Thanks. Jättebra, if i may take a guess based on
your name and the contents of your webpage. :)

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.