NetBash is a small utility u can use in MVC projects created by Luke Lowrey. It’s an alternative for endless administrative pages for every function you need. It’s nested in your view and styled as a real bash command window.
In one of my side projects I created my own commands to send text messages to cell phones, to activate a mass mailing and so on. As I manage the application I get regularly questions for custom database queries where no administration screen is created for. Luke Lowrey already provided the NetBash.Database Nuget package to query the database. After installing the package I wasn’t able to get the database queries to work.
Open source
Luke Lowrey was so nice to create a GitHub repository with the source code for NetBash and the NetBash.Database plugin. As a new Github user I forked the NetBash.Database repository and cloned the code locally. I added the projects to my projects solution and removed the existing references to the Nuget packages. After adding new references to the added projects I was ready to start debugging.
First thing I noticed was the option to add the connectionstring name to the NetBash sql command but no way to fetch all connectionstrings names from the config file.As the project has multiple connectionstrings for different database it would be a help to have a way to visualize them before starting to query. First job, add an option to show the connectionstring names.
New NetBash option
Luke used NDesk to define the options that can be used in the Database plugin. Adding an option is quite simple. In the SqlCommand class we alter the Process method.
var p = new OptionSet() {
{ "e|execute", "Executes an sql query",
v => _command = Command.Execute },
//{ "i|info", "Shows database information",
// v => _command = Command.Info },
{ "t|tables", "Lists tables and space used optional filter on provided table name",
v => _command = Command.Tables },
{ "s|schema", "Display table schema for the provided table name",
v => _command = Command.Schema },
{ "clear", "Removes all rows from database",
v => _command = Command.Clear },
{ "cn|connectionstring","Returns the connectionstring name that will be used",
v=>_command=Command.ConnectionStringName},
{ "lcn|listcn","Shows all connectionstring names found",
v=>_command = Command.ListConnectionStrings},
{ "c=|conn=", "Name of connection string to use (defaults to first found)",
v => _connectionName = v },
{ "h|help", "show this list of options",
v => _command = Command.Help }
};
Here we added the ‘lcn’ command in the OptionSet (line 19). Before we can set the _command parameter we have to add our command to the Command enum.
private enum Command
{
Execute,
Info,
Tables,
Schema,
Clear,
Help,
ConnectionStringName,
ListConnectionStrings
}
All we need now is a private method where we loop over all the connectionstrings and add them to a StringBuilder (see getConnectionStringNames()).
private string getConnectionStringNames()
{
var sb = new StringBuilder();
for (int i = 0; i < ConfigurationManager.ConnectionStrings.Count; i++)
{
sb.AppendLine(ConfigurationManager.ConnectionStrings[i].Name);
}
return sb.ToString();
}
We then return the result to the Process method (line 21,22).
switch (_command)
{
case Command.Execute:
return execute(query);
case Command.Info:
return executeEmbedded("DbInfo.sql");
case Command.Tables:
return getTables(query);
case Command.Schema:
return showSchema(query);
case Command.Clear:
return clearRecords();
case Command.ConnectionStringName:
return getConnectionStringName();
case Command.ListConnectionStrings:
return getConnectionStringNames();
case Command.Help:
default:
return showHelp(p);
}
Now we can use the “SQL -lcn” command in the NetBash interface to list us all the connectionstrings that are in the config file.
Entity Framework formatted connectionstring support
Now we can show all connectionstring names I encountered the next problem. To query the database Luke makes use of a SqlCommand. This command needs a SqlConnection object that has the correct connection string.
In the project we use Entity Framework (EF) that needs his own type of formatting for the connectionstrings. The SqlConnection object can’t work with this types of strings. But the type of connectionstring that we have to use is embedded in the EF string. All we need to do is check if the connectionstring is the EF type and then parse it. In the System.Data.EntityClient namespace Microsoft added a EntityConnectionStringBuilder that we can use to parse the string.
private string CheckForEntityFrameworkConnectionString(string connString)
{
if (connString.Contains("provider connection string="))
{
//Parse connectionstring from Entity connectionstring
var entityBuilder = new EntityConnectionStringBuilder(connString);
return entityBuilder.ProviderConnectionString;
}
return connString;
}
The EntityConnectionStringBuilder has a constructor that takes an EF connectionstring. After that we can access the ProviderConnectionString parameter that will contain the string we need. We add the CheckForEntityFrameworkConnectionString in the getConnectionString method and we’re ready to query our database.
private string getConnectionString()
{
var connString = ConfigurationManager.ConnectionStrings[_connectionName] ??
ConfigurationManager.ConnectionStrings[0];
string connectionString = CheckForEntityFrameworkConnectionString(connString.ConnectionString);
return connectionString;
}
Github commit and Pull Request
Now we added the functionality we wanted we can commit it all to our Github repository that is a fork of the original repository. After committing we want to inform Luke that we have some changes that he maybe would like to incorporate in his code. Github has this functionality on board. You can issue a “Pull request”. With this request you can ask the manager of the original repository to include the changes you have made. The manager then has to choose the changes he wants to merge into his code. I’ve added my ‘Pull Request’ and hopefully Luke likes the changes I added and will merge them into his fantastic utility.
Only 2 weeks from now and a new edition of Tech Ed Europe starts, this time in Amsterdam. The last Tech Ed Europe was in Berlin in the cold month of November 2010. Feedback from most visitors was to organize a new edition in the summer instead of the winter. Microsoft listened to this and skipped a 2011 edition to be back with a summer 2012 edition.
