SQL 2005 - CLR Integration - Custom XQuery/XPath functions (C#)
Source Code
Last month we started to make extensive use of XML Types in MS SQL 2005 in one
of our projects. One of the
requirements were that an user must be able to search within specific nodes.
So I thought, awesome we'll make use of XQueries/XPath's within SQL to
query these XML fields - everything went well - until I wanted to write a case
insensitive (upper/lower case functions) search query and found that SQL
doesn't fully support
XQuery/XPath
Infact, Microsoft only recently added the functions I need to SQL 2008.
a Quick search on google revealed that a lot of people were suggesting that one
should use the translate function (as workaround) available in SQL 2005's XQuery.
I do however feel that this is one of those things we can solve using CLR Integration - which would also
make it possible to add any other functionality we might need as well.
In my example, we're going to create an upper-case function for XML Types within SQL 2005.
You will need to create a context class inheriting from the abstract class XsltContext(System.Xml.Xsl)
public class CustomContext : XsltContext { public override IXsltContextFunction ResolveFunction(string prefix, string name, XPathResultType[] ArgTypes) { if (name == "upper-case") { return new UpperCase(); } return null; } ... ...
We also need to create a class inheriting from the IXsltContextFunction Interface (which contains the implementation of our function), which would basically look like this:
public class UpperCase : IXsltContextFunction { public UpperCase() { } public XPathResultType[] ArgTypes { get { return null; } } public object Invoke(XsltContext xsltContext, object[] args, XPathNavigator docContext) { return args[0].ToString().ToUpper(); } public int Maxargs { get { return 1; } } public int Minargs { get { return 1; } } public XPathResultType ReturnType { get { return XPathResultType.String; } } }
To make this work (e.g. wire everything up), we need to add our "context class" to our XPathExpression like this - which makes our newly created function accessible:
XPathDocument doc = new XPathDocument(@"items.xml"); XPathNavigator nav = doc.CreateNavigator(); CustomContext ctx = new CustomContext(); XPathExpression expr = nav.Compile(@"*//item[contains(upper-case(string(@value)), 'AB')]"); expr.SetContext(ctx); XPathNodeIterator nodes = nav.Select(expr); foreach (XPathItem node in nodes) { Console.WriteLine(node.Value); }
Finally we add this functionality to our CLR Function like so:
[SqlFunction] public static bool XQuery([SqlFacet(MaxSize = -1)]String value, String expression) { if (!String.IsNullOrEmpty(value)) { using (StringReader sr = new StringReader(value)) { XPathDocument doc = new XPathDocument(sr); XPathNavigator nav = doc.CreateNavigator(); CustomContext ctx = new CustomContext(); XPathExpression expr = nav.Compile(expression); expr.SetContext(ctx); XPathNodeIterator nodes = nav.Select(expr); return nodes.Count > 0; } } return false; }
Register the assembly:
CREATE ASSEMBLY CLRDemos FROM 'C:\procs\CLRXQuery.dll' WITH PERMISSION_SET = SAFE;
Reference the function:
CREATE FUNCTION XQuery ( @value NVARCHAR(MAX), @expression NVARCHAR(4000) ) RETURNS BIT AS EXTERNAL NAME [CLRDemos].[CSTruter.com.functions].[XQuery]
Example of usage:
SELECT * FROM friends WHERE dbo.XQuery(CAST(details AS VARCHAR(MAX)), '//item[upper-case(string(@value))="DEF"]') = 1
Like you can see its quite simple adding non-supported features.
Date - 2010-03-23 18:44:14
Comments - 0
C# Threading: BackgroundWorker
Source Code
Running intensive tasks like database transactions, uploads/downloads etc, on the
same thread as your UI, can cause your UI to become unresponsive.
To solve this issue, we can move these intensive tasks to separate/background threads.
For this purpose we can use the BackgroundWorker component/class, which you can drop
to your form from the toolbox within Visual Studio - or simply create an instance
of the BackgroundWorker class.
Lets have a quick look, at the demo code that you can download at the top of the post. We're going to create a small little application like this:
BackgroundWorker bw = new BackgroundWorker(); public mainForm() { InitializeComponent(); bw.DoWork += new DoWorkEventHandler(bw_DoWork); bw.ProgressChanged += new ProgressChangedEventHandler(bw_ProgressChanged); bw.WorkerReportsProgress = true; bw.WorkerSupportsCancellation = true; } private void btnGo_Click(object sender, EventArgs e) { if (cbxWorker.Checked) { if (!bw.IsBusy) // If the thread isn't started, start it { bw.RunWorkerAsync(); } } else { // What would happen if we didn't use a background thread? for (int i = 0; i < 50; i++) { Thread.Sleep(100); lbxItems.Items.Insert(0, i); } } }
The method handling the DoWork event runs within our background thread, making it the ideal spot to run intensive tasks, it is where we do the work (as the name suggests).
void bw_DoWork(object sender, DoWorkEventArgs e) { for (int i = 0; i < 50; i++) { Thread.Sleep(100); // Some intensive task if (bw.CancellationPending) { e.Cancel = true; } if (!e.Cancel) { bw.ReportProgress(i); // Inform the main thread of our progress } else { break; } } }
The method handling the ProgressChanged event runs within the thread where our UI resides, which makes it the ideal place where we can report whats currently happening in our background thread - e.g. access form controls etc.
void bw_ProgressChanged(object sender, ProgressChangedEventArgs e) { lbxItems.Items.Insert(0, e.ProgressPercentage); }
private void btnStop_Click(object sender, EventArgs e) { bw.CancelAsync(); }
If you really really need to access controls that resides in the thread handling the UI from your background thread (and not the ProgressChanged event), you can do it like this.
void addItem(Int32 i) { if (lbxItems.InvokeRequired) { Action<Int32> a = new Action<int>(addItem); this.BeginInvoke(a, i); } else { lbxItems.Items.Insert(0, i); } }
Notice the InvokeRequired property, this tells us if the control we're trying to access is available within our current context, if its not we resend our caller via delegate to the BeginInvoke method, until we've contacted the thread where our control resides in.
There is also an event called RunWorkerCompleted, that gets triggered as soon as our backgroundworker completes.
There is a lot of things to consider when designing applications that use threads like race conditions, deadlocks, livelocks, starvation etc - perhaps something I can write about in a future post - but this post should give you a basic idea of how to get started at least.
Date - 2010-03-20 22:51:51
Comments - 0
First 6 7 8 9 10 11 12 13 14 15 Last / 42 Pages (83 Entries)