Programming with C#

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)); } }

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) + 

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 va