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.
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With