mysql in 子句,您如何使用MySql IN子句

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 FindPostsByWebSiteList(IEnumerable wsl)

{

var pl = new List();

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;

}

}

}

解决方案

I have found the answer. Here it is

public static IList FindPostsByWebSiteList(string[] urls)

{

var pl = new List();

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;

}

}

}