How do you use the MySql IN clause

Question

I am trying to figure out how to use the MySql In cluse with ASP.NET C#. Here is my code

var WebSites = string.Join(",", wsl.Select(x => "'" + x.DomainUrl + "'").ToArray());
string q = "select Id, Url, Title, Date, ImageUrl from post where WebSiteUrl IN (@Url)";

When I dont use parameters this code works. When I include parameters I get no results from the query.

Here is my code for parameters

cmd.Parameters.Add("@Urls", MySqlDbType.Text).Value = WebSites;

here is whole code

 public static IList<Post> FindPostsByWebSiteList(IEnumerable<WebSite> wsl)
 {
        var pl = new List<Post>();
        var WebSites = string.Join(",", wsl.Select(x => "'" + x.DomainUrl + "'").ToArray());
        string q = "select Id, Url, Title, Date, ImageUrl from post where WebSiteUrl IN (@Urls)";

        using (MySqlConnection con = new MySqlConnection(WebConfigurationManager.ConnectionStrings["MySqlConnectionString"].ToString()))
        {
            using (MySqlCommand cmd = new MySqlCommand(q, con))
            {
                cmd.Parameters.Add("@Urls", MySqlDbType.Text).Value = WebSites;
                con.Open();

                var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    var p = new Post();
                    p.Id = reader.GetInt32("Id");
                    p.Url = reader.GetString("Url");
                    p.Title = reader.GetString("Title");
                    p.Date = reader.GetDateTime("Date");
                    p.ImageUrl = reader.GetString("ImageUrl");
                    pl.Add(p);
                }
                return pl;
            }
        }
    }

That happens because you bind all the urls in a single string. — zerkms, Jul 24 '11 at 02:55
I am not familiar with binding parameters in C#, but are you sure you can bind in this manner? Usually you will bind an actual parameter of the query. What you are doing is binding part of the query. The `IN` clause is expecting a comma-separated list. You are giving it a single variable instead, whose value contains the text of a comma-separated list. I'd imagine this will cause problems. — Brad, Jul 24 '11 at 02:57

score 3 · Accepted Answer · answered Jul 24 '11 at 04:08

I have found the answer. Here it is

    public static IList<Post> FindPostsByWebSiteList(string[] urls)
    {
        var pl = new List<Post>();
        var urlArray = urls.Select((x,y) => "@url" + y.ToString()).ToArray();
        var urlsJoined = string.Join(",", urlArray);
        string q = string.Format("select Id, Url, Title, Date, ImageUrl from post where WebSiteUrl IN ({0})", urlsJoined);

        using (MySqlConnection con = new MySqlConnection(WebConfigurationManager.ConnectionStrings["MySqlConnectionString"].ToString()))
        {
            using (MySqlCommand cmd = new MySqlCommand(q, con))
            {
                for (int x = 0; x < urlArray.Length; x++)
                {
                    cmd.Parameters.Add(urlArray[x], MySqlDbType.Text).Value = urls[x];
                }

                con.Open();

                var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    var p = new Post();
                    p.Id = reader.GetInt32("Id");
                    p.Url = reader.GetString("Url");
                    p.Title = reader.GetString("Title");
                    p.Date = reader.GetDateTime("Date");
                    p.ImageUrl = reader.GetString("ImageUrl");
                    pl.Add(p);
                }
                return pl;
            }
        }
    }

score 2 · Answer 2 · answered Jul 24 '11 at 02:55

2

You have referenced @Url instead of @Urls

maybe just a typo in your question though

answered Jul 24 '11 at 02:55

Lea Hayes

62,536
16
62
111

Hmmm..I have corrected the mistake but its still not returning any results – Luke101 Jul 24 '11 at 03:00
The problem you have is that your query becomes IN ( "..." ) the entire list of urls gets encoded as a string. You might need to compose that part of the query manually using `MySqlHelper.EscapeString` would need to be tested though – Lea Hayes Jul 24 '11 at 03:06

score 0 · Answer 3 · edited May 23 '17 at 12:08

0

The IN statement should expect an Array of strings, and you are passing a single string

Your final SQL is looking like this:

select Id, Url, Title, Date, ImageUrl from post where WebSiteUrl IN ('url1,url2,url3')

Instead of

select Id, Url, Title, Date, ImageUrl from post where WebSiteUrl IN ('url1', 'url2', 'url3')

Check this question:

Add List<int> to a mysql parameter

edited May 23 '17 at 12:08

Community

1
1

answered Jul 24 '11 at 03:06

everton

7,579
2
29
42

How do you use the MySql IN clause

3 Answers3

Linked