Using SQL Cursors in VB.NET

Introduction

I have fallen in love with SQL. I think that, for some of you who have been following my articles (hopefully, there are a few of you), it has become quite apparent. I am fortunate to have a job in which I can find new ways to improve previous written code, or simply learn new features. Today, I want to teach you about SQL Cursors, and explain how to use them through Visual Basic.

For the uninformed…

Databases

A database consists of the following objects:

• Tables
• Queries
• Views
• Stored Procedures
• Functions

Tables

A table consists of rows (records) and columns (fields). A row, or record, contains all the information pertaining to one topic. A column, or field, is once piece of information.

Queries

A query is a means of extracting information from a database.

Getting into the SQL language now will take too long because there are many aspects and we simply do not have time. If you want to learn or refresh your SQL skills, the following links may be worth your while:

• http://www.w3schools.com/sql/
• http://www.codeguru.com/database/sqlserver-search-dialog-class/

Views

A View is a dynamic result set. More information on Views can be found here.

Stored Procedures

A Stored Procedure is a sequence of SQL code that can perform tedious tasks quickly. By making use of Stored Procedures, you will save a lot of time and won’t need to run each query (that can reside in a Stored Procedure) manually. More information on Stored Procedures can be found here.

Functions

Functions can help with string manipulation, date manipulation, and computing Averages, just to name a few. A complete list of SQL Functions can be found here.

Cursors

Cursors process individual rows returned by database system queries and enable manipulation of whole result sets at once.

An SQL Cursor is used to iterate through a result set. An SQL Cursor loops through each row of a result set, one row of the result set at a time. Similar to an SQL WHILE Loop, an SQL Cursor allow programmers to treat each result of a SELECT statement separately by looping through them. For a complete list of the mechanics of an SQL Cursor, refer to this MSDN Article.

A Practical Example

Open SQL Server, and add the following into a new query:

CREATE PROC [dbo].[CreateCursorEx]
   AS
      BEGIN
@StudentID as INT --StudentID Variable

@StudentName as NVARCHAR(50) --StudentName Variable

@StudentCursor as CURSOR --Student Cursor object

SET @StudentCursor = CURSOR FOR --Information to loop over
SELECT StudentID, StudentName
   FROM StudentCourses

OPEN @StudentCursor
FETCH NEXT FROM @StudentCursor INTO @StudentID,
   @StudentName --Get next Row
WHILE @@FETCH_STATUS = 0 --Continue
BEGIN
   PRINT cast(@StudentID as VARCHAR (50)) + ' ' +
      @StudentName;
   FETCH NEXT FROM @StudentCursor INTO @StudentID,
      @StudentName;
END
END

In Visual Basic

Imports System.Data.SqlClient
Private Sub Button1_Click(sender As Object, e As EventArgs) _
      Handles Button1.Click
   Dim connCursor As SqlConnection = New _
      SqlConnection("Server=YOURSERVER;" & _
         "uid=YOURID;pwd=YOURPASSWORD;")
   Using (connCursor)
      Dim sqlComm As New SqlCommand
      sqlComm.Connection = connCTE
      sqlComm.CommandText = "CursorStoredProcedure"
      sqlComm.CommandType = CommandType.StoredProcedure
      connCursor .Open()
      sqlComm.ExecuteNonQuery()
   End Using

End Sub

Conclusion

Using SQL Cursors within a loop will enable you to save a lot of time and processing power to get the best performance out of your SQL databases. Please, continue exploring the world of SQL to see everything that you can do with databases.