.Net Foundation On .NET, 6 Nov 2019

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Working with the new Microsoft.Data.SqlClient

Description

Introducing the new Microsoft.Data.SqlClient
https://devblogs.microsoft.com/dotnet/introducing-the-new-microsoftdatasqlclient?WT.mc_id=ondotnet-c9-cephilli

Microsoft.Data.SqlClient GitHub repo
https://github.com/dotnet/SqlClient?WT.mc_id=ondotnet-c9-cephilli

Client programming for SQL Server documentation
https://docs.microsoft.com/en-us/sql/connect/homepage-sql-connection-programming?view=sql-server-ver15&WT.mc_id=ondotnet-c9-cephilli

In this episode, Vicky Harp joins Jeremy to talk about the new Microsoft.Data.SqlClient. They discuss why this new package was created, feature scenarios it supports, and also show off some demos of how to migrate.

[01:20] - Why is there a new SqlClient package
[03:54] - How do the features compare to the previous SqlClient support?
[06:33] - Moving to Microsoft.Data.SqlClient Demo

#dotnet #dotnetcore #azuresql

A

Big news., the sql client, is moving to nuget.

A

This is going to help you keep pace with new features, regardless of if you're on.net framework, or.net core.

A

check this out on on. net.

A

Hello, and welcome to another episode of on.net.

A

I'm your host jeremy likness and today, I'm with vicky harp,.

A

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,.

A

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.

B

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.

B

that includes the drivers,, the programmability interfaces,, the command line, tools, the gui tools like sql projects in visual studio, ssms, azure data, studio,.

B

And so really the whole lot.

A

That's a lot.! So if I'm a developer, I'm.net.

B

I've probably touched sql server,.

A

And what you're saying is if I touch sql server, I've probably touched the tools that you're working with?.

B

Yes, absolutely, millions of people in fact.

A

Amazing. so I understand there's some changes coming through.

A

We're obviously shifting in the.net arena.

A

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..

A

What are the changes and why would I even care, as a.net developer?.

A

Yeah. so just definitionally,.

B

Most people who are using.net, who are interfacing with.

B

Sql server they're using the sql client driver and that's in system.data.sqlclient.

B

Now, either they're using that directly and using data readers, etc,.

B

Or they're using something like an orm or framework which is in turn using that sql client driver.

A

Okay., that has always been.

B

The premier interface and assembly for communicating with sql server.

B

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,.

B

And so we have a really rich ecosystem of that..

B

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.

B

okay.. Then the sql client in.net core is a separate version of sql. Client.

B

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.

B

In addition to that, sql server itself is shipping more frequently and is itself now able to run on linux and on docker containers,.

B

And so we've got a changing environment where we are wanting to be able to ship more quickly.

B

okay.. So to that end, we now have a nuget package called.

B

Microsoft.Data.Sqlclient, which has all of.

B

The client functionality that was in system.data.sqlclient.

B

But it's in a package that you can consume on your own and which can ship independently of the framework.

B

now this package that I can consume on my own,.

A

Is this a different set of apis or a different.

A

Approach to using the client or is it intended to be a swap out?.

A

It's very much intended to be a swap out.

B

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,.

B

All of the object references all of these assemblies are the same,.

B

And so you should be able to just change you're using statements and any qualified references, and it would take effect immediately.

A

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?.

B

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.

B

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..

B

It's been available in full framework..

B

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,.

B

Which are highly requested features related to sql server, 2019.

B

So the notion is that going forward we'll be shipping new features of sql server that have a driver or assembly level consideration.

B

In the nuget package in microsoft.data.sqlclient.

B

Whereas traditional system.data.sqlclient, we maintained because there are millions of users of it.

B

sure., you may not need that new function, yet,.

B

But it'll be available for them when they're ready.

B

okay.. So if I'm fine, where I'm at,, I can stay.

A

But it's a very low pain point as far as.

A

The migration: that's the intention.

B

Because it's just a name space, change.

A

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.

A

correct., so you're not having to.

B

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.

A

That's a package that's available today that I can get as a preview? yes,, so it's available.

B

We have it on the nuget gallery and it's also available on github.

B

A

