>> Big news. The SQL client is moving to NuGet.
This is going to help you keep pace with new features
regardless of if you're on.NET framework or.NET Core.
Check this out on On. NET.
[MUSIC].
>> Hello, and welcome to another episode of On.NET.
I'm your host Jeremy Likness and today I'm with Vicky Harp,
and we're going to talk about some changes that are coming
to the SQL client for dot net.
But before we jump into that, Vicky,
why don't you tell us a little bit about
who you are and what you do with Microsoft.
>> Sure. So I'm Vicky Harp.
I'm the PM lead
for the SQL Server Tools group and so were the group
responsible for pretty much the entire stack of experiences
from driver up to the graphical interfaces in the portal.
That includes the drivers,
the programmability interfaces, the command line tools,
the GUI tools like SQL
projects in Visual Studio SSMS Azure Data Studio,
and so really the whole lot.
>> That's a lot. So if I'm a developer, I'm.NET.
I've probably touched SQL server,
and what you're saying is if I touch SQL Server I've
probably touched the tools that you're working with?
>> Yes absolutely, millions of people in fact.
>> Amazing. So I understand there's some changes coming through.
We're obviously shifting in the.NET arena.
We've got.NET Framework.
We've got.NET Core.
Now it sounds like when we're looking
at the SQL client that was always
part of the core system there are
some changes coming down for that.
What are the changes and why would I even care as a.NET developer?
>> Yeah. So just definitionally,
most people who are using.NET who are interfacing with
SQL Server they're using
the SQL client driver and that's in system.data.sqlclient.
Now either they're using that directly and using data readers etc,
or they're using something like an ORM or framework
which is in turn using that SQL client driver.
>> Okay.
>> That has always been
the premier interface and
assembly for communicating with SQL Server.
SQL Server goes back 30 years.
It's been here as long as.NET has been here,
and it's always been where we put
our features first and where you try them
first and then they from their fan
out to the other drivers and languages,
and so we have a really rich ecosystem of that.
But it has some limitations in as.NET
is changing and as the speed of development is changing.
So for one thing,
we have seen system.data.sqlclient
in full framework is particle framework,
and so whenever there are updates to it it comes
through updates to the framework.
>> Okay.
>> Then the SQL client in.NET Core
is a separate version of SQL client.
Has a somewhat different set of functionality.
So there's some features that are not present in
in.NET Core that are available in full framework.
In addition to that, SQL Server itself is
shipping more frequently and is
itself now able to run on Linux and on Docker containers,
and so we've got
a changing environment where
we are wanting to be able to ship more quickly.
>> Okay.
>> So to that end we now have a NuGet package called
Microsoft.data.SQLclient which has all of
the client functionality that was in System.Data.SQLClient.
But it's in a package that you can consume on your
own and which can ship independently of the framework.
>> Now this package that I can consume on my own,
is this a different set of APIs or a different
approach to using the client or is it intended to be a swap out?
>> It's very much intended to be a swap out.
There's intentionality even in the name of it.
It's a one- word change from
System.Data.SQLClient to Microsoft.data.SQLClient and otherwise,
all of the object references all of these assemblies are the same,
and so you should be able to
just change you're using statements and
any qualified references and it would take effect immediately.
>> Okay. That's awesome,
and then if I do that I'm
able to take advantage of the same features now
in.NET Core that were different you're saying before, right?
>> To a certain extent. So there's still some places where we're
needing to build up that difference.
But we are even in the preview version shipping
some new features compared to what
was available in System.Data.SQLClient.
So for example, always encrypted has
been a feature in SQL Server for a number of years.
It's been available in full framework.
It is now in the preview package of
Microsoft.data dot SQL client available for.NET Core,
and then we're also shipping support for
data classification and UTF-8 support,
which are highly requested features related to SQL Server 2019.
So the notion is that going forward we'll be
shipping new features of SQL Server that have
a driver or assembly level consideration
in the NuGet package in Microsoft.data.SQLClient.
Whereas traditional System.Data.SQLClient we
maintained because there are millions of users of it.
>> Sure.
>> You may not need that new function yet,
but it'll be available for them when they're ready.
>> Okay. So if I'm fine where I'm at, I can stay.
But it's a very low pain point as far as
the migration that's the intention
because it's just a name space change.
Then once I do that I should be able to take
advantage of more future changes as they come out,
and I get that advantage if I'm doing
a.NET core app that everything ships together.
I'm not depending on what framework
is installed or what version on my target machine.
>> Correct. So you're not having to
necessarily control for all of those things.
You can have a known state
whenever you build it and it's going to be
the same whenever you deploy it.
>> That's a package that's available
today that I can get as a preview?
>> Yes, so it's available.
We have it on the NuGet gallery and it's also available on GitHub.
>> Okay.
>> So that it's in
preview now and it will be going to GA in pretty short order.
>> Okay. Great. Now, I understand you have some examples
that you can share about using the new client?
>> Yes, absolutely. So just real quick I'll show you.
Is this the GitHub location?
So it's at.NETSQLClient.
You can give that a search.
As at the time of this filming,
we don't have all of the source code moved over to it yet.
But that should becoming really in a matter of days or weeks.
>> Okay.
>> So this has the release notes.
If you want to read a little bit more about it and have
something that you want to share with people
outside of this video,
we have a blog post here on the.NET blog.
If you look up introducing the new Microsoft data SQL client.
It has some of the same things that we've already discussed about
the rationale and what the roadmap is going forward.
>> Okay.
>> So what I've got here in terms of a demo
is a WPF app and it's just the simplest little app.
Basically, this is
a little animals services app where you can get.
When they pick up a dog and they've got than just the name,
for some reason they don't have their actual license on there.
They can look up that dog name and see if there
is a dog that has the license with that name and then
see if the breed and
the color matches or that they have a notion of who they
might call given that they
don't know for sure who this belongs to.
So there's just a little sample app.
>> Okay.
>> The way that this works is we have
this little button that we can
search and we have a SQL connection.
It'll call a search and then down here in
the search area you can see it's creating a parameter
passing in the name and then
basically searching and filling
the grid with any of the dogs have that name.
>> Okay.
>> Relatively straight forward little data
app. So I'm going to run this.
I also point out that this is a WPF app and here's
the full framework version and down here I have
the NET Core version of the same application.
So I'm going to run the full framework version.
Instance here. Okay, wait a moment.
>> You know the trick, right?
You move the mouse clockwise.
>> Yes, and it makes it go faster.
>> Speeds it up makes the hamsters run faster. Here we go.
>> Yeah exactly. So let's just look up Hunter.
Let's see how many dogs we have named Hunter.
So we've got 58 and you can
see we've got the license type the breed,
and let's say we said okay,
we found a little Shi Tzu in black
and then we can go over here when you take
a look and this is the owner zip.
I'm using just the zip code for this demo.
But you would imagine it would be all
of the owners contact information.
>> Okay.
>> You see that works perfectly well.
Now if I go down here in the.NET Core version,
we're going to see a little bit of a different response here.
So this is a case where we're assuming
that someone is trying to convert to.NET Core for the first time.
So they've run into this,
and so the first time they run it, they get it all set up.
We run Hunter and we're going to get.
Search here. Get error
keyword not supported column encryption setting.
>> Okay.
>> So what does that mean? So if I take a look over at the table,
we can see that that owners zip that
I was referring to earlier is actually encrypted.
So it's encrypted with column encryption here,
and it's using actually a key that I have locally on my machine.
So this is something that you are able
to handle transparently through
a feature called Always Encrypted and that was being handled
in a full framework and it wasn't being handled in.NET Core.
>> Okay. Right.
>> If I go over here and change the see
the column encryption setting enabled that's enabling net feature.
So if I change connection strings,
then I can rebuild it,
and now we don't have that column encryption enabled,
and let's see how that looks.
We'll look up Hunter gain.
Give it a search, and
we're able to get data but it's a byte array.
>> So we're at least seeing
rows but we're still not seeing some code?
Right, and so that's going to be the case if we look at it.
This is by design,
if you don't have the key then what you're
going to see is the encrypted version of visitor.
>> You're protecting the personally identifiable data.
>> Correct.
>> Sensitive data in the table.
>> So what we're needing to do in
this application is to switch over from
System.Data.SqlClient to Microsoft.data.SQLClient so
that the.NET Core version will have this functionality.
So I'm going to switch back to
our other connection string
here and then walk you through the process.
So up here first I'm going to go to my manage NuGet packages.
Go over here to browse and type in Microsoft.data.SQLClient.
Make sure you've included pre-release in this case.
>> Right.
>> Then hit "Install". Give that just a second to install that,
and "Okay" "Accept".
>> Sign your life away.
>> Sign I my like away, and now I've got that installed
and then for the.NET core we also need to do the same thing.
So I'm going to NuGet packages.
Again, "Install", okay, "Accept",
and now that it's installed for both.
Then when I go back over to
this main window XAML, we see System.Data.SqlClient.
I'm going to change that to Microsoft.Data.SQL client,
and that is in fact it.
>> So you weren't kidding.
It's just that one line change basically.
>> Just that one line change. Because all of
these other things SQL connection etc.
I might have fully qualified them.
But in this case I didn't, and
it isn't bad comment that someone might do so.
If you did need to do so you could do a search replace.
>> Okay.
>> Now, I'm going to do a quick solution,
get that package in there,
and now I'm going to run that..NET Core version again.
For instance, once again with
Hunter search moment of
truth and now we have the owner's zip code.
>> Got it. So it's now handling
the encryption in the.NET core version as well.
>> Okay.
>> So this is true for
other features as well that you would see light up and like I
mentioned UTF-8 support data classification and more features
moving forward will be lit up through
this package versus SQL client.
>> Awesome. So we've
got a blog post that we'll put in the show notes.
You share the GitHub repo and you mentioned that by
the time someone's watching this it will either be in
general availability or should be fairly close to that.
>> Yeah. So it's going to be
coming into general availability pretty quickly
and the code will be showing up on GitHub very quickly as well.
So we're moving fast.
>> All right. Sounds great.
Well, that is the new SQL client that you have.
Notice that we've taken a lot of effort to make it as easy as
possible for you to be able to convert over and use
that new client as she demonstrated for us today.
We've got a link for you to visit so that you can
learn more about this and get started with your own projects.
That's aka.ms/sqlclient.
SQL client no-spaces that'll
take you directly where you want to go
>> Thank you so much for
sharing the client with us and for your time today.
>> Thank you.
>> Appreciate it.
[MUSIC]