Posts

Showing posts from October, 2011

Generate class from a LINQ projection

Image
using System;
using System.Linq;
using Microsoft.CSharp;
using System.CodeDom;

public static class QueryExtensions
{
public static string GetClassDefinition<T>(this IQueryable<T> query, string className)
{
var type = typeof(T);
var sb = new StringBuilder();
sb.AppendLine(string.Format("public class {0}", className));
sb.AppendLine("{");
using (var provider = new CSharpCodeProvider())
{
foreach ( var prop in type.GetProperties())
{
var typeRef = new CodeTypeReference(prop.PropertyType);
var propertyTypeName = provider.GetTypeOutput(typeRef);
if ( !propertyTypeName.StartsWith("<>"))
{
propertyTypeName = propertyTypeName.Replace("System.Nullable<", String.Empty)
.Replace(">","?")
.Replace("System.", String.Empty);
sb.AppendLine(string.Format("\tpublic {0} {1} {{get; set;}}", propertyTypeName, prop.Name));
}
}
}
sb.AppendL…

MS-SQL script to generate update statements for rtrim and ltrim

Recently I did a bulk insert into a table from a CSV file and this data had trailing spaces. I didn't notice until some email addresses look like weren't valid.

I read this article which explains when the data is trimed or not

Since I had many columns to update I needed to find another solution to write/generate the update statements for "update table set column = rtrim(ltrim(column))"


declare @table_name varchar(150) = 'dbo.Member'
declare @stmt nvarchar(max)



set @stmt =
(
select '[' + c.name + '] = rtrim(ltrim([' + c.name + ']))' + ',' + char(10) + char(9)
from
dbo.syscolumns c
inner join dbo.systypes st ON st.xusertype = c.xusertype
inner join dbo.systypes bt ON bt.xusertype = c.xtype
where
c.id = object_id(@table_name,'U') and (bt.name = 'varchar' or bt.name = 'nvarchar')

for xml path ('')
)
if @stmt is not null
begin

set @stmt = 'update ' + @table_name + char(10) + 'set ' + substri…

Update between two tables when there is no relation between them

I needed a SQL statement which fills the null values from the second column of #T1 table with values from #T2(C1).

There is no foreign key or match between the columns of those two tables.

I found two approaches:

1. Using CTE

create table #T1 (C1 varchar(10), T2C1 varchar(10))
create table #T2 (C1 varchar(10))

insert into #T1 values ('A1', '1')
insert into #T1 values ('A2', null)
insert into #T1 values ('A3', null)
insert into #T1 values ('A4', '4')
insert into #T1 values ('A5', null)

insert into #T2 values ('a')
insert into #T2 values ('b')

;with t2 as
(
select C1, row_number() over (order by C1) as Index2
from #T2
)
,t1 as
(
select T2C1, row_number() over (order by C1) as Index1
from #T1
where T2C1 is null
)
update t1
set t1.T2C1 = t2.C1
from t2
where t1.Index1 = t2.Index2

select * from #T1

drop table #T1
drop table #T2

2. With Derived Tables

create table #T1 (C1 varchar(10), T2C1 varchar(10))
create table #T2 (…