Returning/displaying multiple rows from SQL database

[pagey]

Hello there,

I have a program that calculates several different Maths functions. There is an SQL database on the back-end which records the name of the user, the name of the math function they use, and the date.

I would like to return and display all of the rows where one particular username is featured. So, if a user has used the system 5 times, 5 rows could be displayed in a datagrid on my form.

I understand that I need to make the SQL connection, use the correct SQL statement, and use an Data Adapter to hold the data and then put it into a dataset. Thing is, I have no idea how to do this!

Don't suppose anyone could shed any light on this?

SQL code I have so far is: (its being hosted by WCF)

Public Function DatabaseSearch(ByVal UserName As String) As Boolean Implements SearchServiceContract.DatabaseSearch
        Dim connection As SqlConnection
        Dim command As SqlCommand
        Dim query As String
        Dim connStringBuild As New SqlConnectionStringBuilder

        Try
            connStringBuild.DataSource = "BUSDEV"
            connStringBuild.InitialCatalog = "Northwind"
            connStringBuild.UserID = "vbasic"
            connStringBuild.Password = "*******"

            query = "SELECT * FROM WCFTrigNP WHERE username = '" & UserName & "'"
            connection = New SqlConnection(connStringBuild.ConnectionString)
            command = New SqlCommand(query, connection)


            connection.Open()
            command.ExecuteNonQuery()

        Catch ex As Exception
            Return False

        End Try

        Return True
    End Function

Thanks guys

 

[ss7thirty]

If you want to do it the fastest possible way use a datareader to populate a datagrid and this will go nice and fast. Datasets I find to be really slow and annoying and I have not yet found a good reason to use one. (Though I am sure there are many)

But you can just use a datarow object in a while loop like this:

Imports System.Data.SqlClient
...
Dim strSql as String = "YOUR QUERY HERE"
Dim strConnection as string = "YOUR CONNECTION STRING"
Dim sqlCon as New SqlConnection(strConnection)
Dim dr as SqlDataReader

Try
sqlCon.Open
Catch Ex as Exception
msgbox(ex.message)
Throw ex
End try
Dim tblData as New YourDataTable
Dim rwData as YourDataRow
Dim cmd as New SqlCommand(strSql, sqlCon)
dr = cmd.ExecuteReader
While dr.read

rwData = tblData.NewRow
rwData.whatever = dr("whatever")
...
...
rwData.something = dr("something")
End While

 

[pagey]

Thanks SS.

Does anybody know how to implement this with a Data Adapter?

 

[cjard]

You mean 2001, .NET 1.0 style? Why would you do that when youre on .NET 3.5?

Read the DW2 link in my sig, note that it is meant for .NET 2, switch to any 3.5 specific articles where they exist. Start with Creating a Simple Data App