how to iterate through reader for notnull without repeating code over and over

Question

First off, I am new to programming (especially with C#) and thanks for your help.

I have a static web form with about 150 form objects (most checkboxes). I decided to go 1 record per form submission in the sql db. So, for example, question X has a choice of 5 checkboxes. Each of these 5 checkboxes has a column in the db.

I have the post page complete(working) and am building an edit page where I load the record and then populate the form.

How I am doing this is by passing a stored proc the id and then putting all the returned column values into the according object properties, then setting the asp control object to them.

An example of setting the asp controls to the selected value:

questionX.Items[0].Selected = selectedForm.questionX0
questionX.Items[1].Selected = selectedForm.questionX1
questionX.Items[2].Selected = selectedForm.questionX2

As you see, this is very tiresome since there are over 150 of these to do. Also, I just found out if the response is NULL then I get the error that it cant be converted to a string. So, I have added this line of code to get past it:

This is the part where I am populating the returned column values into the object properties (entity is the object):

if (!String.IsNullOrEmpty((string)reader["questionX0"].ToString()))
{entity.patientUnderMdTreatment = (string)reader["questionX0"];}

So, instead of having to add this if then statement 150+ times. There must be a way to do this more efficiently.

Answer 1

I recommend using the NullableDataReader. It eliminates the issue.

Answer 2

this is a quick & easy way of doing it.. there are some suggestions to investigate LINQ, and I'd go with those first.

for (int i = 0; i < 150; i++)
{
    if (!String.IsNullOrEmpty((string)reader["questionX" + i.ToString()].ToString()))
    {entity.patientUnderMdTreatment = (string)reader["questionX" + i.ToString()];}

}

... though this wouldn't be any good with the

questionX.Items[0].Selected = selectedForm.questionX0
questionX.Items[1].Selected = selectedForm.questionX1
questionX.Items[2].Selected = selectedForm.questionX2

lines

Answer 3

First of all, it seems that you are using string.IsNullOrEmpty(value), but this won’t check for the special DBNull value that is returned from databases when the data is null. You should use something more akin to value is DBNull.

The rest of your problem sounds complex, so please don’t be put off if my answer is complex too. Personally I would use custom attributes:

Declare a custom attribute

The following is a skeleton to give you the idea. You may want to use the “Attribute” code snippet in Visual Studio to find out more about how to declare these.

[AttributeUsage(AttributeTargets.Field, AllowMultiple = false)]
public sealed class QuestionColumnAttribute : Attribute
{
    public string ColumnName { get; private set; }
    public QuestionColumnAttribute(string columnName)
    {
        ColumnName = columnName;
    }
}

Use the custom attribute in the entity class

Where you declare your entity class, add this custom attribute to every field, for example where patientUnderMdTreatment is declared:

[QuestionColumn("questionX0")]
public string patientUnderMdTreatment;

Iterate over the fields

Instead of iterating over the columns in the reader, iterate over the fields. For each field that has a QuestionColumnAttribute on it, get the relevant column from the reader:

foreach (var field in entity.GetType().GetFields())
{
    var attributes = field.GetCustomAttributes(typeof(QuestionColumnAttribute), true);
    if (attributes.Length == 0)
        continue;

    object value = reader[attributes[0].ColumnName];
    if (!(value is DBNull))
        field.SetValue(entity, value.ToString());
}

For the first part of your question where you set the ASP controls, you can use a similar strategy iterating over the fields of selectedForm, and this is probably simpler because you don’t need a custom attribute — just take only the fields whose name starts with “questionX”.

Answer 4

so I hear two questions:
- how to deal with null coming from IDataReader?
- how to deal with multiple fields?

Lets start with simple one. Define yourself a helper method:

public static T IsDbNull<T>(object value, T defaultValue)
{
    return (T)(value is DBNull ? defaultValue : value);
}

then use it:

entity.patientUnderMdTreatment = IsDbNull<string>(reader["question"], null);

Now how to map entity fields to the form? Well that really is up to you. You can either hardcode it or use reflection. The difference of runtime mapping vs compile-time is likely to be completely irrelevant for your case.
It helps if your form fields have identical names to ones in the DB, so you don't have to do name mapping on top of that (as in Timwi's post), but in the end you'll likely find out that you have to do validation/normalization on many of them anyway at which point hardcoding is really what you need, since there isn't a way to dynamically generate logic according to the changing spec. It doesn't matter if you'll have to rename 150 db fields or attach 150 attributes - in the end it is always a O(n) solution where n is number of fields.

I am still a little unsure why do you need to read data back. If you need to preserve user's input on form reload (due to validation error?) wouldn't it be easier/better to reload them from the request? Also are entity and selectedForm the same object type? I assume its not a db entity (otherwise why use reader at all?).

Its possible that there are some shortcuts you may take, but I am having hard time following what are you reading and writing and when.