// SqlServerFrom.cs
public IEnumerable<dynamic> QueryBulk(IDbConnection conn, SqlPipelineBase pipeline)
{
return conn.Query(pipeline.Query, buffered: false);
}
// SqlToCb.cs
foreach(var row in rows)
{
// ... snip ...
await collection.UpsertAsync(documentKey, row);
// ... snip ...
}Posts tagged with 'csharp'
Welcome to day 16 of the 2021 C# Advent! Make sure to check out all the other great Advent items that have been opened so far!
I have been working on an experimental tool called SqlServerToCouchbase. The goal is to help people automate their relational data moving and refactoring into a Couchbase JSON database as much as possible.
It is a .NET library that you can use (in, for example, a console project). It maps a relational concept like "table" to a NoSQL concept of "collection" (among other things). Couchbase is particularly suited to this, because Couchbase also supports SQL as a querying language (with JOINs / ACID / INSERT / UPDATE / etc), and has supported SQL for many years. If that sounds interesting to you, I’d love for you to leave your feedback, criticisms, suggestions, and even pull requests on GitHub.
What I want to focus on today, however, are three great .NET libraries that I used to help build SqlServerToCouchbase. Three wise gifts: SqlServer.Types (gold), Dynamitey (frankincense), and Humanizer (myrrh).
dotMorten.Microsoft.SqlServer.Types (Gold)
The gift of gold signified that the receiver was as important as a king.
SQL Server has many data types. Mapping these data types into C# types (and ultimately to JSON) is usually straightfoward.
-
varchar, nvarchar, text? string. -
int, float, decimal, money? number. -
bit? boolean. -
Even XML can become a string.
But what about the other types? Spatial types, mainly: Geography and geometry? That’s what Microsoft.SqlServer.Types is for: to provide C# types that can store propietary SQL Server data type values.
However, notice the "dotMorten" part of the library name? Unfortunately, the official Microsoft.SqlServer.Types library is not a .NET Standard library. So, Morten Nielsen created the dotMorten.Microsoft.SqlServer.Types library.
There’s a code example below, but you won’t see the library in action explicitly.
I use Dapper to query SQL Server data, store those results in C# dynamic objects, and then give those objects to the Couchbase .NET SDK (which ultimately serialized it to JSON).
That means that a row of SQL Server data, like this:
SELECT a.AddressID, a.SpatialLocation
FROM AdventureWorks2016.Person.Address a
WHERE a.AddressID = 1
Gets transformed into a Couchbase JSON document like this:
SELECT a.AddressID, a.SpatialLocation
FROM AdventureWorks2016.Person.Address a
WHERE a.AddressID = 1;[ {
"AddressID": 1,
"SpatialLocation": {
"HasM": false,
"HasZ": false,
"IsNull": false,
"Lat": 47.7869921906598,
"Long": -122.164644615406,
"M": null,
"STSrid": 4326,
"Z": null
}
} ]So, even if a SQL Server database is using one of these less common data types, SqlServerToCouchbase can still move it.
Dynamitey
The second gift is frankincense. This is an expensive incense fit for a holy king.
Another challenge of SqlServerToCouchbase is getting the value of the primary key. In Couchbase, a document key exists as a piece of "metadata" about the document. However, in SQL Server, a primary key consists of one (usually) or more (uncommon) fields in a table. These fields can have ANY name. Usually it’s something like "ID", "AddressID", "ADDRESS_ID", etc. But it can vary from table to table.
Once I know the names of the fields, I need to examine the dynamic object to get the values of those fields. This is where I use Dynamitey.
Dynamitey is a utility library that provides extensions to the DLR, including:
-
Easy Fast DLR based Reflection (what I’m using it for)
-
Clean syntax for using types from late bound libraries
-
Dynamic Currying
-
Manipulation of Tuples
And more.
Key names can be retrieved from SQL Server by querying INFORMATION_SCHEMA.KEY_COLUMN_USAGE. I can use those names to get the values like so:
// append key values together with :: delimeter
// for compound keys
var keys = await _config.GetPrimaryKeyNames(tableSchema, tableName, _dbFrom);
var newKey = string.Join("::", keys.Select(k => Dynamic.InvokeGet(row, k)));If a primary key is made up of one column and the row has a value of "1", then that becomes the document key in Couchbase. If a primary key is made up of multiple columns, with values of "123" and "456", that becomes a document key in Couchbase of "123::456".
If it weren’t for Dynamitey, I’d have to create C# classes for every table. And that greatly reduces the amount of automation.
Humanizer
The third gift is myrrh. Another expensive gift. This one is fit for a holy, but also human king.
Humanizer is a .NET library that manipulates string, dates, numbers, etc, for display to a human. There are many things it can do, but I use it for pluralization.
When making the transition from relational to Couchbase, one of the things you must consider is when to embed data into documents. For instance, in relational, you may have two tables (Person and EmailAddress) in order to support a situation where a person has more than 1 email addresses.
SELECT p.BusinessEntityID, p.FirstName, P.LastName
FROM AdventureWorks2016.Person.Person p
WHERE p.BusinessEntityID = 1
SELECT e.EmailAddress
FROM AdventureWorks2016.Person.EmailAddress e
WHERE e.BusinessEntityID = 1
(In this example, there’s only 1 email address, but the model supports more).
In a document database like Couchbase, it’s often preferable (though not required) to embed those email addresses into an array in the person document. Something like:
{
"BusinessEntityID" : 1,
"FirstName" : "Ken",
"LastName" : "Sánchez",
"????" : [
{ "EmailAddress" : "[email protected]"}
]
}But what do I put into the "????" in that JSON? If I use the name of the table ("EmailAddress"), that implies that there’s only one. I would rather it be called "EmailAddresses". Hence, I use Humanizer to pluralize it:
spec.ArrayAppend(From.TableName.Pluralize(), docToEmbed.ContentAs<dynamic>(), true);So, now it becomes:
{
"BusinessEntityID" : 1,
"FirstName" : "Ken",
"LastName" : "Sánchez",
"EmailAddresses" : [
{ "EmailAddress" : "[email protected]"}
]
}Thanks for checking out these three libraries! I hope these will help you some day. Don’t forget to check out the rest of the 2021 C# Advent.
Short version:
The 2021 C# Advent is now open! If you've participated before, I suggest you visit https://csadvent.christmas to claim your spot right away!
Long version:
The C# Advent is an annual event, showcasing content (blog posts, videos, articles, podcast episodes, whatever!) from C# developers and enthusiasts, every day, from December 1st to December 25th.
Each day will feature two pieces of content. Each piece of content links back to the main C# Advent site, kinda like an old fashioned webring.
What do I have to do to participate?
Right now: just sign up. Go to https://csadvent.christmas, look for an open spot, and claim it. You'll receive an email with a special link. Click that link, fill out the form, and you're in! You do NOT need to tell me a title, topic, idea, or anything like that at this stage.
What do I do after I claim a spot?
Work on some content. It must be C# related and it must link back to https://csadvent.christmas, but otherwise, it can be whatever you'd like. Historically, it's mostly blog posts, but there's also been videos and podcast episodes. Any content that can be linked to is fine with me!
Do I give you the content to publish?
No! Publish it on your website, YouTube channel, podcast, whatever. That's the whole point: to help you get attention and noticed by the C# community.
When must I have it finished?
Technically, you don't need to have it done until the day that you claimed. I'd recommend that you get it done before then, because December is a busy holiday time for many of us. Just don't make it public until the day you signed up for.
What does "Advent" mean?
Advent is a season leading up to Christmas Day. Advent Calendars mark the days until Christmas. For children, the marking of each day might include a piece of candy or a small toy.
What are some good examples of C# Advent content?
ANY and ALL content that's related to C# is good in my book. The point is to promote the community, our love of C#, and to lift everyone up! However, if you're looking for some good examples to check out, here are a few that I really like:
- Combining Integration and UI Automation in C# from Hilary Weaver-Robb - the very first one happens to be one of the best
- The .NET Core Podcast: Noda Time with Jon Skeet - a podcast episode with one of the top C# names in the community
- Blazor Advent Calendar - a video about creating an Advent Calendar that is, itself, part of an Advent Calendar
But remember: you don't have to do anything flashy! If you care about C# and you've got something you want to share, I want you to claim a spot!
How long have you been doing this?
The first C# Advent calendar was in 2017.
You're out of open spots! I missed it!
Maybe not! December can be a hectic month, and sometimes people need to drop out. If you want to be on standby, contact me. Being on standby is one of the hardest jobs, since people may drop out with very little notice. But, if you are up to the task, I very much appreciate it, and I'm willing to accomodate or help you in any way I can.
This year's Advent is going to be a little different that the Advents in 2019, 2018, and 2017.
This year, the C# Advent has its very own site!
Not only that, but the process is more automated this year. Gone are the days of me having to maintain an Excel spreadsheet.
Step 1:
Go to www.csadvent.christmas. Find an open spot in the Advent, and click to claim it. You will need to login with GitHub. There are two spots every day, for a total of 50 spots.
Step 2:
Fill out the request form. You'll need a name and email at least. You can also supply a URL for the link for your name (e.g. if people click "Matthew Groves", I want it to go to my Twitter). You can also add an optional comment. Only the Name and URL will appear publicly! Click submit.
Step 3:
You should get a confirmation email. I will get an email too, so I can go in and approve your request (approval is a necessary, because there could be a date conflict, or there could be spammers, etc).
Step 4:
When approved, you will get another email. Approval should be pretty quick if I'm awake (I'm in the eastern US time zone).
Step 5:
Create your content. Don't publish it until the day you selected. Make sure to link back to www.csadvent.christmas. At any point, you can login to the Contributors Area to supply a link and title of your content.
You do NOT have to do this until the day you picked. However! You will receive an automated email reminder 2 days ahead of time.
Step 6:
When it's your day, tell everyone about your article and the C# Advent! Tweet them! Submit others to Reddit! Facebook! LinkedIn! Whatever else! Let's show the world how great the C# community is! Please use the #csadvent hash tag when applicable.
Thank you! Looking forward to another great Advent in 2020.
Note from Matt: This is a guest post by James Bender for the Third Annual C# Advent.
For those who don’t know me, I’m that guy. You know the type; the ones who is super OCD about their code, stresses component-based architecture that encourages reuse, and who’s into Unit Testing. I think a lot of problems in the world could be solved if we all just practiced Test Driven Development.
So, when Matt reached out to me about writing this post, the first thing I thought of was unit testing! And so that’s what this post will be about!
Everyone: GROOOAAAANNN
Hey! When you all start unit testing, I’ll stop harping about it!
While I prefer the idea of TDD, I understand that it isn’t always an option. People who are just starting off with unit testing might not feel comfortable with the “test-first” paradigm, which is totally understandable and completely fine. Some people (like myself) often inherit codebases where the code is already there, but there is not a test to be found. Sometimes, if feels like it would take a miracle to make these code bases even testable!
Behold, a Christmas Miracle!
Well, I may be overselling the whole “miracle” aspect, but this will still be pretty cool. We’re going to take some code and refactor it to make it more testable.
Here is our starting point:
This is an amalgamation of a lot of the problems I see with code that limit its testability. To be fair, this code as a lot of issues, but today we’ll just deal with the ones that limit its testability.
There are a variety of ways to deal with refactoring this, and not everyone will approach it the same way. In my case, the first thing I’m noticing is that the function we’ll be refactoring (ProcessDataExtract) doesn’t return a value. This can make it difficult to determine if my test passes; if I don’t have any output, how can I verify the method worked? Yeah, I could create a mock of the exportChannel object and ask it if its Save method was called, and what data it was called with. But this isn’t the best approach. I’m relying on a supporting object (the mock) to provide basic functionality that my test should be able to do without help. And what happens if there’s a change to ExportChannel causing me to change my mock? This makes this test difficult to maintain. So, for my first refactoring, I’ll be changing the return type:
On line 10, I change the function signature to return an IList of type string. I added a return statement on line 33 to return the results list that is sent to the Save method of the ExportChannel. This enables me to interrogate the results list directly to verify my test. It also makes ProcessDataExtract itself more useable by being able to provide (return) data as opposed to seemingly swallow it up.
The next thing I notice (frankly, it’s kind of hard to miss), is that I have some static dependencies. On line 8 I have a class-level reference to the DataStore service, and at line 12 (in the method body) I have another one to ExportChannel. These have got to go!
Those of you who might be new to testing might be asking why this is a problem? You’ve probably seen dozens of tutorials showing this being done. The problem is that this creates a very brittle piece of software that cannot adopt well to change. If I wanted to add a constructor argument to DataStore I would have to make a change everywhere this service is invoked. That could be dozens, if not hundreds of places. Another issue, which has a direct impact to testability, is that I may eventually have a situation where I want to use different specialized versions of this service in different situations. Creating a direct static dependency to DataStore in this manner makes that almost impossible.
In a testing environment I don’t want to use the actual production implementation of these services. They may make my test slow. Things like I/O operations, web service calls, and database access are notoriously slow. These are crucial for running the application but make testing difficult.
Another concern is that when I write a test, I ONLY want to test the specific code in my method; not the code in the dependencies, which should have their own specific tests. To solve these issues, when testing this code, I want to use mocked versions of these services. Mock are basically stand-in objects that can return canned values that the code can use to verify its logic. Mocking can be a very involved topic, and so I won’t got into a deeper discussion here. If you would like to learn more, please see the links at the end of this post.
But, with statically bound dependencies, how do I get the code to use my mock objects instead of the real ones when I want to run a test? The answer is to refactor our code to use Dependency Injection:
In this refactoring, I have added a constructor to the AppEngine class which takes a DataStore as a parameter. The consumer of this class will pass in an instance of DataStore (or an object derived from DataStore), which my class can use. Additionally, I added parameter of type ExportChannel to the method ProcessDataExtract. This again requires that the calling method provide an object of type ExportChannel. This enables me to pass in mocked objects as part of my test; my test will be the consumer of the AppEngine class and will be calling the ProcessDataExtract method.
As alluded to above, this not only enables to me to pass-in mocked values for testing, but also allows me to pass in specialized versions of those classes based on whatever my current context is, which enhances code reusability. This is especially evident with the ProcessDataExtract method. I may need to run several types of extracts with this method that would only very by the type of ExportChannel. Now, I can reuse this method and pass in whatever ExportChannel I want. This is similar to the Strategy Pattern, which you can read more about here.
So, this method is already looking a lot better. You might even think that we’re done, and there’s no way we can improve the testability of this method. But there’s still one problem. It’s subtle.
Do you see it?
Hint: What happens if I run this test now, and then run it again in an hour? Am I guaranteed to get the same results?
Look at line 17. This line creates a problem for me. The ProcessDataExtract function sorts records based on the current time. If I am using a mock of DataService to pass in a static, canned set of records the code is going to compare the current time captured on line 17 to the time in my test records. Time goes on, but my test data stands still, and eventually, my results are going to change, which could cause my test to act unpredictably.
When creating testable code, a good practice is to abstract away anything that might change. In this case, the value of DateTime.Now changes constantly. So, we need to take it out of our equation, at least for testing:
To solve this issue, I have removed the call to DateTime.Now on line 17 and made currentDate a parameter for the ProcessDataExtract function. This means that when I run my tests, I can pass in any date I want, which means I will be getting consistent test results. Like the previous refactor, it also enhances the reusability of this method. I’m no longer bound to the current date. I can run it for past dates and future dates without having to create a separate method.
And that’s it! With three easy-ish steps we took a piece of untestable legacy code and made it testable! Now, I know this was small example. But the concepts don’t care about the size of the code; there may be more work to do for some of these in larger code bases, but you will still be able to use these techniques to handle most refactoring situations.
Shameless plug: I you would like to learn more, please check out my book, which is available on Amazon.
Happy Holidays!
What is the C# Advent?
The C# Advents in 2018 and 2017 were so much fun. It's now time to sign up for 2019.
Just like last year, each day of the Advent calendar will have up to TWO blog posts. That means that there is a maximum of FIFTY slots! So, tell your C# friends and let's fill up this calendar.
A little history: I heard about the F# Advent Calendar, a tradition that's been carried on since 2010 (2014 in English). I think this is a great idea, and so I organized one for C#! (I asked Sergey Tihon for permission!). If you are running other developer advent calendars, just let me know and I will link to them here:
Let's Get Started
I need you to write a C# blog post, create a video, write an article, etc.
Here are the rules:
- Reserve a slot on Twitter (with hash tag #csadvent) or leave a comment on this post. You do not have to announce your topic until the day you reserve.
- Prepare your content (in English).
- Add a link in your content that links back to here, so that your readers may find the entire advent. You can host your content anywhere you'd like: your own site, dev.to, hackernoon, medium, wordpress, youtube, dzone, etc.
- Publish your content on the specified date. Your content must be related to C# in some way, but otherwise the content is completely up to you. I've posted a few ideas below to get your creativity flowing.
- Share your post on Twitter with hashtags #csharp and #csadvent
Below are all the slots, and who has claimed each date.
I will do my best to keep this up to date. The slots will be first come first serve. I also allowed last year's authors to get first crack. I will claim the last remaining spot for myself. I will prepare a post just in case someone has to drop out.
Alternates:
- IF ALL FIFTY SLOTS FILL UP, please leave a comment or tweet with #csadvent anyway and I'll put you on this standby list:
- Standby list:
- Myself.
- You, if you want to be.
Some ideas/topics to help inspire you:
- Blazor - C# for the browser
- Your latest open source contribution - show the community how you contributed and why
- Your favorite C# language feature - it doesn't even have to be a new feature, just blog about something you love about C#
- Introduce your favorite NuGet package / library. Even if it's a library you take for granted, not everyone has heard about it.
- How to avoid a pitfall you found with performance/memory/etc
- Integration/deployment of a C# application with Jenkins, Docker, Kubernetes, TeamCity, Azure, etc
- Write a "how to" for one of the many tools discussed in an episode of the Cross Cutting Concerns podcast or the DotNetBytes podcast
- Interview someone about C# and post the audio or a transcript.
- Implement a simplified example of a design pattern in C#
Thanks to everyone who is participating!
