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# – Mapping nullable columns with SqlDataReader

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 C# – Using SqlDataReader to process multiple result sets

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 Modifying JSON in SQL Server

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 Querying JSON in SQL Server

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 – Foreach Table

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 SQL Server – Get the next id using OUTPUT instead of IDENTITY or SEQUENCE

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 PowerShell – Saving SQL query results to a CSV file

SQL – Use HAVING to filter GROUP BY

WHERE filters individual rows. HAVING filters aggregated rows. When you use GROUP BY, you are grouping rows together into aggregated rows. How do you filter the aggregated rows? You use the HAVING clause. Example of using HAVING Let’s say we want to see all NFL divisions where all teams have played in the Super Bowl … Read more SQL – Use HAVING to filter GROUP BY

C# – How to use IN with Dapper

Let’s say you have a SQL Query that uses IN and you want to execute it using Dapper. Your query looks something like this: Here’s how you’d execute that with Dapper: Then you’d call it like this: There are two key things to notice about this: You have to exclude the parentheses. I specified one … Read more C# – How to use IN with Dapper

SSMS – Change how many rows are returned by SELECT Top 1000 Rows

When you right-click a table in SSMS, you can “Select Top 1000 Rows” and “Edit Top 200 Rows.” You can change how many rows are returned by changing the defaults. Tools SQL Server Object Explorer Change the values under Table and View Options Change these values to whatever makes sense in your situation. Note: if … Read more SSMS – Change how many rows are returned by SELECT Top 1000 Rows