Form Flickr into a Gridview/Repeater with C#

Yesterday I had to create a way to custom link pictures from Flickr to a website. Here is a simple way to do it using an RSS feed.

First start by getting the RSS feed that you want to use. Pull up Flickr and open up the Photostream or set that you would like to use. Near the bottom of the page you will find the link to the RSS.

I grabbed a random rss for this example:

http://api.flickr.com/services/feeds/photos_public.gne?id=13588395@N00&lang=en-us&format=rss_200

Trim the url to take off the querystring data after the id.
http://api.flickr.com/services/feeds/photos_public.gne?id=13588395@N00

---

Now onto the code

In the code behind, add this code:


public DataTable getFlickrRSSFeed(string url){
XmlTextReader reader = new XmlTextReader(url + "&format=cdf");
DataSet ds = new DataSet();
ds.ReadXml(reader);
return ds.Tables[4];
}


This will take the passed rss url and request it from flickr in the cdf (common data format) format. (yes I understand that reads redundant)

You can use the other formats if you wish, but cdf was the most friendly format to be returned as a DataSet.

I used table 4, since in cdf format it held the most relevant information for what I was trying to achive (the image, title, date and description).

Lets move on to the next part of code:

protected void Page_Load(object sender, EventArgs e)
{
myFlickr.DataSource = getFlickrRSSFeed("http://api.flickr.com/services/feeds/photos_public.gne?id=13588395@N00");
myFlickr.DataBind();
}

myFlickr will be the id of the dataview or repeater (whichever you decide to use)

That is all the code that is necessary in the code behind, the rest we will do in the aspx page.

In my aspx page, I decided to use a repeater to achieve the format I wanted.

<asp:Repeater ID="myFlickr" runat="server">
<ItemTemplate>
<div class="flickrPhoto">
<div class="image">
<%# Eval("ABSTRACT").ToString().Substring(Eval("ABSTRACT").ToString().IndexOf("<a href=\"http://www.flickr.com/photos/"), (Eval("ABSTRACT").ToString().LastIndexOf("/></a>") + 6) - Eval("ABSTRACT").ToString().LastIndexOf("<a href=\"http://www.flickr.com/photos/"))%>
</div>
<div class="info">
<p class="title">
<%# Eval("TITLE") %>
<%# Convert.ToDateTime(Eval("LASTMOD")).ToString("MM/dd/yyyy") %>
</p>
<p class="desc">
<%# Eval("ABSTRACT").ToString().Substring(Eval("ABSTRACT").ToString().LastIndexOf("<p>")+3,Eval("ABSTRACT").ToString().LastIndexOf("</p>") - (Eval("ABSTRACT").ToString().LastIndexOf("<p>")+3))%>
</p>
</div>
</div>
</ItemTemplate>
</asp:Repeater>


As you may notice, getting the image, title and description alone, takes a little string manipulation. After going through all of flickr's rss formats, I noticed that none of them separated these elements out into their own rss elements, so I had to identify parts of the string that always matched up before the elements I wanted.

For the description, if it is not present it will display the image and it's link. For handling this I just threw in some css that hid any images in the desc field. You could go more complex and filter this out, but for now this was a job I was trying to keep as light as possible.

Hope this helps make a similar task easier for someone out there.

Share on Facebook Tweet Me

Multiple SQL data rows to one row in ASP.Net GridView

Yesterday I came across one of those moments where you know something can be coded the way you want, but for the life of you, you can't think of how to do it or find how to do it anywhere.

The concept is this:

You have a table similar to this

Date	Group	Value
01/01/10	A	1
01/01/10	B	5
01/01/10	C	3
01/01/10	D	12
01/01/10	E	1

After running it through a gridview you want it to display like this:

Date	A	B	C	D	E
01/01/10	1	5	3	12	1

To achieve this the SQL statement is the key to making life easy. Here is a sample of what you can do.


<asp:SqlDataSource ID="connection1" runat="server"
ConnectionString="<%$ ConnectionStrings:Connection1%>"
SelectCommand="SELECT date,
SUM(CASE group WHEN 'A' Then Value END) As ValueA,
SUM(CASE group WHEN 'B' Then Value END) As ValueB,
SUM(CASE group WHEN 'C' Then Value END) As ValueC,
SUM(CASE group WHEN 'D' Then Value END) As ValueD,
SUM(CASE group WHEN 'E' Then Value END) As ValueE
From Table
Group by date
Order by date desc">


Now with the grid view you can just call your alias for each value:

<asp:GridView ID="GridView1" runat="server" DataSourceID="connection1" AutoGenerateColumns="false" AlternatingRowStyle-CssClass="even" RowStyle-CssClass="odd" AllowPaging="true" PageSize="5" GridLines="None">
<Columns>
<asp:BoundField DataField="date" DataFormatString="{0:d}" HeaderText="Date" />
<asp:BoundField DataField="ValueA" HeaderText="A"/>
<asp:BoundField DataField="ValueB" HeaderText="B"/>
<asp:BoundField DataField="ValueC" HeaderText="C"/>
<asp:BoundField DataField="ValueD" HeaderText="D"/>
<asp:BoundField DataField="ValueE" HeaderText="E"/>
</Columns>
</asp:GridView>



Good luck with your coding.

Share on Facebook Tweet Me

Converting SQL into an XML Document using C#

After seeing poor implementation of this I decided it would be a good idea to make a simple guide available.

First we will start by declaring the xmlDocument and what will be its root element

XmlDocument xmlDoc = new XmlDocument();
XmlElement root = xmlDoc.CreateElement("root");


Next we will jump over to the SQL command that will be used to get your data. Obviously this query below is just an example, make sure yours is relevant.

string sqlQuery = "SELECT col1, col2, col3 From myTable Where col3 <> ''";


Declare your SqlConnection. I suggest using your web.config to set this up. Technically you can do this multiple ways, but here are a couple.

1.
In web.config:

<appSettings>
<add key="sqlConnection" value="Data Source=192.168.0.1;Initial Catalog=myDB;User ID=myUser;Password=myPassword;"/>
</appSettings>

Then use the key to build the connection.

SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["sqlConnection"]);


Or option 2, which is considered to be more proper:
In web.config:

<connectionStrings>
<add name="sqlConnection" connectionString="server=192.168.0.1;uid=myUser;pwd=myPassword;database=myDB" providerName="System.Data.SqlClient" />
</connectionStrings>

Then using the connection string to build the connection:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConnection"].ConnectionString);


Honestly, I'm currently using option 1 for the application that brought this to mind. Mostly because I'm keeping my new connections in the same format used by the prior developer, while I work on more important changes.

After you declare your connection, start up a try/catch block, since we are going to open up the connection. Always a good thing to be safe around connections.

try{
conn.Open();


Once these are declared we are going to
Now it is time to start reading the sql data and giving the value to the nodes and attribute


SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(sqlQuery, conn);
myReader = myCommand.ExecuteReader();
while(myReader.Read())
{


Now we want to declare the elements to be used in the xml. For the example I am going to do 1 row node with a child col node that has an attribute.


XmlNode row= xmlDoc.CreateElement("row");
XmlNode col1 = xmlDoc.CreateElement("col");
XmlAttribute col2 = xmlDoc.CreateElement("attribute");


After the nodes are declared we can assign the values


//attribute value assignment
col2.value = myReader["col2"].ToString();
//node value assignment
col1.InnerText = myReader["col1"].ToString();


Then once you have your nodes containing the proper values, you can append them

//Append the Attribute to col1
col1.Attributes.Append(col2);
//Append col1 to the row
row.AppendChild(col1);
//Append the row to the root
root.AppendChild(row);


Now we can close up the while loop and try/catch

}
}catch(Exception ex){
//do something if you want
Response.Write(ex.ToString());
}


Then we can append the root to the xml document

xmlDoc.AppendChild(root);


Now you sucessfully have an xml document with values from the sql table. At this point you can use the document or save it using xmlDoc.save(path);
Here is the resulting format of your xml:

<root>
<row>
<col attribute='Col2 data'>Col1 data</col>
</row>
</root>

Full Code:

using System;
using System.Xml;
using System.Data.SqlClient;

public class SqlToXml
{
public XmlDocument getSqlToXml{
XmlDocument xmlDoc = new XmlDocument();
XmlElement root = xmlDoc.CreateElement("root");

string sqlQuery = "SELECT col1, col2, col3 From myTable Where col3 <> ''";

SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["sqlConnection"]);

try{
conn.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(sqlQuery, conn);
myReader = myCommand.ExecuteReader();
while(myReader.Read())
{
XmlNode row= xmlDoc.CreateElement("row");
XmlNode col1 = xmlDoc.CreateElement("col");
XmlAttribute col2 = xmlDoc.CreateElement("attribute");

col2.value = myReader["col2"].ToString();
col1.InnerText = myReader["col1"].ToString();

col1.Attributes.Append(col2);
row.AppendChild(col1);
root.AppendChild(row);
}
}catch(Exception ex){
//do something if you want
Response.Write(ex.ToString());
}

xmlDoc.AppendChild(root);

return xmlDoc;
}
}


Share on Facebook Tweet Me