How do I get my query results to display in my web part?

Question

I'm working on my very first web part for SharePoint 2010 in Visual Studio 2010. Please keep that in mind as you continue reading.

I'm trying to build a web part that runs a simple query against a SQL database (not my SharePoint db) and displays the results when the user hits a refresh button. The results will always be one column of data, and between 0 and 6 rows.

I've tried using a DataList, a GridView, and a ListView and setting their DataSource to the DataTable I fill with the query results, but I can never get the object to populate any data when I test it.

Here's my .ascx.cs file:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

namespace ActiveMoxyUsers.AMUWebPart
{
    public partial class AMUWebPartUserControl : UserControl
    {
        public DataTable UserList = null;
        public SqlConnection MoxyConn = new SqlConnection(@"server=***;database=***;user id=***;password=***");

        protected void Page_Load(object sender, EventArgs e)
        {
            UserList = new DataTable();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            UpdateList();
        }

        protected void UpdateList()
        {
            if (UserList != null)
                UserList.Clear();
            using (SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT DISTINCT CASE WHEN login_name LIKE '%\%' THEN UPPER(REPLACE(login_name, 'WSDOMAIN\', '')) ELSE UPPER(login_name) END AS UserID FROM sys.dm_exec_sessions WHERE program_name='Moxy';", MoxyConn))
            {
                adapter.Fill(UserList);
                DataList1.DataSource = UserList;
                Label1.Text = UserList.Rows[0][0].ToString();
            }
        }
    }
}

And here's my .ascx file:

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>
<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> 
<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> 
<%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>
<%@ Import Namespace="Microsoft.SharePoint" %> 
<%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="AMUWebPartUserControl.ascx.cs" Inherits="ActiveMoxyUsers.AMUWebPart.AMUWebPartUserControl" %>
<asp:DataList ID="DataList1" runat="server" CellPadding="4" ForeColor="#333333" 
    RepeatColumns="1" ShowFooter="False" ShowHeader="False">
    <AlternatingItemStyle BackColor="White" ForeColor="#284775" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <ItemStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <SelectedItemStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
</asp:DataList>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<br />
<asp:Button ID="Button1" runat="server" Text="Refresh" 
    onclick="Button1_Click" />

Label1 exists specifically to check whether or not my query is returning any data at all, and it is.

What am I missing? Why won't my DataList display my results?

Answer 1

Right after your DataList1.DataSource = UserList; you need to bind it to the DataList using DataList1.DataBind();

adapter.Fill(UserList);
DataList1.DataSource = UserList;
DataList1.DataBind();
Label1.Text = UserList.Rows[0][0].ToString();