Site Sections
Posts
49
Comments
83
Trackbacks
0
August 2008 Entries
Returning Cursor from Stored Procedure Executing Dynamic Query
I usually stay on the C# development of things, and rarely venture to create SQL statements/code, other than the occasional SELECT / UPDATE / INSERT.  However, we have a requirement from our Product Management group to DELETE records permanently from the database.  We have proper enforced foreign keys in our DB schema, so one option we have is to create a stored procedure to help deletion, which will walk thru the referencing foreign keys to the record being deleted and then also delete those records (the children records essentially).  Yes, I know records should not be deleted (it should just be marked as deleted or inactive or something), but it was a requirements insistence where my opinion was overridden.

Having the stored procedure (hence I'll refer to this as sproc) is nice since consumer just need to call the same sproc passing in the table name & record id to delete, and the sproc will do the heavy lifting.  However, the sproc has some tasks to solve; since children records can contain further children records, the sproc needs to call itself recursively.  Since the sproc needs to query different tables (depending on which record is deleted), the sproc has to build dynamic SQL statements and execute that.  Since you need the values of the record Ids that references the parent (so it can see if those records have further children), you also need a CURSOR to loop thru those Ids).

Separately, each of the problem is fairly simple enough, sprocs can call itself recursively, you can create sprocs that execute dynamic SQL, and you can have sprocs that has a CURSOR as OUTPUT.  However when you combine them together, I consider them to be a fairly neat challenge to try to tackle.  The hard part is actually with getting a CURSOR back from a dynamic SQL, so I'm going to focus on that part.

To execute dynamic SQL, you need to use the built-in sp_executesql sproc in SQL Server.  The sp_executesql sproc can accept the following parameters:
  1. The SQL statement that will be executed
  2. The parameters & types that's needed by the SQL statement (#1) in text format
  3. The actual parameters to use in that SQL statement

So, essentially sp_executesql will create a stored procedure, containing the SQL statement passed (#1), with the parameter list of that sproc as defined in #2 and then it will invoke that newly created stored procedure, passing in the parameters as defined in the latter parameters.

To solve the problem, then we just need to create a dynamic SQL that has a CURSOR parameter which is an OUTPUT parameter.  The following test sproc demonstrates this:

ALTER PROC TestProc

AS

       DECLARE @dynamicSQL nvarchar(200)

       -- Have code that will construct the dynamic SQL

       SET @dynamicSQL = 'SELECT FirstName FROM Contacts'

 

       -- The cursor that will be filled by the dynamic SQL

       DECLARE @outputCursor CURSOR

       -- Create the dynamic SQL to fill a CURSOR instead

       SET @dynamicSQL = 'SET @outputCursor = CURSOR FORWARD_ONLY STATIC FOR ' +

              @dynamicSQL + ' ; OPEN @outputCursor'

 

       -- Execute dynamic sql

       exec sp_executesql                  -- sp_executesql will essentially create a sproc

              @dynamicSQL,                 -- The SQL statement to execute (body of sproc)

              N'@outputCursor CURSOR OUTPUT', -- The parameter list for the sproc: OUTPUT CURSOR

              @outputCursor OUTPUT         -- The parameter to pass to the sproc: the CURSOR

 

       -- Code that will just output the values from the cursor

       DECLARE @firstName nvarchar(200)

       FETCH NEXT FROM @outputCursor INTO @firstName

 

       -- Loop while there're more things in the cursor

       WHILE @@FETCH_STATUS = 0

       BEGIN

              PRINT @firstName

              FETCH NEXT FROM @outputCursor INTO @firstName

       END

 

       -- Be nice, close & deallocate cursor

       CLOSE @outputCursor

       DEALLOCATE @outputCursor

I just choose to use a simple SELECT to a table as the dynamic SQL statement; you can make it to be whatever SELECT is needed based on parameter being passed.  Then the sproc declares a CURSOR, which will then be used to contain the result of the SQL statement.  The whole text, including the CURSOR is the dynamic SQL we want to execute.  sp_executesql is then called with the SQL statement, we then define the statement to have 1 parameter (which is the CURSOR as an OUTPUT parameter), and then we pass in the cursor as the object to pass into the dynamic SQL to be executed.  The latter half of the code just loops thru the cursor to proof that the results are proper.

Not having dealt with much SQL code, this was an interesting journey for me.  There are other ways to solve the problem (DELETE records), we can use cascade delete, but it doesn't support self-referencing tables.  In the end we didn't use this approach, instead we just create a sproc for each table that needs to be deleted; it seems like a heck of a lot of maintenance job, but I'll let the DBA handle that.
posted @ Tuesday, August 26, 2008 11:49 AM | Feedback (6)
Refresh / Update WPF controls
Sometime in the past, a friend asked me how to update a control to show status while his code is doing a loop of stuff.  Essentially changing the text of a label (or sophisticatedly we can say a text-based progress bar).  In my past coding with MFC and WinForms, it's fairly easy enough, you just invalidate and do an update (Invalidate / UpdateWindow in MFC or Invalidate / Update in WinForms).  This approach also coincides with how Windows UI operate, where you specify the region that needs to be redrawn and then you send a message to the message pump for that control to paint itself.

So, I expected something similar (if not exactly the same) to also be present in WPF; much to my surprise, there is no equivalent.   All my internet searches actually shows how to do this using background thread - it is the approach that needs to be taken in a proper programming context, however there are times when you just want to do something quick & dirty or you want to augment an existing app / port where you don't want to introduce new elements.  There are also considerations to be made when both UI and worker thread access the same data, especially with regard to data binding (see my post about collection change not supporting multi-threading out of the box).

So, I've decided to add a helper method to refresh a WPF control.  I really appreciated the Refresh method in WinForms (which executes both Invalidate & Update), so I'm renaming my method to be Refresh as well.  The code snippet below also show some C# specific techniques, namely: anonymous delegates and extension methods.

public static class ExtensionMethods

{

   private static Action EmptyDelegate = delegate() { };

 

   public static void Refresh(this UIElement uiElement)

   {
      uiElement.Dispatcher.Invoke(DispatcherPriority.Render, EmptyDelegate);
   }
}

private void LoopingMethod()

{
   for (int i = 0; i < 10; i++)
   {
      label1.Content = i.ToString();
      label1.Refresh();
      Thread.Sleep(500);
   }
}


The LoopingMethod is just the method I use in my Window class to update the label (updating the progress) and then the code does some heavy lifting (Sleep ).  The Refresh method is the extension method that takes any UI element and then calls that UIElement's Dispatcher's Invoke method.  The trick is to call the Invoke method with DispatcherPriority of Render or lower.  Since we don't want to do anything, I created an empty delegate.  So how come this achieves refresh functionality?

When the DispatcherPriority is set to Render (or lower), the code will then execute all operations that are of that priority or higher.  In the example, the code already sets label1.Content to something else, which will result in a render operation.  So by calling Dispatcher.Invoke, the code essentially asks the system to execute all operations that are Render or higher priority, thus the control will then render itself (drawing the new content).  Afterwards, it will then execute the provided delegate (which is our empty method).

Pretty weird; there was a post somewhere in my google search that led me this route, and I was surprised as to how it worked.  I couldn't find it anymore, but credit where credit is due, someone else figured out that Invoke-ing a Render or lower priority task will result in the UI being redrawn.

Update (January 20, 2009):

A Commenter asked for a full sample, so I've uploaded one here.  I don't speak Spanish, but Google translator was working great!

Update (February 26, 2009):

A Commenter asked for a VB.NET sample, so I've uploaded one here.
posted @ Monday, August 25, 2008 10:22 AM | Feedback (12)
Calling Generic Method when given a type
Every now and then I have a need to call a generic method, but I can't quite call it generically.  Usually that particular situation involves needing to call a generic method, and I have the type for that generic method.  There are various reasons/examples of why anyone would get to this point, in my case we have an infrastructure code that can deal with objects generically, but  because it is infrastructure code, we can't have it refer to the actual entity classes that are in the higher layer/tier.

However, if we only have the type, we can't just call the method as is, we have to call it via reflection.  And as much as I like reflection, it has some drawbacks.  Consider the following snippet:

public void InvokeGenericMethod(object o)

{

   MethodInfo mi = this.GetType().GetMethod("MyGenericTestMethod", BindingFlags.DeclaredOnly | BindingFlags.NonPublic | BindingFlags.Instance);

   mi = mi.MakeGenericMethod(new Type[] { o.GetType() });

   mi.Invoke(this, new object[] { o });

}
 

private string MyGenericTestMethod<T>(T value)

{

   return value.GetType().ToString();

}

To call the method (it is private), I have to call Type.GetMethod with some not very nice looking parameters.  The name supplied is in quotes; as such if the actual method name is changed, this code will still compile, it'll just crash when actually ran.  Mind you, the code works; it's just not very maintenance-friendly.

A trick I use for this then is to use delegates as follows:

public void InvokeGenericMethod(object o)

{

   Func<string, string> temp = MyGenericTestMethod<string>;

   MethodInfo mi = temp.Method.GetGenericMethodDefinition();

   mi = mi.MakeGenericMethod(new Type[] { o.GetType() });

   mi.Invoke(this, new object[] { o });

}
 

private string MyGenericTestMethod<T>(T value)

{

   return value.GetType().ToString();

}

By using delegates, I no longer need to use reflections to get the method to call; I just point to the method as a delegate, and then create a generic method definition from that delegate.  Everything after that is still the same.  One nice benefit to this is if the method name is changed, the refactoring engine will also change the delegate reference, and if it doesn't the code will fail at compile-time.  Nicer, cleaner looking code as well, IMHO.
posted @ Friday, August 22, 2008 9:41 AM | Feedback (0)
Archives
Post Categories
News