In an effort to better understand SQLite and ADO.NET 2.0, I decided to rewrite the whole thing from scratch using managed C++ and the 2.0 framework. It's been an uphill battle, but fun nonetheless. Apparently the Compact Framework will allow for mixed/managed C++ in Whidbey.
In doing so, I've been digging into the existing ADO.NET code you guys have written, and comparing it to mine as well as to other data providers -- just sanity checking myself.
I have 2 questions on the datareader:
1. Given the following statement: "UPDATE foo set bar=1;SELECT * from foo;UPDATE foo set bar=2", if one calls ExecuteReader() on the library and then calls Read(), does your library automatically process the commands up to the first row-returning statement and then begin returning the data?
2. If you call the Close() method of the DataReader given the above circumstances after reading the last row but never calling NextResult(), does the last UPDATE method get called?
I've also been fiddling with GetTableSchema(). I made a small change to the SQLite 3.1.1 beta to fix the buggy "full_colum_names" pragma and am using this feature to give much better schemas back. If you guys are interested in the theory, it goes something like this:
Given the statement:
SELECT Foo.ID as A, Foo.Bar As B, Bar.ID As C FROM Foo INNER JOIN Bar ON A = C
Making sense if this is a mess from a parsing perspective, so I don't even bother. Instead, I create a new temporary connection to the database, set PRAGMA full_column_names=1, then prepare the above statement on it. It returns me the actual table.column of each returned column, from which I parse the table and column and call the table_info() pragma.
I also don't manually process parameters as you guys are doing. It's much cleaner to let SQLite process them and simply map to them later as users add parameters to a command. If you're interested in the details, I'll post those as well -- but for now it's quitting time and I'm headed home.
Bah! I spoke too soon. Looks like they're not going to support managed C++ on the CF framework afterall. Suckage.
Funny, this project started out life as Managed C++. When Alexander took over main development, he rewrote it in C#, and one of the reasons for doing that was to support the CF. Ah, the circle of life. ;-)
To answer your questions:
1. The Update command will be executed, but DataReader.read() returns false, indicating that any commands after the first one are ignored. This was tested with a test case designed around your SQL fragment.
2. See above:)
Could you please provide more details on the buggy pragma you have mentioned above please :)
Re the Managed C++ thing... If you take a look through the General forum (or the ReadMe.txt file, I can't remember which), that is talked about.
The reason I ask is this:
When I execute the same command in SQLCommand, the SQLDataReader executes all 3 statements at once and the first call to Read() begins reading the data from the SELECT statement. You may want to change your DataReader code so it does the same sort of thing. You shouldn't have to explicitly call NextResult() to get to the SELECT clause in the sequence. To the user, all 3 commands were combined into one, and there was only 1 SELECT in the command, therefore ReadNext() should be unnecessary to begin reading.
Also, since only one row-returning result was processed for the DataReader, when the DataReader's Close() is called, all remaining commands should be executed on the database to ensure all the intended commands in the sequence were executed.
Additionally, any non-row-returning commands should be executed and skipped when calling NextResult(). If a command consisting of 2 SELECT statements was interspersed between many update statements, then you should only have to call NextResult() twice. To the user there were only 2 result sets, and no matter how many updates occurred after the last resultset was processed, the user should never have to call NextResult() many times to ensure they all get executed.
As for the bug in sqlite, it's documented
My "fix" didn't work properly, so I hacked the code and created a new pragma called "real_column_names" which will always return the column names of a resultset in Table.Column format regardless of any aliases on either the columns or the tables.
Once I get that back, I query each table, get the schema details and fill in the schema with the missing pieces. It works really well. Given the statement:
SELECT C.ID As A, D.ID As B, 'Bar' as E FROM Foo AS C INNER JOIN Bar AS D ON C = D
The real_column_names pragma I added returns the columns as:
Foo.ID, Bar.ID, E
If full_column_names was working, the returned columns would've been:
C, D, E
full_column_names allows aliases to override the column names, where as my new pragma doesn't. the full_column_names pragma is broken however, so on statements such as SELECT * FROM Foo the returned columns do not contain the table name.