Create a SQL Server Stored Procedure

Create a stored procedure in SQL Server from the Adventure Works database using the View vVendorWithAddresses. This one uses a variable to limit the number of Vendors returned for this article.

Create a Windows Form Application

Create a new Windows Form Application in Visual Studio 2017 that will handle any errors thrown back by SQL Server (or in the code itself).

This application will require Interfaces and Classes for a Service and a Repository and a Class for the ContainerBuilder. In the upper part of the code, add references to use System.Data and System.Data.SqlClient.

Create the DI Interfaces

Service Interface - Create an Interface for the service to Write the data and return a DataTable.

Repository Interface - Create an Interface for the repository to WriteData to return a DataTable via the Service.

Create the DI Classes

Repository Class - Create a new Class that implements the Repository Interface with a generic Constructor and 3 variables for the SQL connection and the Stored Procedure name + the string representing the Department. Implement the Repository Interface member WriteData which will return a a Data Table. Add an additional function to Get the Data for the DataSet.

First, add the repository public variables and constructor.

Add the DataSet Function - Add a Function GetData which takes the Sql Connection String and Stored Proc name + the ComboBox Department to execute the SqlCommand and Fill the DataAdapter with the DataSet for the Repository.

Service Class - Create a new Class that implements the Service Interface. Add a field of the local variable of the Interface Repository type. Create the required Service Interface member Write which calls the Repository Interface member WriteBytes to process the byte array into a Report and pass the DataTable back to the Form in frmMain in the Windows App.

Creating the Windows Form - Design

ConfigurationBuilder Class - Add an Application Config file to the project (app.config). Add the ConnectionString and user Stored Proc name to the file.  Also add a userSP variable to the configuration file for the stored proc.

In the Windows Form add a Text Box for Error Messages, a Command Button to Exit the Application, a Data Grid, and a ComboBox to the Form.

 

Add a new DataSet to the Combo Box to populate it with the Human Resources Departments.

 

Add a new Binding Source for the New ComboBox DataSet.

 

Windows Form Code

The Main Code for FrmMain registers an instance of the DataRepository with its variables for the Connection String and the User Stored Proc.  It then uses DI to register the DataService using the DataRepository.  A container is created which will resolve the DataService into a variable which will then call the Write function of the Service using the selection in the ComboBox1 as the variable to output a DataTable.  The DataTable is then used as the dataSource for a BindingSource.  The BindingSource is then used as the DataSource for dataGridView1 to display the results from the stored procedure written at the beginning of this article.

The Form_Load event code uses the departmentTableAdapter Fill method to populate the DataSet for the departments used in the comboBox dataSource..

Combo Box Code on Selected Index Changed Event - when the comboBox1 value is changed, a new dataTable is created by calling the DataService with a New value which calls the SQL Server Stored Proc and delivers the correct data back to the DataSource for the dataGrid.

Testing the Windows Form

Test the Windows Form using a couple of different selections from the Combo Box. This example uses Engineering.

This Windows Form example uses the selection of Sales from the Combo Box.