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