Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


Windows Form Application - Using DI and a DataSet

C# Windows Form Application

This article describes how to create a Windows Form Application pull data into a DataGrid based on a value selected in a Combo Box in a Form. The application will use Autofac DI and retrieve the fields from the AdventureWorks2017 database on SQL Server for the ComboBox. The selected variable from the Combo Box will be passed into a Service and used as a variable in a SQL Server Stored Procedure. The DataTable returned will be populated into a DataGrid on the Windows Form.

 

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.

SQL Server Stored Procedure for Data

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).

Create Windows Form App

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.

Service Interface

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

Repository Interface

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.

Repository Class

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.

Additional function for Data Retrieval in Repository Class

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.

Service Class

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.

App Configuration File

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.

Windows Form 

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

Windows Form Data Set 

Add a new Binding Source for the New ComboBox DataSet.

Windows Form Binding Data Source 

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.

Form Main Code

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

Form_Load code

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.

Console Application EPPlus Output

Testing the Windows Form

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

ComboBox Selection of Engineering Department

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

ComboBox Selection of Sales Department