Montag, 20. April 2020

MS SQL Merge upsert

multiple record writing - steps to achieve the fastest update of an table:

- create temp database table. This is a copy of the destination table to fill for finally.
- fille the temp database table with the bulk copy function of System.data.sqlClient.
- execute the MERGE script.

MERGE example:

MERGE [dbo].[THE_TABLE] as t
USING [dbo].[TEMP_THE_TABLE] as s
ON t.MY_ID=s.MY_ID AND t.MY_SEQUENCE=s.MY_SEQUENCE
WHEN MATCHED THEN 
UPDATE SET
        t.MY_VALUE=s.MY_VALUE,
        t.MY_TIMESTAMP=s.MY_TIMESTAMP
WHEN NOT MATCHED THEN 
INSERT (MY_ID, MY_SEQUENCE, MY_VALUE, MY_TIMESTAMP)
VALUES (s.MY_ID, s.MY_SEQUENCE, s.MY_VALUE, s.MY_TIMESTAMP);

(t=target, s=source)

Dienstag, 7. April 2020

Linq Introduction with short basically samples

Linq - basic how to samples:


using System.Linq;

...

[System.Serializable]
public class Item
{
public string Name;
public int ItemID;
public int Buff;
}


public class Main
{

public List<Item> Items;
public void Start()
{

string[] names = { "ralf", "Peter", "Thomas", "Christian", "Patrice" };
/* ANY demo */
var nameFound = names.Any(name => name == "Peter");
Debug.Log("Found name : " + nameFound);
/* Contains */
var nameContained = names.Contains("Christian");
Debug.Log("Found conatined : " + nameContained);
/* Distinct - remove duplicates*/
var uniqueNames = names.Distinct();
/* Where */
var result = names.Where(n => n.Length > 5);
/* select greater than results */
int[] grades = { 10, 20, 30, 40, 50, 60, 65, 70, 75, 33, 80, 13, 90, 100 };
var highGrades = grades.Where(i => i > 65);
foreach (int grade in highGrades)
{
Debug.Log("selected grade : " + grade.ToString());
}
/* select ORDER BY .. DESCENDING  */
var descGrades = grades.OrderByDescending(i => i);
foreach (var item in descGrades)
{
Debug.Log("Int item = " + item);
}
/* precondition before moving on - image the Items collection including 5x Item with various values */
/* check if ItemID 3 exists in the List of Item */
var containsId3 = Items.Where(i => i.ItemID == 3);
var itemId3found = Items.Any(i=>i.ItemID == 3);
Debug.Log("is Items including ItemId 3 : " + itemId3found);
/* select all items with buff greater then 60 and print them out */
var itemsGT60 = Items.Where(i => i.Buff > 60);
foreach (var buff in itemsGT60)
{
Debug.Log("buff Items > 60 : " + buff);
}
/* calculate the average of all the buff stats */
var resultAverage = Items.Average(i => i.Buff);
Debug.Log("Average : " + resultAverage);
}
}