C#

using(SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();
 
    //
    // Regular query - returns a bit value from a table
    //
 
    string sql = "SELECT BitColumn FROM Table WHERE Name = 'Luis'";
    SqlCommand cmd = new SqlCommand(sql, conn);
    bool result = Convert.ToBoolean(cmd.ExecuteScalar());
 
    //
    // Stored procedure - returns bit value in output parameter and in return value
    //
 
    cmd = new SqlCommand("sp_Test", conn);
    cmd.Parameters.Add("@BoolVal", SqlDbType.Boolean).Direction = ParameterDirection.Output;
    cmd.Parameters.Add("RetVal", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
    cmd.ExecuteNonQuery();

    int result1 = Convert.ToInt32(cmd.Parameters["RetVal"].Value);
    bool result2 = Convert.ToBoolean(cmd.Parameters["@BoolVal"].Value);
}

CREATE PROCEDURE sp_Test (@BoolVal bit OUT) AS
BEGIN
    -- Return in output parameter
    SELECT @BoolVal = BitColumn FROM Table WHERE NAME = 'Luis';
 
    -- Return value of the stored procedure
    IF @BoolVal IS NULL
        RETURN 0
    ELSE
        RETURN 1
END

Yevgeny Efter wrote:

If I decide to write a stored procedure, where do I store it?

Yevgeny Efter wrote:

When you write "cmd = new SqlCommand("sp_Test", conn);", how will SQL Server know what "sp_Test" is?

cmd.CommandTpe = CommandType.StoredProcedure;

Line 25: productsPerPage = Int32.Parse(ConfigurationManager.AppSettings["ProductsPerPage"]);

When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.