SQLite in .NET

SQLite is a great database when you don't need the benefit of a full server-based database. You can use it in desktop or mobile applications, for small or medium sized web applications, or anywhere else you can think of. It's simple, but still has a rich feature set. Some of its behavior is a little bit quirky , but once you learn how to use it, you can use it in your application basically the same way as you would SQL Server or any other database. This video will walk through some of the basics, showing how similar it is to any other database when using .NET. View code on GitHub


.NET's DateTime struct is basically just a number with some properties and methods wrapped around it. It can represent a date and time, but no concept of where in the world that time is. The DateTimeOffset struct adds on a UTC offset, which means it now represents a given point in time (ignoring leap seconds - that screws things up). Suppose you have a DateTime of "2021-04-09 12:34:56.789". This is precise to the millisecond, but is that UTC time? Arizona time? Does it account for daylight saving time? Who knows? A DateTimeOffset includes the UTC offset, so "2021-04-09 12:34:56.789-0700" is much clearer - this is 7 hours before UTC time. You can decide what you want to do with it from there. Note that this is an offset, not a time zone - this doesn't account for daylight saving time or other weird scenarios. However, in my personal experience, unless you're strictly 100% local with no possibility of change, I would treat all times everywhere as

Non-buffered Output in ASP.NET

Most of the time, it makes sense to build a complete response and return it all at once. Sometimes, if you have a big result, like a large report, you’ll want to send the file a response a little at a time, to keep from storing a huge item in memory. You can write to a file and then output that file in the response, or you can dynamically write and flush the response a little at a time, until the response completes. public ActionResult GetFile ( ) { string [ ] lines = { " line 1 " , " line 2 " , " line 3 " , " line 4 " } ; Response . Clear ( ) ; Response . ClearHeaders ( ) ; Response . ContentType = " text/plain " ; Response . AddHeader ( " Content-disposition " , " attachment;filename=somefile.txt " ) ; Response . Buffer = Response . BufferOutput = false ; foreach ( string line in lines ) { Response . Write ( line + " \r\n " ) ; Re

Which version of .NET Core is installed

Turns out there’s a super easy command for seeing which version of .NET Core is installed on your desktop or server:  dotnet --info

Versioned content in MVC

When you add a script or stylesheet to your HTML page, those requests can be cached by the browser, potentially providing outdated content to the browser. If you’re not using a bundler or anything fancy like that, then the only way to prevent this problem is to create a brand new URL whenever your script file or stylesheet changes. This is easily accomplished with a querystring, so if your app was previously using app.js?v=1 and you change that to app.js?v=2, the browser will definitely not use the cached version, and will instead make a new request to the server, and pull the latest one. Using the following technique, you can guarantee that you’ll always pull the latest script and stylesheet when working in dev, and when in production, you can define a “version key”, possibly in a config file or database, and if you ever update just the static content, you would only need to update that key, and it would force all browsers to pull new content. public static class HtmlExtensions {

JSON infinite loops in ASP.NET

If you accidentally (or purposely) have an infinite loop in an object, where it has a reference that points back to itself, when you try to return that object as JSON in ASP.NET, you get an error: JsonSerializationException: Self referencing loop detected for property... To avoid that, you can add a line to your Startup ConfigureServices method: // dotnet add package Microsoft.AspNetCore.Mvc.NewtonsoftJson services . AddNewtonsoftJson ( options = > { options . SerializerSettings . ReferenceLoopHandling = ReferenceLoopHandling . Ignore ; } ) ;


SQL Server lets you execute dynamic SQL with the EXEC command. However, if you're accepting any user input as part of the query, you'll be subject to SQL injection attacks. The system proc sp_executesql gives you the ability to build a parameterized statement dynamically, and execute it, passing in the parameter values. As long as you're building the query safely, you won't be subject to SQL injection. View code on GitHub