question

first you should fix the sql injection Security flaw in your code.

you should really use triggers rather than code to handle cascading deletes. but you can use the output clause to get the key fields

     var query = @" 
         delete orders 
         where prod_id =@pid and user_id=@uid 
         output deleted.Id 
     "; 
     
     using (var conn = new SqlConnection(strcon)) 
     { 
         conn.Open(); 
         var cmd = new SqlCommand(query, conn); 
         cmd.Parameters.AddWithValue("@pid", prodid); 
         cmd.Parameters.AddWithValue("@uid", user_id); 
         using (var reader = cmd.ExecuteReader()) 
         { 
             while(reader.HasRows) 
             { 
                 Console.WriteLine(reader.GetSqlInt32(0)); 
             } 
         } 
     } 
 }