ADO Query Function

[Runtest]

Hi all. I have been trying to work into converting software from ADO to the much needed ADO.NET. I am stuck on something though.

 

    Public Function adoQuery(ByVal sqlQuery As String, ByVal table As String, Optional ByVal ignore As String = "") As DataSet
       Dim SQLConnection As New System.Data.SqlClient.SqlConnection(connectionString)

       Try
           SQLConnection.Open()

           Dim SQLString As String = sqlQuery
           Dim SQLCommand As New SqlClient.SqlCommand(SQLString, SQLConnection)
           Dim SQLAdapter As New SqlDataAdapter
           SQLAdapter.SelectCommand = SQLCommand
           Dim SQLDataset As New DataSet
           SQLAdapter.Fill(SQLDataset, table)
           Dim SQLDataTable As DataTable = SQLDataset.Tables(table)
           SQLConnection.Close()
           SQLConnection.Dispose()

           adoQuery = SQLDataset
       Catch ex As Exception
           MessageBox.Show(ex.Message)
       End Try
   End Function

 

This function works fine. But, I have to specify the table when I query. With adodb I could just:

 

Dim rs as ADODB.Recordset
rs = query("select * from tblCustomers")

blah = rs("lname").Value

 

But now I have to do something like.

 

Dim rs As New DataSet
rs = adoQuery("select * from tblCustomers", "tblCustomers")

blah = rs.Tables("tblCustomers").Rows(i).Item("lname").ToString()

 

This would be fine except there are allot of very complicated queries where specifying a table just will not work. Is there anyway to get this function to return a dataset structured in a similar way without specifying a table?

 

Thanks in advance for any help.

[Runtest]

I figured it out and here is the answer.

 

    Public Function adoQuery(ByVal sqlQuery As String) As DataSet
       Dim SQLConnection As New System.Data.SqlClient.SqlConnection(connectionString)

       Try
           SQLConnection.Open()

           Dim SQLString As String = sqlQuery
           Dim SQLCommand As New SqlClient.SqlCommand(SQLString, SQLConnection)
           Dim SQLAdapter As New SqlDataAdapter

           SQLAdapter.SelectCommand = SQLCommand

           Dim SQLDataset As New DataSet

           SQLAdapter.Fill(SQLDataset)

           SQLConnection.Close()
           SQLConnection.Dispose()

           adoQuery = SQLDataset
       Catch ex As Exception
           MessageBox.Show(ex.Message)
       End Try
   End Function

[orca44]

I'm glad that you found a solution. You should also consider using the SqlDataReader for instances where you don't intend on writing any data back to the database.