SqlDataReader and Dynamic Formating Read Data

Question

I have a C# app that is reading data from a stored procedure and then creates a message. I am using SqlDataReader to read the information from the database. Previously, I have had to create a new class every time I have a different type of message and it is getting out of control. By different, I mean a different number of columns and/or different types of data each of the columns contain. I am trying to make it easier to add different message types and not sure how to dynamically format the data as I read it. I have tried to format the data after it has been read into my Data# variables, but it its just junk because it gets formatted as the wrong type. So, the only way I currently see it to handle the data when I read into my variables and I can't figure out how.

My format of my message is as follows:

Message
   Subject
   (Text)
     MessageHeader1
     MessageHeader2
     Title
     Header1
     Header2
     Header3
     (Data)
        Data1
        Data2
        Data3

The issue is that the Data# variables could contain different type of information. It could currently contain strings, integers, Dates, Percents, etc. Is there a way to format the data based on another value or some other method so that the different type of data could be formatted correctly?

if (dr.Read())
{
    message.Subject = dr["Subject"].ToString();
    message.MessageText = new AlertMessageText()
    {
        MessageHeader1 = dr["MessageHeader1"].ToString(),
        MessageHeader2 = dr["MessageHeader2"].ToString(),
        Title = dr["Title"].ToString(),
        Header1 = dr["Header1"].ToString(),
        Header2 = dr["Header2"].ToString(),
        Header3 = dr["Header3"].ToString(),
        MessageData = new List<AlertMessageData>(),
    };
}

...

while (dr.Read())
{
    message.MessageText.MessageData.Add(new AlertMessageData()
        {
            Data1 = string.Format("{0:n0}", dr["Data1"]),
            Data2 = string.Format("{0:n0}", dr["Data2"]),
            Data3 = string.Format("{0:MM/dd/yyyy}", dr["Data3"]),    // Formats Data as a Date 
        });
}

Roy Goode · Accepted Answer · 2012-02-02 16:50:59Z

1

To determine the data type of the value and then apply a specific format, you could do this:

while (dr.Read())
{
    var msg = new AlertMessageData();
    for (int i = 1; i < 4; i++)
    {
         var value = dr["Data" + i];
         string format = "{0:n0}";
         if (value is DateTime)
         {
            format = "{0:MM/dd/yyyy}";
         } 
         else if (value is string)
         {
            format = "{0}";
         }      

         var stringValue = string.Format(format, value);
         if (i == 1) msg.Data1 = stringValue;
         if (i == 2) msg.Data2 = stringValue;
         if (i == 3) msg.Data3 = stringValue;
    }
    message.MessageText.MessageData.Add(msg);
}

edited Feb 2, 2012 at 16:50

answered Feb 2, 2012 at 16:25

Roy Goode

3,00023 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ChrisNel52 · Accepted Answer · 2012-02-02 16:19:54Z

1

You can use the IDataReader's GetFieldType() method to find the field's type.

For example, to find the type of your first field it would look like this:

Type t = dr.GetFieldType(0)

If you know the type of your field, you could use one of the IDataReader's "Get()" methods to auto-convert for you. For example, if you knew your first field was a double, you could do this:

double d = dr.GetDouble(0);

answered Feb 2, 2012 at 16:19

ChrisNel52

15.2k3 gold badges33 silver badges38 bronze badges

Collectives™ on Stack Overflow

SqlDataReader and Dynamic Formating Read Data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related