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?
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.
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: