Sqldatareader optional parameter

ado.net asp.net c# dapper sqldatareader

Question

I have a datareader that is getting results from my stored procedure. The stored procedure depending on certain values such as ("ismarried" = true) returns 10 coulmns but if ("ismarried" = false) it returns only 5 columns.

In my asp.net page my datareader is expecting 10 columns no matter what and wanted to know if there was a way in my asp.net c# code to have optional parameters. I do know you can use ISNULL("Column", '') in SQL but instead of doing that i was hoping there was a way to maybe tell my datareader that these 5 parameters might not always exist.

Thanks

Accepted Answer

You can tell how many columns that the stored procedure returned by using the FieldCount property. If it returns 5 or 10 your code can react accordingly.


Popular Answer

Instead of checking the columns returned and then mapping based of the count of fields etc, a cleaner solution would be to create a class, let's say Person like so...

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsMarried { get; set; }
    //etc...
}

Then, you can use dapper to return your result...

var people = cnn.Query<Person>("spName", 
                     commandType: CommandType.StoredProcedure).ToList();

dapper will map the fields to your class property, and ignore any missing fields. Be aware that the property names will need to match the field names from the database. This will cut down on any logic check and having to map each property by hand to the field returned.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why