How can I use ExecuteScalar in this scenario?

Question

I have this SQL Select statement to select the basket id where the user id equals a specific ID.

I want to store the result of the query in a variable, so I thought I could have done:

BasketPage.SelectCommand="SELECT BasketID FROM tblBasket WHERE (UserID = '9f96bd94-91b9-4328-8214-4eac179c3a26')"

var BasketID = BasketPage.ExecuteScalar().ToString();

But apparently I need to create a new instance of an SQL Command for it to work, how can I do this for the BasketPage Select Command?

Why do you think you need to create a new instance of an SQL Command? — Conrad Frix, Dec 06 '10 at 00:52
Because the method wouldn't appear or let me use it unless I created a new instance. — , Dec 06 '10 at 00:56
What's the code of you `BasketPage` class? Does it actually have a `SelectCommand` property? I don't get this approach, a code sample of your `BasketPage` class and the way you're using ADO.NET within it might help. =) — Will Marcouiller, Dec 06 '10 at 00:59

Will Marcouiller · Accepted Answer · 2010-12-06T01:18:44.410

2

I don't know about your BasketPage, but there is no way you can't perform this query against the underlying database using the ExecuteScalar method. Assuming SQL Server:

using (var connection = new SqlConnection(connString))
    using (var command = connection.CreateCommand()) {
        command.CommandText = "select BasketId from tblBasket where UserId = N'9f96bd94-91b9-4328-8214-4eac179c3a26'";
        command.CommandType = CommandType.Text;

        try {
            var basketId = (string)command.ExecuteScalar(); // Assuming BasketId is a string, since you called ToString() in your question.
        } finally {
            command.Dispose();
            connection.Dispose();
        }
    }

edited Dec 06 '10 at 01:18

answered Dec 06 '10 at 01:07

Will Marcouiller

23,773
22
96
162

Why both the finally and the using? – Conrad Frix Dec 06 '10 at 01:21
This is due to exception handling which is better to let unhandled exceptions bubble up, otherwise you got something to do with it, it is better code to write `try...finally` than `try...catch`, if you're only going to throw the exception again, or throw a new one, that is, because of the stack trace thing. While writing the `finally` clause anyway, instead of letting it blank, I write the dispose lines, while I know that these objects will be disposed anyway. So, when the day comes that I am not working with `IDisposable`s, I should think of freeing the resources one way or another. – Will Marcouiller Dec 06 '10 at 01:38
If I understand correctly, you like the finally because it makes you "...think of freeing the resources one way or another." "...when the day comes that I am not working with 'IDisposable's" Ok so why bother with the `using` statements then – Conrad Frix Dec 06 '10 at 18:35

score 1 · Answer 2 · answered Dec 06 '10 at 01:01

1

Assuming you're using SQL Server have you tried something like

BasketPage.SelectCommand = 
         new SqlCommand("SELECT BasketID FROM tblBasket WHERE (UserID = '9f96bd94-91b9-4328-8214-4eac179c3a26')",
                      "yourconnectionstring");

answered Dec 06 '10 at 01:01

Conrad Frix

51,984
12
96
155

How can I use ExecuteScalar in this scenario?

2 Answers2