RSS Feed

Cursors in T-SQL

Lately, I have started an internship in one of the IT Companies located in Wroclaw. I am really happy because this is a great opportunity to catch “real job” experience. Academic projects are a different kind of story.

I had a task to implement some MsSql queries and some operations on retrieved results. My first thought was to implement a simple for loop, iterate thought the result collection, do something on each result, „voila” .

Sure, Easier said than done.

Simple loop thought the results of SQL Query is great if we want to do something with a bunch of the data, but what should we do when with every iteration we want to do a specific operation on exactly one record. There is a problem because We would have to track the index of the current record. This would take a lot of effort to write test, etc. If you don’t want to waste a lot of time try the “Cursors”. They are ideal for this kind of a situation. They are similar to iterator in collections. You can fetch records, one by one and do some operations on them.

Cursors

Let’s assume that we have some Table called „UserData” with typical Columns:

  • ID
  • Name
  • Date
  • isActive

The simplest sql query ….

SELECT * FROM UserData

will return :

  • 1 , Michał Franc , 2008-10-10 , 1
  • 2 , Stefan Romański , 2006-01-01 , 1
  • 3 , Maria Kozłowska , 2005-04-04 , 1

This is my test Data [Those are my secret personalities on the Net ].

Let’s ask for the name of the users with Date value before year 2007. This is another query falling to the „simple” category.

SELECT Name FROM USerData Where DATEDIFF(day,’2007-01-01′,Date) < 0

Result:

  • Stefan Romański
  • Maria Kozłowska

Let’s create a procedure to take an ID as a parameter and set the isActive column to 0. This procedure is used to set all users with Date before year 2007 to isActive status 0.

Lets Create Query for ID’s .

SELECT ID FROM DaneUzytkownikow Where DATEDIFF(day,’2007-01-01′,Data) < 0

Result:

  • 2
  • 3

We have ID’s of inactive users. We will use the cursors now to run a procedure for every ID.

Before going further let me describe you how to use Cursors:

  1. Create temporary variables for data fetched from result row
  2. Create Cursor and assing a Select Query to it
  3. Open Cursor , this commands fils Cursor with data returned from assigned Query
  4. Iteration on records with Fetch function , assign data to temporary  variables
  5. Run procedure with temporary variables as a parameters
  6. repeat step 4 and 5
  7. Close Cursor , disposing resources
  8. Cursor Deallocation
1 DECLARE @UserId int 2 3  Declare @Cursor Cursor 4 5  Set @Cursor = Cursor FOR SELECT ID FROM UserData Where DATEDIFF(day,'2007-01-01',Data) < 0 6 7 Open @Cursor 8 9 Fetch Next From @Cursor Into @UserId 10 11 While (@@FETCH_STATUS = 0) 12 13 Begin EXEC SetInactive @Id = @UserID 14 15 Fetch Next From @Cursor Into @UserID End 16 17 Close @Cursor Deallocate @Cursor


1.Declaring Temporary Variable @UserId

DECLARE @UserId int – This variable on each iteration will

2.Create Cursor

Declare @Cursor Cursor

Set @Cursor = Cursor

For SELECT ID FROM UserData Where DATEDIFF(day,’2007-01-01′,Data) < 0

– We have to assign sql query.

3.Open Cursor.

Open @Cursor – Assigned Query is executed.

4.Iterating through records.

We have to store data from row in temporary variable

Fetch Next From @Cursor Into @UserId

While (@@FETCH_STATUS = 0) – The while loop will iterate till last row returned from the query.

5.Executing Procedure with temporary variable as a parameter

Begin EXEC SetInactive @Id = @UserID

After executing the procedure we need to fetch next data.

Fetch Next From @Cursor Into @UserID End

7 i 8.Closing and Deallocating Cursor

Close @Cursor Deallocate @Cursor

And that would be all its quite simple and easy. Cursors are very useful in lot of scenarios.

Cursors are also available in  MySql and Oracle.


BlogEngine .Net + Database

