.NET Core SDK 2.1 shipped with a new and exciting new feature: global tools. If you worked with npm, you already have a good idea what global tooling is about. Think angular CLI or similar. It is an executable, written in .NET Core that you can install on your machine. It can perform any number of useful tasks. It is an alternative to npm for example, whereby .NET developer can wrap their command line utilities inside a globally installed executable. They can distribute such tool using NuGet. This pains a very nice picture for developers.
I am going to embark on a journey to create a tool that creates stored procedures from a database. In this post we will walk through the steps of creating a project and adding useful command like support package, McMaster.Extensions.CommandLineUtils. Let’s get started.
I am using Visual Studio 15.8.2, latest current release. I also have the latest .NET Core SDK installed. I am going to start a new project, and pick Console App (.NET Core) project template. By convention, the tool must start with “dotnet-“, so make sure to give your project this name. Go ahead and create the project.
Now, add a reference to nuget package called McMaster.Extensions.CommandLineUtils. Build your project to make sure it works Ok. I am now going to add a new class called Generator, which will be my entry point.
[Command(
Name = "dotnet storedprocsgen",
FullName = "dotnet-storedprocsgen",
Description = "Generates stored procedures",
ExtendedHelpText = "This tool generates stored procedures. Use -s to specify server name and -d for the database name. SSPI will be used.")]
[HelpOption]
public partial class Generator
The Command parameter specifies typical help options, such as name and full help. Full help will be shown if the tool is invoked with the –help options. For now I am going to add two command line options: server and database. I am going to support short and long option names, such as –s or –server.
public partial class Generator
{
[Required(ErrorMessage = "You must specify server name / -s or -server option")]
[Option("-s|--server", CommandOptionType.SingleValue, Description = "Server name", ShowInHelpText = true)]
public string Server { get; }
[Required(ErrorMessage = "You must specify database name / -d or -database option")]
[Option("-d|--database", CommandOptionType.SingleValue, Description = "Database name", ShowInHelpText = true)]
public string Database { get; }
I am using Option attribute from McMaster utilities package. Template has both short and long option names as well as reasonable description. I am going to create a main method that will attempt to connect to the database. This is where I am going to end this post. Here is the final version of the class.
using McMaster.Extensions.CommandLineUtils;
using System;
using System.ComponentModel.DataAnnotations;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading.Tasks;
namespace dotnet_storedprocsgen
{
[Command(
Name = "dotnet storedprocsgen",
FullName = "dotnet-storedprocsgen",
Description = "Generates stored procedures",
ExtendedHelpText = "This tool generates stored procedures. Use -s to specify server name and -d for the database name. SSPI will be used.")]
[HelpOption]
public partial class Generator
{
[Required(ErrorMessage = "You must specify server name / -s or -server option")]
[Option("-s|--server", CommandOptionType.SingleValue, Description = "Server name", ShowInHelpText = true)]
public string Server { get; }
[Required(ErrorMessage = "You must specify database name / -d or -database option")]
[Option("-d|--database", CommandOptionType.SingleValue, Description = "Database name", ShowInHelpText = true)]
public string Database { get; }
public async Task<int> OnExecute(CommandLineApplication app, IConsole console)
{
Console.WriteLine($"Connecting to server {Server} to database {Database}");
using (var connection = CreateConnection())
{
await connection.OpenAsync();
Console.WriteLine("Connected...");
}
return Program.OK;
}
public string CreateConnectionString()
{
var builder = new SqlConnectionStringBuilder();
builder.DataSource = Server;
builder.InitialCatalog = Database;
builder.IntegratedSecurity = true;
return builder.ConnectionString;
}
public SqlConnection CreateConnection()
{
return new SqlConnection(CreateConnectionString());
}
}
}
OnExecute is the main entry point that command line utilities nuget will invoke. We just need to wire up this inside Program.cs
using McMaster.Extensions.CommandLineUtils;
using System;
using System.Threading.Tasks;
namespace dotnet_storedprocsgen
{
class Program
{
// Return codes
public const int EXCEPTION = 1;
public const int OK = 0;
public static async Task<int> Main(string[] args)
{
try
{
return await CommandLineApplication.ExecuteAsync<Generator>(args);
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.Error.WriteLine("Unexpected error: " + ex.ToString());
Console.ResetColor();
return EXCEPTION;
}
}
}
}
You will notice that I am using async Main feature. If your project fails to build, you will need to edit csproj file to make it look as following:
<Project Sdk=”Microsoft.NET.Sdk”>
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>netcoreapp2.1</TargetFramework>
<PackAsTool>true</PackAsTool>
<IsPackable>true</IsPackable>
<LangVersion>7.1</LangVersion>
</PropertyGroup>
<ItemGroup>
<PackageReference Include=”McMaster.Extensions.CommandLineUtils” Version=”2.2.5″ />
<PackageReference Include=”System.Data.SqlClient” Version=”4.5.1″ />
</ItemGroup>
</Project>
I am forcing the compiler to use 7.1 C#. I am also flagging the project as PackAsTool and IsPackable. Then I can invoke dotnet pack command to create NuGet packages with the tool. I am going to create a batch file to help me install and test my tool. It needs to live in the same folder as csproj file.
dotnet build dotnet pack dotnet tool uninstall dotnet-storedprocsgen -g dotnet tool install dotnet-storedprocsgen -g --add-source "C:\Users\serge\Source\Repos\dotnet-storedprocsgen\dotnet-storedprocsgen\bin\Debug"
Let’s walk through my commands. First one just builds the project. Second one creates NuGet pacakge from it. Third one uninstalls current version of the global tool. Fourth one reinstalls it. Once I run this batch file, I can test my tool by typing
dotnet storedprocsgen --help
This will show me the command line options:
Usage: dotnet storedprocsgen [options]
Options:
-?|-h|–help Show help information
-s|–server Server name
-d|–database Database name
This tool generates stored procedures. Use -s to specify server name and -d for the database name.
SSPI will be used.
Cool. Now I can type
dotnet storedprocsgen -s . -d edge-sql
Assuming I have SQL Server installed on local machine and have a database called edge-sql, I will see the following:
Connecting to server . to database edge-sql
Connected…
This confirms that my connection string is working. More to come in subsequent posts.