C# – Mapping nullable columns with SqlDataReader

When you have a database table with null columns, and you’re using SqlDataReader to map the results, there are a few things to do: Use nullable types in the model class. Example: instead of int use int? so the value can be null. Use SqlDataReader.IsDBNull() to check if you need to set the value to … Read more

C# – Using SqlDataReader to process multiple result sets

In this article I’ll show how to use the SqlDataReader ADO.NET class in two scenarios involving multiple result sets: Batches – When you execute multiple SELECTs in a single query. Each SELECT returns a different result set. You use a single reader to process the batch. Nested Queries – When you use multiple SqlDataReaders on … Read more

Modifying JSON in SQL Server

There are two ways to modify JSON data in a SQL Server table: Use the JSON_MODIFY() function in an UPDATE statement to change individual properties inside the JSON data. Completely replace the JSON data with a regular UPDATE statement. In this article, I’ll show how to insert, update, and delete JSON data by using the … Read more

Querying JSON in SQL Server

In SQL Server 2016 they added support for handling JSON data. You can add JSON data, query it, and modify it. The JSON API in SQL Server is well-suited for simple scenarios where you only need to do basic querying and manipulation. For advanced querying, the SQL Server JSON API gets complicated and hard to … Read more

SQL Server – Foreach Table

To execute a query against all tables in a database, you can use the sp_msForeachTable stored proc, like this: Note: The ? is a placeholder for the table name. In this article I’ll show two examples of using sp_msForeachTable. Example – Get record counts for all tables This query saves record counts to a temp … Read more

SQL Server – Get the next id using OUTPUT instead of IDENTITY or SEQUENCE

There are multiple ways to increment ids in SQL Server: IDENTITY columns SEQUENCEs (added in SQL Server 2012) Atomic UPDATEs with the OUTPUT clause In this article I’ll show the third way – using an atomic UPDATE with OUTPUT. Tables – Ids and Users I have the following two tables – Ids and Users: Stored … Read more

PowerShell – Saving SQL query results to a CSV file

Here’s how to execute a SQL query and export the results to a CSV file: If Invoke-SqlCmd is missing, install the SqlServer module If it’s complaining about not having Invoke-SqlCmd available, you will need to install the SQL Server PowerShell module. 1 – Run PowerShell as administrator 2 – Add Microsoft’s PSGallery as a trusted … Read more