Advent Challenge #8 Beginners
Santa muses about many things, especially using Excel files without an Excel installed. Santa reads a tip about EPPlus, a library to use Excel files without Excel, and tried it in this challenge. Santa remembers Challenge #3, he is still a poor man and still wishes an Office Suite. But he doesn’t have one yet, so he uses EPPlus to read the Excel files.
Furthermore, Santa has decided to use SQL, a query language widely used in business. He has tried LINQ and has his problems with it. That’s why Santa uses SQLite in this challenge.
Why SQLite an elf asked 
. Santa answers, this library implements a small, fast, self-contained, high-reliability, full-featured SQL database engine. It is the most used database engine in the world and its built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day. It can only be a benefit to use these in the context of UiPath. What you have learned here you can use everywhere again.
Santa downloads from SQLite.org the precompiled binaries for .NET, here the statically linked Binaries for 32-bit Windows. Santa builds a NuGet package, to import it via UiPath Package Manager.
Here the nuspec file to create the NuGet package:
<?xml version="1.0"?>
<package >
<metadata>
<id>SQLite</id>
<version>1.0.113.0</version>
<authors>Public Domain</authors>
<description>SQLite</description>
<tags>sqlite sql database</tags>
</metadata>
<files>
<file src="System.Data.SQLite.dll" target="lib\net46"></file>
</files>
</package>
After the installation of the additional packages Santas environment looks like this:
EPPlus and SQLite are imported successfully. Now I am ready to go, Santa thinks. 
Now Santa code in C# the following sequence:
//-Begin----------------------------------------------------------------
try {
string sql = string.Empty;
SQLiteCommand cmd;
//-Create database----------------------------------------------------
SQLiteConnection.CreateFile("DataAdvent.db");
SQLiteConnection con;
con = new SQLiteConnection("Data Source=DataAdvent.db;Version=3;");
con.Open();
//-Create Tasks table-------------------------------------------------
sql = "CREATE TABLE Tasks (Task TEXT, RegionCode TEXT)";
cmd = new SQLiteCommand(sql, con);
cmd.ExecuteNonQuery();
//-Create Executions table--------------------------------------------
cmd.CommandText = "CREATE TABLE Executions (Task TEXT, RegionCode TEXT, Attempt INTEGER, Result TEXT, Comment TEXT)";
cmd.ExecuteNonQuery();
//-Create Results table-----------------------------------------------
cmd.CommandText = "CREATE TABLE Results (Task TEXT, eTask TEXT, tTask TEXT, isDefinedTask BOOLEAN, TaskIsStarted BOOLEAN, hasValidRCExecuted BOOLEAN)";
cmd.ExecuteNonQuery();
//-Open Excel file----------------------------------------------------
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
FileInfo fi = new FileInfo("DataAdvent.xlsx");
ExcelPackage Excel = new ExcelPackage(fi);
//-Copy data from Tasks worksheet to Tasks table----------------------
ExcelWorksheet ExcelTasks = Excel.Workbook.Worksheets["Tasks"];
for(int row = 2; row <= ExcelTasks.Dimension.End.Row; row++) {
cmd.CommandText = "INSERT INTO Tasks (Task, RegionCode) VALUES ('" + ExcelTasks.Cells[row, 1].Value + "', '" + ExcelTasks.Cells[row, 2].Value + "');";
cmd.ExecuteNonQuery();
}
ExcelTasks.Dispose();
//-Copy data from Executions worksheet to Executions table------------
ExcelWorksheet ExcelExecutions = Excel.Workbook.Worksheets["Executions"];
for(int row = 2; row <= ExcelExecutions.Dimension.End.Row; row++) {
cmd.CommandText = "INSERT INTO Executions (Task, RegionCode, Attempt, Result, Comment) VALUES ('" +
ExcelExecutions.Cells[row, 1].Value + "', '" +
ExcelExecutions.Cells[row, 2].Value + "', " +
ExcelExecutions.Cells[row, 3].Value + ", '" +
ExcelExecutions.Cells[row, 4].Value + "', '" +
ExcelExecutions.Cells[row, 5].Value + "');";
cmd.ExecuteNonQuery();
}
ExcelExecutions.Dispose();
Excel.Dispose();
//-Insert all available Tasks into Results table----------------------
cmd.CommandText = "INSERT INTO Results (Task) SELECT Executions.Task FROM Executions UNION SELECT Tasks.Task FROM Tasks";
cmd.ExecuteNonQuery();
//-Update Executions and Tasks Task column in Results table-----------
cmd.CommandText = "UPDATE Results SET eTask = (SELECT Task FROM Executions WHERE Executions.Task = Results.Task), tTask = (SELECT Task FROM Tasks WHERE Tasks.Task = Results.Task)";
cmd.ExecuteNonQuery();
//-Update isDefinedTask in Results table------------------------------
//-
//- If a task is present in eTask but not in tTask
//- then isDefinedTask is false, otherwise true
//-
//--------------------------------------------------------------------
cmd.CommandText = "UPDATE Results SET isDefinedTask = CASE WHEN (eTask IS NOT NULL AND tTask IS NULL) THEN false ELSE true END";
cmd.ExecuteNonQuery();
//-Update TaskIsStarted in Results table------------------------------
//-
//- If a task is defined in tTask and also present in eTask
//- then TaskIsStarted is true, otherwise false
//-
//--------------------------------------------------------------------
cmd.CommandText = "UPDATE Results SET TaskIsStarted = CASE WHEN (tTask IS NOT NULL AND eTask IS NOT NULL) THEN true ELSE false END";
cmd.ExecuteNonQuery();
//-Update hasValidRCExecuted in Results table-------------------------
//-
//- If a Task in Execution is executed,
//- in a region which is not defined in Tasks for this Task ID
//- then hasValidRCExecuted is false, otherwise true.
//-
//--------------------------------------------------------------------
//-Create Tasks mirror table------------------------------------------
cmd.CommandText = "CREATE TABLE mirrorTasks (Task TEXT, RegionCode TEXT);";
cmd.ExecuteNonQuery();
//-Split RegionCode with multiple entries-----------------------------
cmd.CommandText = "INSERT INTO mirrorTasks (Task, RegionCode) WITH split(Task, RegionCode, str) AS (SELECT Task, '', RegionCode||',' FROM Tasks UNION ALL SELECT Task, substr(str, 0, instr(str, ',')), substr(str, instr(str, ',') + 1) FROM split WHERE trim(str) != '') SELECT Task, RegionCode FROM split ORDER BY Task, RegionCode;";
cmd.ExecuteNonQuery();
//-Delete rows with empty RegionCode----------------------------------
cmd.CommandText = "DELETE FROM mirrorTasks WHERE RegionCode IS NULL OR trim(RegionCode) = '';";
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE Results SET hasValidRCExecuted = CASE WHEN (Task IN (SELECT e.Task FROM Executions AS e LEFT JOIN mirrorTasks AS t ON e.Task = t.Task AND e.RegionCode = t.RegionCode WHERE t.RegionCode IS NULL GROUP BY e.Task HAVING SUM(e.Attempt) > 0)) THEN false ELSE true END;";
cmd.ExecuteNonQuery();
//-Update hasValidRCExecuted in Results table-------------------------
//-
//- If the task was never executed
//- then hasValidRCExecuted is false.
//-
//--------------------------------------------------------------------
cmd.CommandText = "UPDATE Results SET hasValidRCExecuted = false WHERE Task NOT IN (SELECT Task FROM Executions GROUP BY Task HAVING SUM(Attempt) > 0);";
cmd.ExecuteNonQuery();
//-Delete Tasks mirror table------------------------------------------
cmd.CommandText = "DROP TABLE mirrorTasks;";
cmd.ExecuteNonQuery();
//-Output of the Results table----------------------------------------
cmd.CommandText = "SELECT Task, isDefinedTask, TaskIsStarted, hasValidRCExecuted FROM Results ORDER BY Task;";
SQLiteDataReader Result = cmd.ExecuteReader();
Console.WriteLine("TaskID\tisDefinedTask\tTaskIsStarted\thasValidRCExcuted");
while(Result.Read()) {
Console.WriteLine(
Result.GetString(0) + "\t" +
Result.GetBoolean(1).ToString() + "\t\t" +
Result.GetBoolean(2).ToString() + "\t\t" +
Result.GetBoolean(3).ToString()
);
}
con.Close();
} catch(Exception ex) {
Console.WriteLine(ex.Message);
}
//-End------------------------------------------------------------------
The code is well documented, so there is no need for further explanation, Santa hopes.
With the code Santa gets this result:
Main.xaml (13.5 KB)
As far as Santa can see, it looks good.
Santa finds the direct use of a local SQL engine very exciting. Santa has worked in Walldorf for some time and sees the use of SQL as an interesting way in the context of UiPath.
Did you know that SQL is not called Structured Query Language? No, it is called Santa’s Query Language. Santa gave this idea away to IBM in the 70s. Unfortunately he did not patent the name.
: Sure Santa, I can’t believe you reached out to me. I’ll be happy to help you with your tasks.
and a Happy New Year 
.
and Happy New year to all my fellow 
You have to make a community post on writing LINQ for beginners.
… He nailed it again with his LINQ amazing skills. 
Advent Challenge #1
the 
about 
against 
OR 
→ 
Anil_G
Yoichi
raja.arslankhan
Alexsandro_RPA
Ben_siu
guilherme_melato
Shivam_Rana
copy_writes
nikhil.girish
gustavo.souza