In the future i m planning to redeploy my blog on BlogEngine.Net and because in my .Net Group we are making a site also on this engine i m going to explain / write about this great platform. I think that this post could lead to a thematic series about mechanism in BlogEngine .Net

The engine has a nice architecture and you can learn a lot from it. There are lot of design patterns in practical use.

Let’sStart with BlogEngine DataSource.

Databases are one of the typical choices when we want to store all kinds of data. Comments , Posts , UserData etc.  nowadays almost everything is stored in Databases. Smae goes for BlogEngine .Net. There is also a different DataStore available , XML stored data , this aproach uses XML files. :et’sleave this interesting approach for a later post.

There are a lot of DataBase Engines available. We can use MSSql , MySql , OracleDb etc. They are based on official SQL specification. Of course when there is a big competition on the market there are different ideas and implementations. Every Engine has its own unique featuers and solutions almost their own original language.

Detailed Comparision :  Comparison of different SQL implementations CROSS COMPARE OF SQL SERVER, MYSQL, AND POSTGRESQL

Fortunately core of the SQL standard is solid. It is a basis upon which concrete technologies are built. We can create universal “code” based on the standard which will run on every technology. This is very important , we can create independet code.  Remember ……

Program to an ‘interface‘, not an ‘implementation‘.” (Gang of Four 1995:18)

If you are programming to concrete implementation you are asking yourself for problems in the near future.

BlogEngine’s database code is based on the Abstract Factory pattern. The Factory creates specific implementation by analyzing parameters in Connection String. The implementation contains concrete methods to specific DataBase implementation. Everything is nicely “wrapped” in Provider Factory. The Provider class defines the interface , common methods for every provider. This creates an abstraction layer between our program logic and Data Source.

Abstract Factory is a design pattern , which conceals the “creation” logic of the object. Factory returns wrapped object based on the parameters. In our case we are getting generic object used to comunicate with every Database. We doesn’t have to worry about its implementation. It is hidden and should be. We should be interested only on the interface which is the same for every class.

The Provider Factory is creating DBProviderFactory Object which is used to create  Connection and Command. Those are abstract objects. Their role is to delegate our task to objects which are inheriting from them.

The Concrete implementation of MSSQL logic , in the SQLCommand , inherits from DBCommand. By using the Down Cast it changes our “general” DBCommand class to more concrete and specific Object SQLCommand. By changing the Provider we are just changing the inherited class which is hidden behind the DBCommand

string connString=ConfigurationManager.ConnectionStrings[connStringName].ConnectionString; 

string providerName = ConfigurationManager.ConnectionStrings[connStringName].ProviderName; 

DbProviderFactory provider = DbProviderFactories.GetFactory(providerName); 
using (DbConnection conn = provider.CreateConnection())
   {
     conn.ConnectionString = connString; 
         using (DbCommand cmd = conn.CreateCommand()) 
           {   
             using (DbDataReader rdr = cmd.ExecuteReader()) 
              {        
               // Reading Logic        
              }        
             cmd.ExecuteNonQuery();         
             //itd     
           } 
    }

GetFactory methods decides on which provider to return back based on the parameter in Connection String. Provider creates his specific Connection and Command.

We are not using concrete classes like SQLConnection or SQLcommand everything is wrapped on more  general objects  DBConnection and DBCommand .

After brief presentation i would like to to show you how to connect to DB engine. I want to create DB logic for “pools”.

Let’s Do It !:

In this case. I have implemented only Database logic . BlogEngine also supports XML files as a data source . To attach DB logic for our objects i have created 2 partial classes. One of them extends DbBlogProvider  class ,which contains communication logic. The BlogProvider ,class is defines common interface for both the xml and db data storing concept . This is the another abstraction layer by which we can attach differetn db data storing procedures without significant change in the existing code .

I just had to extend the partial class with my own methods.
namespace BlogEngine.Core.Providers { public partial class DbBlogProvider: BlogProvider { public override bool CheckPoll(Poll poll) { //concrete logic } ... } }

After this step we  just have to use the provider mechanism in BlogEngine.

_provider = BlogEngine.Core.Providers.BlogService.Provider;

_provider.CheckPoll(poll);