Execute a `Hyperlinq`-like query in LinqPad programmatically

Question

I use LinqPad with the MySQL IQ driver to query data from a Magento database. I not only use this for reporting on the database but for doing updates. I can use the standard SubmitChanges() method to update data, but that often ends up with unbearably slow updates that can literally take hours - one of my tables has 35,707 records that I recreate on a regular basis.

So instead I generate SQL statements in my LinqPad queries and then execute them in a separate tab after selecting "SQL" in the language drop-down.

For example, my output might be something like this:

UPDATE catalog_category_product SET position = 6040 WHERE (category_id = 156 AND product_id = 12648);
UPDATE catalog_product_entity_media_gallery_value SET label = 'Sandy Beach' WHERE ((store_id = 0) AND (value_id = 8791));
-- Done.

I have recently found that LinqPad has a nice class called Hyperlinq that allows me to write code like this:

(new Hyperlinq(QueryLanguage.SQL, myGeneratedSqlText, "Run Query")).Dump();

The result is that a hyperlinq is put in the output window that will run the query (in my example the contents of myGeneratedSqlText) in a new tab and execute the query.

This is very convenient.

However, I now want to be able to save a log of queries that are executed. There doesn't seem to be (an easy) built-in way to manually execute a "generated" query in LinqPad. I can certainly use Util.Run to execute an existing saved query, in fact I do something like this:

Util
    .OnDemand("Run Query", () =>
    {
        var fn = createOutputQueryFileName(); // Timestamped query name
        System.IO.File.WriteAllText(fn, myGeneratedSqlText);
        var run = Util.Run(fn, QueryResultFormat.Text);
        var result = run.AsString();
        return result.StartsWith("[]") ? "Success" : result;
    })
    .Dump();

The only drama with this is that I have to prefix the text in myGeneratedSqlText with the following:

var preamble = @"<Query Kind=""SQL"">
  <Connection>
    <ID>ec026b74-8d58-4214-b603-6d3145e03d7e</ID>
    <Driver Assembly=""IQDriver"" PublicKeyToken=""5b59726538a49684"">IQDriver.IQDriver</Driver>
    <Provider>Devart.Data.MySql</Provider>
    <CustomCxString>[DELETED]</CustomCxString>
    <Server>127.0.0.1</Server>
    <Database>prod_1_8</Database>
    <Password>[DELETED]</Password>
    <UserName>[DELETED]</UserName>
    <NoPluralization>true</NoPluralization>
    <NoCapitalization>true</NoCapitalization>
    <DisplayName>Production Tunnel</DisplayName>
    <EncryptCustomCxString>true</EncryptCustomCxString>
    <Persist>true</Persist>
    <DriverData>
      <StripUnderscores>false</StripUnderscores>
      <QuietenAllCaps>false</QuietenAllCaps>
      <Port>6606</Port>
    </DriverData>
  </Connection>
</Query>
";

I would really like to avoid all of this preamble stuff and include a line like this in my Util.OnDemand(...) code:

var run = Util.Run(QueryLanguage.SQL, myGeneratedSqlText, QueryResultFormat.Text);

(But this method doesn't exist.)

The key requirement here is to display a hyperlinq in the LinqPad output window that, if clicked, will save the query to disk as a log and also execute the query.

Can anyone suggest a clean way for me to do it?

Answer 1

I hope I've understood you correctly. When you've selected a connection in the top bar, your UserQuery becomes a datacontext. For this reason, you can use ExecuteQuery and ExecuteCommand on this within an action based Hyperlinq.

new Hyperlinq(() => {
                "do log work here".Dump();
                this.ExecuteQuery<string>(generatedSelect).Dump("Results");
                this.ExecuteCommand(generatedCommand).Dump("Results");
             }, "Run Query").Dump();

Unfortunately this outputs to the current tab, but hopefully this will at least get you most of the way to done :)

Here's an image of it at work:

As you're using MySQL, you can go via the connection property on this:

new Hyperlinq(() => {
            "do log work here".Dump();
            using (var command = this.Connection.CreateCommand())
            {
                // Usual command logic here
            }
         }, "Run Query").Dump();