|
|
Rank: Addict
Joined: 2/19/2007
Posts: 634
Location: Belgium
|
Hi, I need to export members ( around 4000) to xml But the way i do it is to slow ( takes around 30 seconds ) THis is the code I use Code:
protected void Page_Load(object sender, EventArgs e)
{
XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, System.Text.Encoding.UTF8);
writer.WriteStartDocument();
writer.WriteStartElement("members");
foreach (Member tempMember in Member.GetAll)
{
writer.WriteStartElement("member");
writer.WriteAttributeString("Vendeur", tempMember.getProperty("vendeur").Value.ToString());
writer.WriteAttributeString("Courtier", tempMember.getProperty("courtier").Value.ToString());
writer.WriteAttributeString("Photo", tempMember.getProperty("memberphoto").Value.ToString());
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndDocument();
writer.Close();
}
So how can i make this faster
Umbraco tips and tricks: http://www.nibble.be - umbraco mvp 08/09 - certified level 1 & 2 professional
|
|
Rank: Fanatic
Joined: 7/20/2006
Posts: 407
Location: Amsterdam
|
I think 4000 members in 30 seconds is not too slow to be honest......
|
|
Rank: Fanatic
Joined: 7/20/2006
Posts: 407
Location: Amsterdam
|
I think 4000 members in 30 seconds is not too slow to be honest......
|
|
Rank: Addict
Joined: 2/19/2007
Posts: 634
Location: Belgium
|
It can be much faster, instead of using the umbraco api to generate the xml, i made a sql select statement, it fires in less then a second. ( because the api does multpiple statements this is very slow )
Umbraco tips and tricks: http://www.nibble.be - umbraco mvp 08/09 - certified level 1 & 2 professional
|
|
Rank: Devotee
Joined: 7/20/2006
Posts: 86
|
Hi Tim,
Would you mind posting the code/query?
Thx, Len.
|
|
Rank: Addict
Joined: 2/19/2007
Posts: 634
Location: Belgium
|
Well the way i did it was this : For each property of the member i created a view Like this one, where i select the values of the property with alias 'courtier' Code:
SELECT member.nodeId, data.dataNvarchar AS courtier
FROM locaction.cmsMember AS member INNER JOIN
locaction.cmsPropertyData AS data ON member.nodeId = data.contentNodeId
WHERE (data.propertytypeid =
(SELECT id
FROM locaction.cmsPropertyType
WHERE (Alias = 'courtier')))
And then i made another view that combined al the other views :p Code:
SELECT locaction.cmsMember.nodeId, locaction.viewVendeur.vendeur, locaction.viewGroupe.groupe, locaction.viewCourtier.courtier,
locaction.viewNomDuCourtier.nomducourtier, locaction.viewActive.active, locaction.viewImage.image
FROM locaction.cmsMember INNER JOIN
locaction.viewActive ON locaction.cmsMember.nodeId = locaction.viewActive.nodeId LEFT OUTER JOIN
locaction.viewVendeur ON locaction.cmsMember.nodeId = locaction.viewVendeur.nodeId LEFT OUTER JOIN
locaction.viewGroupe ON locaction.cmsMember.nodeId = locaction.viewGroupe.nodeId LEFT OUTER JOIN
locaction.viewCourtier ON locaction.cmsMember.nodeId = locaction.viewCourtier.nodeId LEFT OUTER JOIN
locaction.viewNomDuCourtier ON locaction.cmsMember.nodeId = locaction.viewNomDuCourtier.nodeId LEFT OUTER JOIN
locaction.viewImage ON locaction.cmsMember.nodeId = locaction.viewImage.nodeId
Umbraco tips and tricks: http://www.nibble.be - umbraco mvp 08/09 - certified level 1 & 2 professional
|
|
Rank: Addict
Joined: 2/19/2007
Posts: 634
Location: Belgium
|
The C# code I used to ouput the xml : Code:
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["umbracoDbDSN"].ToString());
string selectMembers = "select * from viewMembers";
SqlCommand selectCommand = new SqlCommand(selectMembers, conn);
DataTable memberExport = new DataTable("Participants");
conn.Open();
memberExport.Load(selectCommand.ExecuteReader());
conn.Close();
Response.ContentType = "text/xml";
Response.Write("<?xml version='1.0'?>");
memberExport.WriteXml(Response.OutputStream, XmlWriteMode.IgnoreSchema);
Umbraco tips and tricks: http://www.nibble.be - umbraco mvp 08/09 - certified level 1 & 2 professional
|
|
Rank: Devotee
Joined: 7/20/2006
Posts: 86
|
Thx, that is great.
Len
|
|
Rank: Addict
Joined: 7/19/2006
Posts: 580
Location: Preston, UK
|
Tim,
Many thanks for this i was going to use Member library from Per. He uses the api also and warned me it can get a bit slow if too many members.
Ive said this before in another post that the life and death of an opensource project is community participation. Its great to see plenty of people willing to get stuck in and help each other out :D
Ismail
Level 2 certified. If it aint broke dont fix.
|
|
Rank: Addict
Joined: 2/19/2007
Posts: 634
Location: Belgium
|
Hey Ismail, Glad i could be of some help. When I have the time i will try to make a usercontrol that generates the statement automaticly ( just choose the membertype and the propertys ) Because know it's a lot of copy / pasting. Greets
Umbraco tips and tricks: http://www.nibble.be - umbraco mvp 08/09 - certified level 1 & 2 professional
|
|
Rank: Umbracoholic
Joined: 7/20/2006
Posts: 1,066
Location: Charleston, West Virginia, United States
|
I was messing around with SQL today, and thought I would give this a look. Not sure how well it will handle a large number of users, but it is still worth a try. I will play further to see if I can get some group listings into the member as well.. Of course, you will still have to write the extension to utilize this and load the xml properly.. Code:
SELECT 1 as Tag, null as Parent,
umbracoNode.[id] AS [node!1!id],
cmsContentVersion.[versionId] AS [node!1!version],
umbracoNode.[parentID] AS [node!1!parentID],
umbracoNode.[level] AS [node!1!level],
umbracoNode.[nodeUser] AS [node!1!writerID],
cmsContent.[contentType] AS [node!1!nodeType],
0 AS [node!1!template],
umbracoNode.[sortOrder] AS [node!1!sortOrder],
umbracoNode.[createDate] AS [node!1!createDate],
cmsContentVersion.[versionDate] AS [node!1!updateDate],
umbracoNode.[text] AS [node!1!nodeName],
'' AS [node!1!urlName],
umbracoUser.[userName] AS [node!1!writerName],
cmsContentType.[alias] AS [node!1!nodeTypeAlias],
umbracoNode.[path] AS [node!1!path],
cmsMember.[loginName]AS [node!1!loginName],
cmsMember.[email]AS [node!1!email],
null AS [data!2!id],
null AS [data!2!versionID],
null AS [data!2!alias],
null AS [data!2]
FROM umbracoNode
inner join cmsMember on umbracoNode.[id] = cmsMember.nodeId
inner join cmsContent on umbracoNode.[id] = cmsContent.nodeId
inner join cmsContentType on cmsContent.contentType = cmsContentType.nodeId
inner join cmsContentVersion on umbracoNode.[id] = cmsContentVersion.contentId
inner join umbracoUser on umbracoNode.nodeUser = umbracoUser.[id]
WHERE umbracoNode.nodeObjectType = '{39EB0F98-B348-42A1-8662-E7EB18487560}'
and cmsContentVersion.versionDate = (select Max(v1.versionDate) from cmsContentVersion v1 where v1.ContentId = cmsContentVersion.ContentId)
Union All
SELECT 2 as Tag, 1 as Parent,
cmsPropertyData.contentNodeId as [node!1!id],
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
cmsPropertyData.[id],
cmsContentVersion.versionId,
cmsPropertyType.Alias,
isnull(Convert(Nvarchar(4000),dataInt),
isnull(Convert(Nvarchar(4000),dataDate),
isnull(Convert(Nvarchar(4000),dataNvarchar),
isnull(Convert(Nvarchar(4000),dataNtext),''))))
FROM umbracoNode
inner join cmsPropertyData on umbracoNode.[id] = cmsPropertyData.contentNodeId
inner join cmsPropertyType on cmsPropertyData.propertytypeid = cmsPropertyType.[id]
inner join cmsContentVersion on umbracoNode.[id] = cmsContentVersion.contentId
WHERE umbracoNode.nodeObjectType = '{39EB0F98-B348-42A1-8662-E7EB18487560}'
and cmsContentVersion.versionDate = (select Max(v1.versionDate) from cmsContentVersion v1 where v1.ContentId = cmsContentVersion.ContentId)
ORDER BY [node!1!id], [data!2!id]
FOR XML Explicit
• 2007/2008 MVP • 2008/2009 MVP • Core Developer • Certified Professional Level I & II •
|
|
Rank: Umbracoholic
Joined: 7/20/2006
Posts: 1,066
Location: Charleston, West Virginia, United States
|
With groups added in the format /node/group; Pretty cool.. Code:
SELECT 1 as Tag, null as Parent,
umbracoNode.[id] AS [node!1!id],
cmsContentVersion.[versionId] AS [node!1!version],
umbracoNode.[parentID] AS [node!1!parentID],
umbracoNode.[level] AS [node!1!level],
umbracoNode.[nodeUser] AS [node!1!writerID],
cmsContent.[contentType] AS [node!1!nodeType],
0 AS [node!1!template],
umbracoNode.[sortOrder] AS [node!1!sortOrder],
umbracoNode.[createDate] AS [node!1!createDate],
cmsContentVersion.[versionDate] AS [node!1!updateDate],
umbracoNode.[text] AS [node!1!nodeName],
'' AS [node!1!urlName],
umbracoUser.[userName] AS [node!1!writerName],
cmsContentType.[alias] AS [node!1!nodeTypeAlias],
umbracoNode.[path] AS [node!1!path],
cmsMember.[loginName]AS [node!1!loginName],
cmsMember.[email]AS [node!1!email],
null AS [data!2!versionID],
null AS [data!2!alias],
null AS [data!2],
null AS [group!3],
null AS [group!3!id]
FROM umbracoNode
inner join cmsMember on umbracoNode.[id] = cmsMember.nodeId
inner join cmsContent on umbracoNode.[id] = cmsContent.nodeId
inner join cmsContentType on cmsContent.contentType = cmsContentType.nodeId
inner join cmsContentVersion on umbracoNode.[id] = cmsContentVersion.contentId
inner join umbracoUser on umbracoNode.nodeUser = umbracoUser.[id]
WHERE umbracoNode.nodeObjectType = '{39EB0F98-B348-42A1-8662-E7EB18487560}'
and cmsContentVersion.versionDate = (select Max(v1.versionDate) from cmsContentVersion v1 where v1.ContentId = cmsContentVersion.ContentId)
Union All
SELECT 2 as Tag, 1 as Parent,
cmsPropertyData.contentNodeId,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
cmsContentVersion.versionId,
cmsPropertyType.Alias,
isnull(Convert(Nvarchar(4000),dataInt),
isnull(Convert(Nvarchar(4000),dataDate),
isnull(Convert(Nvarchar(4000),dataNvarchar),
isnull(Convert(Nvarchar(4000),dataNtext),'')))),
null,
null
FROM umbracoNode
inner join cmsPropertyData on umbracoNode.[id] = cmsPropertyData.contentNodeId
inner join cmsPropertyType on cmsPropertyData.propertytypeid = cmsPropertyType.[id]
inner join cmsContentVersion on umbracoNode.[id] = cmsContentVersion.contentId
WHERE umbracoNode.nodeObjectType = '{39EB0F98-B348-42A1-8662-E7EB18487560}'
and cmsContentVersion.versionDate = (select Max(v1.versionDate) from cmsContentVersion v1 where v1.ContentId = cmsContentVersion.ContentId)
UNION ALL
SELECT 3 AS Tag, 1 AS Parent,
umbracoNode.[id],
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
umbracoMemberGroup.[text],
umbracoMemberGroup.[id]
From umbracoNode
inner join cmsMember2MemberGroup on umbracoNode.[id] = cmsMember2MemberGroup.Member
inner join umbracoNode as umbracoMemberGroup on cmsMember2MemberGroup.MemberGroup = umbracoMemberGroup.[id]
WHERE
umbracoNode.nodeObjectType = '{39EB0F98-B348-42A1-8662-E7EB18487560}'
ORDER BY [node!1!id], Tag, Parent
FOR XML Explicit
• 2007/2008 MVP • 2008/2009 MVP • Core Developer • Certified Professional Level I & II •
|
|
Rank: Devotee
Joined: 3/31/2007
Posts: 64
Location: Switzerland
|
|
|
Rank: Addict
Joined: 7/19/2006
Posts: 580
Location: Preston, UK
|
Casey, Im in the process of writing a member exporter dashboard control. I have used your xml query i then load the xml into xpath doc then spit out csv using FileHelpers. I have to do this because filehelpers prefers straight sql then it can outout csv. I tried to update the query so that instead of xml i get rows of data for each member with groups as csv list in the column. Unfortunately my sql skills are a bit pants. Any chance of rewriting the query to not output xml? ;) Also i noticed that prevalues come out as ids. Im hoping once i get it all working to release out to the community. Although I have seen a workitem in codeplex think it was jesper who created it for member export. Many thanks in advance. Ismail
Level 2 certified. If it aint broke dont fix.
|
|
Rank: Umbracoholic
Joined: 7/20/2006
Posts: 1,066
Location: Charleston, West Virginia, United States
|
Hi Ismail, The above SQL call tends to have occasional issues (byte count for many members, and other errors have been reported), and may not be the best route. Another route is to pull the cmsContentXml.xml field for all members (inner joing cmsMember), and load the pre-generated xml that way. The only catch here is that the member Groups are not present. Below is a quick snippet to pull the members into an xml doc. Not sure if this is the most efficient manner, but, I got frustrated with the .NET xml objects. I am planning to release an xslt extension library (which can be used in .net controls as well) with this and other methods to allow for faster access to members and media, plus much more. Code:
XmlDocument xd = new XmlDocument();
DataSet ds = SqlHelper.ExecuteDataset(
new SqlConnection(GlobalSettings.DbDSN),
CommandType.Text,
@"
Select ccx.xml from cmsContentXml ccx inner join cmsMember cm on ccx.nodeId = cm.nodeId
");
StringBuilder sb = new StringBuilder();
sb.AppendLine("<root count=\"" + ds.Tables[0].Rows.Count + "\">");
foreach (DataRow dr in ds.Tables[0].Rows)
{
sb.AppendLine(dr["xml"].ToString());
}
sb.AppendLine("</root>");
xd.LoadXml(sb.ToString());
return xd.CreateNavigator().Select("/root");
Case
• 2007/2008 MVP • 2008/2009 MVP • Core Developer • Certified Professional Level I & II •
|
|
Rank: Umbracoholic
Joined: 7/20/2006
Posts: 1,066
Location: Charleston, West Virginia, United States
|
I may have a means to pull the groups in as well. I will play and let you know.
• 2007/2008 MVP • 2008/2009 MVP • Core Developer • Certified Professional Level I & II •
|
|
Rank: Umbracoholic
Joined: 7/20/2006
Posts: 1,066
Location: Charleston, West Virginia, United States
|
Re-Reading that.. The problem with the above query, and sql in general, is the dynamic nature of umbraco in this case. You don't know how many properties there are. Thus, you end up with a multiple records per member to cover every field. You can remove the For XML at the bottom, and it will return straight records, but it will not be close to what you are after. With SQL 2005, you can use Pivot Tables to rotate the properties into columns, but I have not tried this as of yet. This would probably be the approach you would want to take... Hope this helps..
• 2007/2008 MVP • 2008/2009 MVP • Core Developer • Certified Professional Level I & II •
|
|
Rank: Addict
Joined: 7/19/2006
Posts: 580
Location: Preston, UK
|
Casey,
Thanks for the help. I did take out the xml bit but like you say not quite there. I am going to take a look at pivot tables see if i can figure it.
I did do something like this ages on another CMS ago using temp tables and cursors :no: it was messy but worked.
Regards
Ismail
Level 2 certified. If it aint broke dont fix.
|
|
Rank: Devotee
Joined: 3/31/2007
Posts: 64
Location: Switzerland
|
Casey Neehouse wrote:
The problem with the above query, and sql in general, is the dynamic nature of umbraco in this case. You don't know how many properties there are. Thus, you end up with a multiple records per member to cover every field.
I have written some SQL that gets around this issue and gets all property data no matter how many properties the install has. If you put the SQL is a view you get an output of the following colls: MemberType - Member type text nodeId - member node id MemberField - property field name MemberData - property data (note this will look up custom data type descriptions so you dont just get an int for an option box but what that option is) You can restrict this using another query for preformance. See http://forum.umbraco.org/18242
|
|
|
Guest |