none
IF >>else ..else condition doesnot work

    Question

  • hi all

    what is incorrect in my below code

    why does my else statement does not work

    #######################################get mdf files ###############################################
      
      write-host "`n"
     $Drive = Read-Host -Prompt 'Please input the Drive letter  (EG:I,J,D)'
     write-host "`n"
    
    
    $connection= new-object system.data.sqlclient.sqlconnection #Set new object to connect to sql database 
    $DBserver = $row[0]
    $DBName = "Master"
        $connection.ConnectionString ="server=$DBserver;database=$DBName;trusted_connection=True" # Connectiongstring setting for local machine database with window authentication  
        $connection.open() #Connecting successful  
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand #setting object to use sql commands
    
    $SqlQuery2 = "
    create table #db_files(
    db_files varchar(300),
    file_loc varchar(300),
    filesizeMB decimal(9,2),
    spaceUsedMB decimal(9,2),
    FreespaceMB decimal(9,2));
    
    declare @strSQL nvarchar(2000);
    declare @exec nvarchar(2000);
    select @strSQL = 
        'INSERT INTO #db_files
            select
          name
        , filename
        , convert(float,round(a.size/128.000,2)) as FileSizeMB
        , convert(float,round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
        , convert(float,round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
        from dbo.sysfiles a
        ';
    
    DECLARE @dbName varchar(MAX);
    DECLARE @getDBname CURSOR;
    
    SET @getDBname = CURSOR FOR
    select name from sys.databases;
    
    OPEN @getDBname;
    FETCH NEXT
    FROM @getDBname INTO @dbName
    WHILE @@FETCH_STATUS = 0
    BEGIN;
    PRINT @dbName;
        set @exec = quotename(@dbName) + '..sp_executeSQL';
        exec @exec @strSQL
    
    FETCH NEXT
    FROM @getDBname INTO @dbName;
    
    END;
    CLOSE @getDBname;
    DEALLOCATE @getDBname;
    
    select top 10 A.* from (
    SELECT ROW_NUMBER() 
            OVER (order by filesizeMB desc) AS Row,
    db_files,
    file_loc,
    SUBSTRING(file_loc,0,2) Drive_letter,
    SUBSTRING(file_loc,LEN(file_loc)-2,4) extension,
    filesizeMB,
    spaceUsedMB,
    FreespaceMB from #db_files where (db_files like '%WSS_Content' or db_files like '%ProjectService')) as A where a.extension like 'mdf%' and a.Drive_letter ='$Drive' ;
    
    
    drop table #db_files
    "
        $SqlCmd.CommandText = $SqlQuery2# get query  
     
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter #  
        $SqlAdapter.SelectCommand = $SqlCmd 
        $SqlCmd.Connection = $connection
        $DataSet1 = New-Object System.Data.DataSet  
        $SqlAdapter.Fill($DataSet1) | Out-Null 
      
       $connection.Close()
       
       Function getMDF ($DataSet1)
       {        
          foreach ($row1 in $DataSet1.Tables[0]) 
              {  
                if ($row1.ItemArray.Count -gt 0) 
                   {
                       
                        New-Object -TypeName PSObject -Property @{
                       Sr_no = $row1[0]
                       Database_name = $row1[1]
                       Drive = $row1[3]
                       File_Type = $row1[4]
                       filesizeMB = $row1[5]
                       spaceUsedMB= $row1[6]
                       FreespaceMB = $row1[7]
                          }
    
    
                      }
           
                Else {
    
    { write-host 'test' }
                    
                    } 
               }
    
             
    
    }
      getMDF ($DataSet1) |select Sr_no,Database_name,Drive,File_Type,filesizeMB,spaceUsedMB,FreespaceMB |Format-Table -AutoSize 


    tanveer

    Friday, February 17, 2017 7:09 PM

All replies

  • You have extra {} { write-host 'test' }

    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful. (99,108,97,121,109,97,110,50,64,110,121,99,97,112,46,114,114,46,99,111,109|%{[char]$_})-join''

    Friday, February 17, 2017 7:29 PM
  • no i tried that already ..does not seem to display the message test

    tanveer

    Friday, February 17, 2017 7:33 PM
  • It does.  Try this:

    Function getMDF ($DataSet1) {
    		if ($false) {
    			
    			New-Object -TypeName PSObject -Property @{
    				Sr_no = $row1[0]
    				Database_name = $row1[1]
    				Drive = $row1[3]
    				File_Type = $row1[4]
    				filesizeMB = $row1[5]
    				spaceUsedMB = $row1[6]
    				FreespaceMB = $row1[7]
    			}
    			
    			
    		} Else {
    			write-host 'test'
    			
    		}
    }
    

    Most of your code is unnecessary.  We don't use a data adapter outside of a form for many reasons.


    \_(ツ)_/

    Friday, February 17, 2017 7:44 PM
    Moderator
  • You really need to remove those curly brackets from "Write-Host" line. Your code is OK, and there are no silly questions...did you verify that ($row1.ItemArray.Count -gt 0) is really $false? Try to replace $row1.ItemArray.Count and run it like this:

    $test = 0
    if ($test -gt 0) 
                   {
                       
                        New-Object -TypeName PSObject -Property @{
                       Sr_no = $row1[0]
                       Database_name = $row1[1]
                       Drive = $row1[3]
                       File_Type = $row1[4]
                       filesizeMB = $row1[5]
                       spaceUsedMB= $row1[6]
                       FreespaceMB = $row1[7]
                          }
    
    
                      }
           
                Else {
    
    write-host 'test'
                    
                    } 
    Just an advice, try to keep brackets in line, it will ease your life when reading the code. Hope it helps.


    Please remember to "Mark as answer" if it helps. Thank You.

    Friday, February 17, 2017 7:53 PM
  • This is how to do a SQL query with PowerShell.  It is much easier once you understand both PowerShell and the Net SQLCLient.

    $sqlTemplate = @'
       ...
        Change $drive to {0} and use the string formatter
    '@
    
    $Drive = Read-Host -Prompt 'Please input the Drive letter  (EG:I,J,D)'
    $DBserver = $row[0] # ?????
    $DBName = "Master"
    $connStr = "server=$DBserver;database=$DBName;trusted_connection=True"
    $conn = new-object system.data.sqlclient.sqlconnection
    $conn.open() 
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $sqlTemplate -f $drive
    
    $dt = New-Object System.Data.DataTable
    $rdr = $cmd.ExecuteReader()
    $rowsAffected = $dt.Fill($rdr)
    $conn.Close()
    if($rowsAffected -gt 0){
    	$dt | select db_files,file_loc,Drive_letter,extension, filesizeMB, spaceUsedMB, FreespaceMB
    }elseif($rowsAffected -lt 0){
    	Write-Host 'An error occured:' $rowsAffected
    }else{
    	Write-Host 'No data'
    }
    		


    Try to avoid unnecessary comments.


    \_(ツ)_/


    Friday, February 17, 2017 7:56 PM
    Moderator
  • You would be better off just returning the raw data and converting it in PowerShell.

    \_(ツ)_/

    Friday, February 17, 2017 7:58 PM
    Moderator