SQL Query to create XML Elements

I recently had to create an XML file from a Microsoft SQL Server table. I was starting like normal to get my query results and then had to loop thru them in order to assign the data to the XML elements.

Since this table had 150+ fields, it was going to be quite literally a data entry nightmare I didn’t want to face so I figured that I would write a query to loop thru all of the database columns and then append the data needed to make my XML elements.

This query worked like a charm and took a couple minutes to create.

SELECT 'xWriter.WriteElementString("' + name + '", item.' + name + ');'
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.YOUR_TABLE_NAME')

Change your connection string to use your local database

I always find myself changing the connection string in my web.config file when spinning up a new project with ASP.NET MVC.

The reason I change it is because I like to download the free SQL Server Express and use the Microsoft SQL Server Management Studio to manage all of my databases locally.

I always find myself changing the connection string in my web.config file when spinning up a new project with ASP.NET MVC.

The reason I change it is because I like to download the free SQL Server Express and use the Microsoft SQL Server Management Studio to manage all of my databases locally.

<connectionStrings>
	<add name="DefaultConnection"
			connectionString="Data Source=(local);Initial Catalog=YOUR_DATABASE_NAME_GOES_HERE;Integrated Security=True"
			providerName="System.Data.SqlClient" />
</connectionStrings>

c# poco generator from database table using sql script

This is a simple SQL  script that will generate a c# poco class that you can use as a model in your Visual Studio projects.

I like to generate my tables manually when creating a new project. Having the tables setup with foreign keys along with a database diagram in SQL Server gives me the visual that I need when determining my database storage options.

If you’re wondering if I’m using “Migrations”, the answer is NO. I  find that it can get in my way but it’s very cool. I  haven’t figured out a way to incorporate “Migrations”  into my process yet but then again, I have a process that works for me to be productive and that’s the key, to be productive.

The only editing you need to do to this sql script is to change the table name you want to use at the top.

I did find this sql script somewhere on the interwebs and if anyone knows where, please share it with me so I can give credit to the person who wrote this. I couldn’t find it myself so at least I know it’s here for safe keeping.

Copy the code below into a new query window in your Microsoft SQL  Server Management Studio.

DECLARE @TableName sysname = 'YOUR_DATABASE_TABLE_NAME_GOES_HERE';
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 'string'
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;