Solve real coding problems
Articles about using ADO.NET in C# to execute SQL queries and map the results. This is a lower level alternative to using ORMs (such as Dapper and EF Core).
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
When you execute a SQL query and read the results with SqlDataReader, you have two options for getting column values by name (instead of by ordinal number): I’ll show examples below. Read column by name with the indexer You can use the indexer to read columns by name (or by ordinal number). This returns an … Read more
When you create a new project in .NET, and you try to use a class from ADO.NET, such as SqlConnection or SqlDataReader, you’ll get missing reference errors. For example, your code might look something like this, and it’ll have red-squiggly lines under SqlConnection: In the past, you’d simply add a reference to System.Data.SqlClient, like this: … Read more
When you need to insert multiple rows into the database, consider doing a Bulk Insert instead of inserting one row at a time. Bulk Insertions are up to 20x faster than executing SQL Insert repeatedly. The simplest way to do a SQL Bulk Insert is by using the built-in SqlBulkCopy (from System.Data.SqlClient) with a DataTable. … Read more
SqlDataReader returns a DBNull object when a column is null. This isn’t the same as a C# null. You can check if the column is null by comparing it with DBNull.Value or by using SqlDataReader.IsDBNull(). Here’s an example showing these two ways of checking if a column is null: After checking if the column is … Read more
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
