function used to fill a DataTable

[demausdauth]

This is a pretty basic function used to fill a DataTable with data from a SQL database but could easily be adjusted for different types of databases.

Public Shared Function FillTable(ByVal dtReturn As DataTable, ByVal SelectStatement As String, ByVal ConnectionString As String, Optional ByVal Params() As SqlParameter = Nothing) As Boolean

       
        'set the default of the return value
        Dim IsOk As Boolean = False

        'optional to use this statement
        ' clears the datatable, otherwise it will be appended to
        dtReturn.Clear()

        If (Not String.IsNullOrEmpty(ConnectionString) AndAlso Not String.IsNullOrEmpty(SelectStatement)) Then
            Try

                Using sqlCon As New SqlConnection(ConnectionString)

                    If Not sqlCon.State = ConnectionState.Open Then sqlCon.Open()

                    Using sqlCmd As New SqlCommand(SelectStatement, sqlCon)


                        'check for parameters
                        If Params IsNot Nothing Then
                            sqlCmd.Parameters.AddRange(Params)
                        End If

                        'fill the datatable
                        dtReturn.Load(sqlCmd.ExecuteReader())

                        'set the return value based on whether there are any rows to return
                        IsOk = (dtReturn.Rows.Count > 0)

                    End Using 'end sqlCmd
                End Using 'end sqlCon

            Catch exp As SqlException
                'Error logic

            Catch exp As Exception
                'Error logic

            End Try

        End If

        Return IsOk
    End Function

 

[JohnH]

I split this post from thread http://www.vbdotnetforums.com/windows-forms/43424-showing-sql-result.html because I didn't see any relevance to it.
So now we can discuss your function

This is a pretty basic function used to fill a DataTable with data from a SQL database but could easily be adjusted for different types of databases.

You are merely replicating the code for SqlDataAdapter.Fill here. This code is functionally equivalent:

dtReturn.Clear()

Using da As New SqlClient.SqlDataAdapter(selectStatement, connectionString)
    If params IsNot Nothing Then
        da.SelectCommand.Parameters.AddRange(params)
    End If
    Return da.Fill(dtReturn) > 0
End Using

I'd validate parameter arguments like this:

If dtReturn Is Nothing Then
    Throw New ArgumentException("Value cannot be null.", "dtReturn")
ElseIf String.IsNullOrEmpty(selectStatement) Then
    Throw New ArgumentException("Value cannot be null.", "selectStatement")
ElseIf String.IsNullOrEmpty(connectionString) Then
    Throw New ArgumentException("Value cannot be null.", "connectionString")
End If

 

[cjard]

I always found the one liner:

myTableAdapter.FillByXXX(myDataTable, "XXX")

to be much nicer

 

[Arg81]

I always found the one liner:

myTableAdapter.FillByXXX(myDataTable, "XXX")

to be much nicer

Agree....why are people so anti-GUI. It's there for a reason and it does most of the code for you!

 

[cjard]

They follow old tutorials and cling to the delusion of being "in control" that they feel a wizard robs them of..