sqlcommand

C# – Use SqlDbType.Structured for table parameters

by

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

C# – Get column values by name instead of by number with SqlDataReader

by

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

C# – How to handle nulls with SqlDataReader

by

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

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

SqlTypeException: SqlDateTime overflow

by

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

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