Trying to identify money columns in gridview 1

[3587ch]

My columns are auto generated for my grid, they are not always the same columns each time it is rendered. I need to check and apply currency formatting if the column is money. This sample works to apply formatting, but it does not specifically check for money.

Does anyone have an idea how I could reliably check for money columns?


Protected Sub gvDataDisplay_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)

Select Case e.Row.RowType
Case DataControlRowType.DataRow
Dim d As Double = 0.0R

For z As Integer = 0 To e.Row.Cells.Count - 1
Try
d = Convert.ToDouble(e.Row.Cells(z).Text)
If e.Row.Cells(z).Text > 0 Then
e.Row.Cells(z).Text = [String].Format("{0:C2}", d)
Else
e.Row.Cells(z).Text = e.Row.Cells(z).Text
End If
Catch generatedExceptionName As Exception
'e.Row.Cells(z).Text = "0"
End Try
Next
Exit Select
End Select
End Sub

 

[jmeckley]

since you are using auto-generated columns why not format the data before binding. you could map the current underlying datasource to another datatable where every column is strings. put the formatted data into each column and then bind. here is some pseudo code

var data = GetAcutalData();
var forBinding = new Mapper().MapFrom(data);
GridView1.DataSource = forBinding;
DataBind();

GetAcutalData() will query the database.
Mapper would look something like this

class Mapper
{
    private IColumnFormatter[] columnFormatters = new[]
          {
             //list of formatters... order is important!
          };
    public DataTable MapFrom(DataTable input)
    {
        var result = ConstructUsing(input);
        Populate(input, result);
        return result;
    }

    private DataTable ConstructUsing(DataTable from)
    {
         var table = new DataTable();
         foreach(var column in from.Columns)
         {
             table.Columns.Add(column.ColumnName, typeof(string));
         }
         return table;
    }

    private void Populate(DataTable source, DataTable destination)
    {
       foreach(var row in source.Rows)
       {
          var newRow = destination.NewRow();
          ConvertData(row, source.Columns, newRow);
          destination.Rows.Add(newRow);
       }
    }

    private void ConvertData(DataRow source, DataColumnCollection columns, DataRow destination)
    {
       foreach(var column in columns)
       {
           var original = source[column];
           var formatted = columnFormatters
                 .First(f=>f.CanFormat(row, column, source.Columns))
                 .Format(original);
           destination[column.ColumnOrdinal] = formatted;
       }
    }
}

interface IColumnFormatter
{
    bool CanFormat(DataRow row, DataColumn column, DataColumnCollection columns);
    string Format(object value);
}

now you can create individual implementations IColumnFormatter, one for each data type. here are a few quick examples

//this is the default formatter
class StringColumnFormatter : IColumnFormatter
{
   public bool CanFormat(DataRow row, DataColumnCollection columns)
   {
       return true;
   }
   public string Format(object value)
   {
       return value;
   }
}

class NullColumnFormatter : IColumnFormatter
{
   public bool CanFormat(DataRow row, DataColumn current, DataColumnCollection columns)
   {
       return row[column] == null || row[column] == DbNull.Value;
   }
   public string Format(object value)
   {
       return "";
   }
}

class NullDateColumnFormatter : IColumnFormatter
{
   public bool CanFormat(DataRow row, DataColumn current, DataColumnCollection columns)
   {
       if (Column.ColumnType != typeof(DateTime)) return false;
       return DateTime.MinValue.Equals(row[column]);
   }
   public string Format(object value)
   {
       return ""; //display empty string for a minimum date.
   }
}

class DateColumnFormatter : IColumnFormatter
{
   public bool CanFormat(DataRow row, DataColumn current, DataColumnCollection columns)
   {
       return Column.ColumnType == typeof(DateTime);
   }
   public string Format(object value)
   {
       return Convert.ToDateTime(value).ToString("MM-dd-yy");
   }
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[3587ch]

Jason,

I'm not sure I get what yor telling me, yet. Here is what I'm doing now to bind the gridview. Basically I get an SQL statement from a query tool and then put it in a datareader and bind it to the gridview. I have never done the mapping you have suggested.

Sub BindData()

Dim MySQL As String = Session("strSQL")

If String.IsNullOrEmpty(MySQL) Then
Me.Message.Text = "No Data."
Else
Dim MyConn As New SqlConnection(ConnectStr)
Dim objDR As SqlDataReader
Dim Cmd As New SqlCommand(MySQL.Remove(0, 10), MyConn)
Cmd.CommandType = CommandType.Text

Try
MyConn.Open()
objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
gvDataDisplay.DataSource = objDR
gvDataDisplay.DataBind()
Catch ex As Exception
Response.Write(ex.Message)
End Try
End If
End Sub

 

[jmeckley]

so instead of binding directly to the reader, you would bind to the result of the mapped datatable.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[jbenson001]

Format the data in SQL and then bind to the grid. This way you are done, quick and simple.