Run a "Hyperlinq" -like request in LinqPad programmatically

Question

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

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

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 recently discovered that LinqPad has a Hyperlinq class class that allows me to write code as follows:

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

As a result, a hyperlink appears in the output window, which will launch the request (in my example, the contents of myGeneratedSqlText ) on a new tab and execute the request.

It is very comfortable.

However, now I want to keep a log of executed requests. It seems that there is no (simple) built-in way to manually execute the "generated" query in LinqPad. I can of course use Util.Run to execute an existing saved request, in fact I am doing 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 must prefix the text in myGeneratedSqlText as follows:

 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 this preamble material and include such a line in my Util.OnDemand(...) code:

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

(But this method does not exist.)

The key requirement here is to display the hyperlink in the LinqPad output window, which, when pressed, will save the disk request as a log and also execute the request.

Can anyone suggest me a clean way?

Answer 1

I hope I understood you correctly. When you select a connection in the top pane, your UserQuery will become a datacontext. For this reason, you can use ExecuteQuery and ExecuteCommand to do this in a Hyperlinq action.

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

Unfortunately, this is displayed on the current tab, but hopefully this will at least give you most of the way :)

Here is an image of him at work:

As you use MySQL, you can go through the connection property:

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