Hi!
It seems, that some trouble exists in schema generating routine (System.Data.SQLite version is 1.0.60.0).
I wrote an example, that illustrate my problem with reading data from data reader to table, when we to deal with using left outer joins in query.
First, lets create a test database and fiil it with test data:
=================
begin transaction;
create table ArticleLine
(
al_id integer not null primary key autoincrement,
al_name varchar(100) not null
);
create table Catalogue
(
art_id char(6) not null,
al_id integer null
constraint fk_catalogue2 references ArticleLine(al_id),
primary key (art_id)
);
insert into ArticleLine(al_id, al_name) values(1, 'Line 1');
insert into Catalogue(art_id, al_id) values('000001', 1);
insert into Catalogue(art_id, al_id) values('000002', null);
commit;
=================
Second, lets write a small example:
using System;
using System.Data;
using System.Data.SQLite;
namespace SQLiteTest
{
class Program
{
static void Main()
{
using (IDbConnection conn = new SQLiteConnection("Data Source=test.db3"))
{
conn.Open();
string[ queries =
{
"select t1.art_id, t2.al_id from Catalogue t1 " +
"left outer join ArticleLine t2 on t2.al_id = t1.al_id",
"select t1.art_id, t1.al_id from Catalogue t1 " +
"left outer join ArticleLine t2 on t2.al_id = t1.al_id",
"select t1.art_id, t1.al_id, t2.al_name from Catalogue t1 " +
"left outer join ArticleLine t2 on t2.al_id = t1.al_id"
};
foreach (string query in queries)
{
try
{
Console.WriteLine("Executing query:");
Console.WriteLine(query);
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = query;
using (IDataReader reader = cmd.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(reader);
}
}
Console.WriteLine("OK!");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.WriteLine("============================");
}
}
}
}
}
===========================
In this example we can see that "AllowDBNull" property for field " t2.al_id" detects incorrectly (first query)
Ok, I suppose, that we can wrap-arround this by using "t1.al_id" instead of " t2.al_id"... And it really works! (second query)
... but, I need to obtain a "t2.al_name" from query (third query), but in datatable schema this column also has AllowDBNull=false , which is incorrect, when we use "left outer join".
Any ideas about this?
Of course, I can define "ArticleLine.al_name" as nullable, but it does not correspond business rules of task.
Thanks!
P.S. Sorry for my ugly english.. :(