Efficient way of accessing a Boolean from SQL Server
-
Hi, I don't know if this was more suited to the database message board or not but I think my question is more to do with my code rather than the database. I have a basic table with a few columns. One of them is a Bit type and is effectively a flag. All I want to do is get the flag value for a given ID. I've tried using an output parameter in a stored procedure but I don't know the best way of retrieving the flag value and converting it to a Boolean in code. I don't think a SqlDataReader is ideal because it's only one value I want to return.
ALTER PROCEDURE SP_UserValid
@ID INT,
@Valid BIT OUTPUT
AS
SET NOCOUNT ON;
SET @Valid = (SELECT [Valid] FROM tUsers WHERE ID = @ID)Here's my code:
SqlConnection connection = new SqlConnection(_ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_UserValid";
cmd.Parameters.Add(new SqlParameter("@ID", userID));
cmd.Parameters.Add(new SqlParameter("@Valid", bValid));
cmd.Parameters["@Valid"].SqlDbType = SqlDbType.Bit;
cmd.Parameters["@Valid"].Direction = ParameterDirection.InputOutput;connection.Open();
cmd.ExecuteNonQuery();// How do I get the "Valid" as a Boolean value?
cmd.Connection.Close()
Thanks for looking :)
-
Hi, I don't know if this was more suited to the database message board or not but I think my question is more to do with my code rather than the database. I have a basic table with a few columns. One of them is a Bit type and is effectively a flag. All I want to do is get the flag value for a given ID. I've tried using an output parameter in a stored procedure but I don't know the best way of retrieving the flag value and converting it to a Boolean in code. I don't think a SqlDataReader is ideal because it's only one value I want to return.
ALTER PROCEDURE SP_UserValid
@ID INT,
@Valid BIT OUTPUT
AS
SET NOCOUNT ON;
SET @Valid = (SELECT [Valid] FROM tUsers WHERE ID = @ID)Here's my code:
SqlConnection connection = new SqlConnection(_ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_UserValid";
cmd.Parameters.Add(new SqlParameter("@ID", userID));
cmd.Parameters.Add(new SqlParameter("@Valid", bValid));
cmd.Parameters["@Valid"].SqlDbType = SqlDbType.Bit;
cmd.Parameters["@Valid"].Direction = ParameterDirection.InputOutput;connection.Open();
cmd.ExecuteNonQuery();// How do I get the "Valid" as a Boolean value?
cmd.Connection.Close()
Thanks for looking :)
bool isValid = Convert.ToBoolean(cmd.Parameters["@Valid"].Value)
only two letters away from being an asset
-
Hi, I don't know if this was more suited to the database message board or not but I think my question is more to do with my code rather than the database. I have a basic table with a few columns. One of them is a Bit type and is effectively a flag. All I want to do is get the flag value for a given ID. I've tried using an output parameter in a stored procedure but I don't know the best way of retrieving the flag value and converting it to a Boolean in code. I don't think a SqlDataReader is ideal because it's only one value I want to return.
ALTER PROCEDURE SP_UserValid
@ID INT,
@Valid BIT OUTPUT
AS
SET NOCOUNT ON;
SET @Valid = (SELECT [Valid] FROM tUsers WHERE ID = @ID)Here's my code:
SqlConnection connection = new SqlConnection(_ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_UserValid";
cmd.Parameters.Add(new SqlParameter("@ID", userID));
cmd.Parameters.Add(new SqlParameter("@Valid", bValid));
cmd.Parameters["@Valid"].SqlDbType = SqlDbType.Bit;
cmd.Parameters["@Valid"].Direction = ParameterDirection.InputOutput;connection.Open();
cmd.ExecuteNonQuery();// How do I get the "Valid" as a Boolean value?
cmd.Connection.Close()
Thanks for looking :)
You could also use:
bool isValid = Convert.ToBoolean(SqlCommand.ExecuteScalar());I'd probably have usedselect @Valid = [Valid] FROM tUsers WHERE ID=@IDBut what you have is fine. Lastly, you may want to use ParameterDirection.Output for the @Valid parameter since there isn't a value being passed in... -
You could also use:
bool isValid = Convert.ToBoolean(SqlCommand.ExecuteScalar());I'd probably have usedselect @Valid = [Valid] FROM tUsers WHERE ID=@IDBut what you have is fine. Lastly, you may want to use ParameterDirection.Output for the @Valid parameter since there isn't a value being passed in...Thanks to both you guys :) I ended up making my SP simpler:
...
SELECT [Valid] FROM tUsers WHERE ID = @ID
...I then used
ExecuteScalar()and it works great! One question though, what if the value returned from the DB isNULL? I've set the column to disallowNULLvalues but I thought it should probably have something in the code too, just in case :) Lastly, is there a best practice for this sort of thing in terms of performance? -
Thanks to both you guys :) I ended up making my SP simpler:
...
SELECT [Valid] FROM tUsers WHERE ID = @ID
...I then used
ExecuteScalar()and it works great! One question though, what if the value returned from the DB isNULL? I've set the column to disallowNULLvalues but I thought it should probably have something in the code too, just in case :) Lastly, is there a best practice for this sort of thing in terms of performance?If the column is a bit data type then it will always return 0/1 never a null. If the col is an int (I do this) then put a default on the table def of 0 and you will never get null returned
Never underestimate the power of human stupidity RAH
-
If the column is a bit data type then it will always return 0/1 never a null. If the col is an int (I do this) then put a default on the table def of 0 and you will never get null returned
Never underestimate the power of human stupidity RAH
Hi Mycroft, "If the column is a bit data type then it will always return 0/1 never a null" => This is a false statement. I tried using the following query and in all three executions the IsActive field is null. DECLARE @tb TABLE(UID INT ,IsActive BIT) INSERT INTO @tb (UID) SELECT 1 SELECT * FROM @tb DECLARE @tb TABLE(UID INT ,IsActive BIT) INSERT INTO #Temp (UID) SELECT 1 SELECT * FROM #Temp DROP TABLE #Temp DECLARE @tb TABLE(UID INT ,IsActive BIT) INSERT INTO Temp (UID) SELECT 1 SELECT * FROM Temp DROP TABLE Temp please correct me if i am wrong. Mouli.
-
Hi Mycroft, "If the column is a bit data type then it will always return 0/1 never a null" => This is a false statement. I tried using the following query and in all three executions the IsActive field is null. DECLARE @tb TABLE(UID INT ,IsActive BIT) INSERT INTO @tb (UID) SELECT 1 SELECT * FROM @tb DECLARE @tb TABLE(UID INT ,IsActive BIT) INSERT INTO #Temp (UID) SELECT 1 SELECT * FROM #Temp DROP TABLE #Temp DECLARE @tb TABLE(UID INT ,IsActive BIT) INSERT INTO Temp (UID) SELECT 1 SELECT * FROM Temp DROP TABLE Temp please correct me if i am wrong. Mouli.
Ah you are right, either I made a dramatic mistake or this may have changed at some time. There used to be a time when bool/bit could not be null. Still stick a contraint on the table forcing it to 0, that'll fix the issue and will save you writing code in the BL to check for null.
Never underestimate the power of human stupidity RAH