Execute Query On DataTable

Executes a SQL SELECT query on the provided SqlConnection and returns the result as a DataTable .

The command timeout for the SELECT query is increased from the default 30 seconds to 120 seconds. This function is designed to support data editing and updating through the attached SqlDataAdapter.

    Public Function ExecuteQueryOnDataTable(MyConnection As SqlConnection, 
                                            SQLQuery As String) As DataTable

        Dim dtToReturn As DataTable = New DataTable()
        Dim daQuery As SqlDataAdapter = New SqlDataAdapter(SQLQuery, MyConnection)

        ' Without SqlCommandBuilder we can't do update!
        Dim cbQuery As SqlCommandBuilder = New SqlCommandBuilder(daQuery)
        dtToReturn.ExtendedProperties("SqlCommandBuilder") = cbQuery

        ' Increment standard .NET timeout from 30sec to 120sec
        If Not daQuery.SelectCommand Is Nothing Then
            daQuery.SelectCommand.CommandTimeout = 120
        End If

        daQuery.Fill(dtToReturn)
        dtToReturn.ExtendedProperties("SqlDataAdapter") = daQuery
        Return dtToReturn

    End Function

    Public Function ExecuteQueryOnDataTable(MyConnection As SqlConnection, 
                                            SQLQuery As String) As DataTable

        Dim dtToReturn As DataTable = New DataTable()
        Dim daQuery As SqlDataAdapter = New SqlDataAdapter(SQLQuery, MyConnection)

        ' Without SqlCommandBuilder we can't do update!
        Dim cbQuery As SqlCommandBuilder = New SqlCommandBuilder(daQuery)
        dtToReturn.ExtendedProperties("SqlCommandBuilder") = cbQuery

        ' Increment standard .NET timeout from 30sec to 120sec
        If Not daQuery.SelectCommand Is Nothing Then
            daQuery.SelectCommand.CommandTimeout = 120
        End If

        daQuery.Fill(dtToReturn)
        dtToReturn.ExtendedProperties("SqlDataAdapter") = daQuery
        Return dtToReturn

    End Function

Posted by ipercube