Input, Output and Input/Output SqlParameter Behavior Explained


In this article I will explain the sometimes surprising behavior of  the ADO.Net SqlParameter and particularly the effects of ParameterDirection, null value and DBNull.Value on how the parameter is sent to the database.

The companion VisualStudio 2015 solution contains the database project and unit tests for each scenario outlined below.

Continue reading

OUTPUT parameters are the most efficient way to retrieve a single row from a Stored Procedure

T-SQL stored procedures that return a single a value (scalar) or a single row can benefit from the use of OUTPUT parameters. This is not appropriate for multiple row result sets, but if you just need one value, or the results of a single row, you can do it most efficiently with OUTPUT parameters as opposed to SELECTing into a DataReader or DataSet. Continue reading

A Basic Example of CRUD with DataGridView in VB.Net

Intended for new developers, or at least new to ADO.Net, this post will demonstrate how to Create, Read, Update and Insert data into a MS SQL Server database. We will use a VB.Net winforms project, DataGridView and SqlDataAdapters. You can pull down the complete project from GitHub here.

UPDATE: I have also published a similar example application done with Code First EntityFramework 6.

The finished Form looks like this:
finishedform
Continue reading

Restore SQL Server backup to localdb in Visual Studio

The Visual Studio integrated tools for SQL Server are getting better all the time. While working solely in VS might not always be optimal, it is very convenient to not have to switch over to SQL Server Management Studio to do simple queries. Although the VS SQL tools may not be full-featured, you might just be able to get by with them and not need to install SSMS at all.

One of the less intuitive things to do is to restore a backup file that was created on another server. In this post I will show you how to easily do this in the absence of a right-click restore shortcut. For this you must run the SQL for RESTORE.

Continue reading