If you want a quick way to set some default indexes up in your database the best candidates for your indexes would be the foreign keys. They are usually highly selective and many of our queries tend to have some sort of filter on the foreign keys, this makes them good candidates for an index.
You may be quick to think:
'but sql server has indexes on the foreign keys'
That's not true, since a foreign key is an integrity constraint while an index is for performance. Have a look at an interesting discussion over at sqlblog about this: http://sqlblog.com/blogs/greg_low/archive/2008/07/29/indexing-foreign-keys-should-sql-server-do-that-automatically.aspx
The following SQL Script will create a non clustered index on each of the foreign key columns in our database.
--Create non clustered indexes for all foreign key references in the database
DECLARE @sql nvarchar(max)
SELECT @sql = IsNull(@sql + ';' + char(13) , '') + 'CREATE NONCLUSTERED INDEX [IX_' + tablename + '_' + columnname +'] ON [dbo].[' + tablename + '] ( [' + columnname + '] ASC)'
FROM
(
--Script all foreign key columns that are not already an index SELECT
o.name AS tablename, cols.name AS columnName FROM sys.foreign_key_columns fc
inner join sys.objects o on fc.parent_object_id = o.object_id
inner join sys.columns cols on cols.object_id = o.object_id and fc.parent_column_id = cols.column_id
EXCEPT
SELECT o.name, cols.name FROM sys.index_columns icols
inner join sys.objects o on icols.object_Id = o.object_id
inner join sys.columns cols on cols.object_id = o.object_id and icols.column_id = cols.column_id
) T
ORDER BY
tablename,
columnname
--Display the sql that will be executed
Print @sql
--Run the sql and create all the indexes
exec sp_executesql @sql
To monitor the performance of your existing indexes check out the script sp_indexInfo by Tibor Karaszi.
It is a stored procedure that provides some useful metrics for all the indexes in a given table or in a given database.
Plenty of powerful new language features have fairly recently become mainstream with the launch of VS 2008 in Nov 2007 that supports C# 3.0, amongst which:
- Implicitly Typed Local Variables
- Extension Methods
- Lambda Expressions
- Object and Collection Initializers
- Anonymous Types
- Query Expressions
- Automatically Implemented Properties
Although most of us are still trying to fully absorb the above features, there has been some speculation going on about what sort of features we would like to see in the next version of C# and Jeremy Miller has posted a question with what he would like to see.
Here are some of the language features I would really like to see in C#:
1. Implementing Interfaces by delegation to fields
I would like to see support in C# 4.0 that would allow us to delegate the implementation of an interface to a field in the implementing class. For example this could look something like the following:
//faux code
public class Foo : IList<string>
{
private List<string> _Collection implements IList<string>;
public Foo()
{
_Collection = new List<string>();
}
}
The field could be delegated to implement one or more interfaces from the encapsulating class by separating them with a comma. This would remove a lot of redundant code in scenarios like the above one, ie. Instead of implementing a lot of pass through functions on the encapsulating class (Foo) the functions are directly mapped to the delegated interface implementation. This sort of functionality would also enhance support for mixins
This is known as the delegation pattern and from wikipedia:
The delegation pattern is a technique where an object outwardly expresses certain behaviour but in reality delegates responsibility for implementing that behaviour to an associated object in an Inversion of Responsibility. The delegation pattern is the fundamental abstraction that underpins composition (also referred to as aggregation), mixins and aspects.
Taking it one step even further, one could override the delegated implementation in a syntax like so:
public class Foo : IList<string>
{
private List<string> _Collection { get; set; } implements IList<string>;
public Foo()
{
_Collection = new List<string>();
}
//This would override the delegated implementation
// for nice mixin functionality and easy decorator pattern implementation
public int IList.Add(string value)
{
if (!_Collection.Contains(value))
_Collection.Add(value);
}
}
2. Anonymous type return values
I would like to see the anonymous types becoming first class 'citizens' in C#. Anonymous types can only be used in a local scope and cannot be returned from functions. It would be nice if we could return our strongly typed Linq Query Projection from a function eg:
//faux code
public var GetProductInfos()
{
var productInfos =
from p in products
select new { p.ProductName, p.Category, Price = p.UnitPrice };
return productInfos;
}
3. Some Duck-typing or Structural Subtyping support
If a class has a property or a method signature that is the same as the method signature on a declared interface, then that class implicitly implements that interface, if it is not already inheriting from it. The class would implicitly implement an interface if and only if it would implement all the method signatures of a given interface. Basically
if it walks like a duck and it quacks like a duck, then I would call it a duck! (James Riley)
So what is the difference with Structural Subtyping? I would argue that structural subtyping is more suitable to the static style of C#, since it is a 'static duck typing', or according to wikipedia:
Duck typing differs from structural typing in that only the part of the structure accessed at run time is checked for compatibility.
Lets see how this could be beneficial through a use case:
The .Net framework has a few controls that implement a ReadOnly property amongst which are the TextBox, DataGrid, NumericUpDown.
Lets define the IReadOnlyRestrictable interface in our source like so:
public interface IReadOnlyRestricable
{
bool ReadOnly { get; set; }
}
Let's assume that we wanted to Loop through all the controls on a form and anything that satisfies the above interface signature (i.e has a property 'ReadOnly') set its readonly property to true. With ducktyping the qualifying controls would be cast to a valid IReadOnlyRestrictable instance like below, without needing to resort to reflection
foreach (Control c in f.Controls)
{
//would like to have implicit cast to IReadOnlyRestrictable if interface contract is in class we are checking against
IReadOnlyRestricable editable = c as IReadOnlyRestricable;
if (editable != null)
editable.ReadOnly = true;
}
The main advantage I see with ducktyping is that you can declare interfaces for libraries that you do not have access to, this can be useful in scenarios where you would like to minimise dependancies, check out Phil Haacks more extensive post on duck typing and why he believes it would benefit C# developers.
Apparantly, the c# foreach operator already uses duck typing according to Krzysztof Cwalina
4. Safe Null Dereferencing Operator
I would really like to see a safe way to dereference a null value in an expression that of form Object.Property.Property.Value.
For example if we had Customer?.FirstName and Customer was null, then the expression would evaluate to null instead of raising a null reference exception.
Some more examples of how this could work:
//FAUX CODE
//this would throw a null reference exception as usual if either Customer or Order was null
int orderNumber = Customer.Order.OrderNumber;
//this would not compile since it would require a nullable return type
int orderNumber = Customer.Order?.OrderNumber;
//this would return null if a Customer was null or if Order was null
int? orderNumber = Customer?.Order?.OrderNumber;
if (orderNumber.HasValue)
//... do something with it
//instead of having to do
if ((Customer != null) && (Customer.Order != null))
int a = Customer.Order.OrderNumber
Wrap up
Here is a recent interview of the C# 4.0 team in the room where all the magic happens
What is your take? Do you agree? What features you would like to see?
Have you been shying out of putting extra functions on your SQL Server 2005 using SQL CLR simply because you would like to avoid DLL Hell?!
Well, the good news is that you need not be so worried, since SQL Server does not link to the dll itself, but instead it embeds the assembly in the database. What's even better is that you can deploy your assembly by simply scripting it.
Lets do this with an example, in the previous post there is a sample that shows how to create support for regular expressions using SQL CLR. To deploy this on your production server without having to copy any url's you simply need to open Sql Server Management Studio and go to:
DB > Programmability > Assemblies > AssemblyName > RightClick > ScriptAssembly As > Create To > Clipboard | File | New Window
like so:
In fact this will create the following sql script for you:
CREATE ASSEMBLY [TextFunctions]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300C5987F480000000000000000E0000E210B0108000010000000200000000000000E290000002000000040000000004000002000000010000004000000000000000400000000000000008000000010000000000000030040050000100000100000000010000010000000000000100000000000000000000000C02800004B000000004000002803000000000000000000000000000000000000006000000C000000442800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000014090000002000000010000000100000000000000000000000000000200000602E7273726300000028030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C00000000600000001000000030000000000000000000000000000040000042000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000F0280000000000004800000002000500DC200000680700000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CA0F00281100000A2D090F01281100000A2C067E1200000A2A0F00281300000A0F01281300000A17281400000A281500000A2A000330040042000000000000000F00281100000A2D120F01281100000A2D090F02281100000A2C067E1600000A2A0F00281300000A0F01281300000A0F02281300000A17281700000A731800000A2A1E02281900000A2A000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000074020000237E0000E00200005403000023537472696E6773000000003406000008000000235553003C0600001000000023475549440000004C0600001C01000023426C6F620000000000000002000001471500000900000000FA0133001600000100000016000000020000000300000005000000190000000F000000010000000300000000000A0001000000000006003F0038000A00670052000A00720052000600AC009A000600C9009A0006000101E20006000F01E200060023019A0006003C019A00060057019A00060072019A0006008B019A000600A4019A000600C3019A000600E0019A0006000A02F70143001E02000006004D022D0206006D022D020A00B40299020E001103F2020E001703F2020000000001000000000001000100010010001C00000005000100010050200000000096007C000A0001008420000000009600870013000300D22000000000861894001E00060000000100C90200000200CF0200000100C90200000200CF02000003003803210094002200290094002200310094002200390094002700410094002200490094002200510094002200590094002200610094002200690094002200710094002200790094002200810094002C00910094003200990094001E00A10094001E001900D7025C001100E20260001900E8026400A9002403680011002C037000190044037600A90049037A00190094002200090094001E002000830037002E004300D8002E000B0083002E001B0090002E002300BA002E003300BA002E003B00C0002E005B00BA002E004B00BA002E005300BA002E006B00EB002E007B00FD002E006300D8002E007300F4004000830037000480000001000000000000000000000000008B02000002000000000000000000000001002F00000000000200000000000000000000000100460000000000020000000000000000000000010038000000000000000000003C4D6F64756C653E005465787446756E6374696F6E732E646C6C00526567756C617245787072657373696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E0053716C537472696E670052656745784D617463680052656745785265706C616365002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F705365727669636573004775696441747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005465787446756E6374696F6E73004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500696E707574007061747465726E006765745F49734E756C6C0046616C7365006765745F56616C75650053797374656D2E546578742E526567756C617245787072657373696F6E730052656765780052656765784F7074696F6E730049734D61746368006F705F496D706C69636974007265706C6163656D656E74004E756C6C005265706C616365000000000003200000000000D51DC224975A24449F15285FBFA1D5530008B77A5C561934E0890800021109110D110D0A0003110D110D110D110D03200001042001010E04200101020520010111450420010108240100020054020F497344657465726D696E6973746963015402094973507265636973650103200002030611090320000E070003020E0E11590500011109020306110D0800040E0E0E0E11590C010007312E302E302E3000002901002464313338356464382D376539322D343065362D383234632D646632376639393862656630000005010000000017010012436F7079726967687420C2A920203230303700001201000D5465787446756E6374696F6E7300000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000C5987F4800000000020000005F0000006028000060180000525344536A480B33B1CFDD46B12FF5343A6664BF01000000433A5C446576656C6F706D656E745C54657374696E675C434C525C5465787446756E6374696F6E735C6F626A5C52656C656173655C5465787446756E6374696F6E732E7064620000E82800000000000000000000FE280000002000000000000000000000000000000000000000000000F02800000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000D00200000000000000000000D00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00430020000010053007400720069006E006700460069006C00650049006E0066006F0000000C020000010030003000300030003000340062003000000044000E000100460069006C0065004400650073006300720069007000740069006F006E00000000005400650078007400460075006E006300740069006F006E0073000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000044001200010049006E007400650072006E0061006C004E0061006D00650000005400650078007400460075006E006300740069006F006E0073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003000370000004C00120001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005400650078007400460075006E006300740069006F006E0073002E0064006C006C0000003C000E000100500072006F0064007500630074004E0061006D006500000000005400650078007400460075006E006300740069006F006E0073000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C0000001039000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
Without having the dll nor the requirement to link to any dll you now have part of your change script for deploying this assembly to your production server.
Of course, the server still needs to be CLR Enabled, if it is not CLR Enabled you will need to enable it before creating the assembly:
sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
Before we can test our assembly we register our new functions
CREATE Function RegExMatch(@Input NVARCHAR(512),@Pattern NVARCHAR(127))
RETURNS BIT
EXTERNAL NAME TextFunctions.RegularExpressions.RegExMatch
GO
CREATE Function RegExReplace(@Input NVARCHAR(512),@Pattern NVARCHAR(127), @Replacement NVARCHAR(512))
RETURNS NVARCHAR(512)
EXTERNAL NAME TextFunctions.RegularExpressions.RegExReplace
GO
Now we lets test our newly available functions:
Select dbo.RegExMatch('333-4444','\d{3}-\d{4}')
--returns 1
Select dbo.RegExReplace('333-4444','(\d+)-(\d+)', '$2-$1')
--reverses two numbers seperated by a dash returns 4444-333
And that's all needed. If you would like to check out the source code for creating the original dll and compiling it, have a look here
In this post I'll show just how easy it is to make your SQL Server 2005 database support Regular Expressions through what is known as SQL CLR
Just Fire up Visual Studio and create a new library project (I called it TextFunctions)
Add a new Class and Call it Regular Expressions and simply paste in the following Code:
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Data.SqlTypes;
public class RegularExpressions
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true, IsPrecise=true)]
public static SqlBoolean RegExMatch(SqlString input, SqlString pattern)
{
if (input.IsNull || pattern.IsNull) //nulls dont qualify for a match
return SqlBoolean.False;
//Use the static IsMatch method. This is more performant than creating a
// new instance of Regex as the static method also caches the last expressions we used.
return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase);
}
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString RegExReplace(SqlString input, SqlString pattern, SqlString replacement)
{
if (input.IsNull || pattern.IsNull || replacement.IsNull)
return SqlString.Null;
return new SqlString(Regex.Replace(input.Value, pattern.Value, replacement.Value, RegexOptions.IgnoreCase));
}
}
In this example we have two functions: one to Match a Regular expression pattern and one to perform a Replace based on a regular expression pattern.
To enable our SQL Server database to make use of the above functions we need to follow the steps below:
- Compile the library for Release
- CLR Enable Sql Server with:
sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
Reference the assembly and register the functions:
CREATE ASSEMBLY TextFunctions FROM 'c:\Development\Testing\CLR\TextFunctions\bin\Release\TextFunctions.dll'
GO
CREATE Function RegExMatch(@Input NVARCHAR(512),@Pattern NVARCHAR(127))
RETURNS BIT
EXTERNAL NAME TextFunctions.RegularExpressions.RegExMatch
GO
CREATE Function RegExReplace(@Input NVARCHAR(512),@Pattern NVARCHAR(127), @Replacement NVARCHAR(512))
RETURNS NVARCHAR(512)
EXTERNAL NAME TextFunctions.RegularExpressions.RegExReplace
GO
Now we're ready to put our new functions to the test:
select dbo.RegExMatch('me@mymail.com','^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,6}$')
--Verifies an email address
SELECT dbo.RegExMatch('12354', '\d')
--Verifies number Returns 1
SELECT dbo.RegExReplace('Poem Title (Author)', '.*?\((.*?)\).*', '$1')
-- Returns the match within the parenthesis, returns: Author (Useful for splitting one column in two)
SELECT EmailAddress, dbo.RegExMatch(EmailAddress,'^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,6}$')
FROM Users
ORDER BY 2
--Checks your users table for Invalid Email addresses,
--at the top of the results all users with invalid email addresses
Regular expressions can come in really handy especially in data cleansing/transformation operations in your database.
Here are some further pointers to boost creativity with regular expressions:
Email Validation with Regular Expressions
Regular Expressions Cheat Sheet
RegExLib - Library of Regular Expressions for .Net
Well, i guess that's a pretty elaborate title, but it's what this post is about. How you text-wrapping when exporting to Excel via SQL Server Reporting services 2005 on the web be disabled.
It is unfortunate that there is no configuration option in when authoring a report to control whether the text can wrap or not.
Why disable text wrapping?
In the above screenshot we can see that when a given cell is constrained in size (i.e fixed height).
With text wrap on: the cell will not show the complete text unless it is wide enough.
With text wrap off: the complete text will display by overflowing onto the adjacent empty cells. So long as the next cells are empty.
But why would I want to do that when I could simply merge cells B + C + D together? It all comes down to usability and user expectations. If the end user expects to be able to sort the data in the columns then merged cells is just a feature that will be an obstacle in his path. In the screenshot below I try to sort by column B, but 1 of the rows contains merged cells so Excel naturally prompts the user.
If you do find yourself with a reporting services report that you have spent hours creating in your report designer and you are handed the above niche requirement, there are the following options for you friend:
If a third party product is not an option for you, you may find the following helpful.
Disable Text Wrap using Microsoft.Office.Interop.Excel
Office.Interop requires that Excel is installed on the machine that will render the report. Here is the function required:
private static void DisableTextWrap(string fileName)
{
Application excel = new Application();
try
{
Workbook workbook = excel.Workbooks.Open(
fileName,
Type.Missing, false, Type.Missing, Type.Missing,
Type.Missing, true, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
try
{
Worksheet wsheet = workbook.Sheets[1] as Worksheet;
try
{
Range excelRange = wsheet.UsedRange;
try
{
excelRange.WrapText = false;
}
finally
{
Marshal.ReleaseComObject(excelRange);
}
}
finally
{
Marshal.ReleaseComObject(wsheet);
}
workbook.Save();
}
finally
{
workbook.Close(false, fileName, null);
Marshal.ReleaseComObject(workbook);
}
}
finally
{
excel.Quit();
Marshal.ReleaseComObject(excel);
}
}
The above function will open an excel file and disable text-wrap in the used area of only the first spreadsheet
Enable a Web Server to run Excel through COM automation
In order to enable a web server to run Excel through COM automation you can follow this very helpful and detailed advice:
http://blog.crowe.co.nz/archive/2006/03/02/589.aspx
One point to note after following the above instructions
After running DCOMCNFG
In the Security Tab
Under Activation
Click customise. I would recommend that the excel is launched (activated) by a user that has already used excel once on the server you are trying to perform this. So, first create a user, then go to Excel right click and use 'Run As..' option and select the newly created user. The reason for doing this is that when a user runs excel for the first time, excel creates a profile for them and does some custom installation and also pops up a dialog box which is something we would like to avoid during the COM automation. Doing this will ensure that the installation of the user profile is not trying to take place under the COM automation.
Hope this helps someone!
Just a quick tip.
I was unaware until recently that SQL Server 2005 has nicely built in support for hashing and it is called hashbytes
HashBytes ( '<algorithm>', { @input | 'input' } )
<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1
Parameters are the algorithm you wish to hash to and the input string to hash.
Here is a sample along with the return values commented in the next line:
Select HashBytes('MD2', 'Hello world!')
--0x63503D3117AD33F941D20F57144ECE64
Select HashBytes('MD4', 'Hello world!')
--0x0D7A9DB5A3BED4AE5738EE6D1909649C
Select HashBytes('MD5', 'Hello world!')
--0x86FB269D190D2C85F6E0468CECA42A20
Select HashBytes('SHA', 'Hello world!')
--0xD3486AE9136E7856BC42212385EA797094475802
Select HashBytes('SHA1', 'Hello world!')
--0xD3486AE9136E7856BC42212385EA797094475802
MSDN: http://msdn2.microsoft.com/en-us/library/ms174415.aspx
Quite often we create systems that make use of web services so as to expose our Data, or Business methods for client applications to consume them. In a classic 3 tier scenario it may be either the data layer exposed through the web service as a data service, or the business layer exposed through the web service as a business service. I would say that both are perfectly valid for exposure through a web service depending on our needs and requirements. But since web services already exist, why make a custom method and use this method instead of web services? The following paragraph contains some points I dont like when coding standard microsoft web services.
Points I don't like when using Microsoft Web Services.
What I do not like when coding a web service when using the standard .Net framework approach is the feeling of redundant coding I get. Surely you've felt the same at some point, especially when all we are doing is simply coding pass through functions to my business or data layer.
Further to that, I end up having to maintain a mess of web service references in my client application. I need to add one reference to each asmx page created. Further to this, when creating web service references, the Microsoft .Net framework will create a custom Proxy to access this web service. This custom proxy will also re-define any complex types (eg typed datasets or custom classes) returned through the web service. What i dont like about this, is that even though two seperate asmx files may expose the same type, each web service reference added will redifine the same type on its own copy of the proxy. This leaves the client application with multiple definitions of what essentially is the same data type.
What would I like to see instead?
- I would like to be able to switch between 'web service' mode and 'non web service' mode easily, with just a simple switch in the client application.
- I do not want to create redundant code in webmethods just so that I can expose my data or business objects over HTTP. The code is already in those layers, I just want the ability to consume them over an HTTP connection.
- i would just like to add a 'magic' attribute to my business/data classes that would mean 'This object can be invoked over HTTP.
- I would like to be able to consume my business or data objects seamlessly, with no requirements to add tons of web references on my client application.
For these reasons, i thought it would be worthwhile to find a method of dynamically exposing my data or business objects over an HTTP channel. I'd like to share a solution which addresses the above issues, but as in most cases there are tradeoffs.
Whats the tradeoff though? The tradeoff is that this is not webservices and because of that, this custom HTTP invocation protocol is not interoperable across other non .Net platforms.
If interoperability is a requirement in your application then this post does not offer any solutions to the above points. If however interoperability with other platforms is not a requirement, then you may find this helpful. Furthermore this solution requires that your Base Business or Data objects inherit from ContextBoundObject, but this requirement could be overcome in a different implementation.
Method Interception and how this solution works.
This solution is based on the following bullet points:
- Create an object and intercept all method calls to that object
- Determine if we are in a 'web service mode'
- if we are not in 'web service mode', then just call the underlying dataobject method and return its value.
- If we are in 'web service mode' then marshal the call accross to a custom HTTP Handler, this handler will create the dataobject via reflection execute the method and return the value (if any) and output parameters.
The key point of this architecture is the ability to intercept method calls to the object, as soon as we intercept method calls we can decide whether we route the request to the underlying object, or route the request over HTTP to our Handler. This handler is generic and does not require any code modifications. It does however require a reference to our dll that contains our DAL classes.
Thomas Danecker has an excellent post about how to intercept methods with a custom proxy.
http://tdanecker.blogspot.com/2007/09/interception-with-proxies.html
I like that solution because it uses only classes from the .Net framework. What I dont like about it, is the requirement it emposes on the proxyable types to derive from the ContextBoundObject type.
Oren has a nice short post where he compares 7 different options when it comes to method interception, with pros and cons of each:
http://www.ayende.com/Blog/archive/2007/07/02/7-Approaches-for-AOP-in-.Net.aspx
This solution uses interception with native Microsoft .Net classes as per Thomas's example.
Ok, now some code!
Our sample application consists of a DAL, a BLL a UI and a Test Project with some Test Cases. Our project also includes the Dynamic Service itself. So, let's see some of this in action and how it would all fit together. For the purpose of this test, our UI is a console application. This is what it would typically look like.
Listing #1:
using System;
using System.Text;
using DynamicService.Library;
using DynamicService.DAL;
namespace DynamicService.UI
{
class Program
{
static void Main(string[] args)
{
using (WebServiceSettingsScope settingScope =
new WebServiceSettingsScope(
"http://localhost:4040/Dataservice.ashx?db=TestDb", "", "", 3000))
{
TestDal dal = new TestDal();
int i = dal.ReturnInputParameter(10);
Console.WriteLine(i);
}
Console.ReadKey();
}
}
}
And this is what our TestDal class would look like (listing #2):
using System;
using System.Text;
using System.Data;
using DynamicService.Library;
namespace DynamicService.DAL
{
[HttpRemotable]
public class TestDal : ContextBoundObject
{
public TestDal()
{
//This is how the dal knows it's connection string for example...
//the code below could be added in a base dal class that would allow the derived data objects
//to know about the database they are talking to. (useful for multi database scenarios)
DbConnectionStringSettings connectionStringSettings =
ConnectionSettingsScope.ActiveConnectionSettings as DbConnectionStringSettings;
string connectionString;
if (connectionStringSettings != null)
connectionString = connectionStringSettings.ConnectionString;
}
public int ReturnInputParameter(int param)
{
return param;
}
}
}
Well, this should get us going! So, the solution proposes that objects that can be invoked remotely over HTTP are decorated with an HttpRemotable attribute. They must also derive from ContextBoundObject so that their methods can be intercepted by the appropriate proxy and routed accordingly, either to the object directly, or to our generic handler.
Listing #1 shows that the remotable object, in this case our TestDal, should be instantiated within a ConnectionSettingsScope. There are two types of connectionSettingsScope, two classes that derive from this class. There is a DbConnectionSettingsScope and a WebServiceSettingsScope.
Depending on the scope that the TestDal object is being created it will be wrapped with either a ServiceProxy or an InterceptorProxy. The serviceproxy will route the requests to the service defined in the WebServiceSettingsScope. If created within a DbConnectionSettingsScope the InterceptorProxy will simply forward the requests on to the underlying object within the same application domain.
How does this work behind the scenes? The decision of which Proxy is created is made within the HttpRemotableAttribute.
using System;
using System.Text;
using System.Runtime.Remoting.Proxies;
namespace DynamicService.Library
{
public class HttpRemotableAttribute : ProxyAttribute
{
public override MarshalByRefObject CreateInstance(Type serverType)
{
if (!ConnectionSettingsScope.Exists)
throw new Exception("Error creating serviced object: " + serverType.FullName + "\r\n\r\n You must define the ServiceScope to create a ServicedObject\r\ne.g. using(ServiceScope svcScope = new svcScope(...)) \r\n{ //object instantiation } \r\n\r\n");
RealProxy proxy = null;
//if we are using a remote invocation then create a service proxy
if (ConnectionSettingsScope.UseRemoteInvocation)
{
WebServiceSettings webServiceSettings = ConnectionSettingsScope.ActiveConnectionSettings as WebServiceSettings;
if (webServiceSettings == null)
throw new Exception("Unexpected error: the ConnectionScope.ActiveConnectionSettings is null.");
proxy = new ServiceProxy(serverType, webServiceSettings);
}
else
{
//just create a dumb proxy that will forward on the requests locally
proxy = new InterceptorProxy(serverType);
}
MarshalByRefObject transparentProxy = (MarshalByRefObject)proxy.GetTransparentProxy();
return transparentProxy;
}
}
}
HttpRemotable is a special attribute, inheriting from ProxyAttribute. What makes it so special is the CreateInstance method which is called when we try to create an instance of an object that is derived from ContextBoundObject and that is decorated with a ProxyAttribute derived class such as HttpRemotable. It determines the type of proxy to be created depending on the Active ConnectionSettings on our ConnectionSettings stack.
What is this ConnectionSettingsScope that I keep refereing to?
using System;
using System.Text;
using System.Threading;
using System.Collections.Generic;
using DynamicService.Library;
namespace DynamicService.Library
{
public class ConnectionSettingsScope : IDisposable
{
[ThreadStatic] //Each thread will have its own copy of a ServiceScope stack
private static Stack<ConnectionSettings> _ConnectionSettings;
protected static Stack<ConnectionSettings> ConnectionSettingsStack
{
get
{
//this is thread safe, ConnectionSettings is a ThreadStatic variable.
if (_ConnectionSettings == null)
_ConnectionSettings = new Stack<ConnectionSettings>();
return _ConnectionSettings;
}
}
public ConnectionSettingsScope(ConnectionSettings ConnectionSettings)
{
ConnectionSettingsStack.Push(ConnectionSettings);
}
static ConnectionSettingsScope()
{
}
public ConnectionSettingsScope()
{
}
public static bool UseRemoteInvocation
{
get { return ActiveConnectionSettings is WebServiceSettings; }
}
public static bool Exists
{
get { return (ConnectionSettingsStack!= null) && (ConnectionSettingsStack.Count > 0); }
}
public static ConnectionSettings ActiveConnectionSettings
{
get { return GetCurrentThreadConnectionSettings(); }
}
private static ConnectionSettings GetCurrentThreadConnectionSettings()
{
if (ConnectionSettingsStack.Count > 0)
return ConnectionSettingsStack.Peek();
else
return null;
}
// Dispose() calls Dispose(true)
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
// The bulk of the clean-up code is implemented in Dispose(bool)
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
// free managed resources
ConnectionSettingsStack.Pop(); // Remove the latest server url from the threadstatic stack
}
}
}
}
Maybe I need to explain myself a little on the above code snippet. ConnectionSettingsScope contains a ThreadStatic stack of ConnectionSettings. Each time we create a new ConnectionSettingsScope object it pushes the latest ConnectionSettings class on to its ThreadStatic stack (ThreadStatic means that the stack is static to the thread level, each thread has its own ConnectionSettings stack). Each time we create an HttpRemotable object, the Settings used on it, will be based on the latest ConnectionSettingsScope.
DataService.ashx explained
So what happens on the other side of the fence. What happens when I we are creating our HttpRemotable object within a WebServiceSettingsScope?
All method calls are intercepted by the ServiceProxy and a ServiceRequest is sent over to our ServiceHandler. The service request contains all sorts of information of what we are trying to do, it contains information such as the fully qualified name of the underlying real type that has been proxied, the method we want to execute, and all the parameters passed to the method.
This information is used by the handler to instantiate the object via reflection and call the appropriate method. When method execution completes, the handler constructs a ServiceResponse to send back to the client. This response contains the return value along with the values of any output parameters.
The actual connection string to the database is stored on the server side. To allow for a multidatabase scenario, the handler is called in the format of DataService.ashx?db=ConnectionStringKeyName
ConnectionStringKeyName is the key in the web.config of the connectionstring to the database we want to use.
All objects are created within a DbConnectionSettingsScope region on the service handler. They will be pointing to the database in the selected connectionstring entry of the web.config as described above.
The DataService.ashx must contain a reference to the dlls that contain the types to be remotely invoked. In this case a single reference to our Dal will suffice. Furthermore, when publishing this web project we must ensure that the referenced dll is also in place.
I can see some are already thinking that adding references is what i wanted to avoid. True to some extent, what i wanted to avoid was the volume of references added. In classic web services I would have typically created one seperate asmx file per data object that i wanted to expose.
The Service Handler supports:
- Methods with void result
- Methods with parameters whose values are serialisable
- Graceful handling of exceptions and returning them to the client
- Methods with output parameters
- Overloaded methods with different parameter signatures.
- ServiceResponses are gzipped by default to minimise data traffic
The Service Handler does not support:
- Methods whose parameter values are not serialisable
- Generic methods
Testing and Performance
As mentioned above, the generic service handler uses reflection to reconstruct the underlying object being invoked. In order to assess whether this would mean a serious performance hit or not I created a few tests that benchmark it against equivalent invocations against a microsoft .Net web service.
The performance is pretty much similar, and in some cases the custom ServiceHandler calls outperform the equivalent calls in web services.
The following image contains the various test cases that validate the features supported by the generic Service Handler.
Summing it all up
This is a solution that you may consider, so long that cross-platform interoperability is not a concern to your application. The remotable objects should only have stateless atomic operations invoked upon them just as the case would be in Web Services. If you require to have the object alive on the remote tier then Remoting is probably for you. This solution allows you to easily switch between a web service mode and a non webservice mode by literally changing 1 line of code in your application. This solution minimises redundant coding in a web service tier and allows you to expose your objects over an HTTP channel by simply decorating them with one attribute and deriving from ContextBoundObject. I hope this helps yeou shave off some development time from your projects!
Suggestions, complaints, errors? I will be very happy to hear your opinion and feedback on this. There is a visual studio 2008 solution file that can be downloaded from here to get you started. Happy Coding!
Sample solution for this post: DynamicService.zip
For those who have been playing with Linq and getting up to speed with the latest C# 3.0 features, you are most likely aware of the new language feature of Anonymous Types.
An anonymous type allows us to create and an instance of a an object whose type has not been defined in our code but whose properties are inferred from the object initializer.
The following example illustrates this by creating an instance of a variable of an unknown type that has 3 properties
var product = new
{
Name = "Acme Rocket",
Description = "The quick way to the moon",
Stock = 10
};
The variable type of product is not defined in our code. It is inferred through type inference and is of a compiler generated anonymous type that has 3 properties whose types again are inferred through type inference.
Anonymous types are a very powerful feature since they allow for creating LINQ projections without the need to define a class containing the projected fields of our query.
So in the following adapted sample taken from microsoft:
string[] words = { "aPPLE", "BlUeBeRrY", "cHeRry" };
var upperLowerWords =
(from w in words
select new { Upper = w.ToUpper(), Lower = w.ToLower() }).ToList();
foreach (var ul in upperLowerWords)
{
Console.WriteLine("Uppercase: {0}, Lowercase: {1}", ul.Upper, ul.Lower);
}
upperLowerWords is an instance of an IEnumerable<anonymousType> and is also intellisensed as such:
This is all very good. We can create and use anonymous types in our code.
So what is the problem and why am I saying that anonymous type support is incomplete?
The problem lays in the fact that type inference only works on local variables. Which means that the anonymous type in our example (and anonymous types in general) is only useful as a strongly bound object in the scope of the procedure in which it has been created. Anonymous types cannot be returned as strongly typed objects in function return values. They can only be returned as objects as the following code shows
object ReturnAnonymous()
{
return new { Name = "Acme Rocket", Description = "Quick way to the moon", Stock = 10};
}
There is an interesting yet dirty workaround for returning anonymous types and getting strongly typed access to a return value of type anonymousType from the calling code.
However, the suggested approach is to define a solid class in your code and use that as the return type instead of returning an anonymous type. In a LINQ Datalayer scenario this means that for each function that returns a projection of Data we would have to define and maintain empty classes that are merely there just to map to the projected object...
Further reading about this issue
Msdn forum post and proposed resolutions:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2434363&SiteID=1
Rick also talks about this issue in one of his posts (http://www.west-wind.com/WebLog/posts/33570.aspx)
I really enjoy the new features of C# 3.0 but not being able to return anonymous types across functions cripples the potential of this language feature. This appears to be due to limitations in type inference which only works locally, and also seems to be a bit flaky if you ask me, from what I've been reading anyway:
https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=295134
http://blogs.msdn.com/ericlippert/archive/2007/11/05/c-3-0-return-type-inference-does-not-work-on-member-groups.aspx
Conclusion
Anonymous types even only accessible through local type inference are a really powerful language construct that have added value to LINQ queries (projections) . But I sure do look forward to seeing the C# compiler team unleashing the full potential of type inference and anonymous types by allowing us to return anonymous types as return values in our functions.
I recently found myself in the situation where I had to loop through all the days from a StartDate to an EndDate.
Sure easy, all you need to do is something like this:
DateTime startDate = DateTime.Now;
DateTime endDate = DateTime.Now.AddDays(3);
DateTime curDate = startDate;
while (curDate <= endDate)
{
//Do Something with curDate ...
curDate = curDate.AddDays(1);
}
Does the job, but it is not very intuitive in my opinion. It is not that easy to read.
Wouldn't it be so much nicer if we could do something like this:
DayIterator dayIterator = new DayIterator(startDate, endDate);
foreach (DateTime dt in dayIterator)
{
//Do Something with dt...
}
I think the second version really shows the intent of what we are trying to do and is much more easy to follow and maintain.
So how do we go about implementing the DayIterator then? As you will see for yourself it really is simple code that makes use of the yield return statement for Iterators.
Here is the implementation of the DayIterator:
public class DayIterator : IEnumerable<DateTime>
{
private DateTime _StartDate;
private DateTime _EndDate;
public DayIterator(DateTime startDate, DateTime endDate)
{
_StartDate = startDate;
_EndDate = endDate;
}
public IEnumerator<