1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102

using System;
using System.Text;
using System.Collections.Generic;
using System.Windows.Forms;

public class Staff
	{
		public int ID { get; set; }
		public string FirstName { get; set; }
		public string LastName { get; set; }
		public int Salary { get; set; }
		public int Role { get; set; }
	}

public class OutputtingTempTables
{
	static List<Staff> GetStaffMembers()
	{
		List<Staff> rtn = new List<Staff>();
		rtn.Add(new Staff() { ID = 1, FirstName="Bob", LastName="Smith", Salary=14000, Role=1 });
		rtn.Add(new Staff() { ID = 2, FirstName="Rachel", LastName="Jackson", Salary=14500, Role=1 });
		rtn.Add(new Staff() { ID = 3, FirstName="Stephen", LastName="Byrne", Salary=14250, Role=1 });
		rtn.Add(new Staff() { ID = 4, FirstName="Elaine", LastName="Israel", Salary=13000, Role=1 });
		rtn.Add(new Staff() { ID = 5, FirstName="Janet", LastName="Henry", Salary=13500, Role=1 });
		rtn.Add(new Staff() { ID = 6, FirstName="Mildred", LastName="Johnson", Salary=14000, Role=1 });
		rtn.Add(new Staff() { ID = 7, FirstName="Jeffry", LastName="Rivera", Salary=16000, Role=2 });
		rtn.Add(new Staff() { ID = 8, FirstName="Sarah", LastName="Lattimer", Salary=16500, Role=2 });
		rtn.Add(new Staff() { ID = 9, FirstName="Jon", LastName="Caton", Salary=30000, Role=4 });
		rtn.Add(new Staff() { ID = 10, FirstName="Arden", LastName="Dearing", Salary=60000, Role=5 });		
		return rtn;
	}
	
	static string GenerateSQLOutput(List<Staff> staff)
	{
		StringBuilder sb = new StringBuilder();
		sb.AppendLine("DECLARE @staff TABLE (");
		sb.AppendLine(" ID int,");
		sb.AppendLine(" FirstName varchar(50),");
		sb.AppendLine(" LastName varchar(100),");
		sb.AppendLine(" Salary int,");
		sb.AppendLine(" Role int");
		sb.AppendLine(");");
		
		foreach (Staff s in staff)
		{
			sb.AppendLine("INSERT INTO @staff (ID, FirstName, LastName, Salary, Role)");
			sb.AppendLine(string.Format("VALUES ({0}, '{1}', '{2}', {3}, {4});",
				s.ID, s.FirstName, s.LastName, s.Salary, s.Role));
		}
		
		return sb.ToString();
	}
	
	public static void RunSnippet()
	{
		// Create a Collection of Staff Members
		var staff = GetStaffMembers();
		
		// Create the SQL-Formatted Output for a Temp Table
		string sql = GenerateSQLOutput(staff);
		WL(sql);
		Clipboard.SetText(sql);
	}
	
	#region Helper methods
	
	[STAThread]
	public static void Main()
	{
		try
		{
			RunSnippet();
		}
		catch (Exception e)
		{
			string error = string.Format("---\nThe following error occurred while executing the snippet:\n{0}\n---", e.ToString());
			Console.WriteLine(error);
		}
		finally
		{
			Console.Write("Press any key to continue...");
			Console.ReadKey();
		}
	}

	private static void WL(object text, params object[] args)
	{
		Console.WriteLine(text.ToString(), args);	
	}
	
	private static void RL()
	{
		Console.ReadLine();	
	}
	
	private static void Break() 
	{
		System.Diagnostics.Debugger.Break();
	}

	#endregion
}