How to populate a listview in ASP.NET 3.5 through a dataset?

Question

Is it possible to populate a listview with a dataset? I have a function that returns a dataset. Why im asking this is because my SQL is quite complicated and i can't convert it to a SQLDataSource...

Public Function getMessages() As DataSet
    Dim dSet As DataSet = New DataSet
    Dim da As SqlDataAdapter
    Dim cmd As SqlCommand
    Dim SQL As StringBuilder
    Dim connStr As StringBuilder = New StringBuilder("")
    connStr.AppendFormat("server={0};", ConfigurationSettings.AppSettings("USERserver").ToString())
    connStr.AppendFormat("database={0};", ConfigurationSettings.AppSettings("USERdb").ToString())
    connStr.AppendFormat("uid={0};", ConfigurationSettings.AppSettings("USERuid").ToString())
    connStr.AppendFormat("pwd={0};", ConfigurationSettings.AppSettings("USERpwd").ToString())
    Dim conn As SqlConnection = New SqlConnection(connStr.ToString())
    Try
        SQL = New StringBuilder
        cmd = New SqlCommand
        SQL.Append("SELECT m.MESSAGE_ID, m.SYSTEM_ID, m.DATE_CREATED, m.EXPIRE_DATE, ISNULL(s.SYSTEM_DESC,'ALL SYSTEMS') AS SYSTEM_DESC, m.MESSAGE ")
        SQL.Append("FROM MESSAGE m ")
        SQL.Append("LEFT OUTER JOIN [SYSTEM] s ")
        SQL.Append("ON m.SYSTEM_ID = s.SYSTEM_ID ")
        SQL.AppendFormat("WHERE m.SYSTEM_ID IN ({0}) ", sSystems)
        SQL.Append("OR m.SYSTEM_ID is NULL ")
        SQL.Append("ORDER BY m.DATE_CREATED DESC; ")

        SQL.Append("SELECT mm.MESSAGE_ID, mm.MODEL_ID, m.MODEL_DESC ")
        SQL.Append("FROM MESSAGE_MODEL mm ")
        SQL.Append("JOIN MODEL m ")
        SQL.Append("    ON m.MODEL_ID = mm.MODEL_ID ")
        cmd.CommandText = SQL.ToString
        cmd.Connection = conn
        da = New SqlDataAdapter(cmd)
        da.Fill(dSet)
        dSet.Tables(0).TableName = "BASE"
        dSet.Tables(1).TableName = "MODEL"
        Return dSet
    Catch ev As Exception
        cLog.EventLog.logError(ev, cmd)
    Finally
        'conn.Close()
    End Try
End Function

Answer 1

Where to start with this one...

listview.DataSource = getMessages().Tables(0); 
listview.DataBind;

Answer 2

Sure. In the code-behind just set the datasource to the DataTable you want to bind the list to, then call DataBind() on the list:

Protected Sub Page_Load(ByVal sender As object, ByVal e As EventArgs)
    If Not Page.IsPostBack Then
       Me.LoadData()    
    End If
End Sub

Protected Sub LoadData()
    Dim ds As DataSet = Me.GetData()
    Me.lstSample.DataSource = ds("Model")
    Me.lstSample.DataBind()
End Sub

(hopefully that's valid VB.NET code, I normally code in C#).

Answer 3

Is it possible to make the above query in a SQLDataSource?