C# – Use SqlDbType.Structured for table parameters

To use a table-valued parameter (TVP) with SqlCommand (ADO.NET), pass in a DataTable as a SqlDbType.Structured parameter, like this: I’ll show a full example, starting with creating a TVP type and then inserting it as a SqlDbType.Structured parameter as shown above. 1 – Create the TVP type in SQL Server To be able to pass … Read more

SQL – Aggregate functions with GROUP BY

There are five main aggregate functions in SQL: COUNT(), SUM(), AVG(), MIN(), and MAX(). These calculate aggregate values for sets of rows. When you use them with GROUP BY, they calculate the aggregate values for each group. Let’s say you want to get movie streaming stats for each user. Here’s an example of using GROUP … Read more

SQL – Using GROUP BY with ORDER BY

GROUP BY groups rows based on one or more columns. ORDER BY sorts rows. You can use these together to sort groups. There are two options you can use for sorting groups: I’ll show examples below. At the end, I’ll talk about how SQL Server may implicitly sort groups (and why you shouldn’t depend on … Read more

SQL – How to use GROUP BY

You can use GROUP BY to group rows based on one or more columns. This produces one row per group in the query results. Each group contains the grouping columns and the result(s) of any aggregate function(s) (COUNT/MIN/MAX/AVG/SUM) you want to use on the group. Let’s say you have the following table and you want … Read more

Error: Host is not allowed to connect to this MySQL server

Problem When you try to connect to MySQL remotely, you get the following error: Host <hostname or IP> is not allowed to connect to this MySQL server In MySQL, hosts are allowed (whitelisted) per user. So this error means the user you’re trying to connect with doesn’t have your remote host whitelisted. You’re likely trying … Read more

C# – Connect to a MySQL database

The simplest way to connect to a MySQL database in a .NET project is to use the MySql.Data package (from Oracle). It provides classes that implement the standard ADO.NET interfaces (such as IDbConnection). First, add the MySql.Data package to your project (this is using View > Other Windows > Package Manager Console): Now use the … Read more

SQL Server – Copy data from one table to another

To copy data from one table to an existing table, use INSERT INTO SELECT and specify the column list: If the columns are the exact same in the two tables (and there are no identity columns), you don’t need to specify the column list: When the table doesn’t exist, use SELECT INTO, specifying which columns … Read more

Get SQL Server query results as JSON

The simplest way to get query results as JSON is to use FOR JSON PATH in the query (note: this was added in SQL Server 2016): It returns the results as a single JSON string with one JSON object per row: Note: SQL Server returns the JSON without indenting. All examples in this article show … Read more

SQL – Select from information_schema.columns

You can query the information_schema.columns system view to get information about all columns in the database. Here’s an example: Here’s the first few rows this returns: TABLE_NAME COLUMN_NAME Movies Id Movies Title Movies Year In this article, I’ll show more examples of querying information_schema.columns. Get columns filtered by name Let’s say you want to find … Read more

Categories SQL

Case sensitivity in SQL Server

In SQL Server, the collation property controls case sensitivity. Case sensitivity affects sorting and queries (even the column names must match exactly if you’re using a case-sensitive collation at the database-level). The default collation when you create a database in SQL Server is SQL_Latin1_General_CP1_CI_AS. The CI stands for case-insensitive, which means SQL Server databases are … Read more

SqlException: Cannot insert explicit value for identity column

When you have a table with an identity column, and you try to specify the value for identity column when inserting a record, you’ll get the following exception: Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table ‘Movies’ when IDENTITY_INSERT is set to OFF. This error means you have an identity column in … Read more

Cannot drop database because it is currently in use

Problem When you try to drop the database, you get the following error and the drop fails: Cannot drop database because it is currently in use. This means there are other open connections on the database and it won’t let you drop the database. Disclaimer: The solutions in this article involve kicking out other active … Read more

SQL Server – Getting and storing date/time

In this article, I’ll show built-in functions in SQL Server for getting the current datetime and how to get individual parts of the datetime (such as the year). Then show I’ll show how to store datetimes using the four different date/time data types (date, time, datetime2, and datetimeoffset). Getting the current datetime SQL Server has … Read more

Querying JSON in SQL Server

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

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

SQL – Filtering GROUP BY with HAVING and WHERE

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

Change ‘Edit Top 200 Rows’ and ‘Select Top 1000 Rows’

In SQL Server Management Studio (SSMS), when you right-click on a table, it’ll show you the commands “Select Top 1000 Rows” and “Edit Top 200 Rows” in the context menu. You can change the number of rows these commands return. Here’s how to make this change: Now when you right-click a table, it’ll show the … Read more

Saving changes is not permitted in SQL Server

Problem You’re trying to save a table change in SQL Server Management Studio (SSMS) and you get the error message: Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the … Read more

SqlTypeException: SqlDateTime overflow

Problem I am executing a SQL query and trying to set a datetime column to DateTime.MinValue. I’m getting the following exception: System.Data.SqlTypes.SqlTypeException: ‘SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.’ The problem is that DateTime.MinValue is 1/1/0001 12:00:00 AM and the SQL Server DateTime minimum value is 1/1/1753 12:00 AM. Solution … Read more

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

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

C# – Monitor data changes with SQL Server query notifications

You can use SQL Server query notifications to send push notifications to your code when data changes. This is an alternative to polling the database for changes. In this article, I’ll show how to configure this feature and work with it in the code. 1 – Enable Service Broker and configure permissions You need to … Read more

Find the distance between two coordinates using SQL and C#

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