· 7 years ago · Oct 21, 2018, 10:04 PM
1using System;
2
3using (var connection = new SqlConnection(cb.ConnectionString))
4 {
5 connection.Open();
6
7 Submit_Tsql_NonQuery(connection, "2 - Create-Tables",
8 Build_2_Tsql_CreateTables());
9
10 Submit_Tsql_NonQuery(connection, "3 - Inserts",
11 Build_3_Tsql_Inserts());
12
13 Submit_Tsql_NonQuery(connection, "4 - Update-Join",
14 Build_4_Tsql_UpdateJoin(),
15 "@csharpParmDepartmentName", "Accounting");
16
17 Submit_Tsql_NonQuery(connection, "5 - Delete-Join",
18 Build_5_Tsql_DeleteJoin(),
19 "@csharpParmDepartmentName", "Legal");
20
21 Submit_6_Tsql_SelectEmployees(connection);
22 }
23 }
24 catch (SqlException e)
25 {
26 Console.WriteLine(e.ToString());
27 }
28 Console.WriteLine("View the report output here, then press any key to end the program...");
29 Console.ReadKey();
30 }
31 static string Build_2_Tsql_CreateTables()
32 {
33 return @"
34 DROP TABLE IF EXISTS tabEmployee;
35 DROP TABLE IF EXISTS tabDepartment; -- Drop parent table last.
36
37
38 CREATE TABLE tabDepartment
39 (
40 DepartmentCode nchar(4) not null
41 PRIMARY KEY,
42 DepartmentName nvarchar(128) not null
43 );
44
45 CREATE TABLE tabEmployee
46 (
47 EmployeeGuid uniqueIdentifier not null default NewId()
48 PRIMARY KEY,
49 EmployeeName nvarchar(128) not null,
50 EmployeeLevel int not null,
51 DepartmentCode nchar(4) null
52 REFERENCES tabDepartment (DepartmentCode) -- (REFERENCES would be disallowed on temporary tables.)
53 );
54 ";
55 }
56 static string Build_3_Tsql_Inserts()
57 {
58 return @"
59 -- The company has these departments.
60 INSERT INTO tabDepartment
61 (DepartmentCode, DepartmentName)
62 VALUES
63 ('acct', 'Accounting'),
64 ('hres', 'Human Resources'),
65 ('legl', 'Legal');
66
67 -- The company has these employees, each in one department.
68 INSERT INTO tabEmployee
69 (EmployeeName, EmployeeLevel, DepartmentCode)
70 VALUES
71 ('Alison' , 19, 'acct'),
72 ('Barbara' , 17, 'hres'),
73 ('Carol' , 21, 'acct'),
74 ('Deborah' , 24, 'legl'),
75 ('Elle' , 15, null);
76 ";
77 }
78 static string Build_4_Tsql_UpdateJoin()
79 {
80 return @"
81 DECLARE @DName1 nvarchar(128) = @csharpParmDepartmentName; --'Accounting';
82
83
84 -- Promote everyone in one department (see @parm...).
85 UPDATE empl
86 SET
87 empl.EmployeeLevel += 1
88 FROM
89 tabEmployee as empl
90 INNER JOIN
91 tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
92 WHERE
93 dept.DepartmentName = @DName1;
94 ";
95 }
96 static string Build_5_Tsql_DeleteJoin()
97 {
98 return @"
99 DECLARE @DName2 nvarchar(128);
100 SET @DName2 = @csharpParmDepartmentName; --'Legal';
101
102
103 -- Right size the Legal department.
104 DELETE empl
105 FROM
106 tabEmployee as empl
107 INNER JOIN
108 tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
109 WHERE
110 dept.DepartmentName = @DName2
111
112 -- Disband the Legal department.
113 DELETE tabDepartment
114 WHERE DepartmentName = @DName2;
115 ";
116 }
117 static string Build_6_Tsql_SelectEmployees()
118 {
119 return @"
120 -- Look at all the final Employees.
121 SELECT
122 empl.EmployeeGuid,
123 empl.EmployeeName,
124 empl.EmployeeLevel,
125 empl.DepartmentCode,
126 dept.DepartmentName
127 FROM
128 tabEmployee as empl
129 LEFT OUTER JOIN
130 tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
131 ORDER BY
132 EmployeeName;
133 ";
134 }
135 static void Submit_6_Tsql_SelectEmployees(SqlConnection connection)
136 {
137 Console.WriteLine();
138 Console.WriteLine("=================================");
139 Console.WriteLine("Now, SelectEmployees (6)...");
140
141 string tsql = Build_6_Tsql_SelectEmployees();
142
143 using (var command = new SqlCommand(tsql, connection))
144 {
145 using (SqlDataReader reader = command.ExecuteReader())
146 {
147 while (reader.Read())
148 {
149 Console.WriteLine("{0} , {1} , {2} , {3} , {4}",
150 reader.GetGuid(0),
151 reader.GetString(1),
152 reader.GetInt32(2),
153 (reader.IsDBNull(3)) ? "NULL" : reader.GetString(3),
154 (reader.IsDBNull(4)) ? "NULL" : reader.GetString(4));
155 }
156 }
157 }
158 }
159 static void Submit_Tsql_NonQuery(
160 SqlConnection connection,
161 string tsqlPurpose,
162 string tsqlSourceCode,
163 string parameterName = null,
164 string parameterValue = null
165 )
166 {
167 Console.WriteLine();
168 Console.WriteLine("=================================");
169 Console.WriteLine("T-SQL to {0}...", tsqlPurpose);
170
171 using (var command = new SqlCommand(tsqlSourceCode, connection))
172 {
173 if (parameterName != null)
174 {
175 command.Parameters.AddWithValue( // Or, use SqlParameter class.
176 parameterName,
177 parameterValue);
178 }
179 int rowsAffected = command.ExecuteNonQuery();
180 Console.WriteLine(rowsAffected + " = rows affected.");
181 }
182 }
183} // EndOfClass