How to populate dictionary using SQL table data

In my C# project, I have a dictionary, its values are currently static.

var dict = new Dictionary<string, string>
{
    ["0000"] = "UK",
    ["1111"] = "JAPAN",
    ["2222"] = "CHINA",
    ["3333"] = "SRI LANKA",
    ["4444"] = "AUSI",
    ["5555"] = "USA",
};

Now I need to set to its values in dynamically. how can I do that? I have an Oracle DB table called “country” there have two columns called, ID and CountryName as follows,

ID  CID     CountryName

1   0000    UK
2   1111    JAPAN
3   2222    CHINA
4   3333    SRI LANKA
5   4444    AUSI
6   5555    USA

How can I set this table values to my dictionary? Thanks in advance.

Answer

Here I used ToDictionary extension method, which I used to convert the IEnumerable collection into a new dictionary.

public static void Main()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("ID", typeof(int));
    dt.Columns.Add("CID", typeof(string));
    dt.Columns.Add("CountryName", typeof(string));

    dt.Rows.Add(new object[] { 1, "0000", "UK" });
    dt.Rows.Add(new object[] { 2, "1111", "JAPAN" });
    dt.Rows.Add(new object[] { 3, "2222", "CHINA" });
    dt.Rows.Add(new object[] { 4, "3333", "SRI LANKA" });
    dt.Rows.Add(new object[] { 5, "4444", "AUSI" });
    dt.Rows.Add(new object[] { 6, "5555", "USA" });

    Dictionary<string, string> dict = GetDictionary(dt);
}

public static Dictionary<string, string> GetDictionary(DataTable dt)
{
    return dt.AsEnumerable()
      .ToDictionary<DataRow, string, string>(row => row.Field<string>(1),
                                row => row.Field<string>(2));
}

Dotnet fiddle: https://dotnetfiddle.net/KNs9Xl