您如何使用MySql IN子句 [英] How do you use the MySql IN clause
本文介绍了您如何使用MySql IN子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图弄清楚如何将MySql与ASP.NET C#一起使用.这是我的代码
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.
这是我的参数代码
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;
}
}
}
推荐答案
我找到了答案.在这里
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;
}
}
}
这篇关于您如何使用MySql IN子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文