搜索细化为WebMatrix的网站 [英] Search refinement for WebMatrix site
问题描述
我一直在问这个网站了几个问题，并回答很多小块的，但现在我试图拼凑整个事情在一起。
I've been asking a few questions on this site, and have answers to lots of little bits, but i am now trying to piece the whole thing together.
我有一个页面，它显示了所有我在我的数据库的属性。我需要找到一种方法，虽然这些细化的结果，所以例如，只显示有4个卧室等。现在的问题是，我不知道有多少变数我可能在搜索词还没有属性。因此，这里是一个例子。
I have a page where it shows all the properties i have in my database. I need to find a way to refine those results though, so for example, to only show properties that have 4 bedrooms etc. The problem is, i don't know how many variables i might have in the search term yet. So here's an example.
- 用户可能希望看到所有属性，仅仅有4个卧室
- 用户可能希望看到有4间卧室和3个浴室，可供8人等。 所有属性
我已经决定把所有的变量从查询字符串，numbedrooms = 4和;？numbathrooms = 3
I have decided to pull all the variables from a querystring, ?numbedrooms=4&?numbathrooms=3
这里的code我有这么远，但它不工作：
here's the code i have so far, but it doesn't work:
@{
var db = Database.Open("StayInFlorida");
IEnumerable<dynamic> queryResults;
int numBedrooms = Request.QueryString["numBedrooms"].AsInt();
int numBathrooms = Request.QueryString["numBathrooms"].AsInt();
int sleeps = 0;
List<int> argList = new List<int>();
if (Request.QueryString["sleeps"].IsInt())
{
sleeps = Request.QueryString["sleeps"].AsInt();
}
int numOfArguments = 0;
string selectQueryString = "SELECT * FROM PropertyInfo ";
if (numBedrooms != 0)
{
argList.Add(numBedrooms);
selectQueryString += "WHERE numBedrooms = @0 ";
numOfArguments++; //increment numOfArguments by 1
}
if (numBathrooms != 0)
{
argList.Add(numBathrooms);
if (numOfArguments == 0)
{
selectQueryString += "WHERE numBathrooms = @0 ";
}
else
{
selectQueryString += "AND numBathrooms = @" + numOfArguments + " ";
}
numOfArguments++;
}
if (sleeps != 0)
{
argList.Add(sleeps);
if (numOfArguments == 0)
{
selectQueryString += "WHERE sleeps = @0 ";
}
else
{
selectQueryString += "AND sleeps = @" + numOfArguments + " ";
}
numOfArguments++;
}
selectQueryString += "ORDER BY numBedrooms DESC"; //Adjust this order by clause how you see fit.
int[] argArray = argList.ToArray();
if (argArray.Length > 0)
{
queryResults = db.Query(selectQueryString, argArray); //stores a dynamic list to 'queryResults' which can later be easily iterated through with a 'foreach' list and written to the page with razor.
}
}
<!--Results Start-->
@foreach(var row in queryResults){
<div class="container">
<h4><a href="/property.cshtml?propertyid=@row.PropertyID">@row.PropertyName</a></h4>
<h5>Bedrooms: @row.NumBedrooms Bathrooms: @row.NumBathrooms Sleeps: @row.NumSleeps</h5>
</div>
}
<!--Results Finish-->
所以要澄清，我需要找到一个方法来检查，看看是否有针对每个搜索查询的查询字符串项，如果有，将其附加到SQL语句来查看所有结果那场比赛？
So to clarify, i need to find a way to check to see if there is a querystring entry for each search query, and if there is, append it to the SQL statement to see all results that match?
Pleeeeeease帮助，我一直停留在这个很长一段时间。
Pleeeeeease help, i've been stuck on this for a LONG time.
非常感谢
推荐答案
主要是，它似乎是你需要的是一个很好的用户界面的页面。
Mainly, what it seems that you need is a good "user-interface" page.
在该网页，你有几个表单输入元素（单选按钮，文本框，复选框等），其中，如果留为空白，它们将被忽略，但如果没有，您检测和相应的追加你需要的SQL语法SQL字符串。
Within that page, you have several form input elements (radio buttons, text boxes, checkboxes, etc.), where if left blank, they are ignored, but if not, you detect and append accordingly the SQL syntax you need to the SQL string.
例如，让我们只说你有了第一（用户输入）页面上这种形式（我们称之为 page1.cshtml ）
For instance let's just say you had this form on the first (user-input) page (let's call it page1.cshtml):
<!--[HTML]-->
<form action="~/page2.cshtml" method="GET">
<select name="numBedrooms">
<option value="0">Please Select</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
</select>
<select name="numBathrooms">
<option value="0">Please Select</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
</select>
<input type="text" maxlength="3" name="sleeps" />
</form>
当然，可行的方案，而这种形式的输入字段的类型完全maleable您的具体方案，但你的想法，我敢肯定。
Of course, the viable options, and type of input fields on this form are totally maleable to your specific scenario, but you get the idea, I'm sure.
接下来，在开始构造SQL字符串 page2.cshtml 文件的一部分：
Next, in the part of the page2.cshtml file that begins to construct the SQL string:
/*[C#]*/
var db = Database.Open("StayInFlorida");
int numBedrooms = Request.QueryString["numBedrooms"].AsInt();
int numBathrooms = Request.QueryString["numBathrooms"].AsInt();
int sleeps = 0;
List<int> argList = new List<int>();
if (Request.QueryString["sleeps"].IsInt())
{
sleeps = Request.QueryString["sleeps"].AsInt();
}
int numOfArguments = 0;
string selectQueryString = "SELECT * FROM PropertyInfo ";
注意这个字符串，如果不出意外被附加到它，就会做工精细，只是查询数据库中的所有项目，没有语法错误（如果在最后一个额外的空间SQL不介意，这是好知道，因为它会让你的生活更轻松的道路）。但是，现在，你可以开始检查看的什么的信息传递给从previous这个页面，并追加相应的SQL字符串。
Notice that this string, if nothing else were appended to it, would work fine and simply query for all entries in the database, without syntax error (SQL doesn't mind if there is an extra space at the end. This is good to know, because it will make your life easier down the road). However, now, you can start checking to see what info was passed to this page from the previous, and append to the SQL string accordingly.
此外，也许最简单的方法，来查询数据库，而合格的未知直到运行时的参数，是一个数组传递给db.Query方法的适当数量。因此，对于我们测试为有效的每个参数，将被添加到列表，后来转换为数组（我们使用榜第一，因为C＃不允许你定义未知大小的数组，而是一个列表将让你当您去增加值）。
Also, and perhaps the easiest way, to query the database while passing the appropriate number of unknown-until-runtime parameters, is to pass an array to the db.Query method. So, for each parameter that we test as valid, will be added to a list and later converted to array (we use a list first, because C# does not allow you to define an array with unknown size, but a list will allow you to add values as you go).
if (numBedrooms != 0)
{
argList.Add(numBedrooms);
selectQueryString += "WHERE numBedrooms = @0 ";
numOfArguments++; //increment numOfArguments by 1
}
if (numBathrooms != "0")
{
argList.Add(numBathrooms);
if (numOfArguments == 0)
{
selectQueryString += "WHERE numBathrooms = @0 ";
}
else
{
selectQueryString += "AND numBathrooms = @" + numOfArguments + " ";
}
numOfArguments++;
}
if (sleeps != 0)
{
argList.Add(sleeps);
if (numOfArguments == 0)
{
selectQueryString += "WHERE sleeps = @0 ";
}
else
{
selectQueryString += "AND sleeps = @" + numOfArguments + " ";
}
numOfArguments++;
}
selectQueryString += "ORDER BY numBedrooms DESC"; //Adjust this order by clause how you see fit.
请记住，这取决于如何将数据存储在数据库中（nvarchar的，诠释等），你可能需要使用SQL字符串内的铸造，在C＃中的解析，或者简单地调整你写的selectQueryString的方式，当您去，但我不会去的，任何进一步的细节，因为数据类型/转换是出了这个问题的范围。
Keep in mind that depending on how the data is stored in the database (nvarchar, int, etc.) you may have to use casting within the SQL string, parsing in C#, or simply adjust the way you write the selectQueryString, as you go, but I won't go into any further detail on that, because data types/conversions are out of the scope of this question.
接下来，我们的参数列表转换为数组。
Next we convert the list of arguments to an array.
int[] argArray = argList.ToArray();
最后，您只需要查询数据库。但是，我们还需要的确保的该数组我们发送的名单不为空（这很可能由该列表是空的时候，我们将它转换引起的）。我向你保证，如果你传递一个空数组作为第二个参数 db.Query（）方法，你会得到一个服务器端错误。
Lastly, you just have to query the database. However, we also need to MAKE SURE that the array we sent the list to is not empty (which would most likely be caused by the list being empty when we converted it). I assure you, you will get a server-side error if you pass an empty array as the second argument to the db.Query() method.
if (argArray.Length > 0)
{
var queryResults = db.Query(selectQueryString, argArray); //stores a dynamic list to 'queryResults' which can later be easily iterated through with a 'foreach' list and written to the page with razor.
//put your "foreach (var row in queryResults)" loop here.
}
和真的仅此而已。唯一的一点是，有很多东西你可能为了得到与您的特定数据类型，这方面的工作，以批判（因为它们是从表单页面的到来，尤其是因为它们是存储在数据库中）。
And really that's about it. The only thing is, there are a lot of things you may have to critique in order to get this working with your specific data types (as they are coming from the form page, but especially as they are stored within the database).
希望这将至少让你更接近了一步实现你的目标。让我知道如果你需要更多的帮助，有任何疑问，或（尽管我尽了最大努力）发现一个语法或其他错误。
Hopefully this will, at least, get you a step closer to achieving your goal. Let me know if you need any more help, have any more questions, or (despite my best efforts) spot a syntax or other error.
快乐编码，加文！
这篇关于搜索细化为WebMatrix的网站的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
