CODEWITHSUNDEEP
c#mysql
Luke101
Luke101

Reputation: 65268

How do you use the MySql IN clause

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;
            }
        }
    }

Upvotes: 3

Views: 3823

Answers (3)

Luke101
Luke101

Reputation: 65268

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;
            }
        }
    }

Upvotes: 3

everag
everag

Reputation: 7672

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

Upvotes: 0

Lea Hayes
Lea Hayes

Reputation: 64196

You have referenced @Url instead of @Urls

maybe just a typo in your question though

Upvotes: 2

Related Questions