How to retrieve the Value of KeyValuePair from database in C#?

Question

I am working on a project involving KeyValuePair. I have int as key, and string as Value.

What I want is I want to get the Value from KeyValuePair.

I have this code:

var list = new List<KeyValuePair<int, string>>()
{
  new KeyValuePair<int, string>(obj,_Obj.Questions)
            };

Dictionary<int, string> d = new Dictionary<int, string>
{
      { 1, "Welcome, How are you?" },
      { 2, "Tell me something about yourself."},
      { 3, "How much experience do you have?"},
};

So if you see the string Values in KVP like "Welcome, How are you?", "Tell me something about yourself" etc is a static one. All these values are already present in my local database. I want these string values from database only.

I am using MS SQL Database

Is this possible??

Not fully sure if I am understanding since you mentioned a database. But if you want the value from a key value pair, you would use the .Value. https://learn.microsoft.com/en-us/dotnet/api/system.collections.generic.keyvaluepair-2.value?view=netframework-4.8#System_Collections_Generic_KeyValuePair_2_Value. If you are trying to get each value from the dictionary you can loop the dictionary and access the .Value of each item. — Brian, Jan 06 '20 at 05:27
How is the KeyValuePair stored in Db ? Could you share how your table looks like ? — Anu Viswan, Jan 06 '20 at 05:39

score 1 · Accepted Answer · answered Jan 06 '20 at 08:54

You can use any ORM like Entity Framework, however, I will provide an example to do it manually :

var list = new List<KeyValuePair<int, string>>()

using(var connection = new SqlConnection(connectionString)) 
using(var command = new SqlCommand("SELECT * FROM interview_Table", connection))
using(var reader = command.ExecuteReader())
{
    if (connection.State != ConnectionState.Open) { connection.Open(); }

    if (reader.HasRows)
    {
        foreach (var row in reader)
        {
            //ensurance
            var isValidId = int.TryParse(row["Id"]?.ToString(), int out id);

            var description = row["Description"]?.ToString();       

            // only fetch rows with a valid id number with description that is not empty or null
            if(isValidId && !string.IsNullOrEmpty(description))
            {
                list.Add(new KeyValuePair<int, string>(id, description));
            }                   
        }
    }
}

I used DataRow instead of var row in foreach loop. But it is giving me error saying "Unable to cast object of type 'System.Data.Common.DataRecordInternal' to type 'System.Data.DataRow' " — Rohan Rao, Jan 08 '20 at 04:28
@RohanRao `reader` returns `DbDataRecord`, which has no inheritance with `DataRow`. So, it's impossible to cast it to `DataRow` out of the box!. That's why you received that error when you changed `var` to `DataRow`. To solve it you'll need to find a work around, like loading the reader via `DataTable` then convert it to DataRow, (e.g. `DataTable.Load(reader)`). — iSR5, Jan 08 '20 at 07:09

score 0 · Answer 2 · answered Jan 06 '20 at 05:31

Yes, what you are asking is possible.

You'll have to establish a database connection and run a query to pull the data needed. Once the data is received take it and put it into a collection.

This collection could contain KeyValuePairs like you have written or you could create a class that replicates the table structure and have a collection of those objects.

You could also go a step further and look into something such as Entity Framework.

Best of luck!

Athanasios Kataras · Answer 3 · 2020-01-06T05:58:22.980

0

You can use EntityFramework ToDictionary function to do this mapping.

var test = await this.dbcontext.InterviewQuestions
    .Select(x => new { x.Id, x.Description})
    .AsEnumerable()
    .ToDictionaryAsync(xkvp  => xkvp.Id, xkvp.Description);

You can check getting started with EF core if you are using .net core
You can check getting started with EF 6 if you are using .net

One of my personal favorites code first with EF 6 from existing DB will generate most of the code you need.

edited Jan 06 '20 at 05:58

answered Jan 06 '20 at 05:47

Athanasios Kataras

25,191
4
32
61

Thanks :) I am getting an error - The second parameter should be cancellationToken instead of x.Description. – Rohan Rao Jan 06 '20 at 05:54
Try with the updated version. `ToDictionaryAsync` will only map the `InterviewQuestion` object to a key value, so you can only provide the key and a cancellation token for the async part. – Athanasios Kataras Jan 06 '20 at 05:59

How to retrieve the Value of KeyValuePair from database in C#?

3 Answers3