c# - 在SQLite(C#)中，当我合并两个表时，我丢失了列类型信息

在下面的代码中，当我从单个表进行查询时，类型信息是完美的...但是，当我从两个相同定义的表的并集进行查询时，类型信息在一定程度上会丢失如下：

Select * from Test1
Name (System.String)
Date (System.DateTime)
Value (System.Int32)

Select * from Test1 UNION Select * from Test2
Name (System.String)
Date (System.String)  <== DateTime converted to String
Value (System.Int64)  <== Int32 converted to Int64

使用UNION时，是否可以保留类型信息？

码：

        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 into 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 into 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 * from Test1";
        DataTable dt = new DataTable();
        using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
        {
            // create data adapter
            using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
            {
                // this will query your database and return the result to your datatable
                da.Fill(dt);
            }
        }
        Console.WriteLine(sql);
        foreach (DataColumn column in dt.Columns)
            Console.WriteLine(column.ColumnName + " (" + column.DataType + ")");

        sql = "Select * from Test1 UNION Select * from Test2";
        dt = new DataTable();
        using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
        {
            // create data adapter
            using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
            {
                // this will query your database and return the result to your datatable
                da.Fill(dt);
            }
        }
        Console.WriteLine(sql);
        foreach (DataColumn column in dt.Columns)
            Console.WriteLine(column.ColumnName + " (" + column.DataType + ")");

最佳答案

我把它固定在这里了。

而是创建一个视图

  CREATE VIEW "vwTest" AS Select * from Test1 UNION Select * from Test2

然后，您应该从视图中选择

  Select * from vwTest