Grouping Database Records in Windows Form

Introduction

This article explains how to use the Group By clause in a Stored Procedure in a Windows Forms application.

Stored Procedure

A Stored Procedure is a precompiled entity that executes on the database server itself. Stored Procedures are defined in the database by a "proc" or "procedure" keyword and variables are defined by the "@" character. Stored Procedures can be used to do SQL Injection. SQL Injeciton is the technique that allows SQL queries to be hidden from the front end. Stored Procedures are commonly used for security purposes. A Stored Procedure is a group of SQL statements that have been created and stored in the database. A Stored Procedure accepts input and output parameters as well, so that one Stored Procedure can be used over the network by various front ends using various input data.

Group By

The Group by clause allows us to group rows together and additionally can be performed with aggregate functions that work on a group of rows. The Group By clause works with the select command and groups the records of the tables values, and if aggregate functions are used in the select statements, then the Group By clause computes a summary value for each group.

Write the following procedure for creating a database and table in SQL Server:

Create database StudentResults

use StudentResults

create table StudentMarks

(

RollNo int,

FirstName varchar(max),

LastName varchar(max),

Subject varchar(max),

Marks int

)

Write the following procedure for inserting the values in table columns:

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(1,'Pankaj','Lohani','Math',70)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(1,'Pankaj','Lohani','Physics',65)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(1,'Pankaj','Lohani','Chemestry',75)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(2,'Pravesh','Khanduri','Math',85)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(2,'Pravesh','Khanduri','Physics',60)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(2,'Pravesh','Khanduri','Chemestry',70)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(3,'Nimit','Joshi','Math',90)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(3,'Nimit','Joshi','Physics',60)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(3,'Nimit','Joshi','Chemestry',75)

Write the query to execute the table schema:

select * from EmployeeInformation

Fig-tableschema

Create the following Stored Procedure for grouping the records:

create proc TotalMarks

begin

select FirstName, Sum(Marks) as TotalMarks

from StudentMarks

Group by FirstName

end

create proc MaxMarks

begin

select Subject, Max(Marks) as Highest_Marks

from StudentMarks

Group by Subject

end

Now I will show you how to group the records of the database table and display then in a Windows Forms form. Use the following procedure to create do that.

Step 1:

Open Visual Studio then select "Create New Project" --> "F# Console Application".

CreateApplication

Step 2:

Now go to the Solution Explorer on the right side of the application. Right-click on "References" and select "Add references".

SelectReferences

AddReferences

Step 3:

After selecting "Add References", in the framework template you need to select "System.Windows.Forms", "System.Drawing" and "System.Data" while holding down the Ctrl key and click on "Ok".

ImportNamespaces

Write the following code for binding the data table into the datagrid and to work with the Group By clause from a data source to a Windows Forms form.

Step 4:

open System

open System.Windows.Forms

open System.Drawing

open System.Data

open System.Data.SqlClient

let grpbyform=new Form(Text="Grouping the Records")

let constring = @"Data Source=MCNDESKTOP34;Initial Catalog=StudentResults;User ID=; Password="

let con=new SqlConnection(constring)

let com=new SqlCommand()

let adap=new SqlDataAdapter("select * from StudentMarks",constring)

let dt=new DataTable()

adap.Fill(dt) |>ignore

let datagrid=new DataGridView(Top=50,Left=0,Width=500,Height=250)

grpbyform.Controls.Add(datagrid)

let grupbylbl=new Label(Text="Group by clause",Top=270,Width=120,Left=20)

grpbyform.Controls.Add(grupbylbl)

datagrid.DataSource<-dt

let grupbtn=new Button(Top=350,Left=20,Width=120)

grupbtn.Text<-"Grouping by Name"

grupbtn.ForeColor<-Color.Red

grupbtn.BackColor<-Color.Ivory

grpbyform.Controls.Add(grupbtn)

grupbtn.Click.Add(fun _->

let com = new SqlCommand()

com.Connection <- con

com.CommandType <- CommandType.StoredProcedure

com.CommandText <- "TotalMarks"

let adapter = new SqlDataAdapter("TotalMarks",con)

let dt = new DataTable()

adapter.Fill(dt) |>ignore

let gridview = new DataGridView(Top=380,Left=20,Width=240,Height=120)

grpbyform.Controls.Add(gridview)

gridview.DataSource <- dt

)

let maxbtn=new Button(Top=350,Left=270,Width=180)

maxbtn.Text<-"Maximmum Marks by Subject"

maxbtn.BackColor<-Color.Ivory

maxbtn.ForeColor<-Color.Red

grpbyform.Controls.Add(maxbtn)

maxbtn.Click.Add(fun _->

let com = new SqlCommand()

com.Connection <- con

com.CommandType <- CommandType.StoredProcedure

com.CommandText <- "MaxMarks"

let adapter = new SqlDataAdapter("MaxMarks",con)

let dt = new DataTable()

adapter.Fill(dt) |>ignore

let gridview = new DataGridView(Top=380,Left=270,Width=240,Height=120)

grpbyform.Controls.Add(gridview)

gridview.DataSource <- dt

)

let exitbtn=new Button(Top=350,Left=470,Width=40)

exitbtn.Text<-"Exit"

exitbtn.ForeColor<-Color.Red

exitbtn.BackColor<-Color.Ivory

grpbyform.Controls.Add(exitbtn)

exitbtn.Click.Add(fun exit->

grpbyform.Close())

Application.Run(grpbyform)

Step 5 :

Debug the application by pressing F5 to execute the Windows Forms application. After debugging the application the output will be as in the following figure:

AfterDebug

Step 6 :

Now click on the "Grouping by Name" button then the DataGrid will display the records as in the figure below.

Step 7 :

Now click on the "Maximum Marks by Subject" button; the DataGrid will be displayed with a grouping of records as in the figure below.

GrpBySubject

Summary

In this article you saw how to use the Group By clause using a Stored Procedure in a Windows Forms application.