Sql cursors use in vb dot net

SQL Cursors and It’s Use In VB.NET 7th July 2016

In this blog from CRB Tech Reviews, we will see in detail the concept of SQL cursors and it’s use in VB.NET. Cursor Definition: Cursor is basically a database object used to recover data from a result set line by line, rather than the T­SQL commands that work on every one of the rows in the result set at one time. We utilize cursor when we have to update records in a database table in singleton style implies line by line.

Now we will see the basic syntax of a cursor: DECLARE cursor_name CURSOR [LOCAL | GLOBAL] –scope of cursor is defined over here.

[FORWARD_ONLY | SCROLL] – the direction of cursor movement is defined in this line (forward/backward) [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] – basic type of cursor is declared over here.

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] – define locks FOR select_statement – SQL Select statement is mentioned FOR UPDATE [col1,col2,...coln] – names of columns that need to be updated are passed here as parameters.

This is the syntax to define a SQL cursor. Declare Cursor SQL Comaand is utilized to define the cursor with numerous alternatives that affect the scalability and loading conduct of the cursor.

Cursor Life Cycle: 1. Cursor Declaration: For declaring a cursor, one needs to define a SQL statement which returns a result set.

2. Open: Execute the SQL statement defined by the cursor if you wish to populate and open the cursor.

3. Fetch: Once a cursor is opened, then we can access the rows from the cursor one by one or in bulk manner for data manipulation.

4. Close: A cursor needs to be closed explicitly, once data manipulation is done.

5. Deallocate: Once the job is done, the cursor needs to be freed for further use. All system resources associated with the cursor are released using this command. It is just like freeing the memory.

Scrollable Cursors: Developers may declare cursors as scrollable or not scrollable. The scrollability depicts the direction in which a cursor can move.

A line of code may position a scrollable cursor anyplace in the result set utilizing the FETCH SQL command. The SCROLL keyword must be determined while declaration of the cursor. The default value is NO SCROLL, albeit distinctive language ties like JDBC may apply an alternate default.

Using a non­scrollable (or forward­only) cursor, you can FETCH every line at most once, and the cursor naturally moves to the following line. After you get the last line, on the off chance that you fetch once more, you will put the cursor after the last line and get the accompanying code: SQLSTATE 02000 (SQLCODE +100).

DECLARE cursor_name sensitivity SCROLL CURSOR FOR SELECT … FROM …

Scrollable cursors can possibly get to the same row in the result set at various times.

In this manner, data changes (insert, delete, update operations) from different transactions could affect the result set. A cursor can be SENSITIVE or INSENSITIVE to such data adjustments. A sensitive cursor grabs data changes affecting the outcome set of the cursor, and an insensitive cursor does not. Moreover, a cursor might be INSENSITIVE, in which case the DBMS tries to implement sensitivity however much as could be expected.

A Cursor affects the execution power of the SQL Server since it utilizes the SQL Server instances’ memory, diminish concurrency, diminish network bandwidth and lock resources. Consequently it is compulsory to comprehend the cursor types and its capacities with the goal that you can utilize appropriate cursor as indicated by your necessities.

You ought to maintain a strategic distance from the utilization of cursor. Essentially you ought to utilize cursor options like WHILE loop, sub queries, Table variables and temporary variables. We ought to utilize cursor all things considered when there is no alternative with the exception of cursor.

Conclusion: Utilizing SQL Cursors inside a loop will empower you to spare a great deal of time and handling power to get the best execution out of your SQL databases. If it’s not too much trouble keep investigating the universe of SQL to see everything that you can do with databases.

To learn more about databases and .net, you can inquire about VB .NET courses.

For .NET training and placement reviews, please do visit CRB Tech Reviews.

Related Articles: .Net programming concepts explained in detail WHY SHOULD YOU LEARN VISUAL BASIC (VB.NET) ?