January 2010 Entries
Download the sample source code for this post here
In this post I will attempt to describe what I learned while working out how to separate the concerns of a WCF service layer from those of a presentation layer and vice-versa while using a redistributable client assembly and NOT using client proxy code generated by Visual Studio 'Add Service Reference' or svcutil.exe.
The code and scenarios do not necessarily represent best practices and will most likely offend some purists but I do believe that the topics covered represent real world challenges and may provide some insight into some not-so-obvious aspects of...
So I worked out how to, in a TSQL batch script, identity 1:? and M:N relationships.
If any SQL gurus out there can help streamline it or identify problems with my logic I would appreciate it.
DDL for test database
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[DataTypes]'
GO
CREATE TABLE [dbo].[DataTypes]
(
[imageField] [image] NULL,
[binaryField] [binary] (50) NULL,
[varbinaryField] [varbinary] (50) NULL,
[timestampField] [timestamp] NULL,
[textField] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_DataTypes_textField] DEFAULT ('a'),
[ntextField] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_DataTypes_ntextField] DEFAULT ('a'),
[charField] [char] (10)...
I found this one somewhere on SqlTeam.com forums…
DECLARE @sep char(1), @s nvarchar(1024)
SET @sep = ','
SET @s = 'a,b,c';
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 1024 END) AS s
FROM Pieces
Technorati tags: SQL
Recently I posted a question on stackoverflow asking how, in tsql, to differentiate a 1:? from an intermediate 1:n relationship.
Well, while all the inane bullshit polls and trivial questions were getting immediate response and comments and ups, a real technical question garnered no response. hmmmm...
Anyway, as usual, I had to answer my own question.
The solution was as I always thought it was but apparently I had been going about implementing it exactly the wrong way.
Keep in mind that this is in the context of generating source code for DTO objects.
Excluding edge cases like multiple 'inheritance' and abnormal schemas, the characteristics...
You can find the code @ DeadSimpleDTO.codeplex.com
New in 2.01
INotifyPropertyChanged option
One-to-many and many-to-one relationships optionally rendered
Examples:
Default options: basic property bag dto
/*
DeadSimpleDTO
default options
basic property bag dto
*/
using System;
using Northwind;
namespace Northwind
{
public partial class Orders
{
#region Properties
private Int32 _orderID;
public virtual Int32 OrderID
{
get { return _orderID; }
...
In a previous post, Accessing app.config/web.config from T4 template, I provided an include template to provide access to the configuration file. In this post I present a stand-alone template, umm, template that you can stuff your own code into.
ConfigEnabledTemplate.tt
<#@ template inherits="Microsoft.VisualStudio.TextTemplating.VSHost.ModelingTextTransformation" language="C#v3.5" debug="true" hostSpecific="true" #>
<#@ output extension=".cs" #>
<#@ assembly name="EnvDTE" #>
<#@ assembly name="System.Configuration.dll" #>
<#@ Assembly Name="System.Core.dll" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Configuration" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#
// <copyright file="c" company="Sky Sanders">
//
// This source is a Public Domain Dedication.
//
// http://salientt4.codeplex.com
//
// Attribution is appreciated.
//
// </copyright>
/// <summary>
/// This template can serve as a starting point for...
I am always a bit annoyed with myself and others when nunit is employed to perform simple code exercise.
I find this perfect for quickies and one-offs as well as for exercising code in examples. Just drop the file in and be done with it.
µUnit (uUnit.cs)
// <copyright file="uUnit.cs" company="Sky Sanders">
//
// This source is a Public Domain Dedication.
//
// http://salientqc.codeplex.com
//
// Attribution is appreciated.
//
// </copyright>
using System;
namespace Salient.QualityControl
{
/// <summary>
/// uUnit
/// A micro test runner.
///
/// For those times...
this_string IS all Over_the_MAP = ThisStringIsAllOverTheMap
/*
Pascal cases and concatenates a string. Underscores and spaces are
treated as boundaries and are removed after casing.
If a string is all upper or lower it will be proper cased.
Otherwise just capitalize first character.
*/
DECLARE @current_object_name NVARCHAR(128),
@generated_class_name NVARCHAR(128)
SET @current_object_name = 'this_string IS all Over_the_MAP'
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE TABLE #fn_pascal_case_parts ([name] NVARCHAR(256))
DECLARE @fn_pascal_case_input NVARCHAR(256),
@fn_pascal_case_output NVARCHAR(256),
@fn_pascal_case_part NVARCHAR(128),
@fn_pascal_case_sql VARCHAR(MAX)
-- --> PascalCase Function - set input
SET @fn_pascal_case_input = @current_object_name
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
BEGIN --> PascalCase Function common code
-- copy/paste this block as-is and just set/get input/output
-- if modifcations are made - other instances of this code block should be replaced.
-- is ugly but the closest...
PK Columns for [dbo].[Order Details] are "OrderID", "ProductID"
DECLARE @current_object_schema NVARCHAR(128),
@current_object_name NVARCHAR(128),
@generated_key_column_argument NVARCHAR(MAX)
USE Northwind
SET @current_object_schema = 'dbo'
SET @current_object_name = 'Order Details'
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
DECLARE @fn_keylist_ordinal INT,
@fn_keylist_key_name NVARCHAR(128),
@fn_keylist_next_ordinal INT,
@fn_keylist_input_schema NVARCHAR(128),
@fn_keylist_input_name NVARCHAR(128),
@fn_keylist_output NVARCHAR(MAX),
@fn_keylist_pk_name NVARCHAR(128),
@fn_keylist_pk_schema NVARCHAR(128)
-- --> Function KeyList- set input
SET @fn_keylist_input_schema = @current_object_schema
SET @fn_keylist_input_name = @current_object_name
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
BEGIN --> Function KeyList common code
SET @fn_keylist_output = ''
SET @fn_keylist_ordinal =0
SET @fn_keylist_next_ordinal =0
SELECT @fn_keylist_pk_schema = CONSTRAINT_SCHEMA,
@fn_keylist_pk_name = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE ( TABLE_SCHEMA = @fn_keylist_input_schema )
AND ( TABLE_NAME = @fn_keylist_input_name )
AND ( CONSTRAINT_TYPE = 'Primary Key' )
IF ISNULL(@fn_keylist_pk_name, '') != ''
BEGIN
-- build an argument for the TableAttribute
SET @fn_keylist_output = ''
SELECT TOP 1
@fn_keylist_ordinal = [ORDINAL_POSITION]
FROM ...
UPDATE: After using this code a bit more it has become apparent that the Sql2008 types are all string convertable and that Byte[] is a better choice for a generic reader implementation.
You may also handle single field UDT by getting the name of the system_type_id when is_user_defined = 1 in sys.columns.
date NULL = Nullable<DateTime>
DECLARE @current_column_type VARCHAR(30),
@current_column_nullable BIT,
@generated_property_type NVARCHAR(128)
SET @current_column_type = 'date'
SET @current_column_nullable = 1
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
DECLARE @fn_ctype_input_type NVARCHAR(128),
@fn_ctype_input_nullable BIT,
@fn_ctype_output_type NVARCHAR(128)
-- --> ConvertType Function - set input
SET @fn_ctype_input_type = @current_column_type
SET @fn_ctype_input_nullable = @current_column_nullable
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
BEGIN
SET @fn_ctype_output_type = CASE @fn_ctype_input_type
...
I need to know if a string was all upper or all lower and was stumped for a case sensitive string comparison. Ultimately came up with this.
Better ideas???
-- case sensitive - not equal
IF (SELECT 'AbcdEfg' COLLATE Latin1_General_CS_AS) = 'abcdefg'
PRINT 'equal'
ELSE
PRINT 'not equal'
-- case insensitive - e
IF (SELECT 'AbcdEfg' COLLATE Latin1_General_CI_AS) = 'abcdefg'
PRINT 'equal'
ELSE
PRINT 'not equal'
Technorati tags: SQL
Code blocks in a T4 template are run in the context of the templating service and have no notion of app/web config.
Oleg Sych provides much detail on T4 Architecture.
To access configuration files in a template we have to drill down a few levels of abstraction to get a reference to the project hosting the template. From that point we can utilize classes from the Configuration namespace to closely approximate standard Configuration file behavior.
Presented here is a re-usable template, ConfigurationAccessor.tt, that can be included in a template to provide strongly typed access to the configuration file and hosting project, and...
I am working on a spike that requires fetching metadata views from Sql Server's sys and INFORMATION_SCHEMA schemas. Initially, coding a few DTO by hand was no problem but as new views are required it obviously became tedious and typo prone.
I really did not want to break out ANY ORM, I just needed to read the data and a simple reflective filler would do just fine. In any case, I found myself in a query window writing a SQL based DTO generator. It ultimately ended up including a lot of clever functionality and I will present it here but first lets start with...
A few extension methods to help out.....
// Project: Salient.Reflection
// http://salient.codeplex.com
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
public static class AttributeHelpers
{
/// <summary>
/// Returns first non-inherited custom attribute of type T
/// </summary>
public static T GetCustomAttribute<T>(this ICustomAttributeProvider provider)
where T : Attribute
{
return GetCustomAttribute<T>(provider, false);
}
/// <summary>
/// Returns first custom attribute of type T in the inheritance chain
...
Doing interop with an ASP.NET ScriptService in JavaScript is simple, just deserialize the payload, e.g. result.d.
But if you are consuming with managed code it gets a bit tricky.
The 3.5 stack wraps the payload in a 'd' container making deserialization to a similarly shaped type or a type in a shared library (if you are using a shared library, why use JSON? There are reasons... ;-p) impossible using any of the ms serializers.
My solution is to use Newtonsoft's JSON.NET with a simple generic helper class to unwrap the payload..
public class AjaxWrapper<T>
{
public T d;
}
and then deserialize...
I have found, in a painfully tedious fashion, that the mono wcf stack does not care for any but the most basic configuration. I have had much more joy building hosts and channels in code.
Things to watch out for :
no base addresses
no mex
only support for BasicHttpBinding and WebHttpBinding and even that is spotty.
Don't get me wrong, the work they are doing is great and being able to deploy c# code written in VS on OSX and Linux is awesome. It is just painful to have to bump around...
#region UAC
private const int BcmFirst = 0x1600; //Normal button
private const int BcmSetshield = (BcmFirst + 0x000C); //Elevated button
[DllImport("user32")]
private static extern UInt32 SendMessage(IntPtr hWnd, UInt32 msg, UInt32 wParam, UInt32 lParam);
private static void AddShieldToButton(Button b)
{
b.FlatStyle = FlatStyle.System;
SendMessage(b.Handle, BcmSetshield, 0, 0xFFFFFFFF);
}
private static void RemoveShieldFromButton(Button b)
{
b.FlatStyle = FlatStyle.System;
SendMessage(b.Handle, BcmSetshield, 0, 0x0);
}
private static bool IsAdmin()
{
...