sql-select

C# – How to unit test code that uses Dapper

by

Dapper makes your code difficult to unit test. The problem is that Dapper uses static extension methods, and static methods are difficult to mock out. One approach is to wrap the Dapper static methods in a class, extract out an interface for that wrapper class, and then dependency inject the wrapper interface. In the unit … Read more

SQL Server – Bad SELECT query performance due to missing indexes

by

When you have a slow SELECT query, the most likely cause of the problem is that you’re missing an index. When your table has lots of data, having the right indexes will make a difference in the performance. Without indexes, SQL Server has to scan through your entire table to find the rows that match … Read more

C# – Using SqlDataReader to process multiple result sets

by

In this article I’ll show how to use the SqlDataReader ADO.NET class in two scenarios involving multiple result sets: I have a StreamingService database that has Movies, Shows, and Episodes tables (linked to the shows). First I’ll show the model classes I’m mapping the data into. Then I’ll show the two scenarios where I’m using … Read more

Querying JSON in SQL Server

by

In SQL Server 2016, Microsoft added support for handling JSON data: 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 use, so you may want to look into other options. … Read more

Using sp_msForeachTable in SQL Server

by

You can use the sp_msForeachTable stored proc in SQL Server to execute a query/command against all tables in the database. To give you an idea about how to use this, here’s an example of using it to do a SELECT query against all tables: Internally, sp_msForeachTable gets all user table names (from dbo.sysobjects) and opens … Read more

PowerShell – Saving SQL query results to a CSV file

by

With PowerShell, you can execute a SQL query with Invoke-Sqlcmd. If you want to save the query results to a CSV file, you can use Export-Csv. Here’s an example: 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. … Read more

SQL – Filtering GROUP BY with HAVING and WHERE

by

There are two ways to filter GROUP BY results: In other words, WHERE controls which rows will be considered for grouping, GROUP BY groups the filtered rows, and then HAVING filters the groups. I’ll show examples below. Example – Using WHERE with GROUP BY Let’s say you have the following table and you want to … Read more

C# – How to use IN with Dapper

by

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: Exclude the parentheses In a normal SQL Query you … Read more

Conversion failed when converting date and/or time from character string

by

Problem When you’re working with date/times in SQL and want to put a date column in ORDER BY with a CASE statement, you may run into the following error: Conversion failed when converting date and/or time from character string. Here’s an example of a query using ORDER BY with a CASE that produces the error: … Read more

‘CREATE/ALTER’ must be the first statement in a query batch

by

Problem When you’re executing a CREATE/ALTER statement to create a procedure/view/function/trigger, you get one of the following errors: ‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch ‘CREATE VIEW’ must be the first statement in a query batch. ‘CREATE FUNCTION’ must be the first statement in a query batch. ‘CREATE TRIGGER’ must be … Read more

Find the distance between two coordinates using SQL and C#

by

Imagine you’re in the middle of Millennium Park in Chicago. You want some coffee, but want a good deal on it. You open an app and see the nearest coffee shops offering deals to anyone with this app. How did this app know the distance from you to the coffee shops? This article will show … Read more

SQL – Sort into groups, then sort within groups

by

I recently came across a complex sorting problem that required sorting the data into groups, then further sorting the data within each group. I’ll explain this problem with a concrete example, and then I’ll show the SQL query I used to solve the problem. Let’s say we have orders, departments, and users. Users can be … Read more