Skip to main content
Post Migrated Here from stackoverflow.com (revisions)
Source Link
Slai
  • 232
  • 1
  • 9

It needs more Using statements. For example:

Public Shared Function CheckSecurity(strUserID$, strOperation$, strAppID$) As Boolean
    Try
        Using da As New SqlDataAdapter("dbo.CheckSecurity", ConnString) 
            Dim sc = da.SelectCommand, p = sc.Parameters, dt = New DataTable
            sc.CommandType = CommandType.StoredProcedure
            sc.CommandTimeout = m_iSQLTimeOut

            p.Add("@UserID", SqlDbType.VarChar, 15).Value = strUserID
            p.Add("@AppID", SqlDbType.VarChar, 50).Value = strAppID
            p.Add("@Operation", SqlDbType.VarChar, 50).Value = strOperation

            Return da.Fill(dt) > 0     ' .Fill returns the number of rows successfully added
        End Using           ' da is disposed here even if Exception occurs
    Catch ex As Exception
        Throw New ApplicationException("SECURITY ACCESS ERROR")
    End Try
    Return False
End Function

or

Public Shared Function CheckSecurity(strUserID$, strOperation$, strAppID$) As Boolean
    Try
        Using con = New SqlConnection(ConnString), 
              cmd = New SqlCommand("dbo.CheckSecurity", con)

            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandTimeout = m_iSQLTimeOut

            cmd.Parameters.Add("@UserID", SqlDbType.VarChar, 15).Value = strUserID
            cmd.Parameters.Add("@AppID", SqlDbType.VarChar, 50).Value = strAppID
            cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 50).Value = strOperation

            con.Open()
            Using reader = cmd.ExecuteReader
                Return reader.HasRows
            End Using
        End Using       ' con and cmd are closed and disposed here even if Exception occurs
    Catch ex As Exception
        Throw New ApplicationException("SECURITY ACCESS ERROR")
    End Try
    Return False
End Function

Some other examples http://stackoverflow.com/questions/24023575/how-to-pass-parameters-to-sqldataadapter, http://stackoverflow.com/questions/14566980/c-sharp-data-adapter-parameters