Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
how can i minimized this code :
cn.open()
 
Dim temp As String
       temp = "pending for client approval"
 
       str = "select Count(*)  from Asset_Service_Master where CaseStatus=  @temp "
       cmd = New SqlCommand(str, cn)
       cmd.Parameters.Add(New SqlParameter("@temp", temp))
 
       tb_PendingForClientApproval.Text = cmd.ExecuteScalar().ToString
 

       Dim tp As String
       tp = "yes"
       Dim tmp As String = ""
 
       str = "select count(*)  from Asset_Service_Master where CallClosed= @tp &  AND BillingComplete=@tmp   "
       cmd = New SqlCommand(str, cn)
 
       cmd.Parameters.Add(New SqlParameter("@tp", tp))
       cmd.Parameters.Add(New SqlParameter("@tmp", tmp))
 
       tb_CalledClosed.Text = cmd.ExecuteScalar().ToString
 
       cn.Close()
Posted 19-Sep-12 3:07am
Edited 19-Sep-12 3:11am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I don't see much more than:
Dim tp As String  = "yes"
You can also remove a couple of superflous linebreaks.
Regards,
 
— Manfred
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

You have two blocks of code very similar, create a method (with proper arguments) and call it twice.
 
BTW you may also discard the SqlParameters, since you are using constants (write the constants directly inside the queries).
  Permalink  
Comments
neldesai at 19-Sep-12 8:36am
   
is passing constant can protected me against sql injections..?Or i should go with parameterized..Thank u.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

First thing there is nothing wrong with what you did.
 
But since you asked Smile | :)
 
First I would create a stored procedure
CREATE PROCEDURE [dbo].[Asset_Service_Master_Get_Counts]
    @CaseStatus VARCHAR(50) = NULL, --You'll need to add the correct types.
    @CallClosed VARCHAR(50) = NULL,
    @BillingComplete VARCHAR(50) = NULL
AS 
    BEGIN
        SELECT  COUNT(*)
        FROM    Asset_Service_Master
        WHERE   CaseStatus = COALESCE(@CaseStatus, Asset_Service_Master.CaseStatus)
                AND CallClosed = COALESCE(@CallClosed,Asset_Service_Master.CallClosed)
                AND BillingComplete = COALESCE(@BillingComplete,
                                               Asset_Service_Master.BillingComplete)
    END
 
GO
 
Here is the method I would use.
    Public Function GetCount(Optional ByVal caseStatus As String = "", Optional ByVal callClosed As String = "", Optional ByVal billingComplete As String = "") As String
        Using cn As New SqlConnection(connectionString)
            cn.Open()
            If cn.State = ConnectionState.Open Then
                Using cmd As New SqlCommand("Asset_Service_Master_Get_Counts", cn)
                    cmd.Parameters.Add(New SqlParameter("@CaseStatus", IIf(caseStatus = "", DBNull.Value, caseStatus)))
                    cmd.Parameters.Add(New SqlParameter("@CallClosed", IIf(callClosed = "", DBNull.Value, callClosed)))
                    cmd.Parameters.Add(New SqlParameter("@BillingComplete", IIf(billingComplete = "", DBNull.Value, billingComplete)))
                    cmd.CommandType = CommandType.StoredProcedure
 
                    Return cmd.ExecuteScalar().ToString
                End Using
            Else
                Return String.Empty
            End If
        End Using
    End Function
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

hii,
 
use this code
 

Public Sub setvalue()
        Dim para(1) As SqlParameter
        para(0) = New SqlParameter("@temp", "pending for client approval")
        tb_PendingForClientApproval.Text = getValue("select Count(*)  from Asset_Service_Master where CaseStatus=  @temp ", para)
 

        Dim para_2(2) As SqlParameter
        para_2(0) = New SqlParameter("@tp", "yes")
        para_2(1) = New SqlParameter("@temp", "")
 
        tb_CalledClosed.Text = getValue("select count(*)  from Asset_Service_Master where CallClosed= @tp   AND BillingComplete=@tmp ", para_2)
 
    End Sub
 
    Public Function getValue(ByVal Query As string,ByVal para() As SqlParameter) As String)
        Try
            cn.open()
            cmd = New SqlCommand(Query, cn)
            cmd.Parameters.AddRange(para)
            Return cmd.ExecuteScalar().ToString
 
        Catch ex As Exception
        Finally
            If (Not cn.state = ConnectionState.Closed) Then
                cn.close()
            End If
        End Try
        Return ""
    End Function
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

may be your Question is not so clear
but
you can try like this also
cn.open()
 Dim tp As String = ""
        Dim tmp As String = "pending for client approval"
        If tmp = "pending for client approval" Then
            Str = "select Count(*)  from Asset_Service_Master where CaseStatus=  @temp "
        Else
            tp = "yes"
            tmp = ""
            Str = "select count(*)  from Asset_Service_Master where CallClosed= @tp &  AND BillingComplete=@tmp   "
        End If
        md = New SqlCommand(Str, cn)
 
        cmd.Parameters.Add(New SqlParameter("@tp", tp))
        cmd.Parameters.Add(New SqlParameter("@tmp", tmp))
 
        tb_CalledClosed.Text = cmd.ExecuteScalar().ToString
 
        cn.Close()
 
Declare All other Variables as per your Requirement
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 310
1 OriginalGriff 285
2 Afzaal Ahmad Zeeshan 200
3 Sergey Alexandrovich Kryukov 195
4 BillWoodruff 180
0 OriginalGriff 6,499
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,193
3 Manas Bhardwaj 4,657
4 Maciej Los 4,120


Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 20 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100