List all data in sql table, row by row

Question

I am trying to list all data in a table, but it only returns the first row, it doesn't loop the whole table. i need to return the data as strings, because I will use it in a ASMX web service.

And the xml schema only returns the first row

<String> data in row 1<String>

i want it to return somthing like this:

<String> data in row 1<String>
<String> data in row 2<String>
<String> data in row 3<String>

and row 1 to n rows....

I have tested the sql statment in VS2012 query builder and there it works fine. so i need to list out all the data in a way.

Here is my Code

public String finAllCompaniesForSpesficuserByUserId(String userid)
{
    List<String> l = new List<String>();
    try
    {
        String sql = "SELECT Companies.Name FROM UsersInCompanies INNER JOIN Companies ON UsersInCompanies.CompanyId = Companies.CompanyId WHERE UsersInCompanies.UserId ='" + userid + "'";
        con = new SqlConnection(cs);
        cmd = new SqlCommand(sql, con);

        DataTable table = new DataTable();
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        adapter.Fill(table);
        con.Open();

        dr = cmd.ExecuteReader();
        dr.Read();

        //while (dr.Read())
        //{
        //    l.Add(dr["Name"].ToString());
        //}

        foreach (DataRow row in table.Rows)
        {
            return row["Name"].ToString();
        }
    }
    finally
    {
        if (con != null)
            con.Close();
    }
    /*
    foreach (string p in l)
    {
        return p;

    }
    */
    return null;
}

Can someone point me in the right direction or give me an examples?

While not directly related to the problem you are presently having, you have created a SQL injection attack vector by constructing SQL yourself => http://unixwiz.net/techtips/sql-injection.html — cfeduke, Mar 13 '13 at 11:14
You already have an example. You have commented it out. Add each row data to the list. Then return the list at the end of the function. Drop the premature returns. — musefan, Mar 13 '13 at 11:15
Are you creating your own xml using stringBuilder? I hope not, but am asking. — granadaCoder, Mar 13 '13 at 13:28

score 3 · Answer 1 · edited Mar 13 '13 at 11:16

3

foreach (DataRow row in table.Rows)
{
    return row["Name"].ToString();
}

you are returning from very first iteration itself.

edited Mar 13 '13 at 11:16

Doctor Jones

21,196
13
77
99

answered Mar 13 '13 at 11:14

TalentTuner

17,262
5
38
63

my downvote.... im not one for answers that just point out the problems. If you want some points from me, you should give an example of how to fix the problem – musefan Mar 13 '13 at 11:16
3

@musefan: Please you do it.i don't need any point buddy , its just the help i am doing to my fellow programmer. – TalentTuner Mar 13 '13 at 11:19
Every think works fine now. Im resciving the message as SOAP to my android device and populating a spinner with the items spittet by ",". Whop whop. made my day! – user2053451 Mar 13 '13 at 11:41

score 1 · Answer 2 · answered Mar 13 '13 at 11:19

Instead of returning immediately in the for-loop either use a yield statement (and change the return type to IEnumerable<String> - which just moves the for loop out of the function and somewhere else) or use a StringBuilder to build the resulting string.

StringBuilder sb = new StringBuilder(table.Rows.Count * 30); /* 30 is arbitrary */ 

foreach (DataRow row in table.Rows)
{
    // yes 3 separate calls are correct
    sb.Append("<String>");
    sb.Append(row["Name"].ToString())
    sb.Append("</String>\n");
}

/* after closing, cleaning up */
return sb.ToString();

Thank you so much, it works fine now:) Have stuggle with that for a while! — user2053451, Mar 13 '13 at 11:28

score 0 · Answer 3 · answered Mar 13 '13 at 11:38

0

Try this

    var temp=  "<String>" +
            string.Join("</String>\n<String>", dt.Rows.Cast<DataRow>().Select(x => x["Name"].ToString())) +
            "</String>";

answered Mar 13 '13 at 11:38

Pranav1688

175
1
11

List all data in sql table, row by row

3 Answers3