5

I'm using the System.Data.SQLite and I'm trying to retrieve the SQL string that is generated by the query expression below. The query executes correctly, but the SQL string is SELECT NULL AS [EMPTY].

It seems that GetCommand().CommandText is not supported, but if so, how else is it possible to access the generated SQL string?

[<Test>]
member this.showSQL() =
    let connectionString = sprintf @"Data Source=%s;UTF8Encoding=True;Version=3" dbFilename
    let connection = new SQLiteConnection(connectionString)
    use dc = new DataContext(connection)

    let channelMap = dc.GetTable<ChannelData>()

    let map = query {
        for row in channelMap do
        where (row.ChannelId = 1)
        select (row.ChannelId, row.Data0, row.State) }

    let cmd = dc.GetCommand(map).CommandText;
    printf "SQL: %s" cmd
ildjarn
  • 62,044
  • 9
  • 127
  • 211
Armin
  • 1,052
  • 7
  • 18

1 Answers1

-1

The SQLiteCommand object has the associated CommandText property working as intended.

    static void Main(string[] args)
    {
        string sql = "select * from foo";
        SQLiteCommand command = new SQLiteCommand(sql, null);

        Console.WriteLine(command.CommandText);
        Console.ReadLine();
    }

Perhaps you can rework your code to utilize it.

HouseCat
  • 1,559
  • 20
  • 22
  • The code above prints what was put into `SQLiteCommand` before. But what I needed was a way to convert the F# query expression to an SQL string for debugging purposes. Going back to the F# code, the `dc.GetCommand(map)` line actually returns an `SQLiteCommand` instance, but without the SQL I would expect. My suspicion is that `SQLiteCommand` uses SQLite's C API instead of going through the SQL conversion. May be someone can confirm that. – Armin Feb 26 '15 at 07:37
  • Oh I see what you are saying. I suppose it is possible, in which case they may have overridden the ToString command. What does dc.GetCommand(map).CommandText.ToString() produce? – HouseCat Feb 26 '15 at 14:58
  • It produces the same result, because `CommandText` is already a string. – Armin Feb 27 '15 at 07:56