Application Security Testing: An Integral Part of DevOps
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...
- Stored Procedures
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.
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:
A View is a dynamic result set. More information on Views can be found here.
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 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 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
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.