CODEWITHSUNDEEP
vb.netdataset
JoJo
JoJo

Reputation: 4933

How to avoid filling a dataset if just testing for more than 0 records existing?

This function is being called in some cases HUNDREDS of times in some existing code. This existing code is so costly it is not even funny. Do we even need to fill a dataset? Wouldn't it be better just to do maybe an ExecuteNonQuery and test the return value?

Is ther a better way to do it all together?

Private Function GetSomeInfo(ByVal primaryID As Integer) As Boolean
    Dim val As Boolean = False

    Dim CmdReturn As SqlCommand = New SqlCommand
    CmdReturn.CommandText = "uspTheSPROC"
    CmdReturn.CommandType = CommandType.StoredProcedure
    CmdReturn.Parameters.Add(New SqlParameter("@primaryID", primaryID))

    Dim daSQL As SqlDataAdapter = New SqlDataAdapter
    daSQL.SelectCommand = CmdReturn

    Dim dsTheData As DataSet = New DataSet
    Dim ConnMain As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("MainConnection").ConnectionString)
    Try
        CmdReturn.Connection = ConnMain
        CmdReturn.CommandTimeout = 2000
        ConnMain.Open()
        daSQL.Fill(dsTheData, "Data")

        If (dsTheData.Tables(0).Rows.Count > 0) Then
            val = True
        Else
            val = False
        End If
    Catch SQLExcept As SqlException
        Dim lError As Main.ErrorHandlers.ErrorAtServer = New Main.ErrorHandlers.ErrorAtServer()
        lError.ProcessError(SQLExcept, "..." + ".GetContents")
        Return val
    Catch Except As Exception
        Dim lError As Main.ErrorHandlers.ErrorAtServer = New Main.ErrorHandlers.ErrorAtServer()
        lError.ProcessError(Except, "..." + ".GetContents")
        Return val
    Finally
        If ConnMain.State = ConnectionState.Open Then
            ConnMain.Close()
        End If
    End Try

    Return val
End Function

Upvotes: 1

Views: 67

Answers (2)

Bjørn-Roger Kringsjå
Bjørn-Roger Kringsjå

Reputation: 9991

Use the forward-only SqlDataReader and check the HasRows property.

Using reader As SqlDataReader = CmdReturn.ExecuteReader()
    val = reader.HasRows
End Using

Another way is to execute the command scalar.

"The first column of the first row in the result set, or a null reference (Nothing in Visual Basic) if the result set is empty. Returns a maximum of 2033 characters." MSDN

So if the first column in the result set is an int, you can do it like this:

val = (TypeOf CmdReturn.ExecuteScalar() Is Int32)

Upvotes: 2

Daniel
Daniel

Reputation: 13132

In addition to Bjorn's suggestion, you can also change your stored procedure to be a non-scalar like you suggested.

Something like (using SQL Server syntax):

if exists(Select null from [table] where ...) 
  return 1
else
  return 0

If the database hit is expensive enough, you may want to consider adding a wrapper to your procedure that only calls it once for any given primaryID.

A likely better alternative would be to do a single database query where you return all the primaryIDs(possibly filtered somewhat), store those results in a collection like Hashset(of Integer) and then just check if the primaryID was returned.

Upvotes: 2

Related Questions