Dynamic assignment of column values in each row

Question

In the following code, I have manually assigned the values of each column to variables. Is there a shorter code that can do this for me without having to type each column's name, and manually assigning them to individual variables?

while (rdr.Read())
        {
            var book = new BookViewModel();
            book.ship_last_name = rdr["ship_last_name"].ToString();
            book.ship_first_name = rdr["ship_first_name"].ToString();
            book.ship_zip = rdr["ship_zip"].ToString();
            book.ship_state = rdr["ship_state"].ToString();
            book.ship_address = rdr["ship_address"].ToString();
            book.ship_city = rdr["ship_city"].ToString();
            book.day_phone = rdr["day_phone"].ToString();
            book.email_address = rdr["email_address"].ToString();

            model.Add(book);
        }

Also, if possible, can I add if statements in there to check if that particular column in a given row is null or not? Not just for null, but any other conditions.

Thanks in advance. :)

score 1 · Answer 1 · answered Feb 10 '16 at 20:22

1

A little bit shorter code:

while (rdr.Read())
    {
        var book = new BookViewModel {
        ship_last_name = rdr["ship_last_name"].ToString(),
        ship_first_name = rdr["ship_first_name"].ToString(),
        ship_zip = rdr["ship_zip"].ToString(),
        ship_state = rdr["ship_state"].ToString(),
        ship_address = rdr["ship_address"].ToString(),
        ship_city = rdr["ship_city"].ToString(),
        day_phone = rdr["day_phone"].ToString(),
        email_address = rdr["email_address"].ToString() }

        model.Add(book);
    }

Also, I think you might want to use some sort of ORM (like NHibernate) to map this. (Not really sure how this code works, or what DB you're using, etc.) To check if a column is null, I suppose:

if(rdr["column_name"] != null)

or something similar.

answered Feb 10 '16 at 20:22

Miclebrick

31
6

Is LINQ to SQL* an ORM? if so, could you please help me show how to use it? – Bivo Kasaju Feb 10 '16 at 20:28
ORM is Object Relation Mapping (IIRC), and it's used to map objects/classes to SQL automatically. I'm not sure about what "LINQ to SQL" is, but I'd recommend using NHibernate with Fluent. You can install it via nuget in VS (just search fluentnhibernate and install it), and here is a link to their documentation: https://github.com/jagregory/fluent-nhibernate/wiki/Getting-started – Miclebrick Feb 11 '16 at 01:45
You're welcome. If this answer helped, please mark it as the answer. – Miclebrick Feb 12 '16 at 19:55

score 0 · Answer 2 · edited May 23 '17 at 12:31

You can use the column index instead of the Column name, but I don't think that is what you want to do. Using the column names is great for situations in which the table schema might change in some way. With column index you need to have the same order that is being returned with whatever query you are reading.

while (rdr.Read())
{
    var book = new BookViewModel {
    ship_last_name = rdr.GetString(0),
    ship_first_name = rdr.GetString(1),
    ship_zip = rdr.GetString(2),
    ship_state = rdr.GetString(3),
    ship_address = rdr.GetString(4),
    ship_city = rdr.GetString(5),
    day_phone = rdr.GetString(6),
    email_address = rdr.GetString(7) }

    model.Add(book);
}

The above will work, but you may run into trouble with the different way of converting to string or the column indexes. You can use something like the previous answer stated along with some dbnull checking if you are concerned about empty data on some of these columns.

ship_first_name = rdr.IsDBNull(1) ? "empty" : rdr.GetString(1),
ship_zip = rdr.IsDBNull(2) ? "empty" : rdr.GetString(2),
ship_state = rdr.IsDBNull(3) ? "empty" : rdr.GetString(4),

Using one of these conditional operator expressions above. Here is a good break down of conditional operator expressions: Question mark and colon mean in statement? what does it mean?

I don't know if that answers you whole question. I guess it is all up to you on how you would like to format things. Tools like the conditional operator expression are great for smart logic that doesn't take up a million lines of code.

I am aware of conditional operator expression, but learned a new thing about the column index. It might be hectic for large tables but that's definitely a shorter code. Doesn't answer my dynamic assignment of column values, but that's a great deal of help too.. Thanks.. :) — Bivo Kasaju, Feb 12 '16 at 01:44
I was hoping more of a loop kinda thing for each field though — Bivo Kasaju, Feb 12 '16 at 01:45
Its my understanding that your method of using the column names is the dynamic way of assigning reader columns. You could assign class variables that map to each of those column names elsewhere if you are concerned about code length, but in the end, I think you are right on track with your original code. It's a good conversation to have though. — MUlferts, Feb 12 '16 at 13:15
Was your question answered? You should mark one of the responses as the answer or ask for more clarification if so. — MUlferts, Feb 15 '16 at 19:57
No my question wasn't answered, but I guess I'll just go on without it. — Bivo Kasaju, Feb 17 '16 at 13:26

Dynamic assignment of column values in each row

2 Answers2