c# - 在 SQLite (C#) 中，当我对两个表进行 UNION 时，我会丢失列类型信息

Question

在下面的代码中，当我从单个表执行查询时，类型信息是完美的...但是，当我从两个定义相同的表的联合执行查询时...类型信息丢失(到学位)如下:

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 + ")");

Answer 1

我已经把它修好了。

创建一个 View

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

然后你应该从 View 中选择

  Select * from vwTest