Writing an OLE-DB data provider in C#

I am investigating the difficultly in writing an OLE-DB data provider in C#.

Here is a link to the simple OLE-DB provider.

Here is an article on  wsriting custom .net data providers.

I need to start this discussion with a justification of why I would want to do such a thing.

Of late I have been working on a very pure object-oriented project with a design goal of eliminating duplication.

We have a MVC architecture that works fine with all of the business logic being in the Model component.

The problem comes when we get to reporting. We have a requirement to use an external reporting tool. This is the cause of the duplication. As is typical in reporting tools it is oftern easier to code the report as a view than to put too much detail into the reports (this reduces duplication between similar reports).

However this has resulted in calculated fields being defined in both the views and the model. We can always write unit tests to ensure that database calculations matches the model calculations. This results in us loading from views and saving to tables which is far from ideal.

A better approach (which I have used before in a limited form) would be to have the reports run against our model. In order to do this we need to expose our model components as an OLE-DB provider.

Previously I have placed a hook layer between the reporting tool and the database so that I can extend sql with custom commands. This allows the model code (and various common formating objects) to be used from the reports.

This has the benefit of when you change the format of your custom object it is automatically consistent in all of the reports. This is a major time saver on a large project.

VS2005 and version control

VS2005 oddity.

When configuring VS2005 solutions under version control you should not check in the binary suo file.
This however is the file that contains the memory of which is the default project.
The only way around this I have found was to move the project that you want to be the default to the top (well the first non directory) of the sln file (In the Project bit). You also need to move the project up the postSolution section to the top of the list.

There does not appear to be any way to control this in the IDE that is not recorded in the suo file.

This is not documented anywhere I can find.

It appears that the default project is the first project added to the solution. 

Wierd Excel Behaviour

The following is the explanation of a very odd excel behaviour: 


The problem comes when using sql server stored procedures to read data in from an Excel spreadsheet.

The symptom is that Excel will guess the datatype of the columns. If it guesses wrong you get null instead of the data.

I have a general rule of thumb about Excel: if you let it guess it will almost always guess wrong.

Why could microsoft not have added extra connection parameters that allow the caller to specify the data types?

This would not break existing code and would save a lot of heartache.

I have found a workaround:

Export the sheet to tsv and then reimport.

This looses all of the formatting that is getting in the way. 

SQL Server 2000 Documentation Bug

I have found out this is how sql servers internal functions work.

This is one of very few places that I have found books online to be wrong.

Books online can be infurating in that even a search for the exact term required will return 50 articles above the one you actually need! 

SQL-92 niladic function Value returned 
CURRENT_TIMESTAMP       Current date and time. 
CURRENT_USER    Name of user performing insert.
SESSION_USER    Name of user performing insert.
SYSTEM_USER     Name of user performing insert.
USER    Name of user performing insert.
Books online is wrong here

Using Openrowset to query csv and tsv files

The following will read a csv file. 

SELECT * FROM OPENROWSET (‘MSDASQL’, ‘Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:data;’, ‘SELECT * from test.csv’);

The following will read a tsv file (with a txt extension).

SELECT * FROM OPENROWSET (‘MSDASQL’, ‘Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:data;’, ‘SELECT * from test.txt’);

Edgy Eft Ubuntu

Previously I have posted upgrade instructions each time a new release of Ubuntu comes out.

This time due to the problems in the Dapper -> Edgy upgrade I would recommend a clean install.

Over the next few days I will be posting some of the extra configuration details that I needed to get my Linux server back to life. This will include details of mounting a hard drive and configuring Samba to share it with the windows network.