So that it's in.

B

Preview now and it will be going to ga in pretty short order.

A

Okay. great. now,. I understand you have some examples that you can share about using the new client?

A

yes, absolutely.. So just real, quick I'll show you.

B

Is this the github location?.

B

So it's at.netsqlclient.! You can give that a search.

B

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..

A

B

So this has the release notes..

B

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..

B

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.

B

Okay., so what I've got here in terms of a demo is a wpf app and it's just the simplest little app.

B

Basically,, this is a little animals services, app where you can get.

B

when they pick up a dog and they've got than just the name,.

B

For some reason they don't have their actual license on there..

B

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.

B

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.

B

A

Relatively straight forward, little data.

B

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.

B

So I'm going to run the full framework version.

B

Instance, here. okay, wait a moment.

A

You know the trick, right?, you move the mouse clockwise.

B

Yes, and it makes it go, faster. speeds it up, makes the hamsters run faster.. Here we go.

B

Yeah exactly., so let's just look up hunter., let's see how many dogs we have named hunter.

B

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.

B

okay., you see that works perfectly well.. Now, if I go down here in the.net, core version,.

B

We're going to see a little bit of a different response here..

B

So this is a case where we're assuming that someone is trying to convert to.net core for the first time.

B

So they've run into this, and so the first time they run it, they get it all set up..

B

We run hunter and we're going to get.

B

Search here. get error, keyword, not supported column encryption, setting.

B

okay.. So what does that mean?? So if I take a look over at the table,.

B

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.

B

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.

A

B

If I go over here and change the see.

B

The column encryption setting enabled that's enabling net feature.

B

So if I change connection strings,, then I can rebuild it,.

B

And now we don't have that column, encryption, enabled,.

B

And let's see how that looks. we'll look up hunter gain.

B

Give it a search, and.

B

We're able to get data, but it's a byte array., so we're at least seeing.

A

Rows but we're still not seeing some code?.

B

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.

A

You're protecting the personally identifiable data.

A

B

Sensitive data in the table.

A

So what we're needing to do in.

B

This application is to switch over from system.data.sqlclient to microsoft.data.sqlclient, so.

B

That the.net core version will have this functionality.

B

So I'm going to switch back to our other connection string.

B

Here and then walk you through the process. so up here. First, I'm going to go to my manage nuget packages.

B

Go over here to browse and type in microsoft.data.sqlclient.

B

Make sure you've included pre-release in this case.

B

Right. then hit "install". give that just a second to install that,.

B

And "okay" "accept".

A

Sign your life away.

B

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..

B

So I'm going to nuget packages.

B

Again, "install", okay, "accept",.

B

And now that it's installed for both.

B

Then, when I go back over to this main window xaml,, we see system.data.sqlclient.

B

I'm going to change that to microsoft.data.sql client,.

B

And that is in fact it.

A

So you weren't kidding., it's just that one line: change, basically.

B

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..

B

If you did need to do so, you could do a search, replace., okay. now,, I'm going to do a quick solution,.

B

Get that package in there,.

B

And now I'm going to run that..net core version, again.

B

For instance,, once again with.

B

Hunter search moment of.

B

Truth and now we have the owner's zip code.

B

got it., so it's now handling the encryption in the.net core version as well.

A

B

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.

B

Moving forward will be lit up through this package versus sql client.

A

Awesome., so we've got a blog post that we'll put in the show notes..

A

You share the github repo, and you mentioned that by.

A

The time someone's watching this it will either be in general availability or should be fairly close to that.

A

yeah.. So it's going to be.

B

Coming into general availability pretty quickly and the code will be showing up on github very quickly as well.

B

So we're moving fast. all right. sounds great.

A

Well,, that is the new sql client that you have.

A

Notice that we've taken a lot of effort to make it as easy as.

A

Possible for you to be able to convert over and use that new client as she demonstrated for us today.

A

We've got a link for you to visit so that you can learn more about this and get started with your own projects..

A

That's aka.ms/sqlclient., sql, client, no-spaces, that'll,.

A

Take you directly where you want to go.

A

Thank you so much for sharing the client with us and for your time, today.

A

thank you., appreciate it.