how to write query in ado.net consist of data dictionary

Question

i want to write query which is written using linq and data dictionary i want to convert that query in ado.net form

var insData = new Dictionary<string, string>();

                    insData = db.Query<TableName>("select Name, Value from Table where PId={id} and type<>{(int)Enum.PElement}").ToDictionary(key => key.Name??t.TName, Val => Val.Value);

This above is the query i want to convert it into ado.net form to provide dictionary with this type

ToDictionary(key => key.Name??t.TName, Val => Val.Value);

i have written half of the code untill to fill the dataset but further i am quite confused how to go further to convert it to dictionary with keypair value shown above.

Here is the code what i have tried

var dictonary = new SqlCommand($"select Name, Value from Table where PId={id} and type<>{(int)Enum.PElement}", con);
                    SqlDataAdapter sda2 = new SqlDataAdapter(dictonary);
                    DataSet ds2 = new DataSet();
                    sda2.Fill(ds2);
                    IEnumerable<DataRow> dataRows = ds2.Tables[0].Rows.Cast<DataRow>().ToList();
                    var insData = new Dictionary<string, string>();

 foreach (DataRow row2 in dataRows )
 {
insData.ToDictionary(key => key.Name ?? t.TName, Val => Val.Value);
}

but i am getting error like

Keypair value does not contain defination of Name and no accessible >extension method Name accepting first argument of type KeyValuepair

I want to work that query in ado.net like it is working in linq which i have shown above.

Answer 1

I will do something like this :

        IEnumerable<DataRow> dataRows;
        using (var dataSet = new DataSet())
        {
            using (var command = new SqlCommand($"select Name, Value from Table where PId={id} and type<>{(int) Enum.PElement}", con))
            {
                using (var sda2 = new SqlDataAdapter(command))
                {
                    sda2.Fill(dataSet);
                }
            }

            dataRows = dataSet.Tables[0].Rows.OfType<DataRow>();
        }

        return dataRows.ToDictionary(row => row["Name"].ToString(), row => row["Value"].ToString());