// 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 '.net'
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.
Kevin Griffin is using SignalR to update web pages live. This episode is not sponsored! Want to be a sponsor? You can contact me or check out my sponsorship gig on Fiverr
Show Notes:
- It's been a long time since Kevin Griffin has been on the show. Check out 13 Kevin Griffin on Twilio
- Make sure to check out Kevin's entry on the Second Annual C# Advent
- AJAX was coined in 2005
- Some techniques we didn't cover: the Forever Frame, Server-Sent Events, also explained in a Kevin Griffin blog post on SignalR Transports
- Discourse
- Azure SignalR Service
- Swift Kick
Want to be on the next episode? You can! All you need is the willingness to talk about something technical.
Peter Lorimer has built the ASPeKT AOP framework. This episode is sponsored by Uncall.
Show Notes:
-
Other AOP frameworks discussed:
-
We use a bit of AOP terminology. If you need a refresher, check out the terminology posts that cover weaving, cross cutting concern, aspect, advice, pointcut, and join point
-
Book: Adaptive Code via C# by Gary McLean Hall
-
Mono.Cecil is not an AOP framework, but it is a tool to manipulate IL (aka MSIL aka CIL)
-
Unparalleled Adventure blog by Peter Lorimer
Want to be on the next episode? You can! All you need is the willingness to talk about something technical.
Want to be a sponsor? Check out my sponsorship gig on Fiverr
Chris Woodruff is building web APIs with ASP.NET Core. This episode is sponsored by Ivacy.
Show Notes:
-
IIS web server
-
Check out episode 94 with Jeremy Miller on Lamar for more discussion about dependency injection
-
Check out episode 22 with J. Tower on .NET Core for more about .NET Standard.
-
Chris’s baseball API
-
InfoQ stuff:
-
Project Rider from JetBrains
Want to be on the next episode? You can! All you need is the willingness to talk about something technical.
Want to be a sponsor? Check out my sponsorship gig on Fiverr
Merry Christmas! This is the last day of the C# Advent. Make sure to check out all of the other great posts from 2017 and 2018. If you want to be involved next year, look for C# Advent 2019 author sign ups at the end of October 2019, and look for blog posts to start showing up on December 1st, 2019.
What is a background job?
A background job is some code that runs apart from the normal flow of your program. It could be run asynchronously and/or on another thread. As an ASP.NET MVC developer, I tend to think of it as any task that runs outside of an MVC action being invoked.
There’s two kinds of background jobs that I’m aware of:
-
Scheduled - a task that runs every N minutes, or every Y hours, etc. This is what I’m going to show in this post today. It’s great for making periodic checks, ingesting data from some other source, etc.
-
Fire and forget - Some other piece of code kicks off a process to run in the background. It doesn’t block the code (fire), and the code doesn’t wait for a response (forget). This is great for potentially time consuming operations like checking inventory, sending emails, etc, that you don’t need a user to wait for.
What you usually need to do to create background jobs
In my experience, I’ve seen background jobs take a few different forms.
-
Separate Windows service (or Linux daemon, whatever). A console/service program that’s running in addition to your ASP.NET program. This works fine for scheduled jobs.
-
Queueing mechanisms like Kafka or Rabbit. The ASP.NET program will put messages into these queues, which will then be processed by some other program. This is fine for fire-and-forget.
-
Background jobs running within the ASP.NET process itself. In my experience, I’ve used Quartz.NET, which can run within the ASP.NET process. There’s also FluentScheduler (which I’ve not used, and doesn’t seem to come with database integration out of the box?)
With all these options in the past, I’ve experienced deployment difficulties. The wrong version of the service gets deployed, or isn’t running, or fails silently, or needs to be deployed on multiple servers in order to provide scalability/availability etc. It’s totally possible to overcome these challenges, of course. (I should also note that in my experience with Quartz.NET, I never used it in embedded form, and the last time I used it was probably 6+ years ago).
But if I just need a handful of background jobs, I’d much rather just make them part of the ASP.NET system. Yes, maybe this goes against the whole 'microservice' idea, but I don’t think it would be too hard to refactor if you decided you need to go that route. I solve my deployment problems, and as you’ll see with Hangfire (with Couchbase), it’s very easy to scale.
How hangfire works
You can find more details and documentation about Hangfire at Hangfire.io. Really, there are only three steps to setting up Hangfire with ASP.NET Core:
-
Tell ASP.NET Core about Hangfire
-
Tell Hangfire which database to use
-
Start firing off background jobs
In Startup.cs, in the ConfigureServices method:
services.AddHangfire(x => x.UseCouchbaseStorage(configuration, "familyPhotos_hangfire"));Then, in Startup.cs, in the Configure method:
app.UseHangfireServer();I’m using Couchbase in this example, but there are options for SQL Server and other databases too. I happen to think Couchbase is a great fit, because it can easily horizontally scale to grow with your ASP.NET Core deployments. It also has a memory-first architecture for low latency storage/retrieval of job data. Generally speaking, even if you use SQL Server as your "main" database, Couchbase makes a great companion to ASP.NET or ASP.NET Core as a cache, session store, or, in this case, backing for Hangfire.
The configuration variable is to tell Hangfire where to find Couchbase:
var configuration = new ClientConfiguration
{
Servers = new List<Uri> { new Uri("http://localhost:8091") }
};
configuration.SetAuthenticator(new PasswordAuthenticator("hangfire", "password"));(In my case, it’s just running locally).
Steps 1 and 2 are down. Next, step 3 is to create some background jobs for Hangfire to process. I’ve created an ASP.NET Core app to assist me in the cataloging of all my family photographs. I want my application to scan for new files every hour or so. Here’s how I create that job in Hangfire:
RecurringJob.AddOrUpdate("photoProcessor", () => processor.ProcessAll(), Cron.Hourly);Note that I didn’t have to implement an IJob interface or anything like that. Hangfire will take any expression that you give it (at least, every expression that I’ve thrown at it so far).
Step 3 done.
Hangfire is just a NuGet package and not a separate process. So no additional deployment is needed.
How do I know it’s working?
Another great thing about Hangfire is that is comes with a built-in dashboard for the web. Back in Startup.cs, in Configure, add this code:
app.UseHangfireDashboard("/hangfire", new DashboardOptions
{
Authorization = new[] {new HangfireAuthorization()}
});I’m using my own HangfireAuthorization implementation because Hangfire only gives permission to local users by default.
Then, you get a nice dashboard right out of the box, showing you a realtime and history graph.
From this dashboard, you can also look at a more detailed history of what’s run and what’s failed.
You can even kick off recurring jobs manually.
This is only the start
If you’re thinking about adding background jobs to your ASP.NET Core solution, why not give Hangfire a try?
Some more things for you to explore:
-
Scaling: every ASP.NET Core site that gets deployed with Hangfire that points to the same database will be able to process jobs too. As your ASP.NET Core site scales out, hangfire scales out with it. This is another reason that Couchbase is a good fit, because it’s also easy to scale out as your site grows.
-
Cloud: If you are deploying your site as an app service, note that Azure will shut down ASP.NET processes if they haven’t been used in a while. This means Hangfire will shut down with them. There are a couple of ways to deal with this. Check out the Hangfire documentation.
-
Retries: Hangfire will retry failed jobs. Design your background job code to expect this.
-
Hangfire Pro: The commercial version of Hangfire is called Hangfire.Pro, and it comes with some interesting looking batch capabilities. I’ve not needed any of this functionality yet, but for more advanced cases you might need this.
-
Couchbase: a NoSQL data platform that has a built-in memory-first cache layer, SQL support, text search, analytics, and more. There are lots of options for working with Couchbase in .NET. For this post, I used the Hangfire.Couchbase library (available on NuGet).
