Cracking the DDEX nut
Ok, so I've gone and written a handy-dandy ADO.NET 2.0 provider for SQLite and decided to look into the DDEX side of things just for completeness.
Just to get something on the screen, I followed the chapter entitled "Registering a Non-Package-Based DDEX Provider" and am having trouble getting it to show up.Under theSupportedObjects subkey for my guid, I've added the keys:
DataConnectionProperties
DataConnectionSupport
DataSourceInformation
According to the documentation, "You must provide at minimum three keys for the default objects supported by all providers that support ADO.NET" and there they are.
I also entered the appropriate keys in theDataSources subkey as well.
When I clickAdd New Data Source followed byNew Connection, my provider doesn't show up.
I then added underSupportedObjects, aDataViewSupport key and left the value empty.If that key is present in the registry, my provider shows up.
When I select my provider and press theContinue button, the only visible property isConnectionString in the dialog. I can enter one in andTest Connection, and it works ... but when I hit the OK button, I get an error dialog:
Failed to find or load the registered .Net Framework Data Provider.
Any ideas what I'm doing wrong?
In case you want to have a look at the provider, its at:
http://sourceforge.net/projects/sqlite-dotnet2
Robert Simpson
Programmer at Large
Robert,
You will need to add DataViewSupport and DataObjectSupport keys in the registry (under SupportObjects). For DataViewSupport and DataObjectSupport, there are 2 string values that point to your provider specific xml file.
Default is the xml file name and Path is the full path name where the xml file resides. These are string values for each key.
The DDEX SDK has some discussion in it, and it also has 4 samples that are very useful. I used their sample XML files and modified them to fit my needs.
First off, here's a couple things I've discovered on the way to implementing this designer:
1. The query designer uses ODBC escape sequences in JOIN operations when you try and design a query using multiple tables. The only way for it NOT to do this is to actually have a DataSourceInformation derived-class referenced in the registry key. Just having a bunch of explicit entries in the registy under the Dataproviders\Guid\SupportedObjects\DataSourceInformation subkey isn't enough. You NEED a class, even an empty no-implementation class to get the query designer to use non-ODBC join syntax.
To test that, just compile Sample2 from the DataSDK. Create a connection, then design a query. JOIN syntax is proper (non-ODBC). Now close down VS, edit the registry and remove the reference to the SqlDataSourceInformation object as the default value of the DataSourceInformation subkey for that provider. Re-run VS, and try and design a query. It'll use ODBC escape sequences.
2. Trying to implement designer support in your own provider is a complete shot in the dark. In spite of being able to run VS in a debugger and tracing through all the accesses of your code, there's way too many gotchas along the way that remain undocumented.
In spite of the fact that I can get my provider to show up in the Data Sources toolbox; in spite of the fact that it enumerates my tables and views, allows me to design queries, shows all my columns and tables correctly, executes fine -- I cannot for the life of me figure out what I need to implement to get a typed dataset!I've implemented every schema I can think of. Tables, Views, Indexes, Columns, Catalogs, DataTypes, ReservedWords, MetadataCollections. My DbDataReader's GetSchema is by-the-book (though there IS no book!) I've implemented DbCommandBuilder, DbDataAdapter , DbDataReader, DbConnection, DbConnectionStringBuilder, DbCommand, DbProviderFactory, DbParameterCollection, DbParameter, DbTransaction.
I've tried tracing VisualStudio as it roams all over creation after dragging a table onto a DataSet designer, and the errors I get are:
A first chance exception of type 'System.NotSupportedException' occurred in Microsoft.VisualStudio.DataTools.DLL
Additional information: The type concept "TableUniqueKey" is not supported by this enumerator.
An unhandled exception of type 'System.NotSupportedException' occurred in Microsoft.VisualStudio.DataTools.DLL
Additional information: The type concept "TableUniqueKey" is not supported by this enumerator.
A first chance exception of type 'System.NullReferenceException' occurred in Microsoft.VSDesigner.dll
Additional information: Object reference not set to an instance of an object. A first chance exception of type 'Microsoft.VSDesigner.DataSource.DataSourceBuilderException' occurred in Microsoft.VSDesigner.dll
Additional information: Could not retrieve schema information for table or view foo.
All the errors before the last one are caught. The final error regarding the schema information is the one that is presented to me by VS when I drag a table onto a Dataset designer.
The null reference exception before the last (and final) error has also got me stumped. I've set breakpoints on every single method that returns a null, and can't for the life of me find any point in my code where that null might be coming from -- unless its coming from a datatable or dataset I've constructed ... but even then its a complete mystery.
The other problem is that the last error is occurring well after all schema information has been retrieved. Why does this one action give me schema problems when none of the other designer areas are giving me any schema problems?
Is there some list somewhere that tells me what classes, what schemas, and what functions I have to implement at a minimum to get all this to work?
Robert
Given the MSSQL table "sampledb.dbo.SampleTable",
Catalog refers to "sampledb"
Schema refers to "dbo"
Table refers to "SampleTable"
In MSSQL, the "Schema" as I have seen it refers to the user that created the table. I have some tables with a schema of "dbo" and others with a schema of "adminuser", a user account in which some of the tables were created.
In SQLite, there is no user concept, and no schema concept. There is however, a catalog concept because multiple database files can be joined virtually to a single connection instance and queried upon. Therefore in SQLite, two tables:
main.MyTable
linked.MyLinkedTable
"main" is the primary database file and "MyTable" refers to a table in the primary database file.
"linked" is the name of an explicitly-linked external database file linked to the active connection, and "MyLinkedTable" refers to a table in the linked database file.
Ripping out all the Schema information in the XML files and/or other places proved to be more trouble than it was worth in the end.
It did not seem proper to hijack the "schema" and treat it like Catalog, especially if I wanted my GetSchema() calls to use the same parameters and sequence of parameters that all the other providers are using. The order of the array elements given to GetSchema() is important, and I didn't want different meanings for different elements in my provider. Hence, I elected to explicitly ignore schema and set any schema references to null.
Robert
