In SQLite (C#) when I UNION two tables I lose column type information -

in code below when query single table type information perfect... however, when query union of 2 identically defined tables... type information lost (to degree) follows:

select * test1 name (system.string) date (system.datetime) value (system.int32)  select * test1 union select * test2 name (system.string) date (system.string)  <== datetime converted string value (system.int64)  <== int32 converted int64 

is there way can preserve type information when use union?

code:

        sql = "create table test1 " +             "([name] string, [date] date, [value] int)";         using (sqlitecommand command = new sqlitecommand(sql, connection))         { command.executenonquery(); }          sql = "create table test2 " +             "([name] string, [date] date, [value] int)";         using (sqlitecommand command = new sqlitecommand(sql, connection))         { command.executenonquery(); }          sql = "insert test1 (name, date, value) values (@name, @date, @value)";         using (sqlitecommand command = new sqlitecommand(sql, connection))         {             command.parameters.add(new sqliteparameter("@name", "john doe"));             command.parameters.add(new sqliteparameter("@date", datetime.parse("11/30/1958")));             command.parameters.add(new sqliteparameter("@value", 1));             command.executenonquery();         }          sql = "insert test2 (name, date, value) values (@name, @date, @value)";         using (sqlitecommand command = new sqlitecommand(sql, connection))         {             command.parameters.add(new sqliteparameter("@name", "brian rice"));             command.parameters.add(new sqliteparameter("@date", datetime.parse("12/1/1970")));             command.parameters.add(new sqliteparameter("@value", 2));             command.executenonquery();         }          sql = "select * test1";         datatable dt = new datatable();         using (sqlitecommand cmd = new sqlitecommand(sql, connection))         {             // create data adapter             using (sqlitedataadapter da = new sqlitedataadapter(cmd))             {                 // query database , return result datatable                 da.fill(dt);             }         }         console.writeline(sql);         foreach (datacolumn column in dt.columns)             console.writeline(column.columnname + " (" + column.datatype + ")");          sql = "select * test1 union select * test2";         dt = new datatable();         using (sqlitecommand cmd = new sqlitecommand(sql, connection))         {             // create data adapter             using (sqlitedataadapter da = new sqlitedataadapter(cmd))             {                 // query database , return result datatable                 da.fill(dt);             }         }         console.writeline(sql);         foreach (datacolumn column in dt.columns)             console.writeline(column.columnname + " (" + column.datatype + ")"); 

i have fixed here.

create view instead

  create view "vwtest" select * test1 union select * test2 

then should select view

  select * vwtest