SQL to Excel - Cleanup tabs, new lines, ect...

DECLARE @CrLf CHAR(2);
SET @CrLf = CHAR(13) + CHAR(10); -- CarriageReturn + LineFeed

SELECT [PROB_TYPE]
  ,[CONTACTNAME]
      ,[TECHNITION]
      ,[NUMBER]
  --,[DESCRIPTION]
  ,REPLACE(REPLACE(SUBSTRING([DESCRIPTION],1,DATALENGTH([DESCRIPTION])),@CrLf,'-'), CHAR(9), '') AS [DESCRIPTION]
      ,[OPENED]
      ,[CLOSED]
      ,[CLOSINGREMARKS]
      ,[STATUS]
      ,[ENTERED_BY]
      ,[CONT_PHONE]
      ,[CONT_ADDRESS]
      ,[CONT_EMAIL]
      ,[TAG_NO]
      ,[SERIAL_NO]
      ,[MODEL]
      ,[PRIORITY]
      ,[SHOW]
      ,[CODE]
      ,[CLOSEID]
      ,[EMERGENCY_CHANGE]
      ,[ENTERED_ID]
      ,[HRS_EST]
      ,[HRS_ACTUAL]
  FROM [problems]
  where status not in ('closed')
 order by prob_type

MS SQL Tables to C# objects

I am putting this here so I don't have to search for it later.

	declare @TableName sysname = 'Registration'
	declare @Result varchar(max) = 'public class ' + @TableName + '
	{'
	select @Result = @Result + '
	public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
	'
	from
	(
	select
	replace(col.name, ' ', '_') ColumnName,
	column_id ColumnId,
	case typ.name
	when 'bigint' then 'long'
	when 'binary' then 'byte[]'
	when 'bit' then 'bool'
	when 'char' then 'string'
	when 'date' then 'DateTime'
	when 'datetime' then 'DateTime'
	when 'datetime2' then 'DateTime'
	when 'datetimeoffset' then 'DateTimeOffset'
	when 'decimal' then 'decimal'
	when 'float' then 'float'
	when 'image' then 'byte[]'
	when 'int' then 'int'
	when 'money' then 'decimal'
	when 'nchar' then 'char'
	when 'ntext' then 'string'
	when 'numeric' then 'decimal'
	when 'nvarchar' then 'string'
	when 'real' then 'double'
	when 'smalldatetime' then 'DateTime'
	when 'smallint' then 'short'
	when 'smallmoney' then 'decimal'
	when 'text' then 'string'
	when 'time' then 'TimeSpan'
	when 'timestamp' then 'DateTime'
	when 'tinyint' then 'byte'
	when 'uniqueidentifier' then 'Guid'
	when 'varbinary' then 'byte[]'
	when 'varchar' then 'string'
	else 'UNKNOWN_' + typ.name
	end ColumnType,
	case
	when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
	then '?'
	else ''
	end NullableSign
	from sys.columns col
	join sys.types typ on
	col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
	where object_id = object_id(@TableName)
	) t
	order by ColumnId
	set @Result = @Result + '
	}'
	print @Result

view raw SQL Tables to C# Objects hosted with ❤ by GitHub

http://stackoverflow.com/questions/5873170/generate-class-from-database-table

SQLITE NHibernate DateTime2

While working on a project I was using a MsSQL 2012 database for my persistence layer but wanted to use a SQLITE in memory database for my unit/integration testing.  As part of my mapping setup I used an NHibernate Version column which used DateTime2:

NHibernate Mapping Code:

	public abstract class EntityBaseMap<T> : ClassMap<T> where T : EntityBase<T>
	{
	protected EntityBaseMap()
	{
	DynamicUpdate(); // Hibernate will update the modified columns only.
	Id(x => x.Id).Column("Id"); // Numberic Id
	Map(x => x.IsDeleted);
	Map(x => x.CreatedAt);
	OptimisticLock.Version();
	// DateTime2 is not a valid SQLITE database type. To use DateTime2 with SQLITE
	// you need to extend the SQLITE Dialect and
	Version(x => x.ModifiedAt).Column("ModifiedAt").CustomType("DateTime2");
	}
	}

view raw EntityBaseMap.cs hosted with ❤ by GitHub

Unforntuently SQLITE doesn't support DateTime2. After searching around and not finding a good solution, I ended up extending SQLiteDialect class to map it to TEXT per the following link:   http://stackoverflow.com/a/16597386/2544235

SQLiteDialect Code:

	public class CustomDialect : SQLiteDialect
	{
	protected override void RegisterColumnTypes()
	{
	base.RegisterColumnTypes();
	RegisterColumnType(DbType.DateTime2, "DATETIME2");
	}
	protected override void RegisterFunctions()
	{
	base.RegisterFunctions();
	RegisterFunction("current_timestamp", new NoArgSQLFunction("TEXT", NHibernateUtil.DateTime2, true));
	}
	protected override void RegisterKeywords()
	{
	base.RegisterKeywords();
	RegisterKeyword("datetime2");
	}
	protected override void RegisterDefaultProperties()
	{
	base.RegisterDefaultProperties();
	}
	}

view raw CustomDialect.cs hosted with ❤ by GitHub