Create Query String From Query Results

Question

In C# I want to run a SQL Query and parse the results into a QueryString to pass to a web page. In my testing I am trying to do this, but I get an error of

Key already exists

What is incorrect or what is the correct way to do this?

private void PrepQuery()
{
    Dictionary<string, string> dictFormValues = new Dictionary<string, string>();
    string connectionString = null;
    SqlConnection cnn;
    SqlCommand cmd;
    StringBuilder sql = new StringBuilder();
    SqlDataReader reader;
    connectionString = "Data Source=server;Initial Catalog=database;User ID=;Password=";
    sql.Append("select employeename, employeeaddress, employeezip, employeecity, employeestate, employeephone ");
    sql.Append("from abcd ");
    sql.Append("where validated is not null ");
    cnn = new SqlConnection(connectionString);
    try
    {
        cnn.Open();
        cmd = new SqlCommand(sql.ToString(), cnn);
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            dictFormValues.Add("employeename", reader.GetValue(0).ToString());
            dictFormValues.Add("employeeaddress", reader.GetValue(1).ToString());
            dictFormValues.Add("employeezip", reader.GetValue(2).ToString());
            dictFormValues.Add("employeecity", reader.GetValue(3).ToString());
            dictFormValues.Add("employeestate", reader.GetValue(4).ToString());
            dictFormValues.Add("employeephone", reader.GetValue(5).ToString());
            name = reader.GetValue(0).ToString();
        }
        reader.Close();
        cmd.Dispose();
        cnn.Close();
    }
    bool success = false;
    success = FormatForWebAPI();
    if (success = true; { GenerateEmail(); 
    if (sucess = false;) { ShowErrorOnScreen(); }
}

private void FormatForWebAPI();
{
    string strEndpointURL = string.Format("http://requestb.in/pm9rstuv/"); 
    System.Web.Script.Serialization.JavaScriptSerializer json = new System.Web.Script.Serialization.JavaScriptSerializer();
    foreach (var d in dictFormValues) { strPostData += d.Key + "=" + Server.UrlEncode(d.Value) + "&"; }
    strPostData += "hs_context=";
    System.Net.HttpWebRequest r = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(strEndpointURL);
    r.Method = "POST";
    r.Accept = "application/json";
    r.ContentType = "application/x-www-form-urlencoded";
    r.ContentLength = strPostData.Length;
    r.KeepAlive = false;
    using (System.IO.StreamWriter sw = new System.IO.StreamWriter(r.GetRequestStream())) 
    { 
            try { sw.Write(strPostData); }
            catch { return false; }
    }
    return true;
}

Looks like there are duplicates in your data. Dictionarys don't allow duplicate keys. Any particular reason why you're using a Dictionary instead of perhaps a list of tuples (see here: http://www.dotnetperls.com/tuple)? — sr28, Jan 13 '16 at 16:39
You loop the result, so if there are n rows you try to add n keys with the name `employeename` which is not allowed. See [How to build a query string for a URL in C#?](http://stackoverflow.com/questions/829080/how-to-build-a-query-string-for-a-url-in-c) — Alex K., Jan 13 '16 at 16:46
@sr28 - I have never used a tuple, thank you for the link I will take a look — FunFly WhiteGuy, Jan 13 '16 at 16:46
@FunFlyWhiteGuy - that's just a suggestion. It may well be better to create your own 'employee' class with properties for all the info you want to store and create a list of those (see my answer). — sr28, Jan 13 '16 at 16:58

sr28 · Accepted Answer · 2016-01-13T16:53:19.873

Basically Dictionaries don't allow duplicate keys. From MSDN - Dictionary:

Every key in a Dictionary must be unique according to the dictionary's equality comparer.

In your case you are adding 'employeename', 'employeeaddress' etc as keys to your dictionary. So by the looks of it, if you expect to have more than 1 employee (or row of data) then you will get this error, as for each 1 it will try and add the same keys again (employeename etc.)

As per my comment try using Tuples such as a List<tuple<string, string>>. There are various other ways to store data, which contains duplicates such as in a DataTable or perhaps in an xml document. It really depends on what you plan to do with it once you've got it really.

Alternatively create your own class of type 'employee' that contains properties for all the info you want to store (name, address, zipcode etc) and then create a list of those, like List<employee>.

I want to return the values and format them as a query string so I can send to a web page API — FunFly WhiteGuy, Jan 13 '16 at 17:24
@FunFlyWhiteGuy - I would probably go with creating your own class of employee then and creating a list. — sr28, Jan 14 '16 at 08:50

Create Query String From Query Results

1 Answers1