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);
}
}

Mittwoch, 18. März 2020

json - newtowsoft - JArray - SelectToken

how to fill JArray and select all and specific items? 

private Newtonsoft.Json.Linq.JArray UFOS = new jsonlib.Linq.JArray();

dynamic json = Newtonsoft.Json.JsonConvert.DeserializeObject(myUFOjsonSourceString);

var list = json.list; // List of UFO items - 'list' is the root element of the source JSON structure

foreach (var item in list)
{
..
UFOS.Add(item);
...
}

IEnumerable<jsonlib.Linq.JToken> allUFOS= UFOS.SelectToken("$"); // okay, all UFOs

IEnumerable<jsonlib.Linq.JToken> oneUFO = UFOS.SelectToken("$[?(@.Pilot=='E.T.')]"); // okay, if each item of UFO would contain the property 'Pilot', select the one where Pilot=E.T. (you know, this small odd alien who wants to get home again)

Sample II:

Parse XML using Newtonsoft.Json

<root>
  <light>
    <color>red</color>
    <intensity>bright</intensity>
  </light>
  <light>
    <color>green</color>
    <intensity>light</intensity>
  </light>
</root>

XDocument doc = XDocument.Parse(mySampleXmlString); //or XDocument.Load(path)
string jsonText = JsonConvert.SerializeXNode(doc);
dynamic pseudoXml = JsonConvert.DeserializeObject(jsonText);
Newtonsoft.Json.Linq.JArray lights = (Newtonsoft.Json.Linq.JArray)pseudoXml["root"]["light"];
foreach (var light in lights)
{
    string color = (string)light.color;
    string intensity = (string)light.intensity;
}

Dienstag, 17. März 2020

Pass string parameter plus optional placeholder fill up arguments to a method - method signature for String.Format(..)

how to setup a method getting args to be applied using String.Format(....)?

Example:

        private void log(object text, params object[] param)
        {
            string line;
            if (param != null && param.Length > 0)
                line = String.Format(text.ToString(), param);
            else
                line = text.ToString();
            if (Environment.UserInteractive==true)
                Console.WriteLine(line);
        }

text to enum value

classic request how to convert a text entry to an enum value? See..

Colors orange = (Colors) Enum.Parse(typeof(Colors), "Red, Yellow");