3

I'm reading Datatable column values into an array but my current code skips null values.

Question -

How can I achieve the following within the code listed below -

If null value then read the previous available value in the time series.

**For example - note these are column values.... **

Input - 8,9,10,'','',5,11,3,'',2

Code output - 8,9,10,5,11,3,2

Expect_Result - 8,9,10,10,10,5,11,3,3,2

Code - 

public static double[] DataTableColumn(DataTable table, string "dbColumn")
    {
        var column = new List<string>();
        foreach (
           string columnsize in
           from DataRow resultRow in table.Rows
           select resultRow[columnName].ToString().Split('|') into sizes
           from size in sizes
           where !column.Contains(size)
           select size)
        {
            column.Add(columnsize);
        }

        string[] columnarray = column.ToArray();
        double[] doublearray = Array.ConvertAll(columnarray, Double.Parse);

        return doublearray;
    }

FYI - DataTableColumn input DataTable table is from the below code

 public static DataTable DataSet()
    {
        string sql = string.Format(@"select * from dbtable);

        System.Data.DataTable table = new System.Data.DataTable();
        using (OracleConnection connection = new OracleConnection())
        {
            connection.ConnectionString = connectionString;
            connection.Open();

            OracleCommand command = connection.CreateCommand();

            command.CommandText = sql;
            OracleDataReader reader = command.ExecuteReader();


            table.TableName = "Table";
            OracleDataAdapter adapter = new OracleDataAdapter(command);
            adapter.Fill(table);
            Console.WriteLine("State: {0}", connection.State);
            Console.WriteLine("ConnectionString: {0}",
                              connection.ConnectionString);
        }         

        return table;
    }
war10ck
  • 103
  • 1
  • 1
  • 8
  • Can you provide some example input for DataTableColumn? I'm currently just trying to understand what the LINQ is doing (alternative to supplying input you can just explain it). – Blake Thingstad Jan 03 '18 at 21:05
  • Can you simply check if the current columnsize is null and add the previous columnsize (or am I missing something)? – SPQR Jan 03 '18 at 21:13
  • @BlakeThingstad - i have added the example input for DataTableColumn. Think of input values as what I have listed in the example Data_table_column - 8,9,10,'','',5,11,3,'',2 – war10ck Jan 03 '18 at 21:27
  • @user365213 - That is exactly my question.. how can i check within the select statement if columnsize is null then use previous value? – war10ck Jan 03 '18 at 21:35
  • What does "How to roll previous row value" mean exactly? rephrase the title perhaps. – Mark Schultheiss Jan 03 '18 at 21:36
  • @MarkSchultheiss i have rephrased the title.. please see the example values of input and expected output in bold. – war10ck Jan 03 '18 at 21:46
  • `string sql = string.Format(@"select * from dbtable);` is not valid syntax...perhaps you just mean to use `string sql = @"select * from dbtable";` here? – Mark Schultheiss Jan 03 '18 at 21:53
  • This feels like what you might want was a null coalescing operator in your linq as here https://stackoverflow.com/q/34630920/125981 – Mark Schultheiss Jan 03 '18 at 22:06

3 Answers3

1

The below code produces your desired output with your supplied input.

What I have changed about your code is that when the value is empty, it recursively finds the previous value that is not empty.

static void Main(string[] args)
{
    var dt = new DataTable();
    dt.Columns.Add(new DataColumn("column1"));
    var values = new string[] { "8", "9|10", "", "", "5", "11", "3", "", "2" };
    foreach (var value in values)
        dt.Rows.Add(value);
    var result = DataTableColumn(dt, "column1");
    // result contains 8, 9, 10, 10, 10, 5, 11, 3, 3, 2
    Console.ReadKey(true);
}

public static double[] DataTableColumn(DataTable table, string columnName)
{
    var split = table.Rows.Cast<DataRow>()
        .SelectMany(r => r[columnName].ToString().Split('|'))
        .ToList();
    var replaceEmpty = split.Select((v, i) => 
           string.IsNullOrEmpty(v) ? Previous(split, i) : v)
        .ToArray();
    return Array.ConvertAll(replaceEmpty, Double.Parse);
}

public static string Previous(List<string> list, int index)
{
    if (index == 0)
        throw new IndexOutOfRangeException();

    var prev = list[index - 1];
    if (string.IsNullOrEmpty(prev))
        return Previous(list, index - 1);
    else
        return prev;
}
Blake Thingstad
  • 1,639
  • 2
  • 12
  • 19
0

if you know which columns could be null then its simple you can write something similar to this in sql

Data_table_column - 8,9,10,'','',5,11,3,'',2
select col1, col2, col3, 
 ISNULL(col4,col3)col4,ISNULL(col5,col3)col5, col6, col7, col8,
 ISNULL(col9,col8) col9, col10 from #tttt

or you can update your array in your code to check if an element is null/empty then set the value from array[arrayIndex] = array[arrayIndex-1]

Hope this will give you some ideas

Mark Schultheiss
  • 32,614
  • 12
  • 69
  • 100
user1063108
  • 662
  • 1
  • 10
  • 24
  • 1. to clarify its not the column names - ... the code is reading column values from the sql and storing in an array. while reading the column values it skips null values. 2. the code doesn't reach the array part.. by the time code stores the column values in the array - it contains filtered null values..so something needs to be done when its doing a select statement. I have added more comments for this question. Thanks for your help. – war10ck Jan 03 '18 at 21:33
  • are you saying you do not have access to SQL query which you can amend as mentioned above? – user1063108 Jan 03 '18 at 22:26
0
 public static double[] GetColumnTimeSeriesFromDataTable(DataTable table, string columnName)
    {
        var split = table.Rows.Cast<DataRow>()
            .SelectMany(r => r[columnName].ToString().Split('|'))
            .ToList();
        var replaceEmpty = split.Select((v, i) =>
               string.IsNullOrEmpty(v) ? Previous(split, i) : v)
            .ToArray();
        return Array.ConvertAll(replaceEmpty, Double.Parse);
    }

    public static string Previous(List<string> list, int index)
    {
        var prev = list[index];
        if (index == 0 & string.IsNullOrEmpty(list[index])) 
            return Previous(list, index+1);
        else if (string.IsNullOrEmpty(prev))
            return Previous(list, index - 1);
        else
            return prev;
    }
war10ck
  • 103
  • 1
  • 1
  • 8