CODEWITHSUNDEEP
c#sqlwebforms
Damienn
Damienn

Reputation: 65

Looping WHERE variable in SELECT statement for c#

So, I'm trying to create an area called "shoutout" where the current user may view "shoutout", basically messages from the shoutout table from people that are friends with him/her.

So, what I did was first put all the current user's friends into an array called "currentuserfriends", then I wish to select message from the shoutout table where username = all the users in "currentuserfriends".

However I have no idea how to loop in a select statement,

I don't want to have to do something like,

string getuserfriendlist = "SELECT friend FROM friend WHERE username = '" + currentuserfriends[0] + "'";

and increase currentuserfriends[0] manually. Is there a way to loop currentuserfriends until the end in a single select statement? Thanks.

Friend table

username | friend

shoutout table

username | message | datetime

Code:

        string[] currentuserfriends = new string[9999];
string[] posternames = new string[9999];
    string getuserfriendlist = "SELECT friend FROM friend WHERE username = '" + currentuser + "'";

    SqlCommand cmdb = new SqlCommand(getuserfriendlist, con);

    SqlDataReader drb;

    drb = cmdb.ExecuteReader();

    while (drb.Read())
    {

        string currentuserfriend = drb["friend"].ToString();
        currentuserfriends[l++] = currentuserfriend;
    }
    drb.Close();

    string getshoutout = "SELECT * FROM shoutout WHERE username = '" + currentuserfriends + "' AND username ='" + currentuser + "' order by datetime DESC";
    SqlCommand cmdc = new SqlCommand(getshoutout, con);
    SqlDataReader drc;
    drc = cmdc.ExecuteReader();

    while (drc.Read())
    {
        string postername = drb["username"].ToString();
        posternames[m++] = postername;
    }
    drc.Close();

    Label2.Text = posternames[0];
    Label3.Text = posternames[1];
    Label4.Text = posternames[2];
    Label5.Text = posternames[3];
    Label6.Text = posternames[4];

Upvotes: 0

Views: 2577

Answers (4)

Steven
Steven

Reputation: 172626

You can use a List<string> instead of an array. In that case you can do something like this:

var currentuserfriends = new List<string>();

while (drb.Read())
{
    currentuserfriends.Add(drb["friend"].ToString())
}

Like others said, you can optimize your query. I like to take it one step further and advice Entity Framework (or LINQ to SQL). It allows you to do this:

var db = new ObjectContext();

string[] currentuserfriends = (
    from friend in db.Friends
    where friend.Username == currentuser
    select friend.Name)
    .ToArray();

Saves you from having to hand craft your SQL queries and directly prevents your queries from SQL injection, since your current queries are currently vulnerable to SQL injection.

Upvotes: 1

Murugavel
Murugavel

Reputation: 279

First make your array a comma seperated string using the following code

string idString = String.Join(",",currentuserfriends);

Then, Use the below sql statement

SELECT friend FROM friend WHERE username in ("+idstring+");

Upvotes: 0

tafa
tafa

Reputation: 7326

Is there a way to loop currentuserfriends until the end in a single select statement?

Yes, there is. the IN Operator does that.

Upvotes: 0

Liam Sorsby
Liam Sorsby

Reputation: 2982

Use the foreach loop on the array and then generate the SQL query

Upvotes: 0

Related Questions