SQL 2005 - CLR Integration (C#)
Source Code
Back in 2004 - 2005 I created extended stored procedures for SQL 2000 using C++, soon afterwards
we migrated to SQL 2005, which allows us to create stored procedures/functions/triggers/types etc
via the .net CLR (using C#) - providing similar functionality.
Its quite powerful functionality, that allows us to do things we can't normally do within SQL e.g.
my first CLR Procedure we used to send messages to mobile phones - which functioned as a notification
about certain events happening within a customer account.
Thinking a bit further - we can add some proper regex functionality to SQL, perhaps improve XQuery support to SQL XML types - I've seen some code where devs added functionality to query the Sharepoint webservice from CLR procedures.
The API which aid us with CLR integration is located within the
Microsoft.SqlServer.Server namespace
Lets have a quick look at creating some functions:
Trim function:
If you're one of those guys bugging microsoft into creating a trim function (and not into the whole ltrim+rtrim thing), why
not write your own trim function? (Which provides me the opportunity to give an example of a simple scalar function)
[SqlFunction(DataAccess = DataAccessKind.None)] public static String Trim([SqlFacet(MaxSize = -1)]String value) { return value.Trim(); }
How do we deploy this function? Well, I know that it can be deployed via Visual Studio, but if you're stuck with a version that doesn't allow this (like me), you will need to create a class library, include the appropriate namespace (Microsoft.SqlServer.Server) and copy the compiled assembly to a folder of your choice.
Next you need to register the assembly within SQL (in the database you wish to use it):
CREATE ASSEMBLY CLRDemos FROM 'C:\procs\CLRDemo.dll' WITH PERMISSION_SET = SAFE;
If this fails, make sure you've got CLR enabled within SQL (restart required):
sp_configure'clr enabled', 1 RECONFIGURE
Once all of that works, you'll need to reference the CLR function, like so:
CREATE FUNCTION TRIM ( @value NVARCHAR(MAX) ) RETURNS NVARCHAR (MAX) AS EXTERNAL NAME [CLRDemos].[CSTruter.com.functions].[Trim]
In this example I named my assembly CLRDemos, the class named functions lives in the namespace called CSTruter.com.
Split function:
Ever felt the need/desire/longing to split a string within SQL? The following table valued function will provide that functionality, basically we pass a separator and string and this function returns a table with the segments it managed to split.
[SqlFunction(DataAccess = DataAccessKind.None, FillRowMethodName = "FillRow")] public static IEnumerable Split([SqlFacet(MaxSize = 10)]String separator, [SqlFacet(MaxSize = -1)]String value) { Int32 i = 0; String[] values = value.Split(new String[] { separator }, StringSplitOptions.None); foreach (String v in values) { yield return new Object[] { i++, v }; } } public static void FillRow(Object sender, out Int32 id, out String value) { Object[] values = (Object[])sender; id = (Int32)values[0]; value = (String)values[1]; }
Notice the FillRow method, the first parameter contains the current value of our iteration, the rest of the values represent fields that will be returned to the result set - in this case an unique id and a segment value.
Like the previous function, we need to reference the CLR function:
CREATE FUNCTION SPLIT ( @separator NVARCHAR(10), @value NVARCHAR(MAX) ) RETURNS TABLE(id INT, value NVARCHAR(MAX)) AS EXTERNAL NAME [CLRDemos].[CSTruter.com.functions].[Split]
viewFriends Procedure:
What if we need to access tables/data within our database from within our C# methods? Its quite simple, have a look at the following CLR Stored Procedure:
[SqlProcedure] public static void viewFriends() { using (SqlConnection connection = new SqlConnection("context connection=true")) { connection.Open(); SqlCommand command = new SqlCommand("SELECT firstname, lastname FROM friends", connection); SqlDataReader reader = command.ExecuteReader(); SqlContext.Pipe.Send(reader); } }
Notice the connectionstring context connection=true, this simply tells the SqlConnection object to use the connection within context of our method - context being SQL.
The pipe class allows us to send rows to the resultset.
Like the functions we need to reference this procedure:
CREATE PROCEDURE viewFriends AS EXTERNAL NAME [CLRDemos].[CSTruter.com.Procedures].[viewFriends]
getDayNames Procedure:
Lets imagine we want to return a resultset of data/collection from C#, e.g. a list of day names according to language culture - which can be found in the System.Globalization namespace.
[SqlProcedure] public static void getDayNames([SqlFacet(MaxSize = 10)]String name) { SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("id", System.Data.SqlDbType.Int), new SqlMetaData("value", System.Data.SqlDbType.VarChar, 255) }); CultureInfo culture = new CultureInfo(name); String[] DayNames = culture.DateTimeFormat.DayNames; SqlContext.Pipe.SendResultsStart(record); for (int i = 0; i < DayNames.Length; i++) { record.SetInt32(0, i); record.SetSqlString(1, DayNames[i]); SqlContext.Pipe.SendResultsRow(record); } SqlContext.Pipe.SendResultsEnd(); }
Notice SqlDataRecord & SqlMetaData, these classes allow us to define our resultset, once the developer passes a culture name e.g. en-GB (or af-ZA in my case), it returns a list of day names in that language.
Referencing this function will look something like this:
CREATE PROCEDURE getDayNames(@name AS NVARCHAR(10)) AS EXTERNAL NAME [CLRDemos].[CSTruter.com.Procedures].[getDayNames]
All in all it is quite easy to use this technology, hopefully this post gave you a quick look at this functionality.
Be sure to download the source code.
Further reading
Date - 2010-03-14 20:34:01
Comments - 0
ASP.net (C#) - WebControlAdapter
Source Code
The way ASP.net renders webcontrols don't always produce the desired markup, we might
require different markup for different browsers - e.g. a visitor browsing your website
from their mobile / cellphone.
We might even feel the need to change the way ASP.net renders webcontrols all together - luckily
microsoft provided a mechanism called WebControlAdapters.
For the purpose of this post, imagine the following scenario:
Your employer hands you two lists of options, which he wants selectable from DropDownLists, you
think to yourself, awesome I will throw it all together in one DropDownList and distinguish the lists
using optgroups - but suddenly it hits you that the standard DropDownList doesn't support optgroups.
After doing some quick research you decide to create a WebControlAdapter as workaround, even though it probably
isn't the greatest solution for this issue.
Step 1:
Within the App_Browsers, create a browser file, add the following xml:
<browsers> <browser refID="Default"> <controlAdapters> <adapter controlType="System.Web.UI.WebControls.DropDownList" adapterType="CSTruter.DropDownListAdapter" /> </controlAdapters> </browser> </browsers>
Step 2:
Define Group as an attribute within your ListItem(s), something you can also do in the codebehind file, e.g. during databinding.
<asp:DropDownList runat="server" ID="ddlA"> <asp:ListItem Text="C#" Value="1" Group="Microsoft" ></asp:ListItem> <asp:ListItem Text="VB.net" Value="2" Group="Microsoft"></asp:ListItem> <asp:ListItem Text="PHP" Value="3" Group="Open Source"></asp:ListItem> <asp:ListItem Text="Java" Value="4" Group="Open Source" Enabled="false"></asp:ListItem> <asp:ListItem Text="Perl" Value="5" Group="Open Source"></asp:ListItem> </asp:DropDownList>
Step 3:
Create a class inheriting from class WebControlAdapter. Override the RenderContents method (RenderBeginTag & RenderEndTag may also be overriden if needed)
protected override void RenderContents(System.Web.UI.HtmlTextWriter writer) { // The current control being "adaptered" is available within context from the Control property DropDownList dropDownList = (DropDownList)Control; ListItemCollection items = dropDownList.Items; // Retrieve Optgrouping using LinQ var groups = (from p in items.OfType<ListItem>() group p by p.Attributes["Group"] into g select new { Label = g.Key, Items = g.ToList<ListItem>() }); foreach (var group in groups) { if (!String.IsNullOrEmpty(group.Label)) { writer.WriteBeginTag("optgroup"); writer.WriteAttribute("label", group.Label); writer.Write(">"); } int count = group.Items.Count(); if (count > 0) { bool flag = false; for (int i = 0; i < count; i++) { ListItem item = group.Items[i]; writer.WriteBeginTag("option"); if (item.Selected) { if (flag) { throw new HttpException("Multiple selected items not allowed"); } flag = true; writer.WriteAttribute("selected", "selected"); } if (!item.Enabled) { writer.WriteAttribute("disabled", "true"); } writer.WriteAttribute("value", item.Value, true); if (this.Page != null) { this.Page.ClientScript.RegisterForEventValidation(dropDownList.UniqueID, item.Value); } writer.Write('>'); HttpUtility.HtmlEncode(item.Text, writer); writer.WriteEndTag("option"); writer.WriteLine(); } } if (!String.IsNullOrEmpty(group.Label)) { writer.WriteEndTag("optgroup"); } } }
Consider:
When binding from a datasource, (which you wish to persist using ViewState) you will need to override the adapter ViewState methods in order to persist optgroups, observe:
private Object _ViewState; protected override void OnLoad(EventArgs e) { if (Page.IsPostBack) { if (_ViewState != null) { Object[] groups = (Object[])_ViewState; DropDownList dropDownList = (DropDownList)Control; // Add saved optgroups to ListItems for (Int32 i = 0; i < groups.Length; i++) { dropDownList.Items[i].Attributes["Group"] = groups[i].ToString(); } } } base.OnLoad(e); } protected override void LoadAdapterViewState(object state) { // Retrieve existing state _ViewState = state; } protected override object SaveAdapterViewState() { DropDownList dropDownList = (DropDownList)Control; Int32 count = dropDownList.Items.Count; Object[] values = new Object[count]; // Retrieve Optgrouping from ListItem for (int i = 0; i < count; i++) { values[i] = dropDownList.Items[i].Attributes["Group"]; } return values; }
If all goes according to plan, the WebControlAdapter will override the default markup for all DropDownLists in your solution.
Date - 2010-03-11 21:47:12
Comments - 0
First 11 12 13 14 15 16 17 18 19 20 Last / 42 Pages (83 Entries)