c# - 你如何使用 MySql 的 IN 子句

Question

我正在尝试弄清楚如何将 MySql In cluse 与 ASP.NET C# 一起使用。这是我的代码

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

当我不使用参数时，此代码有效。当我包含参数时，我从查询中得不到任何结果。

这是我的参数代码

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

这是完整的代码

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

Answer 1

我找到了答案。在这里

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