DevelopMENTAL Madness

Monday, February 18, 2008

Search the source of SQL Server views and stored procedures


Ever have to work on a database with a large number of views and you needed to search the content of the objects? Well, here's a query which will allow you to search inside your view objects:


DECLARE @search VARCHAR(1000)
SET @search = '[text]'

SELECT c.[Text]
FROM dbo.sysobjects AS v
INNER JOIN dbo.syscomments c ON c.id = v.id
AND CASE WHEN c.Number > 1 THEN c.Number
ELSE 0 END = 0
WHERE v.type = 'V' AND c.[Text] LIKE '%' + @search + '%'



Just set the value of @search to the value you'd like to find.

This was very useful to me as I was trying to muddle through an application which used a lot of nested views and I needed to determine which views referenced the view I was working on.

As a bonus (and of course for my own benefit) here's a method to search the source of stored procedures:

DECLARE @find VARCHAR(1000)

SET @find = '{search text here}'

SELECT
sp.name,
ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')
AND ISNULL(smsp.definition, ssmsp.definition) LIKE '%' + @find + '%'

Labels: , ,

Wednesday, February 13, 2008

Better Generate Scripts Tool for SQL 2005

Frustrated by the inability to script SQL Server 2005 objects with BOTH a CREATE and DROP statement? Generally, I write all my scripts with both statements and then save them and include them in source control, so this hasn't really been a problem for me. But I recently started working on a new project where stored proceedures haven't been maintained in source control and the generate scripts tool is used to sync changes between environments and there are a lot of stored procedures.


So I decided to google for a way to get around this. Greatfully the SqlTeam has created an app named "Scriptio". It lets you generate both CREATE and DROP and has very flexible options similar to SQL 2000 options. You can find it here:


http://www.sqlteam.com/publish/scriptio/.


And for credit where it's due, here's where I heard about it:


http://weblogs.sqlteam.com/tarad/archive/2006/09/20/12374.aspx.


Thanks to Tara Kizer!

Labels: , ,

Tuesday, February 12, 2008

Extend Anonymous Types using Reflection.Emit

I've been playing with the new ASP.NET MVC Framework CTP which uses anonymous types to build url paths which allow you to change the path format. I'm really enjoying the control and the separation of concerns you get.


As I've been writing my MVC application I needed to maintain a parameter in all urls for authenticated users but I didn't want to have to manually include the paramter in every url, form action and redirect. So I wrote some extension functions which wrap the Html and Url helper class methods used to build urls (ActionLink, Form and Url). Additionally, I wrote a controller class which inherits from Controller and overrides RedirectToAction. Then each of my contrllers inherit from my class like this Controller --> myBaseController --> [Instance]Controller. Each of these methods has a set of overloads with a similar signature. In particular each has an overload which accepts an object. This is used to pass in an anonymous type like this:


Html.ActionLink("click here", new {Controller = "Home", Action = "Index", Id = 8 });

RedirectToAction(new {Controller = "Home", Action = "Index", CustomParam = 123});


My wrapper methods needed to append an additional parameter. So initially I was able to do this by creating a new anonymous type composed of the passed in type and my new field like this:


var newValues = new { values, myParam = "user value" };


I chose this method because the object passed in could have any number of properties of any type and so there was no way for me to discover then and create a new type composed of the type and my own type.


This worked great as long as I used my wrapper methods and my controller classes inherited from my custom class.


However, my wrapper methods and controller class were local to my web application in the App_Code directory. So I decided that I wanted to use these classes as part of an MVC library of classes for all my MVC applications. But when I moved these classes to a class library project and these classes were now in an assembly which was external to the web application everything broke down.


Stepping through the code I realized that the anonymous types I was composing was now creating not one type with a composite of the properties of both objects, but an object only two properties. One property was the parameter I was trying to add, the other was a single property of the type of anonymous object passed in as an argument to my method like this in the debugger:


{{Controller = "Home", Action = "Index"}, myParam = "user value"}


As you can see the first type is made a "subtype" of the new type. So I tried converting the types to a dictionary (which is essentially what an anonymous type is). But that didn't work. Reading up on anonymous types I found that the types are created at compile time and types with matching interfaces are reused by creating a single type for all matches. But I needed to create a type at runtime.


I've worked on a project before where we had origionally planned on creating runtime types using Reflection.Emit, but we decided to save the result as an assembly so we could have it at compile time. I figured if I could dynamically discover the properties of the anonymous type and then create a new type which combined the existing properties plus my own then I could pass that as an argument to the url builder methods of the MVC framework. So I read up as a refresher and decided it sounded good in theory. I then proceeded to write up a proof of concept and it worked!


