Sqldatareader optional parameter

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

If it's expecting 10 columns then you have to give it 10 columns. "I know 1+1=2 but I want it to =3!" — NathanAldenSr, Nov 24 '13 at 01:43
Nathan obviously you are incapable of thinking outside of your box according to the other responses looks like there are possibilities. — Tim, Nov 24 '13 at 01:49

score 1 · Accepted Answer · answered Nov 24 '13 at 01:44

1

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.

answered Nov 24 '13 at 01:44

shf301

31,086
2
52
86

score 0 · Answer 2 · answered Nov 24 '13 at 01:47

0

What I do is create the query dynamically. I use string builder to concatenate the query, if a value arrive with null or empty then in the code that parameter is not added and in the where the condition is not added either.

answered Nov 24 '13 at 01:47

Juan

1,352
13
20

Christian Phillips · Answer 3 · 2013-11-25T12:17:52.890

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.

Sqldatareader optional parameter

3 Answers3