I wasted too much time on this issue and thought to write on the forum. I really appriciate if you can help me for this issue.
There is an API which is giving xml output. But It is not gving all fields what I want. The owner of API is saying me that should work and it should give all records. I will show the demo XML file and my datatable C# code to explain my issue.
Column count is 5 and Other columns are not in the data table. I just noticed that is not retrieving records which has an additional attribute like
type="integer"
. There are only 5 records which dont have a type and that is what I can retrieve. So where is the issue?
Please can you help me to get all records from the xml file?
Thanks a lot for your advices. This is a web application and my Linq knowladge is not enough. So please sorry for me. I tried your solution and I am getting an error.
I am trying to set my variables like this.
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(_myDomain + "/rules/651263.xml");
request.Method = "GET";
request.Credentials = new NetworkCredential(_Username, _Password);
request.ContentLength = 0;
HttpWebResponse resp = (HttpWebResponse)request.GetResponse();
StreamReader reader = new StreamReader(resp.GetResponseStream());
string tmp = reader.ReadToEnd();
XDocument Xdoc = XDocument.Parse(tmp);
//Run query
var result = from etickets in Xdoc.Descendants("ticket")
select new
{
eSubject = etickets.Element("Subject").Value,
eNiceId = etickets.Element("nice-id").Value,
eStatusID = etickets.Element("status-id").Value,
eDescription = etickets.Element("Description").Value
};
This is the error I am getting. Please can you advice me where have I missed?
System.NullReferenceException: Object reference not set to an instance of an object. at ......View.b__0(XElement etickets) at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext() at ........View.Page_Load(Object sender, EventArgs e)
Wow.. Excellent ! Thank you very much for your brilliant support ! You saved me. I will mark your replies as answers. Is there anyway to increase your points? I really like your support and you were bloody fast.
If you never mind please can you tell me how to bind these result to a datagrid? I do not want to bind all fields. I just want only 4 fields to bind to the datagridview.
In between can I have your eyes to find my mistakes? :-) I didnt see that case sensitive issue at all.
Wow.. Excellent ! Thank you very much for your brilliant support ! You saved me. I will mark your replies as answers. Is there anyway to increase your points? I really like your support and you were bloody fast.
Never mind, I hope you can also help others when you are free, this will also do you well in technology.
maduranga001
I do not want to bind all fields. I just want only 4 fields to bind to the datagridview.
Write as many as you want, That's OK.
Example:
var result = from e in XDocument.Load("xxx.xml").Descedants("ticket")
select new
{ Subject= e.Element("subject").Value, Description = e.Element("description").Value, NiceId = e.Element("nice-id").Value, DueDate = e.Element("due-date").Value
};
Yes. I will help other forum users and there are some other areas what I can help them.
You saved me my time a lot and I know how it is valuable. Thanks a lot. Please can you help me for this as well please?
I tried to bind the 'result' to a gridview and its working. Then I tried to add paging and sorting like this. Forecolor is also same always and its blue.
But it is saying that is not supporting in this data set. Error is there.
System.NotSupportedException: The data source does not support server-side data paging. at System.Web.UI.DataSourceView.RaiseUnsupportedCapabilityError(DataSourceCapabilities capability) at System.Web.UI.DataSourceSelectArguments.RaiseUnsupportedCapabilitiesError(DataSourceView view) at
How can I add paging and sorting feature?
Also I want to add the 'nice-id' as a part of a hyperlink and link it to another page in the grid view. Please can you advice me how to do this? eNiceID is giving me the correct valu of the url and I want to show this as a hyperlink in the gridview.
Sorry because it doesn't support paging and sorting directly. You have to handle "PageIndexChanging" and "Sorting" two important events to deal with the problems——
0）You should write your own class——
public class MyModel { public string Subject{get;set;} public string Status{get;set;} public string Description{get;set;} public string eNiceId{get;set;} }
Then you should create two private properties——
private string SortColumn
{ get
{
return ViewState["col"]==null?String.Empty:ViewState["col"].ToString();
}
set
{
ViewState["col"]=value;
}
}
private SortDirection SortDirect
{ get
{
return ViewState["dec"]==null?String.Empty:ViewState["dec"] as SortDirection
}
set
{
ViewState["dec"]=value;
}
}
Then you use your selecting statment like this
var result = (from e in XDocument.Load("xxx.xml").Descedants("ticket")
select new MyModel
{
Subject= e.Element("subject").Value,
Description = e.Element("description").Value,
NiceId = e.Element("nice-id").Value,
DueDate = e.Element("due-date").Value
}).ToList();
1）In the Page_Load event in the "if(!IsPostBack)｛……｝", plz save the class list into "ViewState["dt"]".
maduranga001
Member
162 Points
235 Posts
get data from XML datatable
Nov 10, 2011 01:58 AM|LINK
Hello,
I wasted too much time on this issue and thought to write on the forum. I really appriciate if you can help me for this issue.
There is an API which is giving xml output. But It is not gving all fields what I want. The owner of API is saying me that should work and it should give all records. I will show the demo XML file and my datatable C# code to explain my issue.
This is the XML file.
<?xml version="1.0" encoding="UTF-8"?> <tickets type="array" count="9"> <ticket> <assigned-at type="datetime">2011-09-27T16:49:38+10:00</assigned-at> <assignee-id type="integer">5966436</assignee-id> <base-score type="integer">180</base-score> <created-at type="datetime">2011-09-27T16:46:43+10:00</created-at> <current-collaborators nil="true"></current-collaborators> <current-tags>email errors services spade</current-tags> <description>Hi , Here is the Fact Find & Credit Proposal. Sorry it has taken me so long.Kind regards</description> <due-date type="datetime" nil="true"></due-date> <entry-id type="integer" nil="true"></entry-id> <external-id nil="true"></external-id> <field-288754 type=""></field-288754> <group-id type="integer">20061346</group-id> <initially-assigned-at type="datetime">2011-09-27T16:46:43+10:00</initially-assigned-at> <latest-recipients nil="true"></latest-recipients> <nice-id type="integer">6791</nice-id> <organization-id type="integer">20342216</organization-id> <organization-name type="">WWS Selector Group</organization-name> <original-recipient-address>support@mine.com</original-recipient-address> <priority-id type="integer">0</priority-id> <recipient>support@test.zendesk.com</recipient> <req-name type="">Kosi Woolsey</req-name> <requester-id type="integer">26236338</requester-id> <resolution-time type="integer" nil="true"></resolution-time> <solved-at type="datetime" nil="true"></solved-at> <status-id type="integer">1</status-id> <status-updated-at type="datetime">2011-09-27T16:46:43+10:00</status-updated-at> <subject>The Selector Group</subject> <submitter-id type="integer">26236338</submitter-id> <ticket-type-id type="integer">0</ticket-type-id> <updated-at type="datetime">2011-09-27T16:49:38+10:00</updated-at> <updated-by-type-id type="integer">0</updated-by-type-id> <via-id type="integer">4</via-id> <score type="integer">180</score> <problem-id nil="true"></problem-id> <channel nil="true"></channel> <permissions type="array"> </permissions> </ticket> </tickets>public static DataTable GetTickets() { HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(_myDomain + "/rules/651263.xml"); request.Method = "GET"; request.Credentials = new NetworkCredential(_Username, _Password); request.ContentLength = 0; HttpWebResponse resp = (HttpWebResponse)request.GetResponse(); StreamReader reader = new StreamReader(resp.GetResponseStream()); string tmp = reader.ReadToEnd(); StringReader stream = new StringReader(tmp); DataSet ds = new DataSet(); ds.ReadXml(stream); DataTable dt = ds.Tables["ticket"]; return dt; }Ok. I tried to get data from that code.
This is working and returning values
But this is not working..
Column count is 5 and Other columns are not in the data table. I just noticed that is not retrieving records which has an additional attribute like type="integer" . There are only 5 records which dont have a type and that is what I can retrieve. So where is the issue?
Please can you help me to get all records from the xml file?
A big help !
Thanks!
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: get data from XML datatable
Nov 12, 2011 12:39 AM|LINK
Hello maduragnga001:)
You can in fact also use XDocument to deal with your prblem like this by looping the element layer by layer：
public static DataTable GetTickets()
{
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(_myDomain + "/rules/651263.xml");
request.Method = "GET";
request.Credentials = new NetworkCredential(_Username, _Password);
request.ContentLength = 0;
HttpWebResponse resp = (HttpWebResponse)request.GetResponse();
StreamReader reader = new StreamReader(resp.GetResponseStream());
string tmp = reader.ReadToEnd();
XDocument doc = XDocument.Parse(tmp);
var result1 = doc.Root.Element("ticket").Element("subject").Value;
var result2 = doc.Root.Element("ticket").Element("nice-id").Value;
var result3 = doc.Root.Element("ticket").Element("Description").Value;
}
maduranga001
Member
162 Points
235 Posts
Re: get data from XML datatable
Nov 13, 2011 01:58 PM|LINK
Thank you for your reply. Please can yu tell me how can I get data for each raw like I used here.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: get data from XML datatable
Nov 14, 2011 12:22 AM|LINK
Hello again:)
You should use LINQ-TO-XML to fetch more than one "ticket" tags and do looping like this below:
var result = from e in XDocument.Load("xxx.xml").Descedants("ticket")
select new
{
NewProperty = e.Element("Subject").Value,
……………………
};
foreach(var item in result)
{
Console.WriteLine(item.NewProperty……);
}
maduranga001
Member
162 Points
235 Posts
Re: get data from XML datatable
Nov 14, 2011 02:18 AM|LINK
Thanks a lot for your advices. This is a web application and my Linq knowladge is not enough. So please sorry for me. I tried your solution and I am getting an error.
I am trying to set my variables like this.
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(_myDomain + "/rules/651263.xml"); request.Method = "GET"; request.Credentials = new NetworkCredential(_Username, _Password); request.ContentLength = 0; HttpWebResponse resp = (HttpWebResponse)request.GetResponse(); StreamReader reader = new StreamReader(resp.GetResponseStream()); string tmp = reader.ReadToEnd(); XDocument Xdoc = XDocument.Parse(tmp); //Run query var result = from etickets in Xdoc.Descendants("ticket") select new { eSubject = etickets.Element("Subject").Value, eNiceId = etickets.Element("nice-id").Value, eStatusID = etickets.Element("status-id").Value, eDescription = etickets.Element("Description").Value };and getting values like this.
This is the error I am getting. Please can you advice me where have I missed?
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: get data from XML datatable
Nov 14, 2011 03:46 AM|LINK
Hello again:)
Please check very carefully about the node name like "subject"——It should be "subject" not "Subject".——Notice that xml is CASE SENSITIVE.
maduranga001
Member
162 Points
235 Posts
Re: get data from XML datatable
Nov 14, 2011 04:07 AM|LINK
Wow.. Excellent ! Thank you very much for your brilliant support ! You saved me. I will mark your replies as answers. Is there anyway to increase your points? I really like your support and you were bloody fast.
If you never mind please can you tell me how to bind these result to a datagrid? I do not want to bind all fields. I just want only 4 fields to bind to the datagridview.
In between can I have your eyes to find my mistakes? :-) I didnt see that case sensitive issue at all.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: get data from XML datatable
Nov 14, 2011 04:14 AM|LINK
Never mind, I hope you can also help others when you are free, this will also do you well in technology.
Write as many as you want, That's OK.
Example:
var result = from e in XDocument.Load("xxx.xml").Descedants("ticket")
select new
{
Subject= e.Element("subject").Value,
Description = e.Element("description").Value,
NiceId = e.Element("nice-id").Value,
DueDate = e.Element("due-date").Value
};
maduranga001
Member
162 Points
235 Posts
Re: get data from XML datatable
Nov 14, 2011 05:45 AM|LINK
Yes. I will help other forum users and there are some other areas what I can help them.
You saved me my time a lot and I know how it is valuable. Thanks a lot. Please can you help me for this as well please?
I tried to bind the 'result' to a gridview and its working. Then I tried to add paging and sorting like this. Forecolor is also same always and its blue.
But it is saying that is not supporting in this data set. Error is there.
How can I add paging and sorting feature?
Also I want to add the 'nice-id' as a part of a hyperlink and link it to another page in the grid view. Please can you advice me how to do this? eNiceID is giving me the correct valu of the url and I want to show this as a hyperlink in the gridview.
select new{ Subject = etickets.Element("subject").Value, Status = GetTicketStatus(etickets.Element("status-id").Value), Description = etickets.Element("description").Value, eNiceId = _myDomain + "/tickets/" + etickets.Element("nice-id").Value };Once again.. Thank you very very much for your help and really happy about your knowladge.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: get data from XML datatable
Nov 14, 2011 06:15 AM|LINK
Hello again:)
Sorry because it doesn't support paging and sorting directly. You have to handle "PageIndexChanging" and "Sorting" two important events to deal with the problems——
0）You should write your own class——
public class MyModel
{
public string Subject{get;set;}
public string Status{get;set;}
public string Description{get;set;}
public string eNiceId{get;set;}
}
Then you should create two private properties——
private string SortColumn
{
get
{
return ViewState["col"]==null?String.Empty:ViewState["col"].ToString();
}
set
{
ViewState["col"]=value;
}
}
private SortDirection SortDirect
{
get
{
return ViewState["dec"]==null?String.Empty:ViewState["dec"] as SortDirection
}
set
{
ViewState["dec"]=value;
}
}
Then you use your selecting statment like this
var result = (from e in XDocument.Load("xxx.xml").Descedants("ticket")
select new MyModel
{
Subject= e.Element("subject").Value,
Description = e.Element("description").Value,
NiceId = e.Element("nice-id").Value,
DueDate = e.Element("due-date").Value
}).ToList();
1）In the Page_Load event in the "if(!IsPostBack)｛……｝", plz save the class list into "ViewState["dt"]".
2）In the PageIndexChanging event, plz use this:
DataGridView.PageIndex = e.NewPageIndex;
DataGridView.DataSource = ViewState["dt"];
DataGridView.DataBind();
3）In the Sorting event ：
if(e.SortExpression!=SortColumn)
{
SortDirect = SortDirection.Asceding;
SortColumn = e.SortExpression;
}
else
{
if(SortDirect == SortDirection.Asceding)
{
SortDirect = SortDirection.Desceding;
}
else
{
SortDirect = SortDirection.Asceding
}
}
List<MyModel> l = (List<MyModel>)ViewState["dt"];
if(SortDirect == SortDirection.Asceding)
{
ViewState["dt"] = l.OrderBy(m => m.GetType().GetProperty(SortColumn).GetValue(m,null));
}
else
{
ViewState["dt"] = l.OrderByDescending(m => m.GetType().GetProperty(SortColumn).GetValue(m,null));
}
DataGridView1.DataSource = ViewState["dt"];
DataGridView1.DataBind();
To your 2nd question——
You should bind the "eNiceId" to the <a href='<%#Eval("eNiceId")%>'>Your LinK Text Here</a>