CODEWITHSUNDEEP
c#sqlsql-serversqlcommand
user2053451
user2053451

Reputation: 7

List all data in sql table, row by row

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?

Upvotes: 0

Views: 9497

Answers (3)

Pranav1688
Pranav1688

Reputation: 175

Try this

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

Upvotes: 0

cfeduke
cfeduke

Reputation: 23236

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();

Upvotes: 0

TalentTuner
TalentTuner

Reputation: 17566

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

you are returning from very first iteration itself.

Upvotes: 3

Related Questions