Typeof data in GridView/ Listview bound, template and auto-generated fields

Question

Background: I'm populating lots of asp.net c# GridViews and ListViews from a database and subsequently users may export them to Excel. I want export as native Excel (not html). I can't use office automation, and I'm using JET which works fine. I have no control over users' machines.

---

Question: When doing the export, you have to tell Jet what type each field is, in my case "text" (varchar) or "numeric" (double). The difference is that if you export a numeric column, the users can sum the data in Excel, where as strings are exported with a leading apostrophe and so are not much use in arithmetic.

Currently I parse the first data row of the Grid/ListView, check if each value is numeric or text, and assign a type to the column accordingly. That works, except for when I have something in the first column which looks numeric but in fact is a text string. I don't want to parse every row in order to be sure I have the correct data type as some of these exports are quite large.

When I load the Grid/ListView from the database, the database certainly knows what type each field is. So my question is... how do I extract the type of the database item behind a a Grid/ListView item? I could explicitly code it as an attribute on the item, but that's duplicating information I already have, if only I can get to it.

I know that where I have a DataTable then I can get the underlying type from that, but mostly I don't have tables handy, just the Grid/ListView.

-- (edit) Note that Jet will throw if you try to insert an empty string into a nullable numeric column. The way to do this is to omit that column name from the insert statement, or output a zero.

Answer 1

I think what your trying to do is figure out the underlying DataSource of the GridView or ListView after it has been bound?

If so that is no possible since the controls don't really keep track of the DataSource once the has been bound. They are mainly used to fill it's columns, templates, etc and then just those items are maintained.

The only recommendation I can offer is to store the DataSource in another location so that you can access it later and then use it to discover the underlying datatypes used for each column. You could store it in the Session or ViewState if you want but this will add overhead. The other option would be to figure out the datatypes before binding and then storing them in a hidden field with some type of format you can use later to replicate what was being done.

Eg (bad example but gives you an idea) Store in a hidden field the following data:

yourHiddenField.Text = "0,1|1,1|2,0|3,1|etc...";
// format "columnindex,fieldtype(0=double,1=string)" with a pipe delimiter.

Not pretty but illustrates the basic idea I am thinking of.

Answer 2

As far as I can tell the answer is "you can't". My data sources return only strings, so you can't usefully parse any auto-created DataTable for column types.

---

What I did was...

Most column types I can "sniff" from looking at the 1st row of the grid/list. This fails only if 1st column data is null (which tells you nothing), or if the 1st column implies numeric but the column data is for example alpha-numeric.

So for those columns where I know there's a problem, or where testing shows there to be so, I annotate those specific Grid or ListView items with a custom attribute which forces the items to be exported as text or numeric. I default to text, so in practice I mostly just have to annotate those entries I specifically want to render as numeric. Exceptions are some ID numbers which start with leading zeros, which I want to render that way hence go explicitly as text.

Where I have a DataTable (eg for charts), and I can't do the sniffing trick (see above), or the annotation trick (ditto), then I pass a map of the column types to my export code.

In summary, in some cases I am forced to repeat the column type definition either in the Grid/ListView template fields, or in the field-map. Can't win them all. It does work.