|
Apology for late reply. I been trying to get this filter to work for the past couple of days but so far I am sadly failing.
I am trying to filter data by searching for many values for certain parameter. for example, filter data by id1 = 123 (or),234&(or)id=345&name=..ect. Apology for not making my problem clear. I usually check the any api query string by converting it into sql schema, to look for same records/data with the web api query string.
I can get the 'contain' function to work with two parameter like so:
if (query.id1!= null || query.id2!= null)
{
data = data.Where(c => c.ID1.Contains(query.id1) || c.ID2.Contains(query.id2));
}
but i would like to able search many ids from parameter id1 or id2, for example: api/item?id1=345,567&id2=678
I cannot seem to get 'split' function to work with the above code layout.
Thank you for your time and help.
|
|
|
|
|
Every time you post a message, the code changes!
In your previous message, you said you'd managed to get Split working to search for multiple names. You've now changed the code to search for ID1 or ID2 , and to only return records where the value in the database contains the full value passed in the querystring.
I think you need to go back to the start and describe exactly what you are trying to achieve with this method. It would also help to have a stable description of your database entities and query class.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello, I would like to sincerely apologies for the confusion with my data parameters. i will try to explain the continuous issue I am currently experiencing with the 'split' function
as clearly and with
the requested classes and entities.
I would like to be able to call multiple 'name' and 'id1''s entities in a single call, such as api/test?name=storm,prest&id1=123. So far, I can get only get one of the parameter's (name or id1) to output multiple string values.
If I try to execute, the following with more than two parameters: api/test?name=storm,prest&id1=123, I get a 'no data error'.
This is my updated code:
please note: the split function has been updated for checking null strings in 'name' and 'ids' parameters.
public HttpResponseMessage Get([FromUri] Query query)
{
var data = db.database_ICs.AsQueryable();
if (!String.IsNullOrEmpty(query.name))
{
var ids = query.name.Split(',').ToList();
data = data.Where(c => ids.Any(i => (c.Name!= null && c.Name.Contains(i))));
}
if (!String.IsNullOrEmpty(query.id1))
{
var ids = query.id1.Split(',').ToList();
data = data.Where(c => ids.Any(i => (c.ID1!= null && c.ID1.Contains(i))));
data = data.Where(c => c.Name == query.name);
}
if (!String.IsNullOrEmpty(query.id2))
{
var ids = query.id2.Split(',').ToList();
data = data.Where(c => c.ID2 == query.id2);
}
if (query.deal_type != null)
{
data = data.Where(c => c.Type == query.deal_type);
}
if (!data.Any())
{
var message = string.Format("No data was found");
return Request.CreateErrorResponse(HttpStatusCode.NotFound, message);
}
return Request.CreateResponse(HttpStatusCode.OK, data);
}
public class Query
{
public string name { get; set; }
public string id1{ get; set; }
public string id2{ get; set; }
public string deal_type { get; set; }
}</pre>
Name id1 id2 deal_type
prest 789 ms
prest 345 ms
storm 456 bs
storm bs
ariel 123 rs
ariel 223 rs
Thank you so much your time and help. Many thanks.
|
|
|
|
|
OK, I think I see the problem. You're combining the filters for multiple parameters by chaining calls to the Where method. This will only return records which match all of the filters. Looking at your data, none of the records match both ID1 = 123 and (Name.Contains("storm") || Name.Contains("prest")) .
I'm guessing you want to return records which match any of the filters. In this case, the simplest option is to use the Union method:
var data = db.database_ICs.AsQueryable();
var filteredData = new List<IQueryable<database_IC>>();
if (!string.IsNullOrEmpty(query.name))
{
var ids = query.name.Split(',');
foreach (string i in ids)
{
filteredData.Add(data.Where(c => c.Name != null && c.Name.Contains(i)));
}
}
if (!string.IsNullOrEmpty(query.id1))
{
var ids = query.id1.Split(',');
foreach (string i in ids)
{
filteredData.Add(data.Where(c => c.ID1 != null && c.ID1.Contains(i)));
}
}
if (!string.IsNullOrEmpty(query.id2))
{
var ids = query.id2.Split(',');
foreach (string i in ids)
{
filteredData.Add(data.Where(c => c.ID2 != null && c.ID1.Contains(i)));
}
}
if (query.deal_type != null)
{
filteredData.Add(data.Where(c => c.Type == query.deal_type));
}
if (filteredData.Count != 0)
{
data = filteredData.Aggregate(Queryable.Union);
}
if (!data.Any())
{
var message = string.Format("No data was found");
return Request.CreateErrorResponse(HttpStatusCode.NotFound, message);
}
return Request.CreateResponse(HttpStatusCode.OK, data);
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you so much for time and solution. from your suggested code, i was able to execute query name and id1.
I have one small issue, when I add deal_type filter parameter to the ids search string, I do not get the accurate number of records. When i search for query 'name=storm, prest&id1=6782345', i get 10 records, from which have 9 have a deal_type= cs and other with deal-type=ms.
If search the above query with the deal-type parameter such as (api/test?name=storm,prest&id2=6782345&deal_type=ms), I should get 1 record but instead I get 2700 records.
I tired editing/debugging the code using the following logic, but I cannot seem to get to a working solution:
([id1 = value1 or value2] or [id1 = values1 or value2]) and deal_type = ms
Any example would be very much helpful. Apology for causing so much trouble. I really appreciate your hardwork and advice.
Many thanks
|
|
|
|
|
Try moving the deal_type filter to the start:
var data = db.database_ICs.AsQueryable();
if (query.deal_type != null)
{
data = data.Where(c => c.Type == query.deal_type);
}
var filteredData = new List<IQueryable<database_IC>>();
if (!string.IsNullOrEmpty(query.name))
{
var ids = query.name.Split(',');
foreach (string i in ids)
{
filteredData.Add(data.Where(c => c.Name != null && c.Name.Contains(i)));
}
}
if (!string.IsNullOrEmpty(query.id1))
{
var ids = query.id1.Split(',');
foreach (string i in ids)
{
filteredData.Add(data.Where(c => c.ID1 != null && c.ID1.Contains(i)));
}
}
if (!string.IsNullOrEmpty(query.id2))
{
var ids = query.id2.Split(',');
foreach (string i in ids)
{
filteredData.Add(data.Where(c => c.ID2 != null && c.ID1.Contains(i)));
}
}
if (filteredData.Count != 0)
{
data = filteredData.Aggregate(Queryable.Union);
}
if (!data.Any())
{
var message = string.Format("No data was found");
return Request.CreateErrorResponse(HttpStatusCode.NotFound, message);
}
return Request.CreateResponse(HttpStatusCode.OK, data);
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello, Apology for the very late response. I wanted to sincerely thank you for your hardwork and precious time in helping me solving this issue. I manage to get the API split function to work by adding little changes to the code and using your solution.
You have been a great help and I am very grateful for your help and time.
Apology for keep burdening you with this issue.
Many thanks!
Have a great evening.
|
|
|
|
|
Reports are in the form of pdf,ppt and dynamic reports can be handle in Iframe(Which come from other site).And reports can be access by some specific website user..
Anyone can help me for these.
|
|
|
|
|
redpoll
hi
how can i send an image in the my posts?
there aren't any option for this.
please help me
|
|
|
|
|
|
hello to all
i have a problem with the program that see in below;
this program has not any syntax error but doesn't work;
i thank you if help me.
Default.aspx page:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
</asp:ToolkitScriptManager>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server"
TargetControlID="TextBox1" ServiceMethod="GetCompletionList"
UseContextKey="True" MinimumPrefixLength="1" CompletionInterval="100" ServicePath="~/WebService.asmx">
</asp:AutoCompleteExtender>
</div>
</form>
</body>
</html>
Default.aspx.cs
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
}
in .asmx file
<%@ WebService Language="C#" CodeBehind="~/App_Code/AutoComplete.cs" Class="AutoComplete" %>
in .asmx code behind file
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Configuration;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class AutoComplete : System.Web.Services.WebService {
public AutoComplete()
{
}
[WebMethod]
public string[] GetCompletionList(string prefixText, int count)
{
SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString);
string query = "select * from tbl_users where username like username";
SqlCommand cm = new SqlCommand(query,cn);
cm.Parameters.AddWithValue("username","N%"+prefixT ext+"%");
cn.Open();
SqlDataReader dr = cm.ExecuteReader();
List<string> Textbox1 = new List<string>();
String dbValues;
while (dr.Read())
{
dbValues = dr["username"].ToString().ToLower();
Textbox1.Add(dbValues);
}
return Textbox1.ToArray();
cn.Close();
}
}
note:the visual studio doesn't know "Texbox1" (in asmx codbehind)
modified 18-Dec-13 18:38pm.
|
|
|
|
|
For parameter fields you should add prefix @ , like below
Ya Rasoolallah wrote:
string query = "select * from tbl_users where username like @username";
SqlCommand cm = new SqlCommand(query,cn);
cm.Parameters.AddWithValue("@username","N%"+prefixT ext+"%");
thatrajaCode converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute
|
|
|
|
|
thanks
my friend , still does not work.
why?
test it please.
|
|
|
|
|
I don't think you need N in below line
cm.Parameters.AddWithValue("@username","N%"+prefixText+"%");
Debug your code & make sure you get data in SqlDataReader dr (below line)
while (dr.Read())
Before that make sure you have data in your database table tbl_users.
I think you tried example from this article[^] so make sure you didn't miss any steps mentioned there.
thatrajaCode converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute
|
|
|
|
|
hello
the problem is not from @ or N and etc...
my program doesn't have any syntax error but doesn't work not at all.
please debug my program to see why not to work?thanks.
i worked from
that example link that you said step by step exactly.
modified 19-Dec-13 12:18pm.
|
|
|
|
|
I have used many time auto complete Extender. I check out your code behind file.
[WebMethod]
public static string[] City(string prefixText, int count, string contextKey)
{
DataTable dt = new DataTable();
dt = Bal_BindingData._city(prefixText);
string[] movies = new string[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
movies[i] = dt.Rows[i]["City"].ToString();
}
return movies;
}
i think you forgot string Contextkey Parameter in GetCompletionList method.
don't confused my with code i have used three tire technique. I hope your problem has been solved.
jsb
|
|
|
|
|
I know this question is kind of broad, and I apologize. I read the rules, and think I can ask this. However, if this is against the rules, I'm fine with that and will delete it.
I'm new to C# in general, and am now in the 21st chapter of the book "C# for Programmers, Second Edition" by Deitel (isbn 0-13-134591-5)*. I'm on Visual Studio 2010, and this book's examples deal with 2005. Still, that's been no obstacle up until now, with ASP. I got by OK earlier in the chapter, but I'm now being asked to link a particular database to a datagridview control. When I try to link up this database, I get an error that basically says my version of SQL Server is incompatible with the pre-release version of this database. All solutions to this problem indicate that I'm supposed to install SQL Server 2005 just to update the permissions on this database. I went through a lot just to get 2008 fine and running on my computer, and am not going to dip my toe into that river of confusion.
The thing is: it's this particular database. I tried to import another DB from previous lessons just to see, and that one come in fine. I'm going to try to continue the lesson with that one. However, I feel this will be a growing problem until I get back into non DB stuff later. So, I was wondering what ASP.Net/SQL material you guys would recommend (from here or elsewhere) that's thorough and can match what's in this book. I'm looking around here, but I'm drowning in random topics and don't know what to "trust" because I really don't know where to start.
modified 18-Dec-13 18:19pm.
|
|
|
|
|
If you want to start with ASP.NET the perhaps the book "MCTS -70-515 self paced learning kit" is the best place to start with.
If you want to start ASP.NET MVC then "MvcMusicStore tutorial" from codeplex is the best place to start.
For SqlServer, I think get any book on sql server for developers from Microsoft press and that will do. Just check that the book is meant for developers and not the DBAs.
Twenty years from now you will be more disappointed by the things that you didn't do than by the ones you did do. So throw off the bowlines. Sail away from the safe harbor. Catch the trade winds in your sails. Explore, Dream. Discover.
|
|
|
|
|
tyr this website:
thenewboston.org
|
|
|
|
|
I would like a pass value from custom text box to controller in MVC 4.
How can I do that?
Please advise.
|
|
|
|
|
I am totally new to WCF and web services.
I need to create a REST based wcf service for which I have got the sample walkthrough from code project.
I would like have the Uri Template like https://example.com/{v}/{resource}?filter={attribute}{op}{value}&sortBy={attributeName}&sortOrder={ascending|descending}&attributes={attribute list in csv}
attribute:attribute to be searched
op:operator( format of eq,and,or,neq,like,contains ) value:attribute value to be searched
attributeName:sortBy attribute name
sortOrder should be enum with ascending or descending. attributes:attributeslist in csv
Is this possible?
Could anyone help me in configuring UriTemplate and OperationContract for this?
|
|
|
|
|
It looks like you want query support in URLs so that you can pass in query like URLs and get the filtered data from the server. Why dont you create a WCF data service which will provide this support out of the box. And you can also expose a rest endpoint for the same too.
A Beginner's Tutorial for Creating WCF Data Services[^]
Twenty years from now you will be more disappointed by the things that you didn't do than by the ones you did do. So throw off the bowlines. Sail away from the safe harbor. Catch the trade winds in your sails. Explore, Dream. Discover.
|
|
|
|
|
i am displaying records gridview vertically by converting rows into column hence i want column wise paging in gridview. thanks in advance
|
|
|
|
|
|
I think OP asked for paging..conversion part is already over..
|
|
|
|