get value of xml file in database bound gridview

Question

Hi I have a database with a column (coursecode) that is taken from a master xmlfile. Alongside coursecode the database holds store some other data such as start date, end date, etc. I need to update this data regularly so I use a gridview that is bound to the database. The problem is that the course name is only in the xml file. Is there a way I can link the database and the xml file (as it is done with tables in relational databases) to render this data in the gridview. I had a go but the resulting page is very very slow so I assume it can't be done that way. can this be done?

courses.aspx

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ myconn %>" 
    SelectCommand="SELECT CourseCode, Data1, Data2, CourseType FROM Courses ORDER BY CourseCode"> 
     </asp:SqlDataSource>

      <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
    DataKeyNames="CourseCode" DataSourceID="SqlDataSource1">
    <Columns>
         <asp:TemplateField HeaderText="CourseCode" SortExpression="CourseCode">

            <ItemTemplate>
                <asp:Label ID="Label4" runat="server" Text='<%# Bind("CourseCode") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>

            <asp:TemplateField HeaderText="Name">
         <ItemTemplate>
         <asp:Label id="lblDate" runat="server"></asp:Label>
         </ItemTemplate>
         </asp:TemplateField>

         restofgridviewhere

        </Columns>
       </asp:GridView>

courses.aspx.vb

       Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
    For i As Integer = 0 To GridView1.Rows.Count - 1
        'create new label with the contents of lblDate 
        Dim labelDate As Label = DirectCast(GridView1.Rows(i).FindControl("lblDate"), Label)
        Dim Label4 As Label = DirectCast(GridView1.Rows(i).FindControl("Label4"), Label)

       For i As Integer = 0 To GridView1.Rows.Count - 1
       Dim labelDate As Label = DirectCast(GridView1.Rows(i).FindControl("lblDate"), Label)
        Dim Label4 As Label = DirectCast(GridView1.Rows(i).FindControl("Label4"), Label)

        Dim ds As New DataSet()
        ds.ReadXml("file.xml")
        Dim xmlDoc As New XmlDataDocument(ds)

        Dim nodeList As XmlNodeList = xmlDoc.DocumentElement.SelectNodes("courses/course[code = '" & Label4.Text & "']")

        Dim myRow As DataRow
        For Each myNode As XmlNode In nodeList
            myRow = xmlDoc.GetRowFromElement(DirectCast(myNode, XmlElement))
            If myRow IsNot Nothing Then
                labelDate.Text = myRow("name")
            End If
        Next
       Next
      End Sub

file.xml

 <courses>
   <course>
      <name>Course name</name> 
      <code>Course code</code> 
   </course>
   <courses>

Answer 1

A good approach will be to parse your XML into a Dictionary<string,string> where code is the key and name is the value. Now you can write a small GridView helper function to fetch the CourseName using CourseCode.

Here is a working example.

The Markup.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ApplicationServices %>"
    SelectCommand="SELECT CourseCode, Data1, Data2, CourseType FROM Courses ORDER BY CourseCode">
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CourseCode"
    DataSourceID="SqlDataSource1">
    <Columns>
        <asp:TemplateField HeaderText="Course Code" SortExpression="CourseCode">
            <ItemTemplate>
                <asp:Label ID="CourseCodeLabel" runat="server" Text='<%# Eval("CourseCode") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Course Name">
            <ItemTemplate>
                <asp:Label ID="CourseNameLabel" runat="server" Text='<%# GetCourseNameFromCode(Eval("CourseCode")) %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <%--restofgridviewhere--%>
    </Columns>
</asp:GridView>

The Code-Behind

Private Shared courseMapping As New Dictionary(Of String, String)()

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        'uri is the path to the xml
        Dim uri As String = System.IO.Path.Combine(Server.MapPath("."), "App_Data/CourseXML.xml")
        Dim courses As XElement = XElement.Load(uri)
        For Each course As XElement In courses.Elements()
            courseMapping.Add(course.Element("code").Value, course.Element("name").Value)
        Next
    End If

End Sub

Protected Function GetCourseNameFromCode(courseCode As Object) As String
    Return courseMapping(courseCode.ToString())
End Function

Might have to Import System.Xml.Linq also.

Hope this helps.