DataSet problem

[majkinetor]

Hi to all

I am loading excell file using ODBC connection. The excell file contains number of tables (sheets) that should be loaded to dataset. To connect to excell file using MS ODBC driver I set connection string so ODBC returns strings for eatch cell, no matter acctual type in excel file. This will also return column names in first row.

So, I need to load eatch sheet into DataTable, set the correct column names using data in first row and delete this first row after. After that table is added to dataset.

I am using this code to achive this:

OleDbConnection	 conn = new OleDbConnection( XLS_GetExcellConnectionString(xlsFile) );
OleDbCommand	 cmnd = new OleDbCommand("", conn);
DbDataAdapterEx  adapter = new DbDataAdapterEx() ;
OleDbDataReader	 reader;

conn.Open();
xlsDS = new DataSet();
foreach (string tableName in XLS_GetExcelSheetNames(xlsFile))
{
	DataTable dt = new DataTable( tableName );
	
	//read entire table
	cmnd.CommandText = String.Format("SELECT * FROM [{0}]", tableName);

	reader = cmnd.ExecuteReader();
	adapter.FillFromReader ( dt, reader ) ;
	reader.Close();

	//set column names
	foreach (DataColumn col in dt.Columns)
	{
		col.ColumnName = dt.Rows[0][col.Caption].ToString();
		col.Caption	   = col.ColumnName;
	}
	dt.Rows[0].Delete();   //  <---

	xlsDS.Tables.Add(dt);
}
conn.Close();

This is the problem I have:

I am using XMLVisualizer plugin to check the tables and detaset in debug mode. When I check dt on point marked with <--- it shows me that table is correctly set. But, for some reason, I can't view xlsDS dataset. I tried after this operation expression:

string t = xlsDS.Tables[0].Rows[0][0].ToString();

but this one complians about accessing deleted datatable data. XMLVisualizer itself returns some error when trying to view this dataset. So I commented the part of above code when first row is deleted but that didn't help. When I comment part that change column names the error doesn't apper. So, it seems that there is some side effect of changing the colum names. Don't know why this has to do with DataSet as I add tables to it after all processing is done and viewing datatable alone shows it is set correctly.


Any help or tip would be highly appreciated.

Thx.

 

[MadJock]

Hi,

Untested, but pretty sure this is your problem. Delete() does not actually remove the row, it keeps it in a deleted state in case you wanted to use the dataset to update a database.

Try this instead of your delete call:

DataRow row = dt.Rows[0];
dt.Rows.Remove[row];

Hope this helps,

Graeme

"Just beacuse you're paranoid, don't mean they're not after you

 

[majkinetor]

Thank you mad.

I was lurking the MSDN and found the same so I changed line to:

dt.Rows.Remove( dt.Rows[0] );

This helps to access 0,0 element later.
It doesn't help to see dataSet after, using XML Visualizer.

It appears to be a bug in this plugin, maybe somebody using it can confirm that...

 

[MadJock]

Never tried using XmlVisualiser to be honest. I usually set a break point then use 'mydataSet.WriteXml(@"c:\tmp.xml")' from the immediate window. Visual Studio should display the resulting XML quite well...

"Just beacuse you're paranoid, don't mean they're not after you

 

[majkinetor]

You should try XML Visualizer then. Superb stuff.
In debug mode you just select dataset, datatable, array or whatever and right click for XML V. It will show you the greed for the data you selected in XML form. You will also be able to access the schema, filter results etc...

Very useful stuff, can not live without it when DB programming is in question...

 

[majkinetor]

One thing can be done though to set your method behaves much better then XMLVIZ:

You can create little script that monitor changes to xml output u are using and display new results in window aside of Visual Studio. That way, you don't have to select data eatch time, but as soon as WriteXML is processed, script will fetch new data and display it (for instance in your xml viewer).

The best way to create such script is probably AutoHotKey automatition language, but any other solution will sufice, like plain batch, vbs etc...

 

[majkinetor]

I determined what was the problem

This is most defenetily bug in XMLVIZ.
The problem is $ symbol witch Excell adds at the end of table names. For instance if you have Sheet Customers, ODBC will return it as Custmers$.


The solution is to use this code:

DataTable dt = new DataTable( tableName.Replace("$", "") );

after witch XMLVIz works correctly.