none
C# code help RRS feed

  • Question

  • I have a Sql table with colunms M1,M2,M3,id,date. M1,M2 og M3  logs data from machines. Every  minute  it logs if the machine is aktive (1) or idle (0).  The table is been updatet 24/7/365  every minute and therefore has a lot of record. Table name is DataInput. 

     M1,M2,M3,id,date

    0  0 0 1 2012-12-3 12:00:00

    0  1 1 2 2012-12-3 12:01:00

    1  0 1 3 2012-12-3 12:02:00

    1  1 1 4 2012-12-3 12:03:00

    0  1 0 5 2012-12-3 12:04:00

    I need C# code which can compare two rows. When records goes from 0 til 1, the machines starts. I need that timestamp.

    When records goes from 1 to 0, the machine stops. I need that timestamp too.

    I hope anyone will give me a tip to solve this. The record must be inserted to SQL table (result)

    Thanks






    • Edited by Mr. Peter Thursday, January 24, 2019 10:34 AM
    Wednesday, January 23, 2019 3:28 PM

All replies

  • First of all, when you say table, do you mean a relational (SQL) database? In this case the data model is incorrect. You should store in a normalized query, then it is a simple query SQL query.

    Otherwise you can do it in LINQ:

    public static IEnumerable<TResult> SelectWithPrevious<TSource, TResult>
        (this IEnumerable<TSource> source,
         Func<TSource, TSource, TResult> projection)
    {
        // Jon Skeet.
        using (var iterator = source.GetEnumerator())
        {
            if (!iterator.MoveNext())
            {
                 yield break;
            }
            TSource previous = iterator.Current;
            while (iterator.MoveNext())
            {
                yield return projection(previous, iterator.Current);
                previous = iterator.Current;
            }
        }
    }
    
    var query = list.SelectWithPrevious((prev, cur) =>
         new { ID = cur.ID, Date = cur.Date, DateDiff = (cur.Date - prev.Date).Days) });
    

    Or you loop over the result set manually.

    Wednesday, January 23, 2019 3:57 PM
  •  Correct, the data is located in a SQL server 2012
    Wednesday, January 23, 2019 4:20 PM
  • If you look at machine M1. The Machine starts at 2012-12-03 12:02:02 (id=3). The record before (id=2) must be zero. I am looking for START and STOP timestamps. I the case of M1  the query must show 2012-12-03  12:02:00 (START) and 2012-12-03 12:04:00 (STOP). 

    It is the change from 0 to 1 (START) and 1 to 0 (stop) that the query must find.  




    • Edited by Mr. Peter Wednesday, January 23, 2019 4:54 PM
    Wednesday, January 23, 2019 4:47 PM
  • I have a Sql table with colunms M1,M2,M3,id,date. M1,M2 og M3  logs data from machines. Every  minute  it logs if the machine is aktive (1) or idle (0).   

     M1,M2,M3,id,date

    0  0 0 1 2012-12-3 12:00:00

    0  1 1 2 2012-12-3 12:01:00

    1  0 1 3 2012-12-3 12:02:00

    1  1 1 4 2012-12-3 12:03:00

    0  1 0 5 2012-12-3 12:04:00

    I need C# code which can compare two rows. When records goes from 0 til 1, the machines starts. I need that timestamp.

    When records goes from 1 to 0, the machine stops. I need that timestamp too.

    I hope anyone will give me a tip to solve this. Thanks




    The solution to your problem (not really a problem, in my opinion) is

    RI

    DI

    CU

    LOUS

    LY

    simple,

    ( please, notice, I'm not good in separating words into syllables ) 

    for highly elevated and advanced C# coders (like me), and this is the output:

    M1
    	START at: 2012-12-3 12:02:00
    	 stop at: 2012-12-3 12:04:00
    M2
    	START at: 2012-12-3 12:01:00
    	 stop at: 2012-12-3 12:02:00
    	START at: 2012-12-3 12:03:00
    M3
    	START at: 2012-12-3 12:01:00
    	 stop at: 2012-12-3 12:04:00
    
    Hello, world! ( bonus )
    

    Please, notice that I've added a bonus! 

    Thursday, January 24, 2019 3:28 AM
  • As I'm a very nice guy, I built a CSV data for you, separated/delimited by semi-colon ( ; ):

    public static string[] GetCSV()
    {
        return new[]{
            "M1;M2;M3;id;date"
            ,"0;0;0;1;2012-12-3 12:00:00"
            ,"0;1;1;2;2012-12-3 12:01:00"
            ,"1;0;1;3;2012-12-3 12:02:00"
            ,"1;1;1;4;2012-12-3 12:03:00"
            ,"0;1;0;5;2012-12-3 12:04:00"
        };
    }
    

    The full code is in:

    https://rextester.com/CFQVAI90969

    Thursday, January 24, 2019 3:31 AM
  • For lazy people, here's the full code:

    public class Program
    {
        public static string[] GetCSV()
        {
            return new[]{
                "M1;M2;M3;id;date"
                ,"0;0;0;1;2012-12-3 12:00:00"
                ,"0;1;1;2;2012-12-3 12:01:00"
                ,"1;0;1;3;2012-12-3 12:02:00"
                ,"1;1;1;4;2012-12-3 12:03:00"
                ,"0;1;0;5;2012-12-3 12:04:00"
            };
        }
        public static void Main(string[] args)
        {
            string[] csv=GetCSV();
    
            for(int i=0;i<3;++i) // 3 machines, at least
            {
                Console.WriteLine(csv[0].Split(';').Skip(i).First());
    
                string s="0"; // the machines M? MUST all start a "0"!
                bool t=false; // very smart and highly advanced "transition" detector
                csv
                    .Skip(1)
                    .Select(r=>{
                        var a=r.Split(';').Skip(i).ToArray();
                        var x=a[0]==s?false:true;
                        s=a[0]==s?s:a[0];
                        return new{B=x,D=a[a.Length-1]};
                    })
                    .Where(y=>y.B)
                    .ToList()
                    .ForEach(e=>Console.WriteLine("\t{0} at: {1}",(t=!t)?"START":" stop",e.D))
                    ;
    
            }
    
            Console.WriteLine("\nHello, world!"); // bonus
        }
    }
    

    Please, notice, the full code includes the bonus!

    Thursday, January 24, 2019 3:35 AM
  • If you wanna be a little evil, ask Stefan Hoffman, aka Mr ultraLowQualityPoints, to implement similar code using... XML! ROFL, ROFL... and ROFL!

    Thursday, January 24, 2019 3:42 AM
  • If you wanna be a little evil, ask Stefan Hoffman, aka Mr ultraLowQualityPoints, to implement similar code using... XML! ROFL, ROFL... and ROFL!

    ROFL LOL XML...

    But how about loading the data from a repository connected to SQL server and putting it in an object...

            public class TimeStamp
            {
                public bool M1 { get; set; }
                public bool M2 { get; set; }
                public bool M3 { get; set; }
                public int id { get; set; }
                public DateTime date { get; set; }
            }

    Repo:

            public class Repository
            {
                string connectionString;
    
                // ctor other stuff etc ...
                public IEnumerable<TimeStamp> GetTimeStamps()
                {
                    // ...
                    return list;
                }
            }


    • Marked as answer by Mr. Peter Thursday, January 24, 2019 2:25 PM
    • Unmarked as answer by Mr. Peter Thursday, January 24, 2019 2:25 PM
    Thursday, January 24, 2019 1:03 PM
  • Sorry...I don't under stand how your class Repository works. Can you please specify it?
    Thursday, January 24, 2019 2:24 PM
  • Sorry...I don't under stand how your class Repository works. Can you please specify it?

    Hi Mr. Peter,

    for sure,

    the repository class has the task to receive and transmit data from/to your SQL server. So it will connect to your DB and it will execute queries. Micro orm frameworks like fluent data or dapper are very helpful for this.

    I will post a complete repositoy example later when I'm home... 

    Thursday, January 24, 2019 3:00 PM
  • My code looks like:

     public static void Main(string[] args)
            {
                String connectionString = "Data Source=pgc-123;Initial Catalog=testdatabse;Integrated Security=True";
                SqlConnection con = new SqlConnection(connectionString);
                con.Open();
                SqlDataAdapter sqlda = new SqlDataAdapter("SELECT * FROM S71200", con);
                DataTable dt = new DataTable();
                sqlda.Fill(dt);
                con.Close();

            }


            public class TimeStamp
            {

                public bool M1 { get; set; }
                public bool M2 { get; set; }
                public bool M3 { get; set; }
                public bool M4 { get; set; }
                public bool M5 { get; set; }
                public bool M6 { get; set; }
                public bool M7 { get; set; }
                public bool M8 { get; set; }
                public bool M9 { get; set; }
                public bool M10 { get; set; }
                public bool M11 { get; set; }
                public bool M12 { get; set; }
                public bool M13 { get; set; }
                public bool M14 { get; set; }
                public bool M15 { get; set; }
                public bool M16 { get; set; }
                public int id { get; set; }
                public DateTime datetime { get; set; }

    Thursday, January 24, 2019 3:29 PM
  •         public class MachineStates
            {
                public bool M1 { get; set; }
                public bool M2 { get; set; }
                public bool M3 { get; set; }
                public int id { get; set; }
                public DateTime date { get; set; }
            }
    
            public class RunTimespan
            {
                public DateTime From { get; set; }
                public DateTime To { get; set; }
                public TimeSpan TimeSpan => this.To.Subtract(From);
            }
    
            public class Repository
            {
                string connectionString;
    
                public Repository(string connectionString)
                {
                    this.connectionString = connectionString;
                }
    
                public IEnumerable<MachineStates> Read(string pUserName, string pPassword)
                {
                    using (var cn = new SqlConnection(connectionString))
                    {
                        using (var cmd = new SqlCommand("SELECT * FROM S71200;", cn))
                        {
                            using (var reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    yield return new MachineStates
                                    {
                                        M1 = reader.GetBoolean(0),
                                        M2 = reader.GetBoolean(1),
                                        M3 = reader.GetBoolean(2),
                                        id = reader.GetInt32(3),
                                        date = reader.GetDateTime(4),
                                    };
                                }
                            }
                        }
                    }
                }
            }
    
            public class SomeService
            {
                public static Dictionary<string, List<RunTimespan>> Calc(IEnumerable<MachineStates> states)
                {
                    var dic = new Dictionary<string, List<RunTimespan>>
                    {
                        {"M1", new List<RunTimespan>() },
                        {"M2", new List<RunTimespan>() },
                        {"M3", new List<RunTimespan>() }
                    };
                    RunTimespan[] machines = (new RunTimespan[3]).Select(x => (RunTimespan)null).ToArray();                
                    foreach (var state in states)
                    {
                        if(state.M1)
                            if(machines[0] == null)
                            {
                                machines[0] = new RunTimespan
                                {
                                    From = state.date
                                };
                            }
                            else
                            {
                                machines[0].To = state.date;
                                dic["M1"].Add(machines[0]);
                                machines[0] = null;
                            }
                        if (state.M2)
                            if (machines[1] == null)
                            {
                                machines[1] = new RunTimespan
                                {
                                    From = state.date
                                };
                            }
                            else
                            {
                                machines[1].To = state.date;
                                dic["M2"].Add(machines[1]);
                                machines[1] = null;
                            }
                        if (state.M3)
                            if (machines[2] == null)
                            {
                                machines[2] = new RunTimespan
                                {
                                    From = state.date
                                };
                            }
                            else
                            {
                                machines[2].To = state.date;
                                dic["M3"].Add(machines[2]);
                                machines[2] = null;
                            }
                    }
                    return dic;
                }
            }
    Hmm, ok for 16 machines it should be more generic...
    Thursday, January 24, 2019 3:52 PM
  • You have a table like M1 - M16 ... ? It sounds like you should normalize the database... Eg just one table with MachineId, State(on/off => 1/0), DateTime. That should be enough. Why you need to save that the state of some machines don't change?
    Thursday, January 24, 2019 4:05 PM
  • Yes I have 16 machines (M1+M16). The data comes from a PLC. 

    1)  How to normalize the table using C#?

    2) I want to calculate how long time the machine is idle time and runing too, but that can also be done with a normalized table i think. 

    3) Yes my S71200 table contain M1,M2....M16,id,date columns.

    Thursday, January 24, 2019 4:36 PM
  • How do you take data from a SQL table like S71200 and save it into a array. 

     return new[]{
                "M1;M2;M3;id;date"
                ,"0;0;0;1;2012-12-3 12:00:00"
                ,"0;1;1;2;2012-12-3 12:01:00"
                ,"1;0;1;3;2012-12-3 12:02:00"
                ,"1;1;1;4;2012-12-3 12:03:00"
                ,"0;1;0;5;2012-12-3 12:04:00"

    Instead of hard coded data, the new array must contain data from S71200

    Thursday, January 24, 2019 4:43 PM
  •         public class Repository
            {
                string connectionString;
    
                public Repository(string connectionString)
                {
                    this.connectionString = connectionString;
                }
    
                public IEnumerable<string> ReadAsCsv()
                {
                    using (var cn = new SqlConnection(connectionString))
                    {
                        using (var cmd = new SqlCommand("SELECT * FROM S71200;", cn))
                        {
                            using (var reader = cmd.ExecuteReader())
                            {
                                yield return $"M1;M2;M3;id;date\n"; // Headline
                                while (reader.Read())
                                {
                                    yield return $"{reader.GetBoolean(0)};{reader.GetBoolean(1)};{reader.GetBoolean(2)};{reader.GetInt32(3)};{reader.GetDateTime(4)}\n";                              
                                }
                            }
                        }
                    }
                }
            }


    • Edited by DerChris88 Thursday, January 24, 2019 6:30 PM
    Thursday, January 24, 2019 6:21 PM
  •         public class MachineStates
            {
                public List<bool> Ms { get; set; }
                public int id { get; set; }
                public DateTime date { get; set; }
            }
    
            public class RunTimespan
            {
                public DateTime From { get; set; }
                public DateTime To { get; set; }
                public TimeSpan TimeSpan => this.To.Subtract(From);
            }
    
            public class Repository
            {
                string connectionString;
    
                public Repository(string connectionString)
                {
                    this.connectionString = connectionString;
                }
    
                public IEnumerable<MachineStates> Read(int machines)
                {
                    using (var cn = new SqlConnection(connectionString))
                    {
                        using (var cmd = new SqlCommand("SELECT * FROM S71200;", cn))
                        {
                            using (var reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    var ms = new MachineStates
                                    {
                                        Ms = new List<bool>(),
                                        id = reader.GetInt32(machines),
                                        date = reader.GetDateTime(machines+1),
                                    };
                                    for (int i = 0; i< machines;i++)
                                    {
                                        ms.Ms.Add(reader.GetBoolean(i));
                                    }
                                    yield return ms;
                                }
                            }
                        }
                    }
                }
    
                public IEnumerable<string> ReadAsCsv()
                {
                    using (var cn = new SqlConnection(connectionString))
                    {
                        using (var cmd = new SqlCommand("SELECT * FROM S71200;", cn))
                        {
                            using (var reader = cmd.ExecuteReader())
                            {
                                yield return $"M1;M2;M3;id;date\n"; // Headline
                                while (reader.Read())
                                {
                                    yield return $"{reader.GetBoolean(0)};{reader.GetBoolean(1)};{reader.GetBoolean(2)};{reader.GetInt32(3)};{reader.GetDateTime(4)}\n";  
                                }
                            }
                        }
                    }
                }
            }
    
            public class SomeService
            {
                public static Dictionary<int, List<RunTimespan>> Calc(IEnumerable<MachineStates> states, int machineCount)
                {
    
                    var dic = new Dictionary<int, List<RunTimespan>>();
                    for (int i = 0; i < machineCount; i++)
                    {
                        dic.Add(i + 1, new List<RunTimespan>());
                    }
                    RunTimespan[] machines = (new RunTimespan[machineCount]).Select(x => (RunTimespan)null).ToArray();                
                    foreach (var state in states)
                    {
                        for(int i=0;i<machineCount;i++)
                        {
                            if (state.Ms[i] && machines[i] == null)
                            {
                                machines[i] = new RunTimespan
                                {
                                    From = state.date
                                };
                            }
                            else if (!state.Ms[i] && machines[i] != null)
                            {
                                machines[i].To = state.date;
                                dic[i + 1].Add(machines[i]);
                                machines[i] = null;
                            }
                        }
                    }
                    return dic;
                }
            }



    • Edited by DerChris88 Saturday, January 26, 2019 2:41 AM
    Thursday, January 24, 2019 7:07 PM
  • Can you code be integrated with mine:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Data.SqlClient;

    namespace ConsoleApplication3
    {
        class Program
        {

            public class Classname

            {
                public bool M1 { get; set; }
                public bool M2 { get; set; }
                public bool M3 { get; set; }
                public bool M4 { get; set; }
                public bool M5 { get; set; }
                public bool M6 { get; set; }
                public bool M7 { get; set; }
                public bool M8 { get; set; }
                public bool M9 { get; set; }
                public bool M10 { get; set; }
                public bool M11 { get; set; }
                public bool M12 { get; set; }



                public int id { get; set; }
                public DateTime date { get; set; }

            }

            public static void Main(string[] args)
            {
                String connectionString = "Data Source=pgc-123;Initial Catalog=testdatabse;Integrated Security=True";
                SqlConnection con = new SqlConnection(connectionString);

                Classname[] allRecords = null;
                string sql = @"SELECT M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12,id,date from S71200 order by id";
                using (var command = new SqlCommand(sql, con))
                {
                    con.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        var list = new List<Classname>();
                        while (reader.Read())
                            list.Add(new Classname {M1 = reader.GetBoolean(0),
                                                    M2 = reader.GetBoolean(1),
                                                    M3 = reader.GetBoolean(2),
                                                    M4 = reader.GetBoolean(3),
                                                    M5 = reader.GetBoolean(4),
                                                    M6 = reader.GetBoolean(5),
                                                    M7 = reader.GetBoolean(6),
                                                    M8 = reader.GetBoolean(7),
                                                    M9 = reader.GetBoolean(8),
                                                    M10= reader.GetBoolean(9),
                                                    M11 = reader.GetBoolean(10),
                                                    M12 = reader.GetBoolean(11),
                                                    id = reader.GetInt32(12),
                                                    date = reader.GetDateTime(13) });

                        allRecords = list.ToArray();
                        
                    }
                    foreach (var item in allRecords)
                    {
                     Console.WriteLine(string.Format("{0} {1} {2} {3} {4} {5} {6} {7} {8} {10} {11} {12} {13} ", item.M1, item.M2, item.M3,item.M4,item.M5,item.M6,item.M7,item.M8,item.M9,item.M10,item.M11,item.M12, item.id,item.date));
                    }
                    Console.ReadLine();
                }

            }
        }

    }

    Friday, January 25, 2019 9:28 PM
  • Hi Peter,

    my last code can handle different count of machines. The part M1 = ... M2 = ... is now in a loop and you can tell the method 16 machines, the method is implemented to read the first 16 values as boolean, 16+1 is id and 16+2 is date.

    You can use it like this:

            public static void Main(string[] args)
            {
                var repository = new Repository("connectionString;");
                var states = repository.Read(16); // 16 = machine count
                var result = SomeService.Calc(states, 16);
                foreach(var machine in result)
                {
                    Console.WriteLine($"M{machine.Key}");
                    foreach(var state in machine.Value)
                    {
                        Console.WriteLine($"\tTime machine was running: {state.TimeSpan}");
                    }
                }
            }

    Greetings, Chris

    Friday, January 25, 2019 10:04 PM
  •         public class MachineStates
            {
                public List<bool> Ms { get; set; }
                public int id { get; set; }
                public DateTime date { get; set; }
            }
    
            public class RunTimespan
            {
                public DateTime From { get; set; }
                public DateTime To { get; set; }
                public TimeSpan TimeSpan => this.To.Subtract(From);
            }
    
            public class Repository
            {
                string connectionString;
    
                public Repository(string connectionString)
                {
                    this.connectionString = connectionString;
                }
    
                public IEnumerable<MachineStates> Read(int machines)
                {
                    using (var cn = new SqlConnection(connectionString))
                    {
                        using (var cmd = new SqlCommand("SELECT * FROM S71200;", cn))
                        {
                            using (var reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    var ms = new MachineStates
                                    {
                                        Ms = new List<bool>(),
                                        id = reader.GetInt32(machines),
                                        date = reader.GetDateTime(machines+1),
                                    };
                                    for (int i = 0; i< machines;i++)
                                    {
                                        ms.Ms.Add(reader.GetBoolean(i));
                                    }
                                    yield return ms;
                                }
                            }
                        }
                    }
                }
    
                public IEnumerable<string> ReadAsCsv()
                {
                    using (var cn = new SqlConnection(connectionString))
                    {
                        using (var cmd = new SqlCommand("SELECT * FROM S71200;", cn))
                        {
                            using (var reader = cmd.ExecuteReader())
                            {
                                yield return $"M1;M2;M3;id;date\n"; // Headline
                                while (reader.Read())
                                {
                                    yield return $"{reader.GetBoolean(0)};{reader.GetBoolean(1)};{reader.GetBoolean(2)};{reader.GetInt32(3)};{reader.GetDateTime(4)}\n";
                                }
                            }
                        }
                    }
                }
            }
    
            public class SomeService
            {
                public static Dictionary<int, List<RunTimespan>> Calc(IEnumerable<MachineStates> states, int machineCount)
                {
    
                    var dic = new Dictionary<int, List<RunTimespan>>();
                    for (int i = 0; i < machineCount; i++)
                    {
                        dic.Add(i + 1, new List<RunTimespan>());
                    }
                    RunTimespan[] machines = (new RunTimespan[machineCount]).Select(x => (RunTimespan)null).ToArray();                
                    foreach (var state in states)
                    {
                        for(int i=0;i<machineCount;i++)
                        {
                            if (state.Ms[i] && machines[i] == null)
                            {
                                machines[i] = new RunTimespan
                                {
                                    From = state.date
                                };
                            }
                            else if (!state.Ms[i] && machines[i] != null)
                            {
                                machines[i].To = state.date;
                                dic[i + 1].Add(machines[i]);
                                machines[i] = null;
                            }
                        }
                    }
                    return dic;
                }
            }
    
            public static void Main(string[] args)
            {
                var repository = new Repository("connectionString;");
                var states = new List<MachineStates>
                {
                    new MachineStates
                    {
                        Ms = new List<bool> { true, false, false},
                        date = new DateTime(1000),
                        id = 1
                    },
                    new MachineStates
                    {
                        Ms = new List<bool> { true, true, true},
                        date = new DateTime(2000),
                        id = 2
                    },
                    new MachineStates
                    {
                        Ms = new List<bool> { false, false, false},
                        date = new DateTime(3000),
                        id = 3
                    },
                };
                var result = SomeService.Calc(states, 3);
                foreach(var machine in result)
                {
                    Console.WriteLine($"M{machine.Key}");
                    foreach(var state in machine.Value)
                    {
                        Console.WriteLine($"\tTime machine was running: {state.TimeSpan}");
                    }
                }
            }

    I used the dictionary with 3 machines in this example instead of the repository

    There was a bug in the other code so please use this code instead with the other main.

    Greetings, Chris

    • Edited by DerChris88 Friday, January 25, 2019 10:21 PM
    Friday, January 25, 2019 10:11 PM
  •         public class MachineStates
            {
                public List<bool> Ms { get; set; }
                public int id { get; set; }
                public DateTime date { get; set; }
            }
    
            public class RunTimespan
            {
                public DateTime From { get; set; }
                public DateTime To { get; set; }
                public TimeSpan TimeSpan => this.To.Subtract(From);
            }
    
            public class Repository
            {
                string connectionString;
    
                public Repository(string connectionString)
                {
                    this.connectionString = connectionString;
                }
    
                public IEnumerable<MachineStates> Read(int machines)
                {
                    using (var cn = new SqlConnection(connectionString))
                    {
                        using (var cmd = new SqlCommand("SELECT * FROM S71200;", cn))
                        {
                            using (var reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    var ms = new MachineStates
                                    {
                                        Ms = new List<bool>(),
                                        id = reader.GetInt32(machines),
                                        date = reader.GetDateTime(machines+1),
                                    };
                                    for (int i = 0; i< machines;i++)
                                    {
                                        ms.Ms.Add(reader.GetBoolean(i));
                                    }
                                    yield return ms;
                                }
                            }
                        }
                    }
                }
    
                public IEnumerable<string> ReadAsCsv()
                {
                    using (var cn = new SqlConnection(connectionString))
                    {
                        using (var cmd = new SqlCommand("SELECT * FROM S71200;", cn))
                        {
                            using (var reader = cmd.ExecuteReader())
                            {
                                yield return $"M1;M2;M3;id;date\n"; // Headline
                                while (reader.Read())
                                {
                                    yield return $"{reader.GetBoolean(0)};{reader.GetBoolean(1)};{reader.GetBoolean(2)};{reader.GetInt32(3)};{reader.GetDateTime(4)}\n";
                                }
                            }
                        }
                    }
                }
            }
    
            public class SomeService
            {
                public static Dictionary<int, List<RunTimespan>> Calc(IEnumerable<MachineStates> states, int machineCount)
                {
    
                    var dic = new Dictionary<int, List<RunTimespan>>();
                    for (int i = 0; i < machineCount; i++)
                    {
                        dic.Add(i + 1, new List<RunTimespan>());
                    }
                    RunTimespan[] machines = (new RunTimespan[machineCount]).Select(x => (RunTimespan)null).ToArray();                
                    foreach (var state in states)
                    {
                        for(int i=0;i<machineCount;i++)
                        {
                            if (state.Ms[i] && machines[i] == null)
                            {
                                machines[i] = new RunTimespan
                                {
                                    From = state.date
                                };
                            }
                            else if (!state.Ms[i] && machines[i] != null)
                            {
                                machines[i].To = state.date;
                                dic[i + 1].Add(machines[i]);
                                machines[i] = null;
                            }
                        }
                    }
                    return dic;
                }
            }
    
            public static void Main(string[] args)
            {
                var repository = new Repository("connectionString;");
                var states = new List<MachineStates>
                {
                    new MachineStates
                    {
                        Ms = new List<bool> { true, false, false},
                        date = new DateTime(1000),
                        id = 1
                    },
                    new MachineStates
                    {
                        Ms = new List<bool> { true, true, true},
                        date = new DateTime(2000),
                        id = 2
                    },
                    new MachineStates
                    {
                        Ms = new List<bool> { false, false, false},
                        date = new DateTime(3000),
                        id = 3
                    },
                };
                var result = SomeService.Calc(states, 3);
                foreach(var machine in result)
                {
                    Console.WriteLine($"M{machine.Key}");
                    foreach(var state in machine.Value)
                    {
                        Console.WriteLine($"\tTime machine was running: {state.TimeSpan}");
                    }
                }
            }

    I used the dictionary with 3 machines in this example instead of the repository

    There was a bug in the other code so please use this code instead with the other main.

    Greetings, Chris


    Don't you agree that this code (and others above) are sheer rubbish# code?

    Look at my code (some posts above): it is simple, scale-ready (see the for-loop), highly elevated C# code. 

    I used the dictionary with 3 machines in this example instead of the repository


    Only 3?

    Why?

    What if they are 4?

    Or maybe 20?

    Or even 200?

    There was a bug in the other code so please use this code instead with the other main.


    You are right! Such verbose codes, are pretty much susceptible to bugs...

    • Proposed as answer by DerChris88 Thursday, January 31, 2019 3:13 PM
    • Unproposed as answer by DerChris88 Thursday, January 31, 2019 3:13 PM
    Thursday, January 31, 2019 5:25 AM

  •  

    But how about loading the data from a repository connected to SQL server and putting it in an object...

            public class TimeStamp
            {
                public bool M1 { get; set; }
                public bool M2 { get; set; }
                public bool M3 { get; set; }
                public int id { get; set; }
                public DateTime date { get; set; }
            }


    Very sincerely, it's a huge rubbish# proposal!

    Don't you believe me?

    So, Look at the many posts you did using this proposal...

    Clearly, you are inexperienced in programming, and I'll tell you why: what happens if the numbers of computers increase to 4? Or, maybe to 20? Or even to 200!

    200 computers (and even more) are within the numbers of manageable computers by an IT guy.

    Would you increase the numbers of properties to 200?

    Would you?

    Also, would you waste time correcting the remaining code to adapt it to 200 computers (or even 4 computers)?

    ROFL, ROFL... and ROFL!

    Thursday, January 31, 2019 5:26 AM
  • For lazy people, here's the full code:

    public class Program
    {
        public static string[] GetCSV()
        {
            return new[]{
                "M1;M2;M3;id;date"
                ,"0;0;0;1;2012-12-3 12:00:00"
                ,"0;1;1;2;2012-12-3 12:01:00"
                ,"1;0;1;3;2012-12-3 12:02:00"
                ,"1;1;1;4;2012-12-3 12:03:00"
                ,"0;1;0;5;2012-12-3 12:04:00"
            };
        }
        public static void Main(string[] args)
        {
            string[] csv=GetCSV();
    
            for(int i=0;i<3;++i) // 3 machines, at least
            {
                Console.WriteLine(csv[0].Split(';').Skip(i).First());
    
                string s="0"; // the machines M? MUST all start a "0"!
                bool t=false; // very smart and highly advanced "transition" detector
                csv
                    .Skip(1)
                    .Select(r=>{
                        var a=r.Split(';').Skip(i).ToArray();
                        var x=a[0]==s?false:true;
                        s=a[0]==s?s:a[0];
                        return new{B=x,D=a[a.Length-1]};
                    })
                    .Where(y=>y.B)
                    .ToList()
                    .ForEach(e=>Console.WriteLine("\t{0} at: {1}",(t=!t)?"START":" stop",e.D))
                    ;
    
            }
    
            Console.WriteLine("\nHello, world!"); // bonus
        }
    }

    Please, notice, the full code includes the bonus!

    Are you serious? It's asked for loading data from sql and showing the timespan the machines where running and you show the op a csv and some code, doing nothig special? That's so stupid like to tell the customer "no you don't want an apple, take a banana for you apple pie". Do you understand? 

    And you said i used a dictionary with 3 to test, but what if the are 4 or 200? It's still working... -.- It's not limited... I know that this is not clean code like 99% here and 100% of your code. But this should just show how he can do it, you code is doing nothing! that's why it is that short and there are not much placed to rubish it, but you did it, well done -.- ...

    Thursday, January 31, 2019 10:00 AM
  • @ritehere please post right here if you want to discuss it, but don't ruin this thread, too!
    • Edited by DerChris88 Thursday, January 31, 2019 12:12 PM
    Thursday, January 31, 2019 12:11 PM
  • M1
    	START at: 2012-12-3 12:02:00
    	 stop at: 2012-12-3 12:04:00
    M2
    	START at: 2012-12-3 12:01:00
    	 stop at: 2012-12-3 12:02:00
    	START at: 2012-12-3 12:03:00
    M3
    	START at: 2012-12-3 12:01:00
    	 stop at: 2012-12-3 12:04:00
    
    Hello, world! ( bonus )

    @ritehere: I derubbished your code a bit.

    Rubbish#:

                for (int i = 0; i < 3; ++i) // 3 machines, at least
                {
                    Console.WriteLine(csv[0].Split(';').Skip(i).First());
    
                    string s = "0"; // the machines M? MUST all start a "0"!
                    bool t = false; // very smart and highly advanced "transition" detector
                    csv
                        .Skip(1)
                        .Select(r => {
                            var a = r.Split(';').Skip(i).ToArray();
                            var x = a[0] == s ? false : true;
                            s = a[0] == s ? s : a[0];
                            return new { B = x, D = a[a.Length - 1] };
                        })
                        .Where(y => y.B)
                        .ToList()
                        .ForEach(e => Console.WriteLine("\t{0} at: {1}", (t = !t) ? "START" : " stop", e.D))
                        ;
    
                }
    
                Console.WriteLine("\nHello, world!"); // bonus

    A little bit better:

                var headline = csv[0].Split(';'); // This is the right position! 
                for (int i = 0; i < 3; ++i) // 3 machines, at least
                {
                    Console.WriteLine(headline[i]); // It was very "rubbish" to do this here...
    
                    string s = "0"; // the machines M? MUST all start a "0"!
                    bool t = false; // very smart and highly advanced "transition" detector
                    csv
                        .Skip(1)
                        .Select(r => {
                            var a = r.Split(';').Skip(i).ToArray();
                            var x = a[0] == s ? false : true;
                            s = a[0] == s ? s : a[0];
                            return new { B = x, D = a[a.Length - 1] };
                        })
                        .Where(y => y.B)
                        .ToList()
                        .ForEach(e => Console.WriteLine("\t{0} at: {1}", (t = !t) ? "START" : " stop", e.D))
                        ;
    
                }
    
                Console.WriteLine("\nHello, world!"); // bonus

    • Edited by DerChris88 Thursday, January 31, 2019 3:12 PM
    Thursday, January 31, 2019 2:32 PM

  •  

    But how about loading the data from a repository connected to SQL server and putting it in an object...

            public class TimeStamp
            {
                public bool M1 { get; set; }
                public bool M2 { get; set; }
                public bool M3 { get; set; }
                public int id { get; set; }
                public DateTime date { get; set; }
            }


    Very sincerely, it's a huge rubbish# proposal!

    Don't you believe me?

    So, Look at the many posts you did using this proposal...

    Clearly, you are inexperienced in programming, and I'll tell you why: what happens if the numbers of computers increase to 4? Or, maybe to 20? Or even to 200!

    200 computers (and even more) are within the numbers of manageable computers by an IT guy.

    Would you increase the numbers of properties to 200?

    Would you?

    Also, would you waste time correcting the remaining code to adapt it to 200 computers (or even 4 computers)?

    ROFL, ROFL... and ROFL!

            public class MachineStates
            {
                public List<bool> Ms { get; set; }
                public int id { get; set; }
                public DateTime date { get; set; }
            }

    Did you see this before you were writing?

    Is is limited to a number of machines?

    I will stop now discussing it with you because posts like this showing me that you are not really smart. 

    This time you postet an answer on the code with the list:

    9 hours 47 minutes ago

    This time you posted an answer to an older code which was obsolet:

    9 hours 45 minutes ago

    Are you stupid? Or are you just inexperinced in reading code of other people?

    FYI: The list is not limited to a number of machines

    PS: Now I see that you are not only inexperienced in reading code of other people you are also inexperienced of sql, thats why you are always showing your csv things ROFL... you should learn it. 
    • Edited by DerChris88 Thursday, January 31, 2019 3:36 PM
    Thursday, January 31, 2019 3:16 PM
  • @ritehere please post right here if you want to discuss it, but don't ruin this thread, too!

    Have you learned to read, @derchris, aka rubbish# coder?

    That forum says, quite clearly, do not post here!

    Moreover, it is in this forum that you have posted so many rubbish# code, that it takes my precious time to comment (maybe I'll do it some other time)!

    Here is just a "pearl" you posted:

    RunTimespan[] machines = (new RunTimespan[machineCount]).Select(x => (RunTimespan)null).ToArray();
    // by @derchris

    All the rubbish# code above, was written (by you) to to initialize the array with null values!

    You should know that in C# we do it, simply, this way:

    RunTimespan[] machines = new RunTimespan[machineCount];

    Please, take this lesson for you; it is free!

    P.S.: try to explain the validity of the output of your (final) code, against those true/false values...

    Thursday, February 7, 2019 5:08 AM
  • My code looks like:

     public static void Main(string[] args)
            {
                String connectionString = "Data Source=pgc-123;Initial Catalog=testdatabse;Integrated Security=True";
                SqlConnection con = new SqlConnection(connectionString);
                con.Open();
                SqlDataAdapter sqlda = new SqlDataAdapter("SELECT * FROM S71200", con);
                DataTable dt = new DataTable();
                sqlda.Fill(dt);
                con.Close();

            }


            public class TimeStamp
            {

                public bool M1 { get; set; }
                public bool M2 { get; set; }
                public bool M3 { get; set; }
                public bool M4 { get; set; }
                public bool M5 { get; set; }
                public bool M6 { get; set; }
                public bool M7 { get; set; }
                public bool M8 { get; set; }
                public bool M9 { get; set; }
                public bool M10 { get; set; }
                public bool M11 { get; set; }
                public bool M12 { get; set; }
                public bool M13 { get; set; }
                public bool M14 { get; set; }
                public bool M15 { get; set; }
                public bool M16 { get; set; }
                public int id { get; set; }
                public DateTime datetime { get; set; }

    @ OP

    It seems that you already know how to fill a DataTable from the SQL TABLE data.

    In this case, why don't you convert the DataTable in a CSV array?

    And then, use the code I already posted to print the START and STOP times, in an independent way, i.e., it doesn't matter if the number of machines is 3, 30 or 300, or even zero; the code will work without need to re-compile it, because the CSV header can tell us how many machines there are.

    So, for sake of clarity this is the code, structurally:

    namespace StartStopTimes
    {
        public static class Xts // extension methods
        {
            public static string[] AsCsv(this DataTable table,string delim=";")
            {
                // I'LL PROVIDE THIS CODE: IT'S "JUST-5-OR-6-LINES" WITH SMALL STATEMENTS
            }
            public static void PrintStartStopTimes(this string[] csv,string machInitials,string delim=";")
            {
                // I HAVE "ALREADY" PROVIDED THIS CODE; NOW IT'S GOT A NAME
            }
        }
        
        public class Program
        {
            public static DataTable CreateDataTableFromSQLTable()
            {
                // YOU PROVIDE THIS CODE: SEE SqlDataAdapter
            }
            
            public static void Main(string[] args)
            {
                using (DataTable table = CreateDataTableFromSQLTable())
                {
                    string[] csv=table.AsCsv();
                    csv.PrintStartStopTimes("M");
                }
    
                Console.WriteLine("\nHello World!"); // bonus
            }
        }
    }
    

    ( Please, notice that I included a bonus )

    Notice that the extension method AsCsv(...) is really 5-or-6 lines of small statements!

    If you are interested, post a reply.

    Thursday, February 7, 2019 5:20 AM
  • @OP

    It's also possible to apply LINQ to the DataTable, but, converting it to a CSV is so easy (and dealing with it is easier) that it's not worth to deal with the DataTable object.

    Thursday, February 7, 2019 5:33 AM