privatevoid btnSearch_Click(object sender, System.EventArgs e) { //首先需要建立一个StringBuilder对象 StringBuilder sql =new StringBuilder(); // Limit maximum resultset size sql.Append(@"SELECT TOP "); sql.Append(ConfigurationSettings.AppSettings["searchLimit"]); sql.Append(@" [User].UserID, [User].FirstName, [User].LastName, Place.PlaceID, Place.Name AS PlaceName, PlaceType.Name AS PlaceType, PlaceType.TypeID, TimeLapse.Name AS LapseName, TimeLapse.YearIn, TimeLapse.MonthIn, TimeLapse.YearOut, TimeLapse.MonthOut FROM [User] LEFT OUTER JOIN TimeLapse ON TimeLapse.UserID = [User].UserID LEFT OUTER JOIN Place ON Place.PlaceID = TimeLapse.PlaceID LEFT OUTER JOIN PlaceType ON Place.TypeID = PlaceType.TypeID "); // 生成WHERE语句Build the WHERE clause now StringBuilder qry =new StringBuilder(); if (txtFirstName.Text != String.Empty) { qry.Append("[User].FirstName LIKE '%"); qry.Append(txtFirstName.Text).Append("%' AND "); } if (txtLastName.Text != String.Empty) { qry.Append("[User].LastName LIKE '%"); qry.Append(txtLastName.Text).Append("%' AND "); } if (cbPlace.SelectedItem.Value !="0") { qry.Append("[Place].PlaceID = '"); qry.Append(cbPlace.SelectedItem.Value).Append("' AND "); } if (cbType.SelectedItem.Value !="0") { qry.Append("[PlaceType].TypeID = '"); qry.Append(cbType.SelectedItem.Value).Append("' AND "); } if (txtYearIn.Text != String.Empty) { qry.Append("TimeLapse.YearIn = "); qry.Append(txtYearIn.Text).Append(" AND "); } if (txtYearOut.Text != String.Empty) { qry.Append("TimeLapse.YearOut = "); qry.Append(txtYearOut.Text).Append(" AND "); } //根据发表日期的ddlPostTime的下拉列表中所选定的值与当前时间计算时间范围 //如果为所有日期(ddlPostTime.SelectedValue="0")则将日期调为2000年,否则按当前时间减selectedvalue的天数成为查询的日期值 // DateTime qryDateTime=new DateTime(); // if(ddlPostTime.SelectedValue=="0") // { // qryDateTime=new System.DateTime(1999, 12, 31, 23, 59, 0); // } // else // { // Double days=Convert.ToDouble(ddlPostTime.SelectedValue); // qryDateTime=DateTime.Today.AddDays(days); // } // qry.Append("posttime >=#"); //使用时间需加# // qry.Append(qryDateTime).Append("# AND "); //以下语句可构成多条件中一个或多个条件进行搜索 string filter = qry.ToString(); if (filter.Length !=0) { sql.Append(" WHERE "); // Add the filter without the trailing AND sql.Append(filter.Remove(filter.Length -4, 4)); } SqlDataAdapter ad =new SqlDataAdapter(sql.ToString(), cnFriends); dsResults =new DataSet(); ad.Fill(dsResults, "User"); // Adjust label for results if (dsResults.Tables["User"].Rows.Count < Convert.ToInt32(ConfigurationSettings.AppSettings["searchLimit"])) { lblLimit.Text ="Found "+ dsResults.Tables["User"].Rows.Count.ToString() + " users matching your criteria on initial search."; } else { lblLimit.Text ="You're working with the first "+ ConfigurationSettings.AppSettings["searchLimit"] + @" results. If you're looking for someone who's not in this list, please search again with a more precise search criterion."; } // Place results in session state Session["search"] = dsResults; SetResultsState(true); }