Create an Array with Keys and Values from database

Question

I need to do this in my codes;

Get data from 2 columns in a database (laborer and trx_date)
Place the extracted data to a DataTable
Explode the first column in the DataTable
Place exploded_laborer and trx_date to an array with
```
key->exploded_laborer
exploded=>trx_date
```

I am able to get to number 3 I just need to do number 4. My code is below:

private void GetLocalData()
{
    const string sql = @"SELECT laborer, trx_date from tbl_jobs WHERE trx_date BETWEEN @fromDate AND @toDate";
    var laborerDataTable = new DataTable();
    using (var conn = new SqliteAccess().ConnectToSqlite())
    {
        using (var cmd = new SQLiteCommand(sql, conn))
        {
            conn.Open();
            cmd.Parameters.AddWithValue("@fromDate", dtpFrom.Value.ToString("yyyy-MM-dd"));
            cmd.Parameters.AddWithValue("@toDate", dtpTo.Value.ToString("yyyy-MM-dd"));
            laborerDataTable.Load(cmd.ExecuteReader());
        }
    }
    var exploded = new List<string>();
    foreach (DataRow row in laborerDataTable.Rows)
    {

        exploded.Add(row["laborer"].ToString().Split('|')[0]);
    }
}

Your help is very much appreciated.

What you meant by this: I am able to get to number 3 I just need to do number 4. My code is below: could you please be more clear? — sujith karivelil
– sujith karivelil, Commented Jan 13, 2017 at 4:09
I am able to do steps 1 - 3. I just don't know how to code the step 4. — Ibanez1408
– Ibanez1408, Commented Jan 13, 2017 at 5:02

Mohit S · Accepted Answer · 2017-01-13 07:15:08Z

2

I think you are supposed to create Dictionary<TKey,TValue> which represents a collection of keys and values. This might do the trick for you

laborerDataTable.AsEnumerable()
                .Select(row => laborerDataTable.Columns.Cast<DataColumn>()
                        .ToDictionary(column => row[laborer] as string
                                      column => row[trx_date] as string))

Thus the complete code might look like

private void GetLocalData()
{
    const string sql = @"SELECT laborer, trx_date from tbl_jobs WHERE trx_date BETWEEN @fromDate AND @toDate";
    var laborerDataTable = new DataTable();
    using (var conn = new SqliteAccess().ConnectToSqlite())
    {
        using (var cmd = new SQLiteCommand(sql, conn))
        {
            conn.Open();
            cmd.Parameters.AddWithValue("@fromDate", dtpFrom.Value.ToString("yyyy-MM-dd"));
            cmd.Parameters.AddWithValue("@toDate", dtpTo.Value.ToString("yyyy-MM-dd"));
            laborerDataTable.Load(cmd.ExecuteReader());
        }
    }
    var LabDict = laborerDataTable.AsEnumerable()
                .Select(row => laborerDataTable.Columns.Cast<DataColumn>()
                        .ToDictionary(column => row[laborer] as string
                                      column => row[trx_date] as string))
}

Edit

This is just to create a dummy table.

static DataTable GetTable()
{
    // Here we create a DataTable with four columns.
    DataTable table = new DataTable();
    table.Columns.Add("laborer", typeof(string));
    table.Columns.Add("trx_date", typeof(string));

    // Here we add five DataRows.
    table.Rows.Add("Indocin", "12/12/2010");
    table.Rows.Add("Enebrel", "12/1/2011");
    table.Rows.Add("Hydralazine", "1/12/2012");
    table.Rows.Add("Combivent", "11/12/2013");
    table.Rows.Add("Dilantin", "12/11/2014");

    return table;
}

normal core C# way

DataTable laborerDataTable = GetTable(); 
Dictionary<string, string> exploded = new Dictionary<string, string>();
foreach(DataRow row in laborerDataTable.Rows)
{
    exploded.Add(row.Field<string>(0), row.Field<string>(1));
}

Also, how do I print each of the row to a console?

foreach(var dct in exploded)
{
    Console.WriteLine(dct.Key + " Date is " + dct.Value);
}

edited Jan 13, 2017 at 7:15

answered Jan 13, 2017 at 4:13

Mohit S

14.1k6 gold badges36 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ibanez1408 Over a year ago

Mohit, can you please show me in the normal core C# way. I do not know how to use linq yet. Also, how do I print each of the row to a console? Thank you.

Mohit S Over a year ago

what do you mean by normal core C# way should you want to use Dictionary or not? if yes than we can do that in the loop as well.

Ibanez1408 Over a year ago

If this is LINQ then I wouldn't understand it please make it a simple loop. Thanks. But if this is not a LINQ then I guess this the only way to do it or if you can make it more understandable. Thanks.

Mohit S Over a year ago

Hey @Ibanez1408 I have updated the solution. please have a look if this piece of code is understandable or you need further clarification.

sujith karivelil · Accepted Answer · 2017-01-13 04:16:18Z

1

If you are expecting the result to a Key-Value pair then why an array? why not a Dictionary<string, string>? You can try something like this :

 Dictionary<string, string> laborerDict = laborerDataTable.AsEnumerable()
                                                          .ToDictionary(x => x.Field<string>("laborer"), 
                                                                        x => x.Field<string>("trx_date"));

answered Jan 13, 2017 at 4:16

sujith karivelil

29.1k7 gold badges60 silver badges89 bronze badges

Comments

Deepak Sharma · Accepted Answer · 2017-01-13 04:14:07Z

0

Use in built HashMap in java refer the code below HashMap map = new HashMap<>(); Map.put(key,value); And to get data from map use Map.get(key) It has a lot of function, maybe you can refer them by searching Java API

answered Jan 13, 2017 at 4:14

Deepak Sharma

4689 silver badges22 bronze badges

Comments

user3360579 · Accepted Answer · 2017-01-13 04:18:02Z

0

when you want to have Key=>Value better you use Dictionary instead of List

var exploded = new Dictionary<string,string>();
foreach (DataRow row in laborerDataTable.Rows)
{
 exploded.Add(row["laborer"].ToString(),(row["trx_date"].ToString());
}

answered Jan 13, 2017 at 4:18

user3360579

Collectives™ on Stack Overflow

Create an Array with Keys and Values from database

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related