how to get bool value from c# stored procedure

Question

I have used this code:
Stored procedure in SQL:

Alter PROCEDURE [dbo].[Get_Customize_Status_By_OrderId] 
@Order_id varchar(50)

AS
    BEGIN
        select Customize_Status from dbo.ClientOrder where Order_id=@Order_id
    END
GO

Calling that stored procedure in c#:

public static bool getcustomizestatusbyorderid(string orderid)
{
    bool result = false;
    try
    {
        Database db = new SqlDatabase(connectionstring);
        DbCommand cmd1 = db.GetStoredProcCommand("[dbo].[Get_Customize_Status_By_OrderId]");
        db.AddInParameter(cmd1, "@Order_id",DbType.String,orderid);
        int RowAffected = db.ExecuteScalar(cmd1);
    }
    catch (Exception ex)
    {

    }
}

Please tell me how to get customize_status value in c#?

What is the type for Customize_status? and what error you are getting — Raghuveer, Aug 13 '14 at 12:49
Assuming that is ent-lib (yeuch), `ExecuteScalar` returns `object`, not `int` - so the code shown won't actually compile. Is that the real code? Also: *why ent-lib?* — Marc Gravell, Aug 13 '14 at 12:51

score 1 · Answer 1 · answered Aug 13 '14 at 12:54

1

ExecuteScalar returns the first cell selected, so in your example that will be what is returned; all you should need is to cast from object (which is what ExecuteScalar returns) to int:

int status = (int)db.ExecuteScalar(cmd1);

(assuming that Customize_Status is an int, etc)

Note that you might want to check for null which would result from zero rows:

object result = db.ExecuteScalar(cmd1);
if(result == null) {
    // no rows
} else if (result is DBNull) {
    // at least one row; first cell was a database null
} else {
    // at least one row; first cell was non-null
    int status = (int)result;
    // ...
}

"rows affected" makes me think you are confused with ExecuteNonQuery, where frankly the return value is rarely meaningful anyway.

Also: ent-lib? really? why?

answered Aug 13 '14 at 12:54

Marc Gravell

1,026,079
266
2,566
2,900

I have always used `== DBNull.Value` and had never seen the `is DBNull` syntax before so I did some searching and found this here on SO: http://stackoverflow.com/questions/221582/most-efficient-way-to-check-for-dbnull-and-then-assign-to-a-variable . Seems that both work the same but `== DBNull.Value` is slightly more efficient. Either way, +1. – Solomon Rutzky Aug 13 '14 at 15:15
@srutzky it is actually possible to make the == option report incorrectly - if you are evil enough :p (and yes, I am that evil) – Marc Gravell Aug 13 '14 at 17:05
How? By overloading the `==` operator or something like that? Is this is practical concern, if people rarely ever do this (or whatever method you are speaking of)? Just curious. Oh, and if not obvious from answering the "how", then also: why do that? – Solomon Rutzky Aug 13 '14 at 18:05
@srutzky `Activator.CreateInstance(typeof(DBNull), true);` - as for "why": typically an accidental result from serialization libraries or materialization libraries, that don't specifically care about types, but just create instances from hidden constructors. – Marc Gravell Aug 13 '14 at 19:32

Pankaj · Answer 2 · 2014-08-13T12:59:47.497

0

You can do it in this way

bool exists = (int)db.ExecuteScalar(cmd1) == 1;
return exists;

edited Aug 13 '14 at 12:59

answered Aug 13 '14 at 12:45

Pankaj

2,618
3
25
47

Since the method invoked is `ExecuteScalar`, not `ExecuteNonQuery`, it will return the first cell selected; *not* the rows affected; comparing this to 0/1 to talk about the number of rows is to misunderstand what the value is that got returned. – Marc Gravell Aug 13 '14 at 12:57
if the customize_status field value is NULL what i have to do – user2617364 Aug 13 '14 at 12:58
@user2617364 I've updated my answer to cover the 2 different null scenarios – Marc Gravell Aug 13 '14 at 12:59
1

@Pankaj but you are still checking for entirely the wrong thing; `ExecuteScalar` **returns the first cell**, not the number of rows; this would only work if every status was `1` – Marc Gravell Aug 13 '14 at 13:01

score 0 · Answer 3 · answered Aug 13 '14 at 12:48

0

Try this

bool customize_status = (RowAffected > 0);

If records found, it will return true

answered Aug 13 '14 at 12:48

Jesuraja

3,774
4
24
48

Since the method invoked is `ExecuteScalar`, not `ExecuteNonQuery`, it will return the first cell selected; *not* the rows affected; comparing this to 0/1 to talk about the number of rows is to misunderstand what the value is that got returned. – Marc Gravell Aug 13 '14 at 12:57
@Marc Gravell Yes, you are right... And also we dont know what is the value return from the `Customize_Status`.... Without the value we cannot make the condition... – Jesuraja Aug 13 '14 at 13:17

score 0 · Answer 4 · answered Aug 13 '14 at 14:14

To return Boolean values to a C# front end in a way that they can be understood I do this:

SELECT CASE WHEN Customize_Status = 0 THEN 'false' ELSE 'true' END AS [Customize_Status]

Then, regardless of how you get hold of the data in the front end, you can write (let's pretend you returned it as a value in a datarow of a datareader) ...

bool Customize_Status = Convert.ToBoolean(dr["Customize_Status"].ToString());

That is the only way I have ever found to get bools out of SQL Server database and into a C# front end so it can be assigned to a bool.

how to get bool value from c# stored procedure

4 Answers4