Round #583

Use UNION to combine current_employees and former_employees, marking source.

```csharp using System; using System.Data; class Program { static void Main() { // Create data tables DataTable currentEmployees = new DataTable(); currentEmployees.Columns.Add("Name", typeof(string)); currentEmployees.Columns.Add("Age", typeof(int)); currentEmployees.Columns.Add("Department", typeof(string)); currentEmployees.Rows.Add("John", 34, "Sales"); currentEmployees.Rows.Add("Jane", 28, "Marketing"); currentEmployees.Rows.Add("Alice", 32, "HR"); DataTable formerEmployees = new DataTable(); formerEmployees.Columns.Add("Name", typeof(string)); formerEmployees.Columns.Add("Age", typeof(int)); formerEmployees.Columns.Add("Department", typeof(string)); formerEmployees.Rows.Add("Bob", 35, "Engineering"); formerEmployees.Rows.Add("Eve", 29, "Finance"); formerEmployees.Rows.Add("Charlie", 36, "Operations"); // Add a new column 'Source' to each data table currentEmployees.Columns.Add("Source", typeof(string)); formerEmployees.Columns.Add("Source", typeof(string)); // Fill the 'Source' column foreach (DataRow row in currentEmployees.Rows) { row["Source"] = "Current"; } foreach (DataRow row in formerEmployees.Rows) { row["Source"] = "Former"; } // Combine the two data tables DataTable combinedEmployees = new DataTable(); combinedEmployees.Merge(currentEmployees); combinedEmployees.Merge(formerEmployees); // Print the combined data table foreach (DataRow row in combinedEmployees.Rows) { Console.WriteLine($"{row["Name"]}\t{row["Age"]}\t{row["Department"]}\t{row["Source"]}"); } } } ```

```csharp using System; using System.Data; using System.Data.SqlClient; public class EmployeeRepository { private readonly string _connectionString; public EmployeeRepository(string connectionString) { _connectionString = connectionString; } public DataTable GetCombinedEmployees() { using (var connection = new SqlConnection(_connectionString)) { connection.Open(); var query = @" SELECT 'Current' AS Source, * FROM current_employees UNION ALL SELECT 'Former' AS Source, * FROM former_employees"; var command = new SqlCommand(query, connection); var adapter = new SqlDataAdapter(command); var combinedEmployees = new DataTable(); adapter.Fill(combinedEmployees); return combinedEmployees; } } } class Program { static void Main(string[] args) { var connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"; var repository = new EmployeeRepository(connectionString); var combinedEmployees = repository.GetCombinedEmployees(); foreach (DataRow row in combinedEmployees.Rows) { Console.WriteLine($"Source: {row["Source"]}, Employee ID: {row["EmployeeID"]}, Name: {row["Name"]}"); } } } ``` This C# code snippet demonstrates how to use the UNION operator to combine rows from two tables, `current_employees` and `former_employees`, into a single result set. The `Source` column is added to each row to indicate whether the employee is current or former. Here's a step-by-step explanation: 1. We create a class `EmployeeRepository` to encapsulate the database operations. 2. The `GetCombinedEmployees` method establishes a connection to the database and executes a SQL query that uses the UNION ALL operator to combine rows from `current_employees` and `former_employees`. 3. The `Source` column is added to each row to mark its origin. 4. The combined result set is stored in a `DataTable` object, which is then returned by the method. 5. In the `Main` method, we create an instance of `EmployeeRepository`, call `GetCombinedEmployees`, and print the combined employee data to the console. Note that this example assumes you have the necessary database tables and connection details. You should replace the placeholders in the connection string with your actual database credentials and server address.