The process works like this: First use System.ComponentModel.GetProperties to get a PropertyDescriptorCollection from the anonymous type. Fire up Reflection.Emit to create a new dynamic assembly and use TypeBuilder to create a new type which is a composite of all the properties involved. Then cache the new type for reuse so you don't have to take the hit of building the new type every time you need it.


Below is the full class definition. I hope this helps someone out there or at least spurs some discussion about how effective this method is:






using System;
using System.Collections.Generic;
using System.Reflection;
using System.Reflection.Emit;
using System.ComponentModel;
using System.Threading;

namespace DevelopmentalMadness.Utility
{
/// <summary>
/// A Utility class used to merge the properties of
/// heterogenious objects
/// </summary>
public class TypeMerger
{
//assembly/module builders
private static AssemblyBuilder asmBuilder = null;
private static ModuleBuilder modBuilder = null;

//object type cache
private static IDictionary<String, Type> anonymousTypes
= new Dictionary<String, Type>();

//used for thread-safe access to Type Dictionary
private static Object _syncLock = new Object();

/// <summary>
/// Merge two different object instances into a single
/// object which is a super-set
/// of the properties of both objects
/// </summary>
public static Object MergeTypes(Object values1, Object values2)
{
//create a name from the names of both Types
String name = String.Format("{0}_{1}", values1.GetType(),
values2.GetType());
String name2 = String.Format("{0}_{1}", values2.GetType(),
values1.GetType());

Object newValues = CreateInstance(name, values1, values2);
if (newValues != null)
return newValues;

newValues = CreateInstance(name2, values2, values1);
if (newValues != null)
return newValues;

//lock for thread safe writing
lock (_syncLock)
{
//now that we're inside the lock - check one more time
newValues = CreateInstance(name, values1, values2);
if (newValues != null)
return newValues;

//merge list of PropertyDescriptors for both objects
PropertyDescriptor[] pdc = GetProperties(values1, values2);

//make sure static properties are properly initialized
InitializeAssembly();

//create the type definition
Type newType = CreateType(name, pdc);

//add it to the cache
anonymousTypes.Add(name, newType);

//return an instance of the new Type
return CreateInstance(name, values1, values2);
}
}

/// <summary>
/// Instantiates an instance of an existing Type from cache
/// </summary>
private static Object CreateInstance(String name,
Object values1, Object values2)
{
Object newValues = null;

//merge all values together into an array
Object[] allValues = MergeValues(values1, values2);

//check to see if type exists
if (anonymousTypes.ContainsKey(name))
{
//get type
Type type = anonymousTypes[name];

//make sure it isn't null for some reason
if (type != null)
{
//create a new instance
newValues = Activator.CreateInstance(type, allValues);
}
else
{
//remove null type entry
lock (_syncLock)
anonymousTypes.Remove(name);
}
}

//return values (if any)
return newValues;
}

/// <summary>
/// Merge PropertyDescriptors for both objects
/// </summary>
private static PropertyDescriptor[] GetProperties(Object values1,
Object values2)
{
//dynamic list to hold merged list of properties
List<PropertyDescriptor> properties
= new List<PropertyDescriptor>();

//get the properties from both objects
PropertyDescriptorCollection pdc1
= TypeDescriptor.GetProperties(values1);
PropertyDescriptorCollection pdc2
= TypeDescriptor.GetProperties(values2);

//add properties from values1
for (int i = 0; i < pdc1.Count; i++)
properties.Add(pdc1[i]);

//add properties from values2
for (int i = 0; i < pdc2.Count; i++)
properties.Add(pdc2[i]);

//return array
return properties.ToArray();
}

/// <summary>
/// Get the type of each property
/// </summary>
private static Type[] GetTypes(PropertyDescriptor[] pdc)
{
List<Type> types = new List<Type>();

for (int i = 0; i < pdc.Length; i++)
types.Add(pdc[i].PropertyType);

return types.ToArray();
}

/// <summary>
/// Merge the values of the two types into an object array
/// </summary>
private static Object[] MergeValues(Object values1,
Object values2)
{
PropertyDescriptorCollection pdc1
= TypeDescriptor.GetProperties(values1);
PropertyDescriptorCollection pdc2
= TypeDescriptor.GetProperties(values2);

List<Object> values = new List<Object>();
for(int i = 0; i < pdc1.Count; i++)
values.Add(pdc1[i].GetValue(values1));

for(int i = 0; i < pdc2.Count; i++)
values.Add(pdc2[i].GetValue(values2));

return values.ToArray();
}

/// <summary>
/// Initialize static objects
/// </summary>
private static void InitializeAssembly()
{
//check to see if we've already instantiated
//the static objects
if (asmBuilder == null)
{
//create a new dynamic assembly
AssemblyName assembly = new AssemblyName();
assembly.Name = "AnonymousTypeExentions";

//get the current application domain
AppDomain domain = Thread.GetDomain();

//get a module builder object
asmBuilder = domain.DefineDynamicAssembly(assembly,
AssemblyBuilderAccess.Run);
modBuilder = asmBuilder.DefineDynamicModule(
asmBuilder.GetName().Name, false
);
}
}

/// <summary>
/// Create a new Type definition from the list
/// of PropertyDescriptors
/// </summary>
private static Type CreateType(String name,
PropertyDescriptor[] pdc)
{
//create TypeBuilder
TypeBuilder typeBuilder = CreateTypeBuilder(name);

//get list of types for ctor definition
Type[] types = GetTypes(pdc);

//create priate fields for use w/in the ctor body and properties
FieldBuilder[] fields = BuildFields(typeBuilder, pdc);

//define/emit the Ctor
BuildCtor(typeBuilder, fields, types);

//define/emit the properties
BuildProperties(typeBuilder, fields);

//return Type definition
return typeBuilder.CreateType();
}

/// <summary>
/// Create a type builder with the specified name
/// </summary>
private static TypeBuilder CreateTypeBuilder(string typeName)
{
//define class attributes
TypeBuilder typeBuilder = modBuilder.DefineType(typeName,
TypeAttributes.Public
TypeAttributes.Class
TypeAttributes.AutoClass
TypeAttributes.AnsiClass
TypeAttributes.BeforeFieldInit
TypeAttributes.AutoLayout,
typeof(object));

//return new type builder
return typeBuilder;
}

/// <summary>
/// Define/emit the ctor and ctor body
/// </summary>
private static void BuildCtor(TypeBuilder typeBuilder,
FieldBuilder[] fields, Type[] types)
{
//define ctor()
ConstructorBuilder ctor = typeBuilder.DefineConstructor(
MethodAttributes.Public,
CallingConventions.Standard,
types
);

//build ctor()
ILGenerator ctorGen = ctor.GetILGenerator();

//create ctor that will assign to private fields
for (int i = 0; i < fields.Length; i++)
{
//load argument (parameter)
ctorGen.Emit(OpCodes.Ldarg_0);
ctorGen.Emit(OpCodes.Ldarg, (i + 1));

//store argument in field
ctorGen.Emit(OpCodes.Stfld, fields[i]);
}

//return from ctor()
ctorGen.Emit(OpCodes.Ret);
}

/// <summary>
/// Define fields based on the list of PropertyDescriptors
/// </summary>
private static FieldBuilder[] BuildFields(TypeBuilder typeBuilder,
PropertyDescriptor[] pdc)
{
List<FieldBuilder> fields = new List<FieldBuilder>();

//build/define fields
for (int i = 0; i < pdc.Length; i++)
{
PropertyDescriptor pd = pdc[i];

//define field as '_[Name]' with the object's Type
FieldBuilder field = typeBuilder.DefineField(
String.Format("_{0}", pd.Name),
pd.PropertyType,
FieldAttributes.Private
);

//add to list of FieldBuilder objects
fields.Add(field);
}

return fields.ToArray();
}

/// <summary>
/// Build a list of Properties to match the list of private fields
/// </summary>
private static void BuildProperties(TypeBuilder typeBuilder,
FieldBuilder[] fields)
{
//build properties
for (int i = 0; i < fields.Length; i++)
{
//remove '_' from name for public property name
String propertyName = fields[i].Name.Substring(1);

//define the property
PropertyBuilder property = typeBuilder.DefineProperty(propertyName,
PropertyAttributes.None, fields[i].FieldType, null);

//define 'Get' method only (anonymous types are read-only)
MethodBuilder getMethod = typeBuilder.DefineMethod(
String.Format("Get_{0}", propertyName),
MethodAttributes.Public MethodAttributes.SpecialName
MethodAttributes.HideBySig,
fields[i].FieldType,
Type.EmptyTypes
);

//build 'Get' method
ILGenerator methGen = getMethod.GetILGenerator();

//method body
methGen.Emit(OpCodes.Ldarg_0);
//load value of corresponding field
methGen.Emit(OpCodes.Ldfld, fields[i]);
//return from 'Get' method
methGen.Emit(OpCodes.Ret);

//assign method to property 'Get'
property.SetGetMethod(getMethod);
}
}
}
}


Labels: , , , ,