Specify Linqpad Connection Programatically?

tjnorris

Hello,

I hope this question hasn't already been asked and answered. I am trying to find a way in Linqpad to specify a connection (preferably by name) programatically inside a .linq file using one of the existing connections in the Linqpad gui. I have a scenario, where I need to run the same linq queries, against multiple different connections in a loop, and I already have the connections defined in Linqpad. I know that I could set up a SQL connection and change the connection string every time (but the connections are set up using a Driver and have encrypted usernames and passwords). I was hoping that there was an easy way to just specify the connection programatically by name inside the .linq file since I already have the connections set up. Also, I already know that you can use LPRun.exe and pass the parameter ( eg... lprun -lang=e -cxname=CustomerDB script.txt). While this may work, I want the process to run sequentially on the same thread (for error capturing and simplicity - avoiding callbacks, task await ect.. reasons). Is there an equivalent way to set the -cxName from inside a .linq script?

Here is a simplified version of what I am trying to do.

void Main()
{
//specify which connections I would like to run the queries against
List connectionNameList = new List();
connectionNameList.Add("Connection1");
connectionNameList.Add("Connection2");


//for each connection, run queries
foreach(string connectionName in connectionNameList)
{
try
{
RunMyQueriesFor(connectionName)
}
catch(Exception ex)
{
//write errors to log for failed query
}
}

private void RunMyQueries(string connectionName)
{
//set the connection progamatically
This.Connection = connectionName;

//get the latest info from the table
var currentQuery= SomeTable.Where(x => x.SomeDate >= Today || x.SomeOtherDate > Today);

Util.WriteCsv (currentQuery, @c:\somedirectorypath\somefilename.csv);

}

}


Thanks,

Tim

felixmohr

I am doing something similar.

You cannot switch to another named connection, as far as I know, but you can open another connection with the current typed data context.

You can get the connection string for the current connection using
this.Connection.ConnectionString
Please note that the password in that string is replaced with stars after you made the first query.

You can then modify / create the connection string with your your different connections and create a new TypedDataContext object

var db = new TypedDataContext("Data Source=myserver.database.windows.net;User ID=myUser;Password=myPassword;Initial Catalog=myDatabase;app=LINQPad [Query 11];Encrypt=true");
var otherUsers = db.Users.ToList();

you can use "db" instead if "this" in this case to access the other database using the same typed linqpad context.

I iterate a list of azure config files and extract the required connection string settings from that config to use with the TypedDataContext and query all our customer databases

felixmohr

I just checked I and even just read my connection string (the provider connection string of an EntityFramework connection string) and use it directly in a new TypedDataContext, if that helps

var config = MyCustomAzureParser.Load(azureConfigFile.File).ToDictionary(x => x.Key, x => x.Value);
var connection = new SqlConnectionStringBuilder(new EntityConnectionStringBuilder(config["DatabaseConnectionString"]).ProviderConnectionString);

var customerConnection = new TypedDataContext(connection.ConnectionString);
/* query specific customerConnection database */

JoeAlbahari

This strategy will work if all the databases in question have the same schema.

You can retrieve the connection string from another query as follows:

Util.GetMyQueries().First (x => x.FilePath == "...").GetConnectionInfo().DatabaseInfo.GetCxString()

tjnorris

Thanks Felix and Joe! I am going to give this a try. I like being able to query the existing connections and then use the connection information to set the data context. I will give it a try!

Thanks again,

Tim

markeastob

Joe in your example above what is "FilePath"? When I call Util.GetMyQueries(), I don't see FilePath????

nescafe

The LINQ.ObjectModel.Query class implements the ICustomMemberProvider with a specific set of properties to be displayed (as documented here: http://www.linqpad.net/CustomizingDump.aspx)

namespace LINQPad.ObjectModel
{
    public class Query : ICustomMemberProvider
    {

        // [...]

        IEnumerable<string> ICustomMemberProvider.GetNames() => 
            new string[] { "OpenLink", "Location", "Name", "Text", "Language", "FileReferences", "GacReferences", "NuGetReferences", "NamespaceImports", "LastModified" };

        IEnumerable<Type> ICustomMemberProvider.GetTypes() => 
            new Type[] { typeof(object), typeof(string), typeof(string), typeof(string), typeof(QueryLanguage), typeof(IEnumerable<string>), typeof(IEnumerable<string>), typeof(IEnumerable<NuGetReference>), typeof(IEnumerable<string>), typeof(DateTime?) };

        IEnumerable<object> ICustomMemberProvider.GetValues() => 
            new object[] { this.OpenLink, this.Location, this.Name, (((this.Text != null) && (this.Text.Length > 100)) ? (this.Text.Substring(0, 0x5f) + "...") : this.Text), this.Language, this.FileReferences, this.GacReferences, this.NuGetReferences, this.NamespaceImports, this.LastModified };

        // [...]

    }
}

You could either dump to datagrid or run the following query to view all properties:

Util.GetMyQueries().Select(x => Util.ToExpando(x))