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