Using Cursors Examples in T Sql

Ex 1.

Declare @FName as nvarchar(50)
Declare @LName as nvarchar(100)
Declare @Email as nvarchar(255)

Declare PersonCursor CURSOR FAST_FORWARD FOR

Select fname, lname, email from Person
where city = 'Phoenix'
order by fname

OPEN PersonCursor
FETCH NEXT FROM PersonCursor
INTO @FName, @LName, @Email

WHILE @@FETCH_STATUS = 0
BEGIN
-- do row specific stuff here
print 'FName: ' + @FName
print 'LName: ' + @LName
print 'Email: ' + @Email

FETCH NEXT FROM PersonCursor
INTO @FName, @LName, @Email
END

CLOSE PersonCursor
DEALLOCATE PersonCursor


About @@FETCH_STATUS
0 The FETCH statement was successful.

-1 The FETCH statement failed or the row was beyond the result set.

-2 The row fetched is missing.


Ex 2.

DECLARE @price money
DECLARE @get_price CURSOR

SET @get_price = CURSOR FOR
SELECT price FROM PriceTable where SysID<100

OPEN @get_price

FETCH NEXT FROM @get_price INTO @price

WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @Price < 20
SELECT 'Under 20'
ELSE
SELECT @Price

FETCH NEXT FROM @get_price INTO @price
END

CLOSE @get_price
DEALLOCATE @get_price

As cursors are very poor performers and often other options make more sense for performance reasons, we should be caution when using cursors.

About SynchronizationContext

A SynchronizationContext allows a thread to communicate with another thread. In .Net framework, WinForms thread context and WPF thread context provide a customer synchronization. A Windows Forms threading model prefers that any user interface code runs directly on the main "user interface" thread. (Contrast to ASP.NET, whre most work is done inside a thread pool so that asynchronous calls can happen on any thread in that pool).

ex.

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button_Click(object sender, EventArgs e)
{
//get an instance of SynchronizationContext class by calling
//the static Current property of SynchronizationContext class
SynchronizationContext uiContext = SynchronizationContext.Current;
Thread thread = new Thread(Run);
thread.Start(uiContext);
}
private void Run(object state)
{
SynchronizationContext uiContext = state as SynchronizationContext;
//Post or Send method to call some code, Post of Send method takes
//two parameters, a delegate pointing to a method and a state object
uiContext.Post(UpdateUI, "Hi");
}
private void UpdateUI(object state)
{
//state object is a string
string text = state as string;
mListBox.Items.Add(text);
}
}

Send and Post are two possible methods you can marshal code to UI thread. The difference is Send is "Synchronous", calling send will execute the code but block until the executing code complete until returning. Post is "Asynchronous", calling Post is more like fire-and-forget in that it queues up the request and returns immediatedly if possible.