#tsql2sday – NuGet, Visual Studio, PowerShell & dbatools

Of course that you heard the saying

“If the mountain will not come to Muhammad, then Muhammad must go to the mountain”.

Now replace mountain with NuGet and Muhammad with Powershell. Intrigued? Stick with me! 🙂

Before we dive in:

Since I’ve started to attend some community events, namely TugaIT (on this one I was also a volunteer) SqlSaturday,  and other local events, I’ve interacted with many people that are really cool and experienced and even became friends with some. One of those people is Rob Sewell, also known as sql dba with a beard (b | t), a man with an epic beard and an amazing personality. After seeing that he’s hosting this month’s “T-SQL Tuesday” I’ve decided to join.
The “challenge” (as I like to call it, since for me each blog post is a challenge) is simple:

“spend an hour or so with it and tell us how you got on and what and how you learned”

Ok so what have I done and how?

Just to give some context, I’m currently working with ASP.NET Core 2.0 and Entity Framework Core 2.0, using the “database first approach” meaning that whenever there’s a change on the database that changes the model (let’s say add a new table or a new column), there’s the need to reflect those changes on my .NET code.
The way of doing so is by using the Scaffold-DbContext that takes the connection string, the provider and outputdir as parameters (although outputdir is not mandatory, it’s highly recommended since you might want your model on a specific folder). With this you are set, just run the command specifying those parameters and you’ll see the changes reflected on you outputdir folder.

My problem:

There are some tables that I don’t want to be mapped on my .NET code (in my case those are the openiddict and Asp.NET Identity tables). Looking at the Scaffold-DbContext documentation, I can’t find a way to exclude the tables, but I can find a way to say which tables to include. It’s doable… if your database has something like 10 tables!
So, I thought: “How can I get all the tables on my database? And when I get them, how can I exclude those that should not be mapped?”. (please note that in this case, the tables have the same prefix, but we will see an example just ahead) In this post, I’ll be using the AdventureWorks2012 database.

dbatools to the rescue!

First time I’ve heard about dbatools was when Cláudio (b | t) told me that he started contributing to “this open source project developed by the community that aims to make the dba’s tasks easier”. Since that moment, I’ve been following their work, either via twitter or via slack where you can join too!

DO NOT GET INTIMIDATED BY THE NAME!

“That’s not for me, I’m not a DBA, I’m a backend developer”, “those commands must be for those database nerds, not for me”. Well, I’m also a backend developer who write some SQL queries, but there are some useful commands even for “non-dbas” as we will see.
Ok, so since dbatools is so easy to install (on windows 10 you just need to type Install-Module dbatools … ridiculously easy) let’s try to use the Scaffold-DbContext on the powershell console.
This cmdlet can be found on the Microsoft.EntityFrameworkCore.SqlServer package .Since the sintax to install the tools for Entity Framework core is just Install-Package Microsoft.EntityFrameworkCore.Tools, sounds like we can just open powershell and type it in.

Hum… not so easy. Well, the documentation tells us to open Visual Studio and open the Nuget Package Manager Console and type the command. Even more, here it’s stated that:

“The commands listed here are specific to the Package Manager Console in Visual Studio, and differ from the Package Management module commands that are available in a general PowerShell environment. Specifically, each environment has commands that are not available in the other, and commands with the same name may also differ in their specific arguments. “

There’s nothing left to do then because dbatools requires powershell and we can’t use the NuGet commands outside the Package Management Console, meaning we can’t use it on the powershell console.

That’s correct, but Microsoft does not say that you cannot use powershell commands on the Package Manager Console!

Let’s try it. Please pray so that it works!

installDbaToolsError.png

Oh no! It’s red! Chill out and read the error. It’s just stating that in order to install dbatools it must be executed as administrator. I’ve simply ran Visual Studio as administrator, but probably the scoped install would also work (thank you Ed Elliott (b | t ) for point that 🙂 ) Doing so, we get the following:

In my case, I already had it installed, but you should have no problems now.
Now that we have dbatools installed, how is it going to help us? After a quick search I’ve found the Get-DbaTable that, among other parameters, returns the table name. Using the Get-Help command on the console we can see that:

We just need to pass the instance, the database name and our credentials. After that, we select the name property. In order to securely provide your credentials, use the Get-Credential cmdlet. Look at the following example:

Since we can use native powershell cmdlets like the Where-Object, we can easily filter the tables that are prefixed. Let’s assume that we don’t want to include any table that starts with “Employee”. We just need to pipe the result from the previous command to Where-Object {!$_.StartsWith("Employee")} (here you can see my “backend vein”, using the “!” and the StartsWith where I could have used the “pure powershell” and done with -NOT and LIKE) which results in:

$tableNames = Get-DbaTable -SQLInstance localhost -Database AdventureWorks2012 -SqlCredential $myCredentials | Select-Object -ExpandProperty Name | Where-Object {!$_.StartsWith("Employee")}.

Remember that all of this started because Scaffold-DbContext can receive an argument that is an array of strings, which corresponds to the tables to be mapped. Let’s see if we can put all of this together and get the expected result:
scaffoldResult.png

As we can see at yellow, we are getting warning because of some “problems” on the mapping for employees

Take it to the next level:

To avoid extension on this post, I’ll write another one, showing how you can make it even easier and do the scaffold in only one command like this:

I hope that this help you, not only with Scaffold-DbContext but with any other NuGet cmdlets (if I can call it that way) that depend on Powershell cmdlets.

Advertisements

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s