.NET
Fun with Microsoft's Common Language Runtime
Turns out that I'm spending an extra week in Los Angeles, but this time its for something new for me: I'm helping Brock Allen and Jason Diamond with next run of Guerrilla ASP.NET 2.0 at the Hilton South Bay.
In some ways, this is coming full circle for me. Many years ago, I took DevelopMentor's ASP.NET 1.0 class (when the product was still in Beta) and that's what got me excited about what DM does. Its going to be fun to getting back to ASP.NET after a year or so away from it on a day-to-day basis.
A few weeks back, the ADO.NET team released the first in a series of ADO.NET v.Next releases. This was our first look at several exciting technologies like the Entity Data Framework and Mapping Views. Well today, the team announced the release a designer for working with ADO.NET v.Next Entity data.
Go have a look-see.
I was happily coding up an example where I have a ADO.NET 1.x DataTable that's has an auto-incrementing field. Being the conservative type I am, I had coded the two fields to be of type Byte. When trying to create the ForeignKeyConstraint related to that column, I got an exception:
Property not accessible because 'Parent Columns and Child Columns don't have type-matching columns.'.
Sure enough, if you go check the DataType of the auto-incrementing column, its been "upgraded" to System.Int32. Silently. Nice. Now that you know the problem, the fix is easy. Make the columns Int32's. Ick.
Leason learned after 20 minutes of chasing that one down. Its going to be a grrrreeeaaat week.
A few months ago, I published a step-by-step procedure for using certificate to sign an assembly so an external access or unsafe assembly could be run without having to set the hosting database to trustworthy. The major problem with that process was its weight: a lot of steps and some still requirements in terms of certificate management. Turns out that yes, there is an easier way to make it work.
Part one -- preparing the master database
- As needed, start a series of queries against the MASTER database.
- Create the target database (meaning execute a CREATE DATABASE query).
- Code and compile the assembly you want to deploy. Make sure you sign the assembly with a strong name key file. This can be done with a PFX file generated by Visual Studio.
- Create an asymmetric key from from the compiled assembly using a statement like this: create asymmetric key <key_name> from executable file = '<path_to_dll_file>'
- Create a login based on that asymmetric key using a statement like this: create login <login_name> from asymmetric key <key_name>
- Grant that login the right to create either or both an unsafe or external access assembly (as needed) using: grant unsafe assembly to <login_name>
Part two -- preparing the hosting database
- As needed, start a series of queries against the desired user database.
- Create a user in that database mapped to the login created in part, step 5. (e.g. create user <db_user> from login <login_name>)
- Give that user the right to catalog an assembly, e.g.: grant create assembly to <db_user>
- Catalog the desired assembly using the now trusted asymmetric key with a statement like: create assembly <assembly_name> authorization <db_user> from '<path_to_dll_file>' with permission_set = unsafe
From there on, its just a matter of mapping your methods and classes to the desired T-SQL objects.
Note: the following is just to help Google find this post.
is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. RegistryReader
So it only makes sense that if you have XML in the database that evetually you're going to want to do something with it. The combination of XML Indexing and XQuery give us efficient albeit limited transformational functionality in such cases. But what would you do if you wanted to use XSL/T stylesheet instead. SQL Server 2005 doesn't offer an out of the way to do that with T-SQL, but as I say in my talks about SQLCLR, its a reasonable way to get the job done. The first thing you'd do is cruft up a public static method around System.Xml.Xsl.CompiledTransform like this:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.Xsl;
using System.IO;
namespace DM.Examples
{
public partial class XmlLibrary
{
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = false, IsPrecise = false, SystemDataAccess = SystemDataAccessKind.None)]
[return: SqlFacet(IsFixedLength = false, IsNullable = true, MaxSize = -